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