MVCC

MVCC is multi-version concurrency control (Multi-Version Concurrency Control, MVCC for short), which is a way of implementing isolation level based on optimistic locking theory in MySQL, which is used to realize the implementation of committed read and repeatable read isolation level, also often referred to as a multiversion database.The MVCC mechanism will generate a consistent data snapshot (Snapshot) at the point in time of the data request, and use this snapshot to provide consistent reading at a certain level (statement level or transaction level).From the user's point of view, it appears that the database can provide multiple versions of the same data (system version number and transaction version number).

In MVCC multi-version concurrency control, read operations can be divided into two categories:
1. snapshot read
Read is the visible version of the record without locking.such as select

2. current read
The latest version of the record is read, and the record returned by the current read.Such as insert, delete, update, select...lock in sharemode/for update

MVCC: Each row of records actually has multiple versions. In addition to the data itself, each version of the record adds other fields
DB_TRX_ID: records the current transaction ID
DB_ROLL_PTR: points to the data on the undo log logpointer

Committed Read: A snapshot (Read View) is regenerated each time a statement is executed, and each time a query is selected.
Repeatable read: At the beginning of the same transaction, a global snapshot (Read View) of the current transaction is generated, when the query is selected for the first time.

Principles for reading snapshot content:
1. The version has not been submitted and cannot be read to generate a snapshot
2. The version has been submitted, but if it is submitted after the snapshot is created, it cannot be read
3. The version has been submitted, but it is submitted before the snapshot is created, you can read it
4. Your own update in the current transaction can be read

undo log

undo log: The rollback log, which saves a version of the data before the transaction occurs, is used for rollback operations during transaction execution, and is also a key technology for implementing read operations under multi-version concurrency control (MVCC).

DB_TRX_ID: Transaction ID
DB_ROLL_PTR: Rollback Pointer

Illustration: In this figure, the first transaction id is 1000, which uses (DB_ROLL_PTR) to save the address of the previous transaction operation