What is a MVCC

MVCC is multi-version concurrency control.

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

You can think of MVCC as a variant of row-level locking, but it avoids locking in most cases while implementing non-blocking reads, and therefore has lower overhead.

MVCC is realized by saving a snapshot of data at a certain point in time. The core idea is to save the historical version of the data, and realize the concurrency control of the database through multiple version management of the data row.

In this way, we can compare the version number to determine whether the data is displayed or not, and we can read the data without locking the transaction isolation effect.

The realization of the MVCC

In fact, InnoDB adds three hidden fields to each row:

ROW_ID: row ID that increases monotonically as a new row is inserted and will not be included if there is a primary key.

TRX_ID: Records the transaction ID of the transaction that inserted or updated the row.

ROLL_PTR: rollback pointer to undo log records. Each time a record is changed, the column stores a pointer that can be used to find the information before the record was changed. When a record is modified multiple times, there are multiple versions of that record, using the ROLL_PTR link to form a version chain concept.

Take the RR level as an example:

Each time a transaction is started, the system assigns a transaction Id to the transaction. When the transaction executes the first SELECT statement, the system generates a transaction snapshot ReadView at the current point in time, which contains the following attributes:

M_ids: indicates the list of transaction ids of uncommitted read/write transactions in the current system when ReadView is generated.

Min_trx_id: indicates the minimum transaction ID of 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 in the system that should be assigned to the next transaction when the ReadView is generated.

Creator_trx_id: indicates the id of the transaction when ReadView is generated.

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

  1. Trx_id == creator_trx_id: this version can be accessed.

  2. Trx_id < min_trx_id: This version is accessible.

  3. Trx_id > max_trx_id: this version is not accessible.

  4. Min_trx_id <= trx_id <= max_trx_id: if trx_id is in m_IDS, this version cannot be accessed and is available instead.

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 record ROLL_PTR and is compared again until a version that can be seen by the current transaction is found.

For deletions, which are special updates, InnoDB uses an extra flag bit delete_bit to indicate whether to delete. When making a determination, we check if the lower delete_bit is marked, if so, we skip that version and use ROLL_PTR to get the next version to determine.

This is for the RR level, but for the RC level, the whole process is almost the same. The only difference is when the ReadView is generated. The ReadView is generated only once at the start of the transaction and is used all the time. At the RC level, a ReadView is generated for each select.

Does MVCC solve illusions?

Phantom read: Two reads in one transaction using the same SQL, and the second read reads a row newly inserted by another transaction.

Such as:

Select * from user where id < 10 where id = 1

2) Transaction 2 inserts data with id = 2

3) When transaction 1 uses the same statement for the second query, it finds data with id = 1 and ID = 2, and phantom read occurs.

When it comes to phantom reads, we first introduce the concepts of “current reads” and “snapshot reads.”

Snapshot read: Generates a transaction snapshot (ReadView) from which data is retrieved later. A normal SELECT statement is a snapshot read.

Current read: Reads the latest version of data. Update /insert/delete, select… For update, select… Lock in share mode is currently read.

In the case of snapshot reads, the MVCC will not necessarily see the newly inserted rows because it reads from ReadView, so it naturally solves the illusion problem.

MVCC cannot solve the illusion of the current read. Need to use Gap Lock or next-key Lock (Gap Lock + Record Lock) to solve.

In fact, the principle is very simple, with the above example slightly modified to trigger the current read:

select * from user where id < 10 for update
Copy the code

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

If you find it helpful, please give it a thumbs-up.