MySQL MVCC Deep Dive: Lock-Free Reads, Isolation Levels & Phantom Read Mechanics

What Is MVCC? (Plain‑English Definition)

The previous article covered transaction isolation levels and locking; we learned that Read Committed (RC) and Repeatable Read (RR) solve most concurrency problems. Yet a core question remains: why can MySQL achieve transaction isolation without locking, which would block concurrency? The answer is MVCC (Multi‑Version Concurrency Control) .

Stripping away academic jargon, here is a definition you will remember: MVCC is the underlying mechanism that saves historical version snapshots of data, allowing read operations to proceed without locks and without blocking, while still providing transaction isolation. In short: locking is pessimistic control; MVCC is optimistic lock‑free reading.

Every ordinary SELECT query you run relies on MVCC for lock‑free reads — this is the fundamental reason InnoDB’s concurrency performance far exceeds MyISAM’s.

Why Must You Master MVCC? (Root‑Cause Importance)

All advanced MySQL features are built on MVCC:

RC and RR isolation levels depend entirely on MVCC .

It enables lock‑free reads and read‑write non‑conflict , supporting high‑concurrency workloads.

It solves most concurrency data anomalies without sacrificing performance .

Understanding MVCC is the only way to truly grasp why RR prevents non‑repeatable reads but cannot fully prevent phantom reads .

It reveals the deep interplay between snapshot reads, current reads, locks, and transaction isolation.

Core truth: Without MVCC, transaction isolation and locking are just rote memorization — you will never understand MySQL’s internals.

Where MVCC Applies & Its Three Pillars

Applicable Scenarios

✅ Exclusive to the InnoDB storage engine (MyISAM does not support it).

✅ Enabled by default under RC and RR isolation levels.

✅ All ordinary snapshot‑read queries.

✅ High‑concurrency read‑write coexistence scenarios.

Three Indispensable Underpinnings

Hidden columns, Undo Log, Read View — these three together form the complete MVCC logic, each dissected below.

How: Deep Dive into MVCC’s Three Core Components

1. Hidden Columns on Every Row

InnoDB automatically adds three hidden columns to every row (invisible to users); MVCC relies on them entirely: DB_TRX_ID: Transaction ID of the last transaction that modified this row. DB_ROLL_PTR: Roll pointer pointing to the previous version in the undo log. DB_ROW_ID: Unique row ID (used as the clustered index when no primary key exists).

On every update, InnoDB does not overwrite the old data . Instead, it creates a new version, stores the old version in the undo log, and links them via the roll pointer into a version chain .

2. Undo Log (Version‑Chain Repository)

Think of the undo log as a historical‑version backup warehouse . Before each data modification, the old version is written to the undo log, and the roll pointers chain them into a data version chain . This chain serves both transaction rollback and MVCC snapshot reads.

3. Read View (Visibility Filter)

A Read View is generated at the instant a transaction starts; it defines the data‑visibility rules . Its core job: decide which versions the current transaction can see and which uncommitted versions it must ignore — thereby eliminating dirty reads and non‑repeatable reads at the source. A Read View stores the active transaction ID range and the maximum transaction ID.

Snapshot Read vs. Current Read (The 90% Confusion Point)

MVCC serves only snapshot reads — this is critical.

1. Snapshot Read (Lock‑Free | MVCC)

Ordinary SELECT statements.

Read a historical snapshot: no locks, no blocking, high performance .

Mechanism: fetch the appropriate version from the undo‑log version chain.

2. Current Read (Locking | Lock Mechanism)

Read the latest committed data in real time.

Must acquire locks, will block, triggers row locks / gap locks .

Statements: INSERT, UPDATE, DELETE, SELECT ... FOR UPDATE, LOCK IN SHARE MODE.

Mechanism: accesses the newest version, relying on locks for concurrency safety.

Summary: Snapshot reads use MVCC for isolation; current reads use locks for isolation.

RC vs. RR: The MVCC Implementation Difference (Ultimate Decryption)

The entire difference between RC and RR boils down to one sentence: the timing of Read View generation differs .

1. RC (Read Committed)

A new Read View is generated for every query .

Can see the latest committed data from other transactions → non‑repeatable reads occur .

No gap locks, smaller lock scope, higher concurrency.

2. RR (Repeatable Read — MySQL Default)

Read View is generated once when the transaction starts and reused throughout .

All reads see the same fixed snapshot → non‑repeatable reads are impossible .

Because the snapshot is fixed, newly inserted rows have no prior version in the chain; MVCC alone cannot stop phantom reads , so gap locks are used as a safety net.

Ultimate Q&A: Why RR Stops Non‑Repeatable Reads But Not Phantom Reads

The most intuitive low‑level explanation:

Non‑repeatable read = a single row is modified : the old version remains in the version chain; RR reuses the old snapshot, always reading the old version — perfectly avoided.

Phantom read = new rows inserted into a range : inserted rows have no historical version, so the version chain has nothing to intercept — MVCC cannot block them.

Therefore, at RR level: MVCC handles most phantom‑read scenarios; the tiny remainder is covered by gap locks .

Production‑Frequent Pitfalls 💥

Pitfall 1: Assuming all queries lock. Ordinary SELECT is a snapshot read (lock‑free); only writes and explicit locking reads are current reads that contend for locks.

Pitfall 2: Confusing RC and RR internals. The difference is not the locks but the Read View generation timing — once per transaction vs. once per statement.

Pitfall 3: Believing RR completely eliminates phantom reads. Pure MVCC cannot; gap‑lock cooperation is required, which also raises deadlock probability under RR.

Pitfall 4: Long transactions cause undo‑log bloat. Long‑running transactions prevent old versions from being purged; undo log grows, disk usage spikes, performance degrades.

Pitfall 5: Ignoring snapshot‑read staleness. In a long RR transaction, data stays on the old snapshot, blind to external committed changes.

Key Takeaways ✅

MVCC is InnoDB’s lock‑free concurrency core, built on hidden columns, undo log, Read View .

Snapshot reads use MVCC (no locks); current reads use locking (blocking).

RC generates a Read View per query → sees latest commits → non‑repeatable reads possible.

RR generates one Read View per transaction → fixed snapshot → non‑repeatable reads eliminated.

MVCC alone cannot fully solve phantom reads; gap locks are the fallback.

Long transactions pile up undo log, continuously degrading database performance.