Implementation of MySQL Multi-version Concurrency Control MVCC

What is MVCC

MVCC stands for multi-version concurrency control.

MySQL's transactional storage engine improves concurrency performance through multi-version concurrency control (MVCC).

MVCC can be considered a variant of row-level locking, but it avoids locking operations in most cases and implements non-blocking read operations, so the overhead is lower.

MVCC is implemented by saving a snapshot of data at a certain point in time. The core idea is to save historical versions of data and implement database concurrency control by managing multiple versions of data rows.

In this way, we can decide whether to display the data by comparing the version number, and we can ensure the isolation effect of the transaction without locking when reading the data.

MVCC Implementation

In fact, InnoDB adds three hidden fields after each row of records:

• ROW_ID: Row ID, which increases monotonically as new rows are inserted. If there is a primary key, this column will not be included.
• TRX_ID: Records the transaction ID of the transaction that inserted or updated the row.
• ROLL_PTR: Rollback pointer, pointing to the undo log record. Each time a record is modified, a pointer is stored in the column, through which the information before the record was modified can be found. When a record is modified multiple times, there will be multiple versions of the row record, which are linked by ROLL_PTR to form a concept similar to a version chain.

Take RR level as an example:

Each time a transaction is opened, the system assigns a transaction ID to the transaction. When the first select statement is executed in the transaction, a transaction snapshot ReadView at the current time point is generated, which mainly includes the following properties:

• m_ids: indicates the transaction ID list of uncommitted read and write transactions in the current system when the ReadView is generated.
• min_trx_id: indicates the smallest transaction id among the uncommitted read/write transactions in the current system when the ReadView is generated, that is, 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 generating a ReadView.
• creator_trx_id: indicates the transaction id of the transaction when the ReadView is generated.

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:

• trx_id == creator_trx_id: can access this version.
• trx_id < min_trx_id : this version can be accessed.
• trx_id > max_trx_id: This version cannot be accessed.
• min_trx_id <= trx_id <= max_trx_id: If trx_id is in m_ids, this version cannot be accessed, otherwise it is available.

When making a judgment, the latest version of the record is first compared. If the version cannot be seen by the current transaction, the previous version is found through the ROLL_PTR of the record and compared again until a version that can be seen by the current transaction is found.
Deletion is actually a special update. InnoDB uses an additional flag bit, delete_bit, to indicate whether the data is deleted. When we make a judgment, we will check whether the delete_bit is marked. If it is, we will skip this version and get the next version through ROLL_PTR for judgment.

The above content is for the RR level. For the RC level, the whole process is almost the same. The only difference is the timing of generating ReadView. The RR level only generates it once at the beginning of the transaction, and then the ReadView is used all the time. At the RC level, a ReadView is generated every time a selection is made.

Does MVCC solve phantom reads?

Phantom read: The same SQL is used to read twice in a transaction, and the second read contains rows newly inserted by other transactions.
For example:

1) Transaction 1: First query: select * from user where id < 10 and the data with id = 1 is found.

2) Transaction 2 inserts data with id = 2

3) When transaction 1 uses the same statement to query for the second time, data with id = 1 and id = 2 are found, and phantom read occurs.

When talking about phantom reads, we first need to introduce the concepts of "current reads" and "snapshot reads".

• Snapshot read : Generate a transaction snapshot (ReadView), and then get data from this snapshot. Ordinary select statements are snapshot reads.
• Current Read : Read the latest version of the data. Common update/insert/delete, select ... for update, and select ... lock in share mode are all current reads.

For snapshot reads, MVCC will not see newly inserted rows because it reads from ReadView, so the problem of phantom reads is naturally solved.

However, MVCC cannot solve the phantom read of the current read. You need to use Gap Lock or Next-Key Lock (Gap Lock + Record Lock) to solve this problem.

In fact, the principle is very simple. Use the above example to modify it slightly to trigger the current read:

select * from user where id < 10 for update

When Gap Lock is used, the Gap lock locks the entire range of id < 10, so other transactions cannot insert data with id < 10, thus preventing phantom reads.

This is the end of this article about the implementation of MySQL multi-version concurrency control MVCC. For more relevant MySQL multi-version concurrency control MVCC content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!