Detailed introduction to the four transaction isolation levels in MySQL (picture and text)

This article mainly introduces the relevant information of MySQL's four transaction isolation levels in detail. It has certain reference value. Interested friends can refer to

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

1. Basic elements of transaction (ACID)

1. Atomicity: all operations after the transaction starts, Either do it all, or don’t do it at all. It’s impossible to get stuck in the middle. If an error occurs during transaction execution, it will be rolled back to the state before the transaction started, and all operations will be as if they did not happen. That is to say, affairs are an indivisible whole, just like atoms learned in chemistry, which are the basic units of matter.

2. Consistency: Before and after the transaction starts and ends, the integrity of the database constraints has not been destroyed. For example, if A transfers money to B, it is impossible for A to deduct the money but B not to receive it.

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

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

Summary: Atomicity is the basis of transaction isolation, isolation and durability are means, and the ultimate goal is to maintain data consistency.

2. Transaction concurrency issues

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

2. Non-repeatable reading: Transaction A reads the same data multiple times, and transaction B updates and submits the data during the multiple reads of transaction A, resulting in a transaction When A reads the same data multiple times, the results are inconsistent.

　3. Phantom reading: System administrator A changed the scores of all students in the database from specific scores to ABCDE grades, but system administrator B inserted a record of specific scores at this time. When the system administrator After the change was completed, member A found that there was still one record that had not been changed. It was as if he had hallucinated. This is called phantom reading.

Summary: Non-repeatable reading and phantom reading are easily confused. Non-repeatable reading focuses on modification, while phantom reading focuses on adding or deleting. To solve the problem of non-repeatable reading, you only need to lock the rows that meet the conditions. To solve the problem of phantom reading, you need to lock the table

##3. MySQL transaction isolation level

The default transaction isolation level of mysql is repeatable-read

##4. Use examples to illustrate each isolation level

1. Read uncommitted:

(1) Open a client A, and set the current

transaction mode

to read uncommitted (uncommitted read), query the table Initial value of account:

(2) Before client A’s transaction is committed, open another client B and update the table account:

(3) At this time, although client B’s transaction has not yet been submitted, client A can query B’s updated data:

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

(5) Execute the update statement update account set balance = balance - 50 where id =1 on client A. lilei's balance did not become 350, but actually 400. Isn't it strange? Data consistency No question,

If you think so, you are too naive. In the application, we will use 400-50=350, and we don’t know that other sessions have been rolled back. To solve this problem, you can read the submitted Isolation level

2. Read committed

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

# (2) Before the transaction of client A is submitted, open another client B and update the table account:

(3) At this time, client B’s transaction has not yet been submitted, and client A cannot query B’s updated data, which solves the dirty read problem:

(4) Client B’s transaction submission

(5) Client A executes the same query as in the previous step, and the result is The previous step is inconsistent, which creates a non-repeatable read problem. In the application, assuming we are in the session of client A, we query that lilei's balance is 450, but other transactions change lilei's balance value to 400. We don’t know that there will be a problem if we use the value 450 for other operations, but the probability is really 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 table account:

(2) Before client A's transaction is submitted, open another client B, update the table account and submit it. Client B's transaction can actually modify client A's transaction query The rows reached, that is, the repeatable read of mysql will not lock the rows queried by the transaction. This is beyond my expectation. When the transaction isolation level in the sql standard is repeatable read, the read and write operations must lock the rows. Mysql There was no lock, so I went out. Pay attention to locking rows in the application, otherwise you will use lilei's balance of 400 in step (1) as the intermediate value to do other operations

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

# (4) Execute step (1), lilei’s balance is still 400, which is consistent with the query result of step (1) , there is no non-repeatable read problem; then execute update balance = balance - 50 where id = 1, the balance does not become 400-50=350, lilei's balance value is calculated using 350 in step (2), So it is 300. The consistency of the data has not been destroyed. This is a bit magical. Maybe 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) Open the transaction on client A and query The initial value of table account

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) Start a transaction on client B, add a new piece of data, in which the balance field value is 600, and submit

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) Calculate the sum of balance on client A. The value is 300+16000+2400=18700. The value of client B is not included. It is calculated after client A submits it. The sum of the balance actually became 19300. This is because the 600 of client B was included in the calculation. From the customer's point of view, the customer cannot see client B, and it will feel that the world is losing. What a waste. 600 yuan, this is a phantom read. From the developer's perspective, the consistency of the data is not destroyed. But 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 table account:

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. When inserting a record, an error is reported. The table is locked and the insertion fails. The transaction isolation level in mysql is serializable. The table will be locked when the table is locked, so phantom reads will not occur. This isolation level has extremely low concurrency. Often one transaction occupies a table, and thousands of other transactions can only stare and wait until they are finished and submitted. It can be used and 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

Supplement:

1. The specific implementation of the standards stipulated in the SQL specification may be somewhat different in different databases

2. When the default transaction isolation level in mysql is repeatable read, the read rows will not be locked.

3. When the transaction isolation level is serialization, the read data will be locked. Live the entire table

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

5. When a transaction is executed in mysql, the final result will not have data consistency problems, because in a transaction, mysql does not necessarily use the intermediate results of the previous operation when performing an operation. It will use other The actual situation of concurrent transactions is used to deal with it. It seems illogical, but it ensures data consistency; but when the transaction is executed in the application, the result of one operation will be used by the next operation and other calculations will be performed. This is why we have to be careful. We should lock rows during repeatable reading and lock tables during serialization, otherwise the consistency of the data will be destroyed.

6. When transactions are executed in mysql, mysql will comprehensively process each transaction according to the actual situation, resulting in the consistency of the data being not destroyed. However, the application will follow logical routines, which is not as smart as mysql. , data consistency problems will inevitably arise.

7. The higher the isolation level, the more complete and consistent the data can be guaranteed, but the greater the impact on concurrency performance. You cannot have your cake and eat it too. For most applications, you can give priority to setting the isolation level of the database system to Read Committed, which can avoid dirty reads and has better concurrency performance. Although it will lead to concurrency problems such as non-repeatable reads and phantom reads, in individual situations where such problems may occur, the application can use pessimistic locks or optimistic locks to control them.

The above is the detailed content of Detailed introduction to the four transaction isolation levels in MySQL (picture and text). For more information, please follow other related articles on the PHP Chinese website!