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 databaseconstraintshas 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 ordeleting. 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

1. Read uncommitted:

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

to read uncommitted (uncommitted read),query thetable 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,

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)

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)

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)

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)

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)

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:

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!