You know that MySQL transactions are implemented based on the MVCC version chain, but MySQL is a black box for us, and we don’t know much about the underlying implementation. This article mainly introduces the implementation principle of MVCC of InnoDB engine in MySQL, from shallow to deep lead everyone to understand MySQL from the root

InnoDB row format

In InnoDB’s storage engine, records are stored in rows, which means that pages are rows of data. The way records are stored on disk is called row format or record format. Up to now, four different row formats have been designed, namely Compact, Redundant, Dynamic and Compressed. This article is a brief introduction to the Compact row format (other row formats are similar and will not be covered for now). You can modify and view the line format with the following command

CREATE TABLE TABLE ROW_FORMAT= ROW_FORMAT; ALTER TABLE TABLE TABLE ROW_FORMAT= ROW_FORMAT SELECT * FROM 'table_name' WHERE 'table_name';
COMPACT row format



The figure above is a schematic of the structure in the Compact row format, where transactions (MVCC) are associated with the contents of hidden columns

Variable-length field length list

MySQL supports variable-length field types such as VARCHAR, TEXT, BLOB, etc. The number of bytes stored in variable-length fields is not fixed, so we need to store the number of bytes used by the data when we store the real data.

A null value list

Some columns in the table may store NULL values, which would take up a lot of space if they were stored in the actual data of the record, so the Compact row format manages NULL columns and stores them in a NULL list

Record header

Hidden columns
The name of the describe
row_id Column ID (this column hides the primary key if the table does not specify it)
trx_id Transaction id
roll_pointer Rolls back the pointer to an undo log

The four isolation levels in the SQL standard

  • Read UNCOMMITTED: Read UNCOMMITTED.
  • Read Committed: Read COMMITTED.
  • Repeatable READ: Repeatable READ.
  • SERIALIZABLE: Serializable.
Transaction isolation level Dirty read Non-repeatable read Phantom read
READ UNCOMMITTED is is is
READ COMMITTED no is is
REPEATABLE READ no no is
REPEATABLE READ no no no

MVCC principle

Version of the chain

There is a hidden column (ROW_ID, TRX_ID, ROLL_POINTER) in the row format, where ROW_ID is not required.

  • Trx_id: Each time a transaction makes a change to a cluster index record, the transaction ID of that transaction is assigned to the trx_id hidden column.
  • ROLL_POINTER: Each time a cluster index record is changed, the old version is written to the undo log. The hidden column is then used as a pointer to find information about the record before it was changed.

Select * from hero where hero = ‘hero’;

Assuming that the transaction ID that inserted the record is 80, the schematic of the record at this point is shown below

After that, two transactions with ID of 100 and 200 UPDATE this record. The operation procedure is as follows:

Transaction trx_id 100 Transaction trx_id 200
begin
begin
UPDATE hero set name=” hero”
UPDATE hero set name=” hero”
commit
UPDATE hero set name=” zhaoyun”
UPDATE hero set name=” hero”
commit

The version chain at this point is shown in the figure below. You can see that the changes to the records form a linked list, in which each node records the current recordTransaction id(trx_id), MVCC is also one of the four isolation levels at the transaction level that are implemented based on these linked lists.

ReadView

For transactions with the READ Uncommitted isolation level, since records that were modified by UNCOMMITTED transactions can be READ, it is good to READ the most recent version of the record. For transactions that use the Serializable isolation level, specify the use of locks to access records. For transactions with READ COMMITTED and REPEATABLE READ isolation levels, it is necessary to READ the record modified by the COMMITTED transaction. In other words, if the record modified by another transaction has not been COMMITTED, it cannot READ the latest version of the record directly. The core problem is: You need to determine which version in the version chain is visible to the current transaction. For this reason, MySQL designed the concept of ReadView. There are four important properties in ReadView:

  • M_ids: Represents the list of transaction IDs of the read and write transactions that were active in the system at the time ReadView was generated.
  • MIN_TRX_ID: Represents the minimum transaction ID of the active read and write transactions currently in the system at the time ReadView was generated, i.e., the minimum value in m_ids.
  • MAX_TRX_ID: Indicates the ID value that should be assigned to the next transaction in the system when the ReadView is generated.
  • Creator_trx_id: Represents the transaction ID of the transaction that generated this ReadView.

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

  • 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 records, so the version can be accessed by the current transaction.
  • If the trx_id attribute value of the accessed version is less than the min_trx_id value in the ReadView, this indicates that the transaction that generated the version was committed before the current transaction generated the ReadView, so the version can be accessed by the current transaction.
  • If the trx_id attribute value of the accessed version is greater than or equal to the max_trx_id value in ReadView, this indicates that the transaction generating this version was started after the current transaction generating ReadView, so this version cannot be accessed by the current transaction.
  • If the trx_id value of the accessed version is between min_trx_id of the ReadView and max_trx_id of the ReadView, then it is necessary to determine if the trx_id value is in the m_ids list. If the trx_id value is in, then the transaction that generated this version was active when the ReadView was created. This version cannot be accessed; If not, the transaction that generated the version when the ReadView was created has been committed and the version can be accessed.

Based on the above ReadView rules, what is the difference between Read Committed and Repeatable Read?

  • READ COMMITTEDGenerate a ReadView before each read
  • READ COMMITTEDGenerates a ReadView the first time data is read