Analysis of the underlying principle of MySQL multi-version concurrency control MVCC

1 Problems encountered in transaction concurrency

1.1 Dirty Read

When a transaction reads data that has been modified but not committed by another transaction, it is called a dirty read.

1.2 Non-repeatable read

When the same record is retrieved twice within a transaction and the results obtained twice are different, this phenomenon is called a non-repeatable read.

1.3 Phantom Reads

When a transaction queries twice (or multiple times) with the same query condition and the number of entries found is inconsistent, it is called phantom read.

2 Isolation Levels

We have introduced several problems that may be encountered during the execution of concurrent transactions. These problems also have different priorities. We rank these problems according to their severity:

Dirty read > Non-repeatable read > Phantom read

The SQL standard stipulates that for different isolation levels, concurrent transactions can have different degrees of severity.

The specific situation is as follows:

• READ UNCOMMITTED: Read uncommitted.
• READ COMMITTED: Read committed.
• REPEATABLE READ: Repeatable read.
• SERIALIZABLE: Serializable

The SQL standard stipulates that for different isolation levels, concurrent transactions can have problems of varying severity, as follows:

• At the READ UNCOMMITTED isolation level, dirty reads, non-repeatable reads, and phantom reads may occur.
• At the READ COMMITTED isolation level, non-repeatable reads and phantom reads may occur, but dirty reads cannot occur.
• At the REPEATABLE READ isolation level, phantom reads may occur, but dirty reads and non-repeatable reads cannot occur.
• At the SERIALIZABLE isolation level, various problems cannot occur.

3 Version Chain

We know that for a table using the InnoDB storage engine, its clustered index record contains two necessary hidden columns (row_id is not necessary, and the row_id column will not be included when the table we create has a primary key or a non-NULL UNIQUE key):

trx_id : Every time a transaction modifies a clustered index record, the transaction id of the transaction is assigned to the trx_id hidden column.

roll_pointer : Every time a clustered index record is modified, the old version is written to the undo log, and this hidden column acts as a pointer that can be used to find the information before the record is modified.

Assuming that the transaction ID of the record inserted is 80, the schematic diagram of the record at this moment is as follows:

Assume that two transactions with transaction IDs 100 and 200 perform UPDATE operations on this record. The operation flow is as follows:

TRX 100:

UPDATE t_people SET name = 'Guan Yu' WHERE number = 1;
UPDATE t_people SET name = '张飞' WHERE number = 1;

TRX 200:

UPDATE t_people SET name = 'Zhao Yun' WHERE number = 1;
UPDATE t_people SET name = 'Zhuge Liang' WHERE number = 1;

Every time a record is modified, an undo log is recorded. Each undo log also has a roll_pointer attribute (the undo log corresponding to the INSERT operation does not have this attribute because the record has no earlier version). These undo logs can be connected to form a linked list, so the current situation is like the following figure:

After each update to the record, the old value will be put into an undo log, even if it is an old version of the record. As the number of updates increases, all versions will be connected into a linked list by the roll_pointer attribute. We call this linked list a version chain, and the head node of the version chain is the latest value of the current record. In addition, each version also contains the transaction ID corresponding to the generation of the version. The version chain of this record can then be used to control the behavior of concurrent transactions accessing the same record. This mechanism is called多版本并發控制(Mulit-Version Concurrency Control MVCC) .

4 ReadView

4.1 ReadView Definition

InnoDB proposes a concept of ReadView, which mainly includes four important contents:

• (1) m_ids: represents the transaction id list of the active read and write transactions in the current system when the ReadView is generated.
• (2) min_trx_id: indicates the smallest transaction id among the active read and write transactions in the current system when the ReadView is generated, that is, the minimum value in m_ids.
• (3) max_trx_id: indicates the id value that should be assigned to the next transaction in the system when generating a ReadView. max_trx_id is not the maximum value in m_ids. Transaction ids are assigned incrementally. For example, there are three transactions with id 1, 2, and 3, and then the transaction with id 3 is committed. Then when a new read transaction generates ReadView, m_ids includes 1 and 2, the value of min_trx_id is 1, and the value of max_trx_id is 4.
• (4) creator_trx_id: indicates the transaction id of the transaction that generated the ReadView.

4.2 Access Control

With this ReadView, when accessing a record, you only need to follow the steps below to determine whether a version of the record is visible:

• (1) If the trx_id attribute value of the accessed version is the same as the creator_trx_id value in ReadView, it means that the current transaction is accessing the record that it has modified, so the version can be accessed by the current transaction.
• (2) If the trx_id attribute value of the accessed version is less than the min_trx_id value in ReadView, it indicates that the transaction that generated this version has been committed before the current transaction generated ReadView, so this version can be accessed by the current transaction.
• (3) If the trx_id attribute value of the accessed version is greater than or equal to the max_trx_id value in ReadView, it indicates that the transaction that generated this version was opened after the current transaction generated ReadView, so this version cannot be accessed by the current transaction.
• (4) If the trx_id attribute value of the accessed version is between the min_trx_id and max_trx_id of the ReadView (min_trx_id < trx_id < max_trx_id), it is necessary to determine whether the trx_id attribute value is in the m_ids list. If it is, it means that the transaction that generated the version when the ReadView was created is still active and the version cannot be accessed. If it is not, it means that the transaction that generated the version when the ReadView was created has been committed and the version can be accessed.
• (5) If a certain version of data is not visible to the current transaction, then follow the version chain to find the next version of data and continue to determine visibility according to the above steps, and so on, until the last version in the version chain. If the last version is also not visible, it means that the record is completely invisible to the transaction and the query result does not include the record.

4.3 Revisiting Isolation

For transactions using the READ UNCOMMITTED isolation level, since you can read records modified by uncommitted transactions, you can just read the latest version of the records directly.

For transactions using the SERIALIZABLE isolation level, InnoDB uses locking to access records.

In MySQL, a very big difference between READ COMMITTED and REPEATABLE READ isolation levels is the timing of their generation of ReadView.

4.3.1 READ COMMITTED

Read Submitted, a ReadView is generated before each data read.

Suppose now a transaction using the READ COMMITTED isolation level starts executing:

Detailed query:

#Transaction using READ COMMITTED isolation level #Transaction 100 and 200 are not committed, and the value of the column name is Liu BeiSELECT name FROM t_people WHERE number = 1;

The execution process of this SELECET is as follows:

• (1) When executing the SELECT statement, a ReadView is generated first. The content of the m_ids list of the ReadView is [100, 200], min_trx_id is 100, max_trx_id is 201, and creator_trx_id is 0.
• (2) Then select the visible records from the version chain. As can be seen from the figure, the content of the name column of the latest version is Zhuge Liang. The trx_id value of this version is 200, which is in the m_ids list, so it does not meet the visibility requirement. (If the trx_id attribute value of the accessed version is between the min_trx_id and max_trx_id of the ReadView, it is necessary to determine whether the trx_id attribute value is in the m_ids list. If it is, it means that the transaction that generated the version when the ReadView was created is still active and the version cannot be accessed; if it is not, it means that the transaction that generated the version when the ReadView was created has been committed and the version can be accessed), jump to the next version according to the roll_pointer.
• (3) The content of the name column of the next version of Zhuge Liang is Zhao Yun. The trx_id value of this version is also 200, which is also in the m_ids list. Therefore, it does not meet the requirements and continues to jump to the next version.
• (4) The content of the name column of the next version of Zhao Yun is Zhang Fei. The trx_id value of this version is also 100, which is also in the m_ids list. Therefore, it does not meet the requirements and continues to jump to the next version.
• (5) The content of the name column of the next version of Zhang Fei is Guan Yu. The trx_id value of this version is also 100, which is also in the m_ids list. Therefore, it does not meet the requirements and continues to jump to the next version.
• (6) The next version of Guan Yu is Liu Bei. The trx_id value of this version is 80, which is less than the min_trx_id value in ReadView, so this version meets the requirements.

Non-repeatable read: When transactions 100 and 200 are started, the name read is Liu Bei. When transaction 100 is committed, a ReadView is created for each read because the transaction isolation level is read committed. When transaction 200 is read, the generated ReadView m_ids is [200]. At this time, the name read according to the read rule is Zhang Fei.

# Use transaction BEGIN with READ COMMITTED isolation level;
# SELECE1: Transaction 100 and 200 are not submitted, and the name value is Liu Bei SELECT name FROM t_people WHERE number = 1; 

# SELECE2: Transaction 100 is committed, Transaction 200 is not committed #Transaction 200 transaction query, the name value is Zhang Fei, and a non-repeatable read occurs.
SELECT name FROM teacher WHERE number = 1;

4.3.2 REPEATABLE READ

Re-readable, generates a ReadView when reading data for the first time.

Solve the problem of non-repeatable read: 100 transactions and 200 transactions are enabled, and ReadView is created. m_ids is [100,200], and the name read is Liu Bei. When transaction 100 is committed, due to the re-read transaction isolation level, only one ReadView is created, and m_ids is still [100,200]. At this time, the name read according to the read rule is still Liu Bei.

5 Phantom Reads

When a transaction queries twice (or multiple times) with the same query condition and the number of entries found is inconsistent, it is called phantom read.

Transaction T1 at the REPEATABLE READ isolation level first reads multiple records based on a search condition, then transaction T2 inserts a record that meets the corresponding search condition and commits it, and then transaction T1 executes a query based on the same search condition. What will be the result? According to the comparison rules in ReadView, regardless of whether transaction T2 is started before transaction T1, transaction T1 cannot see the commit of T2. However, MVCC in InnoDB under the REPEATABLE READ isolation level can largely avoid phantom reads instead of completely prohibiting phantom reads.

#SELECT: Snapshot read. update: currently read.
REPEATABLE READ can solve the snapshot read phantom read problem. The current phantom read problem cannot be solved.

Examples:

1 Execute begin and execute select *.

2 Open another window to execute insert, select, and commit.

3 Return to the original window to execute the query

4 Execute update and submit

5 Search

6 Conclusion

From the above description, we can see that the so-called MVCC (Multi-Version Concurrency Control) refers to the process of accessing the version chain of records when executing ordinary SELECT operations using transactions at the two isolation levels of READ COMMITTD and REPEATABLE READ. This allows read-write and write-read operations of different transactions to be executed concurrently, thereby improving system performance.

A big difference between the two isolation levels READ COMMITTD and REPEATABLE READ is the timing of generating ReadView. READ COMMITTD generates a ReadView before each normal SELECT operation, while REPEATABLE READ only generates a ReadView before the first normal SELECT operation, and subsequent query operations reuse this ReadView, which can basically avoid phantom reads.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.