Shulou(Shulou.com)06/01 Report--

This article will explain in detail the four transaction isolation levels of MySQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

The mysql tutorial column introduces four transaction isolation levels

1. The basic elements of a transaction (ACID)

1. Atomicity: after the transaction starts, either all operations are done or none are done, and it is impossible to stay in the middle. When an error occurs during the execution of the transaction, it is rolled back to the state before the start of the transaction, and all operations are as if they had not occurred. In other words, affairs are an indivisible whole, just like atoms learned in chemistry, are the basic units of matter.

2. Consistency: before and after the transaction starts, the integrity constraints of the database are not broken. 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 a time, and there is no interference between different transactions. For example, An is withdrawing money from a bank card, and B cannot transfer money to this card until the withdrawal process of An is over.

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.

Second, the concurrency of transactions

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

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

3, illusory reading: system administrator A changes the scores of all students in the database from specific scores to ABCDE grades, but system administrator B inserts a record of specific scores at this time. When system administrator A changes to the end, he finds that there is a record that has not been changed, as if there were hallucinations, which is called phantom reading.

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

III. MySQL transaction isolation level

Transaction isolation level dirty read non-repeatable read uncommitted (read-uncommitted) is non-repeatable read (read-committed) whether it is repeatable read (repeatable-read) whether it is serialization (serializable) No

The default transaction isolation level for mysql is repeatable-read

Fourth, use examples to illustrate the situation of each isolation level.

1. Read unsubmitted:

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

(2) before the transaction of client A commits, open another client B and update the table account:

(3) at this time, although the transaction of client B has not been committed, client A can query the updated data of B:

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

(5) execute the update statement update account set balance = balance-50 where id = 1 on client A. The balance of Lilei is not 350, but 400. Isn't it strange that the data are inconsistent? it would be naive if you think so. In the application, we will use 400-50 sessions 350, not knowing that other sessions have been rolled back. To solve this problem, you can read the submitted isolation level.

2. Read submitted

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

(2) before the transaction of client A commits, open another client B and update the table account:

(3) at this time, the transaction of client B has not been committed, and client A cannot query the updated data of B, which solves the problem of dirty reading:

(4) transaction submission of client B

(5) client An executes the same query as the previous step, and the result is inconsistent with the previous step, that is, the problem of non-repeatable reading occurs.

3. Repeatable

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

(2) before the transaction of client A commits, open another client B, update table account and commit

(3) query all records of table account in client A, and the query result is the same as that of step (1), and there is no problem of non-repeatable reading.

(4) in client A, then execute update balance = balance-50 where id = 1 the balance did not change to 400-50 balance. 350 of the balance value in step (2) is calculated, so it is 300, and the consistency of the data is not broken. The MVCC mechanism is used under the repeatable isolation level, and the select operation does not update the version number, which is a snapshot read (historical version); insert, update, and delete update the version number, which is the current read (current version).

(5) reopen client B, insert a new piece of data and submit it

(6) query all records of table account in client A, 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 the table account:

(2) Open a client B, and set the current transaction mode to serializable, insert a record and report an error, and the table is locked and failed to insert. When the transaction isolation level in mysql is serializable, the table will be locked, so there will be no illusory reading. The concurrency of this isolation level is very low, and it is rarely used in development.

Add:

1. When the transaction isolation level is read commit, the write data will only lock the corresponding rows.

2. When the transaction isolation level is repeatable, if the retrieval condition has an index (including the primary key index), the default locking method is next-key lock; if the retrieval condition does not have an index, the entire table will be locked when updating the data. A gap is locked by a transaction, and other transactions cannot insert records in this gap, which prevents false reading.

3. When the transaction isolation level is serialized, both read and write data will lock the entire table

4. The higher the isolation level, the better the integrity and consistency of the data, but the greater the impact on concurrent performance.

This is the end of the four transaction isolation levels of MySQL. I hope the above content can be of some help and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.