On the consistent reading of transaction isolation in MySQL

Time:2020-6-4

preface

We have known the transaction isolation level of MySQL briefly before. We know that the most commonly used transaction isolation levels in MySQL are repeatable read (RR) and read commit (RC).
In RR level, the data read is consistent from transaction start to transaction commit. In this article, we will carefully analyze how consistent read is implemented and some inconsistent problems.

Snapshot (consistency view)

At the RR isolation level, when a transaction is started, a snapshot (consistency view) of the whole database data will be created for the current transaction operation. Each transaction has its own snapshot (consistency view), so as to ensure the data isolation and the operation between transactions will not affect each other.
It should be noted that the snapshot (consistency view) is not the data copy of the whole database, but based on the transaction ID (transaction_ ID) and row record ID (row TRX_ ID). In InnoDB, each new transaction will have a unique incremental transaction ID, and each row will have multiple modification record IDs. When the current transaction operates on this row, the transaction_ TRX with ID assigned to current line_ ID, and an older version of TRX_ The ID also needs to be saved for the transaction to query the row data.

Multi version

On the consistent reading of transaction isolation in MySQL
In InnoDB, multiple versions are guaranteed to be implemented by rolling back logs, which are U1, U2 and U3 in the figure above. The result of the latest operation in the figure above is v4. To get the value of V1, you need to calculate it step by step according to the value of V4 and undo log. That is to say, the data of V1, V2 and V3 are not physical, but are pushed forward according to the logs.

Current reading

Under RR, the data isolation mechanism is:
1. Not visible if not submitted;
2. Before the current transaction snapshot is created, the submitted
3. Committed is not visible after the current transaction snapshot is created
On the consistent reading of transaction isolation in MySQL
Assume that the initial value of K in the figure above is 1.
According to the above content, what content can a and b select respectively in the figure? (transaction C commits the transaction after updating, so transaction B will not block)
Transaction a creates the snapshot first, then transaction B creates the snapshot, and then executes transaction C.
For a: A, data acquisition is not affected by B and C, which is 1
When performing update for B: B, in order not to affect the update operation of the committed transaction on this row, it will read first and then modify. This read is the current read and the latest data is read. (according to row TRX_ ID), and then update the latest version of the current row, and then B goes to select, and reads the final data 3 updated by C and B

When updating data, it is read first and then write, and this read is the current read, read only the latest committed transaction value. Not only can update be read currently, but also whenSelect... Lock in share mode or select... For updateCan also read currently

Repeatable reading and read submission

The core of repeatable read is consistent read. When a transaction is updated, the current read is used. However, when a row of data is locked by an uncommitted transaction update, other transactions can only wait for the transaction commit lock to release before continuing.
In repeatable read, a data snapshot (consistency view) is established at the beginning of a transaction, and other operations of the transaction share one snapshot (consistency view).
Under read commit isolation, the data snapshot (consistency view) will be recalculated before each statement is executed. Therefore, as long as other transactions commit data updates after the transaction is started and before select, select can query the latest submitted data.

summary

In InnoDB, each row of data has its own row TRX_ ID version, each transaction or statement has its own data snapshot (consistency view). Under normal query, the data visible in the data snapshot is obtained according to the version ID.
So for repeatable reads, queries only acknowledge data that has been committed before the transaction starts.
For read commit, the query only acknowledges data that has been committed before the statement executes.