Home/Learn/MySQL/MVCC (Multi-Version Concurrency Control)

MVCC (Multi-Version Concurrency Control)

Advanced

Transactions

InnoDB stores multiple row versions in the undo log; readers see a snapshot consistent with their transaction start, allowing readers and writers to proceed without blocking each other.

Overview

MVCC is the mechanism InnoDB uses to give each transaction a consistent snapshot of the database without taking read locks. Every row has two hidden system columns: `DB_TRX_ID` (the transaction that last modified it) and `DB_ROLL_PTR` (a pointer into the undo log chain). When a transaction reads a row, InnoDB checks the row's `DB_TRX_ID` against the transaction's **read view** (snapshot of active transactions at the time the read view was opened). If the row was modified by a transaction that was active (uncommitted) when the read view was created, InnoDB follows the undo-log pointer to retrieve an older version — transparently presenting a consistent past snapshot. This means **reads never block writers and writers never block reads** — a key performance advantage over lock-based concurrency.

Read View and Snapshot Timing

Under **REPEATABLE READ** (default), InnoDB creates the read view on the **first statement** of the transaction — all subsequent reads in that transaction see the same snapshot. Under **READ COMMITTED**, a new read view is created for **each statement**, so you always see the latest committed data. The choice between these two behaviours explains the difference in anomalies each level prevents.

MySQL — REPEATABLE READ vs READ COMMITTED snapshot timing

-- Session A: REPEATABLE READ (default)
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;   -- sees $100

-- Session B: commits an update
UPDATE accounts SET balance = 200 WHERE id = 1;
COMMIT;

-- Session A: same transaction, same read view → still sees $100
SELECT balance FROM accounts WHERE id = 1;   -- $100 (snapshot isolation)
COMMIT;

-- Session A: READ COMMITTED
SET SESSION transaction_isolation = 'READ-COMMITTED';
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;   -- $100 (read view created here)

-- Session B commits UPDATE balance=200

SELECT balance FROM accounts WHERE id = 1;   -- $200 (new read view per statement)
COMMIT;

Undo Log Chain and Row Versioning

Each `UPDATE` or `DELETE` does not overwrite the row in-place. Instead, the old row image is written to the **undo log**, and the row's `DB_ROLL_PTR` pointer is updated to point at it. This creates a version chain. InnoDB walks the chain until it finds a version visible to the current transaction's read view. The undo log entry is retained until no active transaction needs the old version — controlled by the **purge thread**.

MySQL — undo log version chain

-- Conceptual version chain for row id=1:
-- Current row:  balance=300, DB_TRX_ID=50, DB_ROLL_PTR → undo#2
-- Undo entry 2: balance=200, DB_TRX_ID=40, DB_ROLL_PTR → undo#1
-- Undo entry 1: balance=100, DB_TRX_ID=30, DB_ROLL_PTR → NULL

-- A transaction with read view "trx < 45" sees balance=200 (latest version <= 45)
-- A transaction with read view "trx < 35" sees balance=100

-- Monitor undo log size (large = long-running transactions holding old versions)
SHOW ENGINE INNODB STATUS;   -- look for "History list length"
-- High history length → slow queries (longer undo chain to traverse)
-- Solution: commit or roll back long-running transactions promptly

MVCC Pitfalls: Long Transactions and Purge Lag

MVCC's main operational pitfall is **undo log bloat** caused by long-running transactions. As long as a transaction holds an open read view, the purge thread cannot remove old row versions — even if they have been superseded by thousands of updates. This causes the "history list length" to grow, which slows down all row lookups (longer undo chain to traverse) and consumes disk space. Always commit or roll back transactions promptly.

MySQL — detect and fix undo log bloat

-- Detect long-running transactions
SELECT trx_id, trx_started, trx_state, trx_query,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30
ORDER BY trx_started;

-- History list length (should be < 1000 ideally)
SELECT NAME, COUNT FROM information_schema.innodb_metrics
WHERE NAME = 'trx_rseg_history_len';

-- Kill an offending long-running transaction
KILL CONNECTION <processlist_id>;

-- Prevent read-only reporting queries from holding undo log:
-- Use a short-lived READ COMMITTED connection for analytics
-- Or route reports to a replica

Key Points to Remember

1MVCC lets readers and writers proceed simultaneously without blocking each other
2Each row has hidden DB_TRX_ID and DB_ROLL_PTR columns tracking version history
3REPEATABLE READ: read view created at first statement; READ COMMITTED: per statement
4InnoDB walks the undo log chain to find the version visible to a transaction's read view
5Long-running transactions prevent purge thread from clearing old versions → undo log bloat
6Monitor "History list length" in INNODB STATUS; kill transactions older than ~30s in OLTP

Interview Questions

How does MVCC allow readers and writers to work simultaneously without locking?

MediumOracle

What hidden columns does InnoDB add to every row to support MVCC?

HardPercona

What causes undo log bloat and how would you fix it?

HardBooking.com

Under REPEATABLE READ, when does InnoDB create the read view?

MediumAmazon

Does MVCC completely eliminate locking in InnoDB?

MediumMicrosoft

Ask Aria about MVCC (Multi-Version Concurrency Control)

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Transaction Isolation Levels

Deadlocks & Locking