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

How does mysql achieve the isolation level? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Isolation level

A transaction specifies an isolation level that defines the extent to which a transaction must be isolated from resource or data changes made by other transactions. The isolation level is described in terms of allowed concurrent side effects, such as dirty or phantom reads.

Control content:

Whether the lock is occupied when reading data and the type of lock requested.

The time taken to read the lock.

Whether a read operation that references a row modified by another transaction:

Blocks other transactions before the exclusive lock on the line is released.

Retrieves the committed version of the row that existed when the statement or transaction was started.

Read uncommitted data modifications.

Implementation of the isolation level:

Unsubmitted read (RU:read-uncommitted):

At the RU level, all data read by the transaction is up-to-date, either after the transaction is committed or in the execution of the transaction (which may be rolled back).

When the isolation level is RU:

All the reads are unlocked, the data read are the latest data, and the performance is the best.

All write plus line-level locks, write and release.

Submit read (RC:read-committed):

Using MVCC technology, add hidden fields to each line (DB_TRX_ID: id,DB_ROLL_PTR of the last transaction that modifies the row: undo log log pointing to the current line, DB_ROW_ID: line identification, DELETE_BIT: delete flag), which implements unlocked read operations.

When the isolation level is RC:

Write operation: add row-level locks. After the transaction starts, the modification record is written to the UNDO log, and the hidden column DATA_POLL_PTR in the data row stores a pointer to the UNDO record for that row.

Read operation: unlocked. When reading, if the row is locked by another transaction, the last valid history record is found by following the hidden column DATA_POLL_PTR pointer (valid record: the record is visible to the current transaction and DELETE_BIT=0).

Repeatable (RR:repeatable-read):

MVCC technology is used to achieve unlocked read operations.

When the isolation level is RR:

Write operation: add row-level locks. After the transaction starts, the modification record is written to the UNDO log, and the hidden column DATA_POLL_PTR in the data row stores a pointer to the UNDO record for that row.

Read operation: unlocked. When reading, if the row is locked by another transaction, the last valid history record is found by following the hidden column DATA_POLL_PTR pointer (valid record: the record is visible to the current transaction and DELETE_BIT=0).

As you can see from the above, the operations at the RC and RR levels are basically the same, but the difference is the visibility of the row record to the current transaction (visibility: that is, which version of the row record is visible to the transaction). The visibility of the RC level to the data is the latest record of the data, and the basic visibility of the RR to the data is the record of the data at the beginning of the transaction.

(1) realization of read_view of row records

In innodb, when a transaction is created, a read_view of the active transaction list in the current system is created, which stores transactions that are not yet commit at the beginning of the current transaction, and the values in these transactions are not visible to the current transaction.

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

Note: the current transaction and the transaction being commit are not in the read_view.

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

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

1. If DB_TRX_ID is less than up_limit_id, the row record is 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, the row record was started after the transaction was created, so the current value of the row record is not visible.

3. If up_limit_id < = DB_TRX_ID

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.