Detailed explanation of Mysql transaction isolation level read commit

View MySQL transaction isolation level

mysql> show variables like '%isolation%';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

You can see that the current transaction isolation level is READ-COMMITTED

Let's take a look at the transaction isolation details under the current isolation level and open two query terminals A and B.

There is an order table below, the initial data is as follows

mysql> select * from `order`;
+----+--------+
| id | number |
+----+--------+
| 13 | 1 |
+----+--------+
1 row in set (0.00 sec)

The first step is to start transactions in both A and B.

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

The second step is to query the number values in the two terminals

 mysql> select * from `order`;
+----+--------+
| id | number |
+----+--------+
| 13 | 1 |
+----+--------+
1 row in set (0.00 sec)

 mysql> select * from `order`;
+----+--------+
| id | number |
+----+--------+
| 13 | 1 |
+----+--------+
1 row in set (0.00 sec)

The third step is to change the number in B to 2, but do not commit the transaction

mysql> update `order` set number=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Step 4: Query the value in A

mysql> select * from `order`;
+----+--------+
| id | number |
+----+--------+
| 13 | 1 |
+----+--------+
1 row in set (0.00 sec)

It is found that the value in A has not been modified.

Step 5: Submit transaction B and query the value in A again

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from `order`;
+----+--------+
| id | number |
+----+--------+
| 13 | 2 |
+----+--------+
1 row in set (0.00 sec)

It is found that the value in A has changed

Step 6. Commit the transaction in A and query the values of A and B again.

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from `order`;
+----+--------+
| id | number |
+----+--------+
| 13 | 2 |
+----+--------+
1 row in set (0.00 sec)

mysql> select * from `order`;
+----+--------+
| id | number |
+----+--------+
| 13 | 2 |
+----+--------+
1 row in set (0.00 sec)

It is found that the values in A and B are changed to 2.

Here is a simple schematic diagram

We can see that when the transaction isolation level is Read Committed, after the transaction in B is committed, the result of the B transaction commit can be read even if A is not committed. This solves the dirty read problem.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:

MySQL transaction isolation level details
In-depth understanding of Mysql transaction isolation level and locking mechanism issues
MySQL Series 10 MySQL Transaction Isolation to Implement Concurrency Control
Mysql transaction isolation level principle example analysis
In-depth understanding of the four isolation levels of MySQL transactions

<<: Analysis of uniapp entry-level nvue climbing pit record

>>: CentOS 8 Installation Guide for Zabbix 4.4