MVCC Explained

Multi-Version Concurrency Control (MVCC) lets InnoDB provide consistent reads without blocking writers, and writers without blocking readers, by keeping multiple row versions and determining which version a transaction is allowed to see. This article explains how MVCC works in InnoDB and how it relates to isolation levels and undo logs.

Overview

• Idea: Instead of locking a row for the whole transaction, InnoDB keeps older versions of the row in the undo log. A read sees a snapshot of data (a consistent view) based on transaction start and visibility rules; writers create new versions without making readers wait.
• Read view: Each transaction (for repeatable read or read committed) gets a read view that determines which row versions are visible (by comparing transaction IDs and the view’s low-water marks).
• Undo: When you UPDATE or DELETE, the old row version is stored in the undo log and linked from the current row. Long-running transactions or large updates can grow undo and history.

Example

Example 1: Two transactions, one updates while the other reads

SQLCopy
-- Transaction A (start time T1)
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- sees 100

-- Transaction B (start time T2)
UPDATE accounts SET balance = 50 WHERE id = 1;
COMMIT;

-- Transaction A reads again (still in same transaction)
SELECT balance FROM accounts WHERE id = 1;  -- still sees 100 (snapshot)
COMMIT;

• Under REPEATABLE READ, A’s read view is fixed at the first read; it keeps seeing the old version (100). B’s update creates a new row version; A never sees it until A commits and starts a new transaction.

Example 2: Read committed — each statement sees latest committed

SQLCopy
-- Session 1
SET SESSION transaction_isolation = 'READ-COMMITTED';
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 100

-- Session 2
UPDATE accounts SET balance = 50 WHERE id = 1;
COMMIT;

-- Session 1
SELECT balance FROM accounts WHERE id = 1;  -- 50 (new read view per statement)

• With READ COMMITTED, each statement gets a fresh read view, so the second SELECT sees the committed 50.

Example 3: How visibility is decided (simplified)

Your trx_id	Row version creator trx_id	Visibility (RR)
100	99 (committed)	Visible
100	101 (not committed)	Not visible
100	100 (your changes)	Visible

• InnoDB compares row version’s transaction ID with the read view’s up_limit_id, low_limit_id, and active list to decide visibility.

Core Mechanism / Behavior

• Row structure: Each row has hidden columns: DB_TRX_ID (last modifier), DB_ROLL_PTR (pointer to undo for previous version), DB_ROW_ID (if no primary key).
• Undo chain: Updates and deletes append undo records; the current row’s DB_ROLL_PTR walks back to older versions. Purge removes undo that is no longer needed by any read view.
• Read view (RR): Created at first read in the transaction; reused until commit. So repeatable read is implemented by a fixed snapshot.
• Read view (RC): New read view per statement; you see the latest committed state at each statement.

Key Rules

• Long-running transactions keep read views and undo alive; avoid holding transactions open for a long time.
• Heavy updates/deletes generate a lot of undo; monitor undo tablespace and history list length. Purge can lag under load.
• Use the right isolation level: RR for consistent snapshots, RC if you want to see committed changes immediately per statement.

What's Next

For isolation levels and anomalies, see Transactions - Isolation & Common Anomalies. For locking (row vs gap), see Locks - Row Lock vs Gap Lock.