Reference · Cheat sheet

Transaction isolation levels

The one-page compressed essence: a plain read is a lock-free MVCC snapshot, and the isolation level just times it.

Lesson: 0005Context: InnoDB, MySQL 8.0+

The one idea

MVCC snapshot

Plain SELECT = consistent nonlocking read: MVCC serves a point-in-time snapshot, sets no locks. Readers don't block writers. The isolation level only decides when the snapshot is taken.

The four levels

Level	Snapshot	Allows
READ UNCOMMITTED	even uncommitted	dirty · non-repeatable · phantom
READ COMMITTED	fresh per statement	non-repeatable · phantom
REPEATABLE READ (default)	fixed at first read	phantom* (largely prevented)
SERIALIZABLE	RR + locking reads	nothing

* InnoDB's REPEATABLE READ prevents phantoms for plain SELECT (they're not in the first-read snapshot) and, for locking reads, via next-key locks (Lesson 0006).

The three anomalies

dirty read	read another txn's uncommitted change
non-repeatable read	same row read twice → different (committed UPDATE between)
phantom read	same WHERE twice → new rows (committed INSERT between)

RR vs RC — the interview answer

REPEATABLE READ → ONE snapshot at first read; reads repeat identically. READ COMMITTED → NEW snapshot each statement; reads can change mid-txn. Same MVCC machinery — only the snapshot TIMING differs.

Commands

SELECT @@transaction_isolation; -- current level SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- new connections START TRANSACTION; … COMMIT; / ROLLBACK; COMMIT then re-SELECT → get a fresher snapshot under RR

Source: MySQL 8.0 Manual — Isolation Levels · Consistent Nonlocking Reads · All lessons