How is the isolation level of mysql implemented?

Mysql isolation level implementation method: When the isolation level is uncommitted read, all reads are not locked, the data read are the latest data, the performance is the best, and all writes are row-level Lock, release after writing. When the isolation level is serialization, both reading and writing are locked.

Isolation level

(Recommended tutorial: mysql tutorial)

There are 4 isolation levels for database transactions. From low to high, they are Read uncommitted, Read committed, Repeatable read, and Serializable. Each level can solve the problems of dirty reads, non-repeatable reads, and phantom reads one by one.

Isolation level implementation:

Uncommitted read (RU: read-uncommitted):

In RU level , all data read by the transaction is the latest data, which may be data after the transaction is submitted, or data during transaction execution (which may be rolled back).

When the isolation level is RU:

• All reads are not locked, the data read are the latest data, and the performance is the best.

• All writes add row-level locks and are released after writing.

Read-committed (RC: read-committed):

Use MVCC technology to add hidden fields to each row (DB_TRX_ID: modify the The id of the last transaction of the row, DB_ROLL_PTR: pointing to the undo log of the current row, DB_ROW_ID: row identifier, DELETE_BIT: deletion flag), which implements non-locked read operations.

When the isolation level is RC:

• Write operation: add row-level lock. After the transaction starts, a modification record will be written in the UNDO log, and the hidden column DATA_POLL_PTR in the data row stores a pointer to the UNDO record of the row.

• Read operation: No locking. When reading, if the row is locked by other transactions, follow the hidden column DATA_POLL_PTR pointer to find the previous valid historical record (valid record: this record is visible to the current transaction, and DELETE_BIT=0).

Repeatable read (RR: repeatable-read):

Use MVCC technology to implement lock-free read operations.

When the isolation level is RR:

• Write operation: add row-level lock. After the transaction starts, a modification record will be written in the UNDO log, and the hidden column DATA_POLL_PTR in the data row stores a pointer to the UNDO record of the row.

• Read operation: No locking. When reading, if the row is locked by other transactions, follow the hidden column DATA_POLL_PTR pointer to find the previous valid historical record (valid record: this record is visible to the current transaction, and DELETE_BIT=0).

We can know from the above: In fact, the operations at the RC and RR levels are basically the same, but the difference lies in: the visibility of the row record for the current transaction (visibility: that is, which version row records are visible to this transaction). The RC level visibility of data is the latest record of the data, and the RR basic visibility of the data is the record of the data at the beginning of the transaction.

(1) Implementation of row record visibility (read_view)

In innodb, when creating a transaction, the list of active transactions in the current system will be Create a copy (read_view), which stores transactions that have not yet been committed at the beginning of the current transaction. The values ​​in these transactions are not visible to the current transaction.

There are two key values ​​​​up_limit_id in read_view (the minimum version number of the current uncommitted transaction -1, transactions before up_limit_id have been submitted, transactions after up_limit_id may be submitted, may not be submitted) and low_limit_id (The next transaction id that has not yet been assigned by the current system is the maximum value of 1 that has occurred so far. Note: low_limit_id is not the id of the largest active transaction.)

Note: Current transaction And the transaction being committed is not in read_view.

(2) Whether it is RC level or RR level, the logic for judging the visibility of row records is the same.

When the transaction reads the row record in undo, the version number (DB_TRX_ID) of the row record will be compared with read_view:

1. If DB_TRX_ID is less than up_limit_id, it means that the row The record has been committed before the current transaction starts, and DELETE_BIT=0, then the row record is visible to the current transaction.

2. If DB_TRX_ID is greater than low_limit_id, it means that the transaction in which the row is recorded was started after this transaction was created, so the current value of the row record is not visible.

3. If up_limit_id< = DB_TRX_ID <= low_limit_id, determine whether DB_TRX_ID is in the active transaction chain. If it is, it is invisible. If it is not, it is visible.

4. If the above judgments are all invisible, read the previous row record of the row in undo and continue the judgment.

The difference between RC-level statement-level snapshots and RR-level transaction-level snapshots is actually realized by the timing of read_view generation.

When executing a statement at the RC level, the original read_view will be closed first and a new read_view will be regenerated. The RR-level read_view is only created at the beginning of the transaction. Therefore, the RU level obtains the latest data every time, while the RR level obtains the data at the beginning of the transaction.

(3) It is worth noting: In the above visibility judgment, although the logic is the same, there are differences in practical meaning:

In In the second step, for the RC level, low_limit_id is the largest transaction ID 1 that has occurred when the statement is executed. It can be considered that when the statement is executed, there is no transaction larger than low_limit_id, so transactions larger than low_limit_id are invisible. of.

For the RR level, low_limit_id is the largest transaction 1 that has occurred at the beginning of the current transaction (it can also be considered as the id 1 of the current transaction, because when the current transaction is created, the id of the current transaction is the largest), Transactions larger than low_limit_id are created after the transaction starts, so they are not visible to the RR level.

In the third step, for the RC level, as long as DB_TRX_ID is not in the active linked list, RC is visible regardless of whether DB_TRX_ID is greater than the transaction id.

For the RR level, because low_limit_id is the current transaction ID 1, it can be considered that transactions smaller than low_limit_id occurred before the current transaction was created, so it only needs to be simply judged whether DB_TRX_ID is in the active linked list.

Serializable: both reading and writing are locked

The above is the detailed content of How is the isolation level of mysql implemented?. For more information, please follow other related articles on the PHP Chinese website!