MySQL's MVCC Multi-Version Controller in a single article!

InnoDB'sMVCC (Multi-Version Concurrency Control) MVCC is a mechanism for MySQL to implement highly concurrent transaction processing. With MVCC, InnoDB can support the following in a highly concurrent environmenttransaction isolationand provideNon-blocking read operationsMVCC allows transactions to read data without locking, ensuring performance and consistency.

I. MVCC Basic Concepts

The core idea of MVCC isBy saving multiple versions of the data to support concurrent reads and writes. Each transaction sees a particular version of a piece of data when it reads it, rather than the most current value. This mechanism relies on theUndo Log, supports multi-version reads by saving the historical version of each row of records. (undolog)

• read-write separation: The main feature of MVCC is that read data does not block write operations and write operations do not block read operations. Read-write separation is realized, which improves the concurrent processing capability of the database.
• Timestamp Versioning: Each transaction has a uniqueTransaction ID (Transaction ID, for short)trx_id）In addition, InnoDB uses transaction IDs to distinguish between different versions of data.

II. Two types of MVCC read operations

1. Snapshot Read: The read is a historical version of the data and is a normal read operation without locking. Snapshot reads are the basis of MVCC's implementation of concurrent reads.
   • for exampleSELECT * FROM table WHERE id = 1;, performs a snapshot read operation without locking.
2. Current Read: The read is the latest version of the data, and locks are applied to ensure data consistency and transaction isolation. The current read is usually accompanied by a write operation.
   • for exampleSELECT * FROM table WHERE id = 1 FOR UPDATE;, which performs the current read operation of the lock.

The working principle of MVCC

InnoDB is passed through theline version control to manage different versions of the data. Each row of data contains two hidden fields in InnoDB's row records:

• trx_id: Indicates the transaction ID of the last time the row was modified.
• roll_pointer: Points to the previous version of the row (i.e., the record in Undo Log), through which the historical version of the data can be found.

1. Commencement and termination of business

• Each transaction starts with a unique transaction ID.trx_id。
• When a transaction reads data, InnoDB determines which version of the data should be seen by the transaction based on the MVCC mechanism, which is largely dependent on the isolation level of the transaction (e.g.Read Committed maybeRepeatable Read）。

2. Data revision and versioning

When a transaction modifies data, InnoDB saves an older version of the current data to theUndo Log in the data row and update the data row with the latesttrx_id cap (a poem)roll_pointer. This way, even if the data is updated, other uncommitted transactions can still access the old version of the data through Undo Log.

Each row of data will have multiple versions of its history and will be passed through theroll_pointer Links to these historical versions.

3. Version chain

The version chain is based on thetrx_id cap (a poem)roll_pointer established, i.e. each time the data is modified, the old version is stored in the Undo Log and the row records point to the old version. The structure of the version chain is as follows:

Latest Records <-- trx_id_x --trx_id_y --trx_id_z... --> History

When a transaction reads data, it reads the data based on the transaction's isolation level, the current transaction'strx_id as well as the datatrx_id to determine which version should be read.

IV. Relationship between MVCC and Transaction Isolation Levels

InnoDB's MVCC mechanism behaves differently at different transaction isolation levels:

1. Read Committed(Read Committed): Transactions will only see the version that has been committed by other transactions, and each query will read the latest committed data. At this isolation level, there is a non-repeatable read problem between transactions.
   • On each query, InnoDB returns the latest version of the data that has been committed so far.
2. Repeatable Read(Repeatable reads, MySQL default isolation level): The transaction sees the same version of the snapshot throughout, and even if another transaction commits new data, the current transaction still sees the consistent snapshot it started with.
   • At the start of a transaction, InnoDB creates aConsistent view, all subsequent queries return the data in that view, ensuring repeatable reads.

Isolation Levels vs. MVCC Read Methods

• Read Committed: Each read is based on the latest version of the current commit that has been made, so snapshot reads are returning the latest data each time.
• Repeatable Read: Snapshot reads are always based on the version snapshot at the start of the transaction, so even if another transaction commits new data, the current transaction still sees the old data until it commits itself.

V. Implementation details of MVCC

1. Transaction ID and version control

InnoDB uses the transaction ID (trx_id) to distinguish modifications to the data by different transactions. Each modification records the transaction ID of the current transaction in the row'strx_id field. Using the transaction ID, InnoDB can determine whether the current transaction can see a particular version of a row of data.

2. Consistent view

Consistent Read View means that the current transaction can only see a snapshot of the data at the time the transaction was started, unless the transaction is using the current read. InnoDB determines which version of the data to read based on the transaction ID of the current transaction and the Consistent View.

• For snapshot reading: Determine which version of data the transaction is seeing based on the start timestamp of the transaction, and the commits of other transactions.
• For the current read: It must be the most recent data, and InnoDB locks the current transaction to prevent other transactions from modifying or reading it.

3. Undo Log and Rollback Segment

When a transaction modifies data, InnoDB stores the original version of the data in theUndo Log In the Undo Log, the historical version of the data is recorded. The history of the data version is recorded in the Undo Log via theroll_pointer, InnoDB can allow other transactions to read an older version of the data when concurrency occurs.

Practical application scenarios of MVCC

1. E-commerce system order inquiries

In an e-commerce system, when a user queries for an order in transaction A, the order data may be modified by other transactions or new records may be inserted. With MVCC, transaction A sees the same order data when querying, and even if other transactions insert new orders, transaction A still sees a stable view of the orders.

2. Account balance inquiries in the financial system

In the financial system, the user account balance query needs to ensure that each query to get the same result, even if the account balance in the background due to some transaction changes, the user can still see the consistent account information, to ensure the consistency of the user experience. MVCC in this scenario can ensure data consistency under high concurrency.

VII. Summary

• MVCC is an important mechanism for high concurrency transactions in InnoDB., which solves the concurrency performance problems associated with traditional database locking by maintaining multiple versions of data and unlocked snapshot reads.
• MVCC combines transaction isolation levels to ensure that dataconsistency cap (a poem)isolationThis allows MySQL to maintain high performance in highly concurrent scenarios.
• Through mechanisms such as Transaction ID, Undo Log, and Consistency View, InnoDB is able to efficiently manage data access between transactions to avoid common concurrent read and write problems such asfantasy reading、non-repeatable etc.