Detailed explanation of the four transaction isolation levels in MySQL

The test environment of this experiment: Windows 10+cmd+MySQL5.6.36+InnoDB

1. Basic Elements of Transactions (ACID)

1. Atomicity: After a transaction starts, all operations must be completed or not done at all. It is impossible for the transaction to get stuck in the middle. If an error occurs during the execution of a transaction, it will be rolled back to the state before the transaction started, and all operations will be as if nothing had happened. In other words, affairs are an indivisible whole, just like the atoms we learned in chemistry, which are the basic units of matter.

2. Consistency: The integrity constraints of the database are not violated before and after the transaction starts and ends. For example, if A transfers money to B, it is impossible for A to deduct the money but B does not receive it.

3. Isolation: Only one transaction is allowed to request the same data at the same time, and there is no interference between different transactions. For example, if A is withdrawing money from a bank card, B cannot transfer money to this card before A completes the withdrawal process.

4. Durability: After the transaction is completed, all updates made by the transaction to the database will be saved to the database and cannot be rolled back.

Summary: Atomicity is the basis of transaction isolation. Isolation and persistence are means. The ultimate goal is to maintain data consistency.

2. Concurrency issues of transactions

1. Dirty read: Transaction A reads the data updated by transaction B, and then B rolls back the operation, so the data read by A is dirty data

2. Non-repeatable read: Transaction A reads the same data multiple times. During the process of transaction A reading multiple times, transaction B updates and commits the data, resulting in inconsistent results when transaction A reads the same data multiple times.

3. Phantom read: System administrator A changes the grades of all students in the database from specific scores to ABCDE grades, but system administrator B inserts a record with a specific score at this time. When system administrator A finishes the change, he finds that there is still a record that has not been changed, as if an illusion has occurred. This is called phantom read.

Summary: Non-repeatable reads and phantom reads are easily confused. Non-repeatable reads focus on modifications, while phantom reads focus on additions or deletions. To solve the problem of non-repeatable reads, you only need to lock the rows that meet the conditions. To solve the problem of phantom reads, you need to lock the table.

4. Use examples to illustrate the situation of each isolation level

1. Read uncommitted:

(1) Open a client A and set the current transaction mode to read uncommitted. Query the initial value of the account table:

(2) Before client A's transaction is committed, another client B is opened to update the account table:

(3) At this time, although client B's transaction has not been committed, client A can query the data that B has updated:

(4) Once the transaction of client B is rolled back for some reason, all operations will be undone, and the data queried by client A is actually dirty data:

(5) When client A executes the update statement update account set balance = balance - 50 where id = 1, Lilei's balance does not become 350, but 400. Isn't it strange? The data consistency is not a problem. If you think so, you are too naive. In the application, we will use 400-50=350 and do not know that other sessions have been rolled back. To solve this problem, you can use the read committed isolation level.

2. Read Submitted

(1) Open a client A and set the current transaction mode to read committed. Query the initial value of the account table:

(2) Before client A's transaction is committed, another client B is opened to update the account table:

(3) At this time, client B's transaction has not been committed yet, and client A cannot query the data that B has updated, thus solving the dirty read problem:

(4) Client B's transaction submission

(5) Client A executes the same query as in the previous step, but the result is inconsistent with that in the previous step, which means that a non-repeatable read problem occurs. In the application, suppose we are in the session of client A and find that the balance of lilei is 450, but other transactions change the balance value of lilei to 400. We do not know this. If we use the value of 450 to perform other operations, there will be problems. However, this probability is very small. To avoid this problem, we can use the repeatable read isolation level.

3. Repeatable Read

(1) Open a client A, set the current transaction mode to repeatable read, and query the initial value of the account table:

(2) Before client A's transaction is committed, another client B is opened, the account table is updated and committed. The transaction of client B can actually modify the rows queried by client A's transaction. In other words, MySQL's repeatable read will not lock the rows queried by the transaction. This is beyond my expectation. According to the SQL standard, when the transaction isolation level is repeatable read, read and write operations must lock rows. MySQL did not lock them. Oh my god. In the application, be sure to lock the row, otherwise you will use the balance of 400 in step (1) as the intermediate value to perform other operations.

(3) Execute the query in step (1) on client A:

(4) Execute step (1). Lilei's balance is still 400, which is consistent with the query result in step (1). There is no non-repeatable read problem. Then execute update balance = balance - 50 where id = 1. Balance does not become 400-50=350. Lilei's balance value is calculated using 350 in step (2), so it is 300. The data consistency is not destroyed. This is a bit magical. Perhaps it is a feature of MySQL.

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei | 400 |
| 2 | hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
rows in set (0.00 sec)

mysql> update account set balance = balance - 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei | 300 |
| 2 | hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
rows in set (0.00 sec)

(5) Start a transaction on client A and query the initial value of the account table

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

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei | 300 |
| 2 | hanmei | 16000 |
| 3 | lucy | 2400 |
+------+--------+---------+
rows in set (0.00 sec)

(6) Open a transaction on client B, add a new data entry with the balance field value of 600, and commit

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

mysql> insert into account values(4,'lily',600);
Query OK, 1 row affected (0.00 sec)

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

(7) When Client A calculates the sum of the balances, the value is 300+16000+2400=18700. The value of Client B is not taken into account. After Client A submits the request, the sum of the balances is calculated again and surprisingly becomes 19300. This is because the 600 from Client B is taken into account. From the perspective of the client, the client cannot see Client B and will think that it is a pie in the sky with 600 extra dollars. This is a phantom read. From the perspective of the developer, the data consistency is not destroyed. However, in the application, our code may submit 18700 to the user. If you must avoid this small probability situation, then you must adopt the transaction isolation level "serialization" introduced below.

mysql> select sum(balance) from account;
+--------------+
| sum(balance) |
+--------------+
| 18700 |
+--------------+
1 row in set (0.00 sec)

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

mysql> select sum(balance) from account;
+--------------+
| sum(balance) |
+--------------+
| 19300 |
+--------------+
1 row in set (0.00 sec)

4. Serialization

(1) Open a client A, set the current transaction mode to serializable, and query the initial value of the account table:

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from account;
+------+--------+---------+
| id | name | balance |
+------+--------+---------+
| 1 | lilei | 10000 |
| 2 | hanmei | 10000 |
| 3 | lucy | 10000 |
| 4 | lily | 10000 |
+------+--------+---------+
rows in set (0.00 sec)

(2) Open a client B and set the current transaction mode to serializable. Inserting a record results in an error. The table is locked and the insertion fails. When the transaction isolation level in MySQL is serializable, the table will be locked, so there will be no phantom reads. This isolation level has extremely low concurrency. Often, one transaction occupies a table, and thousands of other transactions can only watch helplessly. They have to wait for the transaction to finish and be committed before they can be used. This isolation level is rarely used in development.

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into account values(5,'tom',0);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Replenish:

1. The standards specified in the SQL specification may differ from database to database.

2. The default transaction isolation level in MySQL is repeatable read, which does not lock the read rows.

3. When the transaction isolation level is serialized, reading data will lock the entire table

4. When reading this article, if you stand in the perspective of a developer, you may feel that there is no logical problem with non-repeatable reads and phantom reads, and the data will still be consistent in the end. However, from the perspective of users, they can usually only see one transaction (they can only see client A and are unaware of the existence of client B, the undercover), and will not consider the phenomenon of concurrent transaction execution. Once the same data is read multiple times with different results, or new records appear out of thin air, they may have doubts. This is a user experience issue.

5. When a transaction is executed in MySQL, the final result will not have data consistency issues, because in a transaction, MySQL may not use the intermediate results of the previous operation when performing an operation. It will process it based on the actual situation of other concurrent transactions. It seems illogical, but it ensures data consistency. However, when a transaction is executed in an application, the result of an operation will be used by the next operation and other calculations will be performed. We need to be careful about this. We should lock rows during repeatable reads and lock tables during serialization, otherwise the consistency of the data will be destroyed.

6. When a transaction is executed in MySQL, MySQL will process it comprehensively based on the actual situation of each transaction, so that the consistency of the data is not destroyed. However, the application program plays according to the logical routine, which is not as smart as MySQL, so data consistency problems are inevitable.

7. The higher the isolation level, the more it can ensure data integrity and consistency, but the greater the impact on concurrency performance. You can't have your cake and eat it too. For most applications, it is recommended to set the isolation level of the database system to Read Committed, which can avoid dirty reads and has better concurrency performance. Although it may lead to concurrency problems such as non-repeatable reads and phantom reads, in individual cases where such problems may occur, the application can use pessimistic locking or optimistic locking to control it.

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.