In-depth study of MySQL multi-version concurrency control MVCC

MVCC

MVCC (Multi-Version Concurrency Control) is multi-version concurrency control. It is an important function of InnoDB to realize transaction concurrency and rollback. The lock mechanism can control concurrent operations, but its system overhead is large, and MVCC can replace row-level locks in most cases. Using MVCC can reduce its system overhead.

The specific implementation is to add three additional fields to each row of the database:

1. DB_TRX_ID : records the transaction ID of the last transaction that inserted or updated the row
2. DB_ROLL_PTR: Pointer to the undolog corresponding to the row change
3. DB_ROW_ID: Monotonically increasing ID, which is the primary key ID of AUTO_INCREMENT

Snapshot Read

For example, an unlocked select operation is a snapshot read. The emergence of snapshot read is based on the consideration of improving concurrent performance. The implementation of snapshot read is based on multi-version concurrency control, namely MVCC. MVCC can be considered a variant of row locks. In many cases, it avoids locking operations and reduces overhead. Since it is based on multiple versions, the snapshot read may not necessarily read the latest version of the data, but may be a previous historical version.

Current Reading

What is read is the current data, and there is no need to use undo log to trace back to the state before the transaction was started. What is read is the latest version of the record. When reading, it is also necessary to ensure that other concurrent transactions cannot modify the current record, and the read record will be locked.

There are three database concurrency scenarios:

• Read-read: No problems, no need for concurrency control
• Read-write: There are thread safety issues, which may cause transaction isolation issues, and may encounter dirty reads, phantom reads, and non-repeatable reads.
• Write-write: There are thread safety issues and there may be update loss issues, such as the first type of update loss and the second type of update loss

To put it simply, MVCC is to achieve read-write conflict without locking, and this read refers to snapshot read, not current read. Current read is actually a locking operation, which is the implementation of pessimistic lock.

The emergence of MVCC is that the big guys are not satisfied with using pessimistic locks to solve the read-write conflict problem, so there are two solutions:

• MVCC + Pessimistic Locking
  MVCC solves read-write conflicts, and pessimistic locking solves write-write conflicts
• MVCC + Optimistic Locking
  MVCC solves read-write conflicts, and optimistic locking solves write-write conflicts

MVCC Implementation Principle

Three hidden fields

• DB_TRX_ID
  6 bytes, last modified (modify/insert) transaction ID: records the transaction ID that created this record/last modified this record
• DB_ROLL_PTR
  7 bytes, rollback pointer, pointing to the previous version of this record (stored in the rollback segment)
• DB_ROW_ID
  6 bytes, implicit auto-increment ID (hidden primary key). If the data table does not have a primary key, InnoDB will automatically generate a clustered index with DB_ROW_ID

Version chain/undo log

Because the undo log will record the old version of the data before the transaction, and then the rollback pointer in the row record will point to the old version position, thus forming a version chain. Read View will continue to traverse the DB_TRX_ID in the linked list until it finds a DB_TRX_ID that meets certain conditions. Then the old record where the DB_TRX_ID is located is the latest "old version" that the current transaction can see.

Read View

It is a collection of all currently active transactions (transactions that have not been committed) when the transaction is opened. In other words, Read View is the read view generated when a transaction performs a snapshot read operation. At the moment when the snapshot read is executed by the transaction, a snapshot of the current database system will be generated, recording and maintaining the ID of the current active transaction in the system.

Three important Read View structures:

• trx_list (named by me at random)
  A list of values ​​used to maintain the list of transaction IDs that are active in the system at the time the Read View is generated
• up_limit_id
  The smallest transaction ID in the trx_list list
• low_limit_id

The next transaction ID that has not been assigned by the system at the time of ReadView generation, which is the maximum value of the transaction ID that has appeared so far + 1

Why low_limit? Because it is also the minimum value of the transaction ID that the system can allocate at this moment.

The overall process of MVCC implementation:

Summarize

• For high-concurrency transactions, MVCC is more efficient than simple locking
• MVCC only works under the two isolation levels of Read Committed and Repeatable Read.
• At the read committed isolation level, a Read View is generated for each snapshot read (query). Repeatable read generates a Read View only at the beginning of a transaction, and this Read View is used for each subsequent query to achieve different isolation levels.

refer to:

[MySQL Notes] Correctly understand MySQL's MVCC and implementation principles (recommended)

MySQL · Engine Features · InnoDB Transaction System (taobao.org)

Detailed explanation of mvcc - Jianshu (jianshu.com)

This concludes this article on in-depth study of MySQL multi-version concurrency control MVCC. I hope it will be helpful for everyone’s study, and I also hope that everyone will support 123WORDPRESS.COM.