Author: Zhu Qinglin
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.
note:
Only the first time a record is actually modified during the execution of a transaction (such as using INSERT, DELETE, UPDATE statements) is a single transaction ID assigned, which is incremented
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 based on these linked lists to achieve the four isolation levels at the transaction level, as described belowReadView.
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
References:
How does MySQL work
