MySQL’s RR isolation level is set to ReadView.

preface

Readviews are different from repeatable readviews and submitted readviews

ReadView

M_ids: a list of transaction ids representing active read/write transactions in the current system at the time the READVIEW was generated. Active is those transactions that have not been committed in the current system. Min_trx_id: indicates the minimum transaction ID of the active read/write transaction in the system when the READVIEW was generated, that is, the minimum value in m_IDS. Max_trx_id: specifies the transaction ID that should be allocated to the next transaction when the READVIEW is generated. Since transaction ids are allocated incrementally, max_trx_id is the largest ID in m_IDS plus 1. Creator_trx_id: indicates the transaction ID from which this READVIEW was generated. Since a transaction ID is assigned only when changes are made to the records in the table, the default transaction ID in a reading transaction is 0.

Read the rules

Trx_id == creator_trx_id in READVIEW, which indicates that the current reading transaction is reading the record modified by itself. This version can be accessed by the current transaction. Trx_id < min_trx_id indicates that the transaction that generated this version was committed before the current transaction generated READVIEW, so this version can be accessed by the current transaction. Trx_id > max_trx_id indicates that the transaction generating this version is started only after the current transaction generates READVIEW, and this version is not accessible by the current transaction. Trx_id is between READVIEW’s min_trx_id and max_trx_id. If the READVIEW is within this range, the transaction was active when the READVIEW was created and the version is not accessible by the current transaction. If not, the transaction that generated the version of the READVIEW when it was created has been committed and the version can be accessed by the current transaction.

The principle of

RC isolation level can be achieved based on ReadView mechanism, which generates a ReadView for each query, so that the data updated by other transactions can be seen as long as there are other transactions committed before the query (m_IDS list changes).

What about RR? At the RR level, a transaction reads a data, no matter how many times it reads the data, the value is the same. After other transactions modify the data, the value is not seen, even if the transaction commits the data. This avoids the problem of unrepeatable reads. At the same time, if other transactions insert some new data, it is also unreadable, so as to avoid the phantom read problem.

example

First, assume that there is A piece of data inserted by A transaction with transaction ID =50. At the same time, transaction A and transaction B are running at the same time. The ID of transaction A is 60, and the ID of transaction B is 70, as shown in the following figure:

Creator_trx_id = 60, min_trx_id = 60, max_trx_id = 71, m_IDS = [60, 70] Now the ReadView is as shown below:

When transaction A checks this data based on the ReadView, it will find that the trx_id of this data is 50, which is less than min_trx_id in ReadView, indicating that before it initiates the query, A transaction inserted this data and submitted it, so the original value can be checked at this time, as shown in the following figure:

Trx_id = 70, trx_id = 70, trx_id = 70, trx_id = 70, trx_id = 70

Txr_id =70 = trx_id =70 = trx_id =70 = trx_id =70 = trx_id =70 = trx_id =70 = trx_id =70 = trx_id =70 So transaction A reads the same data before and after transaction B modifies the data.