MySQL, as a multi-threaded database, supports concurrent queries from clients and sets its default isolation level to repeatable read. So how does MySQL isolate transactions in concurrent operations? It actually uses the MVCC mechanism to manage concurrent access and implements data isolation between different versions.

The main function of MVCC is to allow multiple transactions to read the same row of data at the same time without blocking each other. The data seen by each transaction is the data version at the beginning of its own transaction, even if other transactions may have modified it in the middle. This means that this mechanism can achieve a repeatable read isolation level.

Read View

In fact, it is not difficult to think that to achieve the above-mentioned effect, we only need to create a snapshot for all the data of the transaction at the beginning of the transaction, and read the data of this snapshot during the transaction execution to eliminate the interference of other transactions. This is actually the principle of Read View in MVCC. Read View is a data snapshot, but it is not simply a copy of all data.

• Read Committed Isolation Level is to generate a Read View before each SELECT statement is executed.
• Repeatable Read Isolation Level is to generate a Read View when the transaction executes the first SELECT, and then use this Read View during the entire transaction.

Just mentioned that Read View is not simply to save a backup of the data, it actually records all transaction details at the moment of creation, including four important fields:

• m_ids. Refers to the transaction id list of active transactions in the current database when the Read View is created.
• min_trx_id. Refers to the smallest transaction id in the active transaction when the Read View is created, that is, the minimum value in m_ids.
• max_trx_id. Refers to the id that the current database is going to assign to the next transaction when creating a Read View, that is, the current global maximum transaction id + 1.
• creator_trx_id. Refers to the transaction id of the transaction that creates the Read View.

Read View

InnoDB clustered index hidden columns

We have just finished talking about the snapshot of the transaction part. Now let's talk about another part of MVCC: hidden columns. As long as InnoDB, the default storage engine of MySQL, is used for data storage, its clustered index will contain the following two hidden columns:

• trx_id. Records the transaction id of the last change to this row of clustered index records.
• roll_pointer. Every time a clustered index is modified, the pointer to the old version of the data written in the undo log is written to this column. Therefore, this column will form a one-way chain, and each old version of the data can be found through this pointer.

InnoDB hidden column

Implementation principle

Now we already know how the Read View generated when the transaction starts records the transaction status, and how the InnoDB hidden column records the data change history. Now we just need to know how the transaction determines whether a row of data can be read by itself based on these two pieces of information during the running process. We can discuss it in three cases:

• trx_id > min_trx_id. This means that this version of the record has been submitted before the Read View is created, so the data of this version is visible to the current transaction.
• trx_id $\geq$ max_trx_id. This means that this version of the record is generated by a transaction that was started after the Read View was created, so it is not visible to the current transaction.
• trx_id $\in$ (min_trx_id, max_trx_id). At this time, it is necessary to determine whether trx_id is in the m_ids list:
  • If it is in the list, it means that the active transaction that generated the version record is still active, and the version data is not visible to the current transaction.
  • If it is not, it is visible.

MVCC version control is for rows. In the same row, the visibility permissions of all columns are consistent. There is no situation where some columns in a row are visible and some are not visible for a transaction.

Contributors

dingyuqi

Changelog

3/25/25, 1:41 AM

View All Changelog

• c2f26-improve(docs): adjust tagson 3/25/25
• 06e23-feat(docs): add a new english aritcleon 3/18/25

Copyright

Copyright Ownership:dingyuqi

License under:Attribution-NonCommercial-NoDerivatives 4.0 International (CC-BY-NC-ND-4.0)