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

01 What MVCC Actually Does

InnoDB stores two hidden fields per row: DB_TRX_ID (transaction ID of last modifier) and DB_ROLL_PTR (pointer to previous version in undo log). Each UPDATE writes the old version to undo log, forming a version chain .

When a transaction runs SELECT, InnoDB creates a ReadView — a snapshot listing all active (uncommitted) transaction IDs. Visibility rules:

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

If trx_id belongs to a transaction started after mine → not visible , follow version chain downward.

If trx_id is in the active list (uncommitted) → not visible , continue downward.

This mechanism lets reads access historical versions without blocking writes.

02 Why Isolation Levels Still Exist

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

READ COMMITTED

Each SELECT creates a new ReadView. If another transaction commits between two SELECTs, the second read sees the new data — non-repeatable read .

REPEATABLE READ

The transaction creates one ReadView at its first SELECT and reuses it for all subsequent SELECTs. Data remains consistent throughout the transaction regardless of external commits.

RC and RR use identical MVCC machinery ; the only difference is when the ReadView is created — per statement vs per transaction. That single timing difference yields two distinct isolation behaviors.

03 Why READ UNCOMMITTED Doesn't Use MVCC

READ UNCOMMITTED allows reading uncommitted changes. Since it wants the very latest version (the head of the version chain), creating a ReadView to filter versions would be pointless. MVCC's purpose is to hide versions that shouldn't be seen; if everything is visible, MVCC adds no value.

04 Why SERIALIZABLE Doesn't Use MVCC

SERIALIZABLE forces all reads to acquire shared locks. 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;

Locking reads block writes and vice versa, abandoning MVCC's non-blocking advantage to achieve the strongest consistency — full serial execution.

05 Visual Summary

06 MVCC Doesn't Handle Writes

InnoDB distinguishes two read types:

Snapshot Read : plain SELECT — uses MVCC, reads historical version, no locks.

Current Read : SELECT ... FOR UPDATE, UPDATE, DELETE, INSERT — must read the latest version, so they acquire row locks and bypass MVCC.

-- Snapshot read: MVCC, may read history, no lock
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 value
UPDATE account SET balance = balance - 100 WHERE id = 1;

In RC and RR, InnoDB's actual strategy is:

Reads use MVCC → lock-free snapshot reads, don't block writes.

Writes use row locks → mutual exclusion, ensure data safety.

Read-write conflicts are avoided because reads access history while writes modify the current version; write-write conflicts are resolved by row locks. This division of labor — not MVCC or locks alone — is why InnoDB achieves high concurrency.

07 Closing Thought

MVCC is a low-level concurrency tool that solves how reads can obtain consistent data without locks. But how consistent — only committed latest (RC), stable for whole transaction (RR), include uncommitted (RU), or fully serializable — is a business-level choice governed by isolation levels. MVCC provides capability; isolation levels define policy. They operate at different layers.