Read commit of Mysql transaction isolation level

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 read commit

Next, take A look at the details of transaction isolation under the current isolation level, and open two query terminals A and B.

Here is an order table. The initial data is as follows

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

Step 1: open transactions in a and B

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

Step 2: query the number values in two terminals

• A

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

• B

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

Step 3 change 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 was found that the value in A was not modified.

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

• B

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

• A

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

Found that the value in A has changed

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

• A

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)

• B

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

It was found that the values in a and B were changed to 2.

Here is a simple diagram

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

Original address