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

This article shows you how to analyze the underlying principles of MySQL multi-version concurrency control MVCC. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

1 problems encountered in transaction concurrency 1.1 dirty reading

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

1.2 non-repeatable

When the same record in a transaction is retrieved twice and the results are different, this phenomenon is called unrepeatable reading.

1.3 Phantom reading

When the same query condition of a transaction is queried twice (multiple times), the number of inconsistencies found is called illusory reading.

2 isolation level

We described above some problems that may be encountered during the execution of several concurrent transactions, which are also prioritized. Let's put them in order according to their severity:

Dirty reading > unrepeatable reading > phantom reading

According to the SQL standard, concurrent transactions can occur with different severity according to different isolation levels.

The specific situation is as follows:

READ UNCOMMITTED: read not submitted.

READ COMMITTED: submitted for reading.

REPEATABLE READ: can be read repeatedly.

SERIALIZABLE: serializable

According to the SQL standard, concurrent transactions can have different severity problems according to different isolation levels, as follows:

Under the READ UNCOMMITTED isolation level, dirty, unrepeatable and phantom reading problems may occur.

Under the READ COMMITTED isolation level, unrepeatable reading and phantom reading problems may occur, but dirty reading problems cannot occur.

Under the REPEATABLE READ isolation level, phantom reading problems may occur, but dirty and non-repeatable reading problems cannot occur.

All kinds of problems cannot occur under the SERIALIZABLE isolation level.

3 version chain

We know that for tables that use the InnoDB storage engine, its clustered index records contain two necessary hidden columns (row_id is not necessary, and the tables we create do not contain row_id columns when they have primary keys or non-NULL UNIQUE keys):

Trx_id: each time a transaction makes changes to a clustered index record, the transaction id of that transaction is assigned to the trx_id hidden column.

Roll_pointer: every time a change is made to a clustered index record, the old version is written to the undo log, and the hidden column acts as a pointer to find the pre-modified information of the record.

Assuming that the transaction in which the record is inserted is a record with an id of 80, the schematic diagram of the record is now shown below:

Suppose the next two transactions with id 100,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 = 'Zhang Fei' WHERE number = 1

Trx 200:

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

Every time a change is made to the record, a undo log is recorded, and each undo log also has a roll_pointer attribute (the undo log corresponding to the INSERT operation does not have this attribute because there is no earlier version of the record). These undo logs can be connected and linked into a linked list, so the situation is like the following figure:

After each update to the record, the old value will be put in a undo log. Even for an old version of the record, with the increase in the number of updates, all versions will be linked into a linked list by the roll_pointer attribute. We call this linked list as the version chain, and the header node of the version chain is the latest value of the current record. In addition, each version contains the corresponding transaction id when the version was generated. Therefore, the version chain of this record can be used to control the behavior of concurrent transactions accessing the same record, and this mechanism is called multi-version concurrency control (Mulit-Version Concurrency Control MVCC).

4 ReadView4.1 ReadView definition

InnoDB puts forward a concept of ReadView, which mainly contains four important contents:

(1) m_ids: represents the id list of active read and write transactions in the current system when the ReadView is generated.

(2) min_trx_id: represents the smallest transaction id of 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: represents the id value that should be assigned to the next transaction in the system when ReadView is generated. Max_trx_id is not the maximum value in m_ids, and the transaction id is allocated incrementally. For example, there are now three transactions in which id is 1 and id 2 is 3, and then transactions with 3 are committed. So when a new read transaction generates ReadView, the m_ids includes 1 and 2. The value of mingling trxroomid is 1 and the value of maxim trxroomid is 4.

(4) creator_trx_id: represents 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 following steps 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 its own modified record, 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 the version has been committed before the current transaction generates ReadView, so the 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 means that the transaction generating the version does not open until after the current transaction generates ReadView, so the version cannot be accessed by the current transaction.

(4) if the trx_id attribute value of the visited version is between min_trx_id and max_trx_id of ReadView (min_trx_id < trx_id < max_trx_id), you need to determine whether the trx_id attribute value is in the m_ids list. If so, the transaction that generated this version is still active when the ReadView is created, and the version cannot be accessed. If not, the transaction that generated that version when the ReadView was created has been committed and the version can be accessed.

(5) if a version of the data is not visible to the current transaction, then follow the version chain to find the next version of the data, continue to follow the above steps to determine visibility, and so on, until the last version in the version chain. If the last version is not visible, it means that the record is completely invisible to the transaction, and the query result does not include the record.

4.3 talk about isolation again

For transactions that use the READ UNCOMMITTED isolation level, since records modified by uncommitted transactions can be read, it is fine to read the latest version of the record directly.

For transactions that use 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 that they generate ReadView at different times.

4.3.1 READ COMMITTED (read submitted)

The read has been committed, and a ReadView is generated before each read.

Suppose you now have a transaction that uses the READ COMMITTED isolation level to begin execution:

Explain the query in detail:

# transactions using READ COMMITTED isolation level # Transaction 100,200 are not committed, and the resulting column name is Liu Bei SELECT name FROM t_people WHERE number = 1

The execution of this SELECET is as follows:

(1) when executing the SELECT statement, the content of the m_ids list of ReadView,ReadView will be [100200], and the content of min_trx_id is 100. the maxim trxlists id is 2011.creatorship trxlists id is 0.

(2) then select the visible records from the version chain. You can see from the figure that the content of the latest version of column name is Zhuge Liang, and the trx_id value of this version is 200. it is in the m_ids list, so it does not meet the visibility requirements. (if the trx_id attribute value of the accessed version is between min_trx_id and max_trx_id of ReadView, you need to determine whether the trx_id attribute value is in the m_ids list. If so, the transaction that generated this version is still active when the ReadView is created, and the version cannot be accessed. If not, the transaction that generated that version when the ReadView was created has been committed and the version can be accessed), skipping to the next version according to the roll_pointer.

(3) the content of Zhuge Liang's next version of column name is Zhao Yun. The trx_ id value of this version is also 200, which is also in the m_ids list, so it does not meet the requirements, so continue to skip to the next version.

(4) the content of Zhao Yun's next version of column name is Zhang Fei, and the trx_ id value of this version is also 100, which is also in the m_ids list, so it does not meet the requirements, so continue to skip to the next version.

(5) the content of Zhang Fei's next version of column name is Guan Yu, and the trx_ id value of this version is also 100. it is also in the m_ids list, so it does not meet the requirements, so continue to skip 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.

Can not be read repeatedly: 100transaction, 200transaction open read to name are all Liu Bei. When a transaction commits, because the committed transaction isolation level is read, a ReadView,200 transaction read is created for each read, and the generated ReadView m_ids is [200], and the name read according to the read rule is Zhang Fei.

# transactions using READ COMMITTED isolation level BEGIN;# SELECE1:Transaction 100,200 are not committed, resulting in a name value of Liu Bei SELECT name FROM t_people WHERE number = 1; # SELECE2:Transaction 100commit, Transaction 200uncommitted transaction query # Transaction 200transaction query, resulting in name value of Zhang Fei, which cannot be read repeatedly. SELECT name FROM teacher WHERE number = 1x 4.3.2 REPEATABLE READ (rereadable)

Rereadable, generating a ReadView the first time the data is read.

Solve the problem of non-repeatable reading: 100,200 transactions are enabled, the ReadView,m_ids is created as [100200], and the name is read as Liu Bei. When 100 transactions are committed, because it is a rereadable transaction isolation level, the ReadView,m_ids created only once is still [100200], and the name read according to the read rule is still Liu Bei.

5 Phantom reading

When the same query condition of a transaction is queried twice (multiple times), the number of inconsistencies found is called illusory reading.

Transaction T1 under the REPEATABLE READ isolation level reads multiple records according to some search criteria, then transaction T2 inserts a record that meets the corresponding search criteria and commits, and then transaction T1 executes the query according to the same search criteria. What will be the result? According to the comparison rule in ReadView, transaction T1 cannot see the commit of T2, regardless of whether transaction T2 is opened before transaction T1. However, under the REPEATABLE READ isolation level, MVCC in InnoDB can largely avoid phantom reading, rather than completely banning phantom reading.

# SELECT: snapshot reading. Update: currently read. REPEATABLE READ can solve the problem of snapshot reading. Can not solve the current problem of fantasy reading.

Case study:

1 execute begin, execute select *.

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

3 go back to the original window to execute the query

4 execute update and submit

5 find

As we can see from the above description, the so-called MVCC (Multi-Version ConcurrencyControl, multi-version concurrency control) refers to the process of accessing the recorded version chain when performing ordinary SELECT operations using two isolation-level transactions, READ COMMITTD and REPEATABLE READ, so that read-write and write-read operations of different transactions can be executed concurrently, thus improving system performance.

A big difference between the isolation levels of READ COMMITTD and REPEATABLE READ is that the timing of generating ReadView is different. READ COMMITTD generates a ReadView before each normal SELECT operation, while REPEATABLE READ only generates a ReadView before the first ordinary SELECT operation, and the subsequent query operations can reuse this ReadView, so that phantom reading can be basically avoided.

The above content is how to analyze the underlying principles of MySQL multi-version concurrency control MVCC. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.