MySQL’s four transaction isolation levels

mysql tutorialThe column introduces four transaction isolation levels

Recommended (free): mysql tutorial

The test environment for this article’s experiment: Windows 10 cmd MySQL5.6.36 InnoDB

1. Basic elements of transactions (ACID)

　1. Atomicity: After the transaction starts, all operations are either completed or not. When doing something, it is impossible to stagnate 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 constraints of the database are not 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.

2. Transaction concurrency issues

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

2. Non-repeatable reading: transaction A reads the same data multiple times, and transaction B reads the same data multiple times in transaction A , the data was updated and submitted, resulting in inconsistent results when transaction A read the same data multiple times.

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 specific score at this time record. When system administrator A completed the modification, he found that there was still one record that had not been modified. 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

##Read-uncommittedYesYesYesNon-repeatable read (read-committed)NoYesYesRepeatable-readNoNoYesSerializableNoNoNo

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 initial value of the table account:

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

　　　　　　　　　　　　　　　　　　　　　　　The transaction has not yet been submitted, but client A can query the updated data of B:

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

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 luck rid us] The data queried by client A is actually dirty data: set balance = balance - 50 where id =1, lilei's balance did not become 350, but actually 400. Isn't it strange? The data is inconsistent. If you think so, you are too naive. In the application, we will use 400 -50=350, I don’t know that other sessions have been rolled back. To solve this problem, you can use the read-committed isolation level

　2. Read-committed

　　(1) Open a client A, and set the current transaction mode to read committed (read committed), query all records of the account table:

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

　 (3) At this time, the transaction of client B has not yet been completed Submit, client A cannot query the updated data of B, solving the dirty read problem:

　 (4) Client B’s transaction submission

　(5) Client A executes the same query as the previous step, but the result is inconsistent with the previous step, which causes a non-repeatable read problem

3. Repeatable read

​ (1) Open a client A, set the current transaction mode to repeatable read, and query all records of the account table

　　　　　　　　　　　　　　　　　　　　　　All records of the account are consistent with the query results in step (1), and there is no non-repeatable read problem

##　 (4) On client A, then execute update balance = balance - 50 where id = 1, the balance does not become 400-50=350, lilei's balance value is calculated using the 350 in step (2), so it is 300, and the consistency of the data is not destroyed. The MVCC mechanism is used under the repeatable read isolation level. The select operation does not update the version number, but is a snapshot read (historical version); insert, update, and delete will update the version number and is a current read (current version).

(5) Re-open client B, insert a new data and submit

(6) In the client End A queries all the records in the account table, and no new data is found, so there is no phantom reading

##　4. Serialization

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

　(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. When the transaction isolation level in mysql is serializable, the table will be locked, so phantom reads will not occur. In this case, this isolation level has extremely low concurrency and is rarely used in development.

　Supplement:

　1. When the transaction isolation level is read-commit, writing data will only lock the corresponding row

　2. When the transaction isolation level is repeatable read, if the search condition has an index (including the primary key index), the default locking method is next-key lock; if the search condition There is no index, and the entire table will be locked when updating data. A gap is locked by a transaction, and other transactions cannot insert records in this gap, thus preventing phantom reads.

　3. When the transaction isolation level is serialization, reading and writing data will lock the entire table

　4. The higher the isolation level, the more complete and consistent the data can be guaranteed, but the greater the impact on concurrency performance.

5. MYSQL MVCC implementation mechanism reference link: https://blog.csdn.net/whoamiyang/article/details/51901888

6. Regarding the next-key lock, please refer to the link: https://blog.csdn.net/bigtree_3721/article/details/73731377

Transaction isolation level	Dirty read	Non-repeatable read	Phantom read

The above is the detailed content of MySQL’s four transaction isolation levels. For more information, please follow other related articles on the PHP Chinese website!