Why MySQL Still Needs Four Isolation Levels Despite MVCC's Lock-Free Reads

01 What MVCC Actually Does

InnoDB adds two hidden fields to every row: DB_TRX_ID: the transaction ID that last modified this row DB_ROLL_PTR: a pointer to the previous version of this row in the undo log

Each UPDATE writes the old version to the undo log, linking versions into a version chain .

When a transaction executes SELECT, InnoDB creates a ReadView snapshot containing the list of currently active (uncommitted) transaction IDs. Visibility rules:

If the version's trx_id committed before my transaction started → visible

If the version's trx_id belongs to a transaction that started after mine → not visible , follow the version chain downward

If the version's trx_id is still in the active list (uncommitted) → not visible , continue down the chain

02 Why Isolation Levels Are Still Needed

MVCC only provides the mechanism to read historical versions; which historical version to read depends on the ReadView creation strategy, and that strategy is exactly what isolation levels control.

READ COMMITTED

A new ReadView is created for every SELECT.

If another transaction commits between two SELECTs, the second read sees the new data — this is non-repeatable read .

REPEATABLE READ

A single ReadView is created at the first SELECT and reused for all subsequent SELECTs in the transaction.

External changes are invisible; the transaction sees a consistent snapshot throughout.

RC and RR use the exact same MVCC mechanism ; the only difference is when the ReadView is created — per statement vs. per transaction. That single timing difference produces two completely different isolation effects.

03 Why READ UNCOMMITTED Doesn't Use MVCC

READ UNCOMMITTED allows reading uncommitted changes from other transactions. Since it wants to see the very latest version (the head of the version chain), there is no need to create a ReadView to filter versions. MVCC's purpose is to hide versions that shouldn't be seen; if everything should be visible, MVCC is redundant. So READ UNCOMMITTED isn't "unable to use MVCC" — using it would just be pointless.

04 Why SERIALIZABLE Doesn't Use MVCC

SERIALIZABLE takes the opposite extreme: every read acquires a shared lock. A plain SELECT is automatically rewritten to SELECT ... LOCK IN SHARE MODE:

-- In SERIALIZABLE, this ordinary SELECT
SELECT * FROM user WHERE id = 1;
-- becomes
SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE;

Reads now block writes and vice versa, directly contradicting MVCC's goal of non-blocking reads. SERIALIZABLE sacrifices MVCC's concurrency advantage for the strongest consistency guarantee — transactions execute completely serially.

05 Visual Summary

06 MVCC Doesn't Handle Writes

MVCC only solves the read problem (snapshot reads). Writes still require locks.

InnoDB has two read types:

Snapshot Read : ordinary SELECT, uses MVCC, reads a historical version, no locks.

Current Read : SELECT ... FOR UPDATE, UPDATE, DELETE, INSERT. These must read the latest version (otherwise an UPDATE could modify stale data), so they bypass MVCC and use row locks.

-- Snapshot read: uses MVCC, may read historical version, no locks
SELECT * FROM account WHERE id = 1;

-- Current read: exclusive lock, reads latest version
SELECT * FROM account WHERE id = 1 FOR UPDATE;

-- Current read: UPDATE internally reads current latest value
UPDATE account SET balance = balance - 100 WHERE id = 1;

InnoDB's actual strategy under RC and RR:

Reads use MVCC → lock-free snapshot reads, no blocking of writes

Writes use row locks → mutual exclusion, ensuring data safety

Reads and writes don't conflict (reads access historical versions, writes access current version); write-write conflicts are resolved by row locks. This division of labor — not MVCC alone or locks alone — is the real reason for InnoDB's high concurrency performance.

07 Closing Thoughts

MVCC is a low-level concurrency control tool that solves how reads can obtain consistent data without locks.

But how consistent — whether to see only the latest committed data (RC), keep data stable for the whole transaction (RR), see even uncommitted changes (RU), or lock reads for absolute serializability (Serializable) — is a business-level choice defined by the isolation level.

MVCC provides the capability; isolation levels define the strategy. They operate at different layers.