Multi-Version Concurrency Control (MVCC): How Non-Blocking Snapshot Reads Work
The mechanics of version chains, roll pointers, read views, and zero-lock reader performance.
Part 9 in Series — Catch up on the previous article: Two-Phase Locking (2PL) & Deadlock Resolution: Shared vs Exclusive Lock Mechanics (Part 8) before diving into this post.
It is 9:00 AM on Monday morning. Your financial analytics application triggers a monthly sales report query:
SELECT SUM(total_amount), COUNT(*)
FROM ledger_entries
WHERE entry_date >= '2026-08-01';
This single query takes 45 seconds to scan through 12,000,000 rows.
In an engine relying exclusively on Two-Phase Locking (2PL), that query would acquire Shared (S) locks on millions of rows. For 45 solid seconds, every online payment service trying to execute an UPDATE ledger_entries would freeze, blocked by exclusive lock conflicts.
Your entire payment gateway would grind to a halt because of a single background reporting job.
Modern production databases avoid this lock contention using Multi-Version Concurrency Control (MVCC).
MVCC enforces a fundamental operational principle: Readers never block writers, and writers never block readers.
The Hidden System Fields in Every Row
To implement MVCC, storage engines like MySQL InnoDB augment every table record on disk with hidden system headers:
+------------------+-------------------+----------------+----------------------+
| User Columns... | DB_TRX_ID (6B) | DB_ROLL_PTR(7B)| DB_ROW_ID (6B) |
+------------------+-------------------+----------------+----------------------+
| id, name, balance| Transaction ID | Undo Log Pointer| Auto-increment Row ID|
+------------------+-------------------+----------------+----------------------+
DB_TRX_ID(6 bytes): The transaction identifier of the last transaction that inserted or modified this specific record.DB_ROLL_PTR(7 bytes): A memory/disk pointer pointing to the corresponding Undo Log Segment. This undo log contains the historical delta payload required to revert the record to its prior state.DB_ROW_ID(6 bytes): A monotonically increasing row identifier assigned automatically if the table lacks an explicit primary key.
How Version Chains Are Constructed
When a transaction updates a row, InnoDB does not overwrite historical column values in-place on the primary page without trace. Instead, it performs three distinct steps:
- It copies the original row values into an Undo Log Page.
- It updates the column values on the primary data page.
- It sets
DB_TRX_IDto the current transaction ID, and setsDB_ROLL_PTRto point directly to the newly written Undo Log record.
Repeated updates build a linked list of historical record snapshots called a Version Chain:
[Clustered Index Page Record]
name: "Alice", balance: 500
DB_TRX_ID: 300
DB_ROLL_PTR --------------------+
|
v
[Undo Log Record (Trx 200)]
name: "Alice", balance: 350
DB_ROLL_PTR --------------------+
|
v
[Undo Log Record (Trx 100)]
name: "Alice", balance: 200
DB_ROLL_PTR: NULL
The version chain flows backwards through time, from the most recent row image in RAM down to older versions preserved in the Undo Log space.
Read Views: Point-In-Time Visibility Rules
How does a reading transaction decide which version in a row’s Version Chain it is permitted to see?
When a transaction performs a non-blocking snapshot read (SELECT), the engine constructs a memory structure called a Read View.
A Read View captures four critical state variables:
m_ids: A list of all transaction IDs that are currently active (uncommitted) at the exact moment the Read View is created.min_trx_id: The smallest transaction ID inm_ids. Any transaction created beforemin_trx_idhas already committed.max_trx_id: The next transaction ID to be assigned by the system (highest activetrx_id+ 1). Any transaction created at or aftermax_trx_idstarted after this Read View was generated.m_creator_trx_id: The transaction ID of the thread that generated this Read View.
The Visibility Algorithm
When a reader evaluates a row version with ID trx_id, it evaluates the following visibility rules:
Is trx_id < min_trx_id?
|
+--------------+--------------+
| YES | NO
v v
[ Row VISIBLE ] Is trx_id >= max_trx_id?
(Committed before ReadView) |
+--------------+--------------+
| YES | NO
v v
[ Row INVISIBLE ] Is trx_id in m_ids?
(Started after ReadView) |
+---------------+---------------+
| YES | NO
v v
[ Row INVISIBLE ] [ Row VISIBLE ]
(Uncommitted at ReadView) (Committed before ReadView)
If the engine determines that the latest row image in the clustered index is INVISIBLE under these rules, the thread follows the DB_ROLL_PTR to inspect the previous undo record in the Version Chain.
It traverses backwards until it encounters a historic row version whose DB_TRX_ID passes the visibility check.
READ COMMITTED vs REPEATABLE READ Mechanics
The primary operational difference between READ COMMITTED and REPEATABLE READ isolation levels in MySQL lies in when the Read View is created:
READ COMMITTED Isolation
- A brand new Read View is generated at the start of every single
SELECTstatement. - If Transaction B updates a row and commits between Query 1 and Query 2 of Transaction A, Query 2 generates a new Read View where Transaction B is no longer in
m_ids. Query 2 sees the new data.
REPEATABLE READ Isolation
- A single Read View is generated once, when the first
SELECTstatement executes inside the transaction. - All subsequent
SELECTqueries reuse that identical Read View snapshot throughout the transaction’s lifecycle. - Even if 1,000 other transactions modify and commit changes to the database while Transaction A runs, Transaction A sees the exact state of the database from the moment its first read occurred.
Concrete MVCC Execution Trace
Let’s trace a concrete timeline with two active transactions:
- Transaction 100 (
m_creator_trx_id= 100) - Transaction 200 (
m_creator_trx_id= 200)
Time Transaction 100 Transaction 200
----------------------------------------------------------------------------------------
T1 BEGIN; (id = 100) BEGIN; (id = 200)
T2 UPDATE accounts SET balance = 999
WHERE user_id = 1;
-- Data Page: balance=999, DB_TRX_ID=200
-- Undo Log: balance=100, DB_TRX_ID=50
T3 SELECT balance FROM accounts
WHERE user_id = 1;
-- T1 creates ReadView:
-- m_ids=[100, 200], min_trx_id=100, max_trx_id=201
-- Reads Data Page: TRX_ID=200. Is 200 in m_ids? YES! INVISIBLE.
-- Traverses ROLL_PTR to Undo Log: TRX_ID=50. Is 50 < min_trx_id(100)? YES! VISIBLE.
-- Returns balance = 100.
T4 COMMIT;
T5 [If READ COMMITTED]:
SELECT balance FROM accounts WHERE user_id = 1;
-- Generates NEW ReadView: m_ids=[100], min_trx_id=100, max_trx_id=201
-- Reads TRX_ID=200. Is 200 in m_ids? NO! VISIBLE.
-- Returns balance = 999.
[If REPEATABLE READ]:
SELECT balance FROM accounts WHERE user_id = 1;
-- Reuses OLD ReadView: m_ids=[100, 200], min_trx_id=100, max_trx_id=201
-- Reads TRX_ID=200. Is 200 in m_ids? YES! INVISIBLE.
-- Traverses ROLL_PTR to Undo Log (TRX_ID=50).
-- Returns balance = 100.
Summary & Next Steps
MVCC allows databases to provide fast, non-blocking reads while preserving strict transactional isolation guarantees:
- System columns (
DB_TRX_ID,DB_ROLL_PTR) link data page records to historical undo segments. - Undo Log Version Chains preserve historical row states across past transaction commits.
- Read Views (
m_ids,min_trx_id,max_trx_id) provide predictable point-in-time visibility filters. REPEATABLE READreuses a single snapshot, whereasREAD COMMITTEDrefreshes its snapshot on every SQL query.
In the next article, we transition to Module 4 and inspect The Volcano Execution Model & Cost-Based Optimizer (CBO).
References & Further Reading
- Eswaran, K. P., Gray, J. N., Lorie, R. A., & Traiger, I. L. (1976). The Notions of Consistency and Predicate Locks in a Database System. Communications of the ACM, 19(11), 624–633.
- Gray, J., Lorie, R. A., Putzolu, G. R., & Traiger, I. L. (1976). Granularity of Locks and Degrees of Consistency in a Shared Data Base. Modeling in Data Base Management Systems, 365–394.
Part 10: The Volcano Execution Model & Cost-Based Optimizer: From SQL to EXPLAIN Plans
Continue to Part 10 →