The database has four separate isolation levels to address problems that can occur when transactions in the database access data in parallel.

The isolation level in MySQL database can be divided into four layers, namely read uncommitted, read committed, repeatable read and serialization. The corresponding problems are dirty reading, unreal reading and unrepeatable reading.

Isolation level

Read uncommitted

Before a transaction commits, its changes can be seen by other transactions.

Read commit (read commited)

After a transaction commits, its changes are seen by other transactions

Repeatable read

The data seen during the execution of a transaction is always the same as the data seen when the transaction is started. At this isolation level, uncommitted transactions are also invisible to other transactions.

Serialization (serializable)

For the same row, write adds write lock and Read adds read lock. When a read-write lock conflict occurs, the last accessed transaction must wait for the previous transaction to complete before execution can continue. This isolation level ensures data correctness, but frequent locking can have a serious impact on performance.

Problems corresponding to different transaction isolation levels

Dirty read

At the “read uncommitted” level, uncommitted changes are seen by other transactions, and to other transactions, the uncommitted changes are dirty data, which is called dirty reads.

Unrepeatable read

Under the “read committed” level, only after the transaction is committed, other affairs to see affairs changes, but when A transaction in the execution of A query A value many times, the query for the first time value is v1, then the transaction B the value changed, when the transaction A query again, you will find this value is different, Unrepeatable read is the result of two different queries for the same transaction.

Phantom read

“Phantom read” is similar to “unrepeatable read”. Transaction A queries A value and inserts it if it does not exist. When transaction A finds that it does not exist, transaction B inserts the value, and when transaction A inserts it again, it will find that there is A value that did not exist in the previous query. The solution to phantom reading is to set the transaction isolation level to serialization, which means that data is locked regardless of whether it is read or written.

Use the following procedure to show the difference

• If the isolation level is “read uncommitted”, the value of v1 is 2. Transaction B has not committed yet, but the result is already seen by TRANSACTION A. So v2 and v3 are both 2.
• If the isolation level is read Commit, v1 is 1 and v2 has a value of 2. Transaction B’s updates are not seen by A until they are committed. So v3 is also going to be 2.
• If the isolation level is Repeatable, v1, v2 are 1, and V3 are 2. At the repeatable read level this requirement is followed: the data seen by the transaction during execution must be consistent before and after.
• If the isolation level is serialized, it will be locked while transaction B performs “change 1 to 2”. Transaction B can not continue until transaction A commits. So from A’s point of view, v1, v2 is 1,v3 is 2.

In the isolation level implementation of a transaction, a view is created in the database and accessed based on the logical result of the view.

• At the Repeatable Read isolation level, this view is created when the transaction is started and is used throughout the transaction execution;
• At the “read Commit” level, this view is created at the start of each SQL statement;
• If it is at the “read uncommitted” level, the latest value on the record is returned directly, with no view concept; At the serialization level, locking is used to avoid parallel access.

Public account: no dream aqiao background reply “group chat”, study together, progress together