Snapshots & the four levels

InnoDB is an MVCC engine: readers look at a consistent snapshot instead of locking rows, so plain SELECTs never block writers and writers never block plain SELECTs. Which snapshot a statement sees — and when reads take locks anyway — is decided by the isolation level.

MySQL implements all four SQL-standard levels (the ladder and its vocabulary, in theory) — and unlike PostgreSQL, all four are actually different:

Level	Dirty read	Non-repeatable read	Phantom	Snapshot
READ UNCOMMITTED	yes — really	yes	yes	none: reads see uncommitted data
READ COMMITTED	no	yes	yes	fresh snapshot per statement
REPEATABLE READ (default)	no	no	no*	one snapshot per transaction
SERIALIZABLE	no	no	no	RR + plain SELECTs take shared locks

* for plain SELECTs; locking reads and writes see the current data — that asterisk is the most important character in this table.

Two things to notice before the demos. MySQL's default is REPEATABLE READ, not READ COMMITTED like PostgreSQL, Oracle, and SQL Server, so most of what your MySQL app does runs at RR. And you set the level with a separate statement — SET TRANSACTION ISOLATION LEVEL … for the next transaction only, or SET SESSION TRANSACTION ISOLATION LEVEL … for the whole session; there is no BEGIN ISOLATION LEVEL … one-liner.

READ UNCOMMITTED means it

PostgreSQL accepts the READ UNCOMMITTED syntax but silently upgrades it to READ COMMITTED. MySQL takes you at your word — and hands you data that was never committed:

A

B

UPDATE balance = 999 (uncommitted)

SELECT balance→ 999 (dirty read)

ROLLBACK — 999 never existed

SELECT balance→ 100 (the real value)

A> BEGIN;
Query OK

A> UPDATE accounts SET balance = 999 WHERE id = 1;
Query OK, 1 row affected

B opts into READ UNCOMMITTED — and sees A's uncommitted 999.

B> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK

B> BEGIN;
Query OK

B> SELECT @@transaction_isolation AS isolation;
    isolation     
------------------
 READ-UNCOMMITTED 
(1 row)

B> SELECT balance FROM accounts WHERE id = 1; -- a dirty read — A never committed this
 balance 
---------
     999 
(1 row)

A rolls back. The 999 B just read never existed.

A> ROLLBACK;
Query OK

B> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     100 
(1 row)

B> COMMIT;
Query OK

Verified against MySQL 8.4.10 · Run it yourself · Scenario source

There is no good reason to run at this level: the 999 that B read — and might have acted on — never existed.

Isolation is a per-transaction setting: the server default of REPEATABLE READ can be overridden per session or per transaction, and READ UNCOMMITTED really does hand you dirty reads where PostgreSQL's never would. From here the chapter walks up the ladder — READ COMMITTED, REPEATABLE READ, SERIALIZABLE — before landing on the lost update, the anomaly your app most likely has today.

Further reading

• MySQL docs: Transaction Isolation Levels
• MySQL docs: Consistent Nonlocking Reads
• The same lesson on PostgreSQL