Master MySQL MVCC: Snapshot vs Current Reads and Isolation Level Mechanics

What: MVCC definition

MySQL InnoDB uses Multi-Version Concurrency Control (MVCC) to store multiple historical versions of each row, enabling lock‑free reads and high‑concurrency writes. MVCC is the core mechanism for transaction isolation.

Why MVCC matters

Understanding MVCC is required to grasp transaction isolation levels and to diagnose hidden data anomalies such as non‑repeatable reads, phantom reads, and the behavior of RC and RR isolation.

Where MVCC applies

Engine must be InnoDB (MyISAM does not support MVCC).

Isolation level must be RC (Read Committed) or RR (Repeatable Read).

Only plain SELECT statements use snapshot reads.

MVCC does not apply to SERIALIZABLE isolation, UPDATE/DELETE/INSERT (current reads), or MyISAM tables.

Snapshot read vs Current read

Snapshot read (ordinary SELECT)

Reads a historical snapshot version, does not lock rows, and uses MVCC. SELECT * FROM user_order WHERE user_id = 1001; Read‑no‑lock, high concurrency.

Returns data as of the transaction start, not the latest.

Dirty reads, non‑repeatable reads, and phantom reads appear in this mode.

Current read (UPDATE/DELETE/INSERT or SELECT … FOR UPDATE)

Reads the latest data from disk, acquires locks, and bypasses MVCC.

UPDATE user_order SET status = 1 WHERE user_id = 1001;
DELETE FROM user_order WHERE user_id = 1001;
INSERT INTO user_order(...) VALUES (...);
SELECT * FROM user_order WHERE user_id = 1001 FOR UPDATE;
SELECT * FROM user_order WHERE user_id = 1001 LOCK IN SHARE MODE;

Returns the latest committed data.

Acquires row or gap locks; reads and writes block each other.

Guarantees real‑time consistency.

InnoDB hidden fields that drive MVCC

DB_TRX_ID : transaction ID of the last modification; updated on every UPDATE/DELETE.

DB_ROLL_PTR : pointer to the previous version, forming a version chain.

DB_ROW_ID : hidden primary key used when no explicit PK exists (not part of MVCC versioning).

Version generation and undo log

Each modification stores the old row in the undo log, writes the new row with a new transaction ID, and links the new row to the old one via DB_ROLL_PTR, creating a version chain.

Read View: isolation rule engine

When a snapshot read transaction starts, MySQL creates a Read View containing:

m_ids : set of active uncommitted transaction IDs.

min_trx_id : smallest ID in m_ids.

max_trx_id : next transaction ID to be assigned.

creator_trx_id : ID of the current transaction.

Visibility check for a row with DB_TRX_ID follows these rules:

If TRX_ID < min_trx_id → visible (committed).

If TRX_ID >= max_trx_id → invisible (future transaction).

If min_trx_id ≤ TRX_ID < max_trx_id → visible only when TRX_ID not in m_ids.

If a version is invisible, the engine follows DB_ROLL_PTR to the previous version and repeats the check.

RC vs RR isolation differences

RC (Read Committed) : a new Read View is generated for each SELECT, allowing the transaction to see other transactions' committed changes (no dirty reads) but causing non‑repeatable reads.

RR (Repeatable Read) : a single Read View is created at the first SELECT and reused for the whole transaction, eliminating non‑repeatable reads but permitting phantom reads; this is MySQL’s default level.

Hands‑on SQL demos

Setup

SET autocommit = 0;
INSERT INTO account(user_name,balance) VALUES ('王五', 2000);

Demo 1 – RC non‑repeatable read

Window A starts a transaction and selects balance 2000. Window B updates balance to 1000 and commits. Window A selects again and sees 1000, demonstrating RC’s changing view.

Demo 2 – RR repeatable read

Switch to RR (default). Window A’s two selects both return 2000 despite Window B’s update, because the same Read View is reused.

Demo 3 – Snapshot vs current read blocking

Window A runs UPDATE (current read) and holds a lock. Window B’s plain SELECT (snapshot read) succeeds instantly, while Window B’s SELECT … FOR UPDATE blocks until Window A releases the lock, confirming MVCC’s non‑blocking snapshot reads.

High‑frequency pitfalls

Confusing snapshot and current reads leads to stale statistics under RR.

Long‑running RR transactions keep old versions, causing undo log growth and disk bloat.

RC can produce unstable business data because repeated reads may differ.

Phantom reads cannot be fully eliminated by MVCC; gap locks are required.

Serializability disables MVCC, reverting to lock‑based concurrency and severe performance loss.

Core cheat sheet

MVCC enables lock‑free reads and high‑concurrency writes while providing isolation.

SELECT = snapshot read (old version); DML or SELECT … FOR UPDATE = current read (latest version with lock).

Hidden fields + undo log + Read View implement MVCC.

RC refreshes Read View each query (non‑repeatable reads); RR reuses a single view (no non‑repeatable reads, possible phantom reads).

Use RC for high‑throughput scenarios, RR for strict consistency, and avoid long‑running transactions.