Instructions for using MySQL isolation Read View

Which historical version can the current transaction read?

Read View is a collection of all current transactions when the transaction is opened. This data structure stores the largest ID and the smallest ID in the current Read View.

This is the list of currently active transactions, as shown below:

ct-trx --> trx11 --> trx9 --> trx6 --> trx5 --> trx3;

ct-trx represents the id of the current transaction, and the corresponding read_view data structure is as follows:

read_view->creator_trx_id = ct-trx;
read_view->up_limit_id = trx3; low water level read_view->low_limit_id = trx11; high water level read_view->trx_ids = [trx11, trx9, trx6, trx5, trx3];

low_limit_id is the "high water mark", that is, the maximum id of the active transaction at that time. If you read the row with db_trx_id>=low_limit_id, it means that the data before these ids have not been committed. As described in the comments, these data are not visible.

if (trx_id >= view->low_limit_id) {
return(FALSE);
}

Note: readview part of the source code

up_limit_id is the "low water mark", that is, the minimum transaction id in the active transaction list at that time. If the db_trx_id of the row is less than up_limit_id, it means that these data have been committed when the transaction created the id. As described in the comments, these data are visible.

if (trx_id < view->up_limit_id) {
return(TRUE);
}

If the db_trx_id of the row is between low_limit_id and up_limit_id, check whether the db_trx_id of the record is in the read_view->trx_ids list of its own transaction. If so, the current version of the record is not visible; otherwise, the current version of the record is visible.

ReadView implementation methods at different isolation levels

1. read-commited:

That is, during each statement execution, read_view is closed and a new read_view is created in the row_search_for_mysql function. This will cause a non-repeatable read phenomenon.

2. Repeatable read:

At the repeatable read isolation level, when the transaction trx structure is created, the current global read view is generated. Created using the trx_assign_read_view function and maintained until the end of the transaction. During the period when the transaction ends, the Read View will not be rebuilt for each query, thus achieving repeatable read.

Supplement: MySQL's mvcc and readview

Isolation level standard definition:

read uncommited A transaction reads the uncommitted results of another transaction (which may be rolled back), which is called dirty read, which may cause dirty read, phantom read, and non-repeatable read problems

read committed A transaction a reads the latest committed result of another transaction b, making the results of transaction a read twice different. This avoids dirty reads, non-repeatable reads, and phantom reads, which is achieved through version chains (mvcc) and readview.

Repeatable read (MySQL's default isolation level) After a transaction reads a record for the first time, and another transaction modifies and commits the record, transaction A still reads the first value. This is repeatable read. The results returned by reading the same data multiple times in the same transaction are the same. A transaction will not read modifications made to existing data by other transactions, even if other transactions have been committed. In other words, the values ​​of the existing data read at the beginning of the transaction are the same at any time before the transaction is committed. However, the newly inserted data of other transactions can be read, which also causes the phantom read problem. It avoids dirty reads and non-repeatable read problems, but phantom reads still exist. Phantom reading will still occur (but MySQL solves the problem of phantom reading) through version chain and readview

Serializable does not allow concurrent reading and writing of the same row of records. It must be executed serially, so there will be no phantom reads or dirty reads.

In MySQL, read committed and repeatable read transaction isolation levels are implemented through mvcc and readview

MVCC stands for Multi-Version Concurrency Control, which maintains multiple versions of a data so that the reading and writing between transactions can obtain corresponding results according to the isolation level without conflicts.

For isolation level read committed, a new readview is generated each time

For the isolation level repeatable read, a readview is generated only when a transaction is started, and does not change until the transaction is committed, so repeatable read can be guaranteed.

When generating readview

m_ids: represents the list of active transaction ids

min_trx_id: minimum transaction id among active transactions

max_trx_id: The maximum transaction id created

creator_trx_id: current transaction id

For the current transaction, follow the following rules to traverse from the latest version and obtain the corresponding version record.

1. The accessed trx_id is the same as the creator_trx_id in readview, indicating that the current transaction is accessing the record modified by itself, which is visible and returned;

2. The accessed trx_id is less than min_trx_id, indicating that the version has been submitted and is visible, and is returned;

3. If the accessed trx_id is greater than or equal to max_trx_id, it means that the version has not been enabled when the readview is generated, so it is not visible and is returned;

4. If the accessed trx_id is between min_trx_id and max_trx_id, check whether it is in m_ids. If so, it means that when the readview is generated, the transaction of this version has not been committed and the version is not visible. If not, it means that when the readview is generated, the transaction of this version has been committed and is visible, and the result is returned.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.