Understanding MySQL InnoDB MVCC: Principles, Implementation, and Concurrency Control

1. Introduction

Before analyzing MVCC, we briefly review MySQL basics and introduce MVCC. (Assume InnoDB engine.)

1.1 Database concurrency scenarios

Three concurrency scenarios: read‑read (no conflict), read‑write (possible dirty, non‑repeatable, phantom reads), write‑write (possible update loss).

1.2 What is MVCC

MVCC stands for Multi‑Version Concurrency Control, a method to allow concurrent access in DBMS and transactional memory.

Multi‑Version Control uses undo logs to keep historical versions, allowing non‑blocking reads while writes acquire locks only on write‑write conflicts.

In short, MVCC in InnoDB improves concurrency by providing non‑locking snapshot reads.

1.3 Current read and snapshot read

Current read uses locking SELECT … LOCK IN SHARE MODE or SELECT … FOR UPDATE, reading the latest version and preventing other transactions from modifying the row.

Snapshot read is a non‑locking SELECT that reads a historical version determined by the transaction’s isolation level and the underlying MVCC mechanism.

1.4 Relationship between current read, snapshot read and MVCC

MVCC implements snapshot reads using three implicit fields, undo logs, and a Read View.

1.5 Problems solved by MVCC

MVCC provides lock‑free concurrency, eliminating read‑write conflicts and solving dirty reads, non‑repeatable reads, and phantom reads (but not update loss).

1.6 Combining MVCC with other locking strategies

MVCC + pessimistic lock (handles write‑write conflicts).

MVCC + optimistic lock (handles write‑write conflicts).

2. MVCC implementation details

2.1 Implicit fields

DB_TRX_ID (6 bytes): transaction ID that created or last modified the row.

DB_ROLL_PTR (7 bytes): pointer to the previous version in the rollback segment.

DB_ROW_ID (6 bytes): hidden auto‑increment primary key if the table lacks a primary key.

Delete flag: marks a row as deleted without physically removing it.

2.2 Undo log

Insert undo log: created for INSERT, visible only to the inserting transaction and removed after commit.

Update undo log: created for UPDATE/DELETE, retained until a purge thread removes it after all relevant transactions finish.

2.3 Read View

A snapshot read generates a Read View that records the IDs of currently active transactions (trx_list) and two limits: up_limit_id (minimum active ID) and low_limit_id (next transaction ID).

Visibility is determined by comparing a row’s DB_TRX_ID against these limits and the active list.

2.4 Overall MVCC workflow

When a transaction performs a snapshot read, InnoDB creates a Read View based on the current active transactions.

The engine uses the Read View to decide which version of a row is visible, traversing the undo‑log chain if necessary.

Example scenarios illustrate how multiple transactions modify the same row, how undo logs form a version chain, and how the Read View selects the appropriate version.

2.5 Difference between RC and RR snapshot reads

In REPEATABLE READ (RR) the first snapshot read creates a Read View that is reused for the rest of the transaction, preventing non‑repeatable reads. In READ COMMITTED (RC) a new Read View is generated for each snapshot read, allowing visibility of committed changes from other transactions.

Conclusion

The article covered MySQL’s implicit fields, undo logs, Read View, and the MVCC implementation process, giving developers a clear understanding of database concurrency and isolation levels.

References

MySQL InnoDB MVCC Implementation