Analyzing the four transaction isolation levels in MySQL through examples

Analyzing the four transaction isolation levels in MySQL through examples

Preface

In database operations, in order to effectively ensure the correctness of concurrent data reading, a transaction isolation level is proposed. There are 4 isolation levels for database transactions. I won’t go into details below. Let’s take a look at the detailed introduction.

There are four isolation levels for database transactions:

  • Read Uncommitted: Dirty reads are allowed, which means that data modified by uncommitted transactions in other sessions may be read.
  • Read Committed: Only committed data can be read. This level is the default for most databases, such as Oracle.
  • Repeatable Read: Repeatable read. All queries within the same transaction are consistent at the start of the transaction, the default level for InnoDB. In the SQL standard, this isolation level eliminates non-repeatable reads, but phantom reads still exist.
  • Serializable: A completely serialized read. Each read requires obtaining a table-level shared lock, and both reading and writing will block each other.

Friends who are new to the concept of transaction isolation may be confused by the textbook definition above. Below we will explain the four isolation levels through specific examples.

First we create a user table:

CREATE TABLE user (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE `uniq_name` USING BTREE (name)
) ENGINE=`InnoDB` AUTO_INCREMENT=10 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Read Uncommitted Isolation Level

We first set the transaction isolation level to read committed:

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED |
+------------------------+
1 row in set (0.00 sec)

Below we open two terminals to simulate transaction one and transaction two respectively. ps: Operation one and operation two mean to be executed in chronological order.

Transaction 1

mysql> start transaction; # Operation 1
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(name) values('ziwenxie'); # Operation 3
Query OK, 1 row affected (0.05 sec)

Transaction 2

mysql> start transaction; # Operation 2
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # Operation 4
+----+----------+
| id | name |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)

It can be clearly seen from the above execution results that at the read uncommited level, we may read data in transaction one that is not committed in transaction two. This is a dirty read.

Read Committed Isolation Level

The above dirty read problem can be solved by setting the isolation level to committed.

mysql> set session transaction isolation level read committed;

Transaction 1

mysql> start transaction; # Operation 1 Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # Operation three+----+----------+
| id | name |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from user; # Operation 5. The modification of operation 4 does not affect transaction 1+----+----------+
| id | name |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from user; # Operation seven+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)
mysql> commit; # Operation 8 Query OK, 0 rows affected (0.00 sec)

Transaction 2

mysql> start transaction; # Operation 2 Query OK, 0 rows affected (0.00 sec)
mysql> update user set name='lisi' where id=10; # Operation 4 Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit; # Operation 6 Query OK, 0 rows affected (0.08 sec)

Although the dirty read problem is solved, please note that in operation 7 of transaction 1, after operation 6 of transaction 2 is committed, the data read twice by transaction 1 in the same transaction will be different. This is the non-repeatable read problem. Using the third transaction isolation level repeatable read can solve this problem.

Repeatable read isolation level

The default transaction isolation level of MySQL's Innodb storage engine is the repeatable read isolation level, so we don't need to make any extra settings.

Transaction 1

mysql> start tansactoin; # Operation 1mysql> select * from user; # Operation 5+----+----------+
| id | name |
+----+----------+
| 10 | ziwenxie |
+----+----------+
1 row in set (0.00 sec)
mysql> commit; # Operation 6 Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # Operation seven+----+------+
| id | name |
+----+------+
| 10 | lisi |
+----+------+
1 row in set (0.00 sec)

Transaction 2

mysql> start tansactoin; # Operation 2mysql> update user set name='lisi' where id=10; # Operation 3Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit; # Operation 4

In operation 5 of transaction 1, we did not read the update of transaction 2 in operation 3. We can only read the updated data after commit.

Does Innodb solve phantom reads?

In fact, phantom reads may occur at the RR level. The InnoDB engine officially claims that this problem is solved by using MVCC multi-version concurrency control. Let's verify whether Innodb really solves phantom reads.

For the convenience of display, I modified the user table above:

mysql> alter table user add salary int(11);
Query OK, 0 rows affected (0.51 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> delete from user;
Query OK, 1 rows affected (0.07 sec)
mysql> insert into user(name, salary) value('ziwenxie', 88888888);
Query OK, 1 row affected (0.07 sec)
mysql> select * from user;
+----+----------+----------+
| id | name | salary |
+----+----------+----------+
| 10 | ziwenxie | 88888888 |
+----+----------+----------+
1 row in set (0.00 sec)

Transaction 1

mysql> start transaction; # Operation 1 Query OK, 0 rows affected (0.00 sec)
mysql> update user set salary='4444'; # Operation six actually affected two rows. Didn't it solve the phantom read?
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from user; # Operation 7, Innodb does not completely solve the phantom read +----+----------+--------+
| id | name | salary |
+----+----------+--------+
| 10 | ziwenxie | 4444 |
| 11 | zhangsan | 4444 |
+----+----------+--------+
2 rows in set (0.00 sec)
mysql> commit; # Operation 8 Query OK, 0 rows affected (0.04 sec)

Transaction 2

mysql> start transaction; # Operation 2 Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(name, salary) value('zhangsan', '666666'); # Operation 4 Query OK, 1 row affected (0.00 sec)
mysql> commit; # Operation 5 Query OK, 0 rows affected (0.04 sec)

From the above example, we can see that Innodb does not solve phantom reads as officially claimed, but the above scenario is not very common and there is no need to worry too much.

Serializable Isolation Level

All transactions are executed serially, at the highest isolation level, and phantom reads will not occur. The performance will be very poor and is rarely used in actual development.

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in:
  • In-depth understanding of the four isolation levels of MySQL
  • Detailed explanation of the four transaction isolation levels in MySQL
  • MySQL detailed explanation of isolation level operation process (cmd)
  • Mysql transaction isolation level principle example analysis
  • Detailed explanation of the implementation principle of transaction isolation level in MySQL
  • MySQL isolation level detailed explanation and examples

<<:  How to quickly build a static website on Alibaba Cloud

>>:  How to modify create-react-app's configuration without using eject

Recommend

A small problem about null values ​​in MySQL

Today, when testing the null value, I found a sma...

MySQL date and time addition and subtraction sample code

Table of contents 1.MySQL adds or subtracts a tim...

Handwritten Vue2.0 data hijacking example

Table of contents 1: Build webpack 2. Data hijack...

Three ways to communicate between Docker containers

We all know that Docker containers are isolated f...

A summary of the reasons why Mysql does not use date field index

Table of contents background explore Summarize ba...

XHTML tutorial, a brief introduction to the basics of XHTML

<br />This article will briefly introduce yo...

Font references and transition effects outside the system

Copy code The code is as follows: <span style=...

Using jQuery to implement the carousel effect

This article shares the specific code for impleme...

JavaScript object-oriented implementation of magnifying glass case

This article shares the specific code of JavaScri...

Summary of knowledge points about events module in Node.js

Through the study and application of Node, we kno...

Synchronize the full and incremental data of a specific MySQL table to the message queue - Solution

Table of contents 1. Original demand 2. Solution ...

MySQL 5.7.25 installation and configuration method graphic tutorial

There are two types of MySQL installation files, ...

Media query combined with rem layout in CSS3 to adapt to mobile screens

CSS3 syntax: (1rem = 100px for a 750px design) @m...

Detailed explanation of vue3 cache page keep-alive and unified routing processing

Table of contents 1. Introduction 2. Use 1. Diffe...

How to reset Zabbix password (one-step)

Problem Description Since we don't log in to ...