Isolation Levels & Read Phenomena

In one line

Read Uncommitted → Read Committed → Repeatable Read → Serializable, and the dirty/non-repeatable/phantom reads each level tolerates.

0/7 · 0%

Think of it like this

A shared shopping list app. If two people can see each other's half-finished, unsaved edits, that's a 'dirty read' and it causes confusion. Isolation levels decide how much of each other's in-progress work people are allowed to see.

Key ideas

01
Dirty read: seeing an UNCOMMITTED row that may roll back (only Read Uncommitted allows it).
02
Non-repeatable read: same query twice returns different committed rows (a row changed between).
03
Phantom read: the ROW COUNT changes between two queries (new rows appear matching the predicate).
04
Read Uncommitted: no isolation — dirty reads allowed. Almost never used in production.
05
Read Committed: each statement sees a fresh committed snapshot — dirty reads gone, non-repeatable remain. Postgres default (per-statement snapshot).
06
Repeatable Read: one snapshot for the whole transaction — non-repeatable gone; phantoms possible. MySQL default; Postgres RR also blocks phantoms via snapshot.
07
Serializable: fully serialized — strongest, slowest; use only when truly required or under clockwork contention.
08
Interview line: 'I'd choose READ COMMITTED (or REPEATABLE READ) because …' — not 'whatever the default is'.

Java / Spring map

→
@Transactional(isolation = Isolation.REPEATABLE_READ) — only needed when the app requires it.

Code & diagrams

which anomalies each level preventsdiagram

Rendering diagram…

IsolationCheatsheet.mdmarkdown

The matrix to internalize.

Level             | dirty read | non-repeatable | phantom
Read Uncommitted  | possible   | possible       | possible
Read Committed    | no         | possible       | possible
Repeatable Read   | no         | no             | possible* (MySQL) / no (PG snapshot)
Serializable      | no         | no             | no

Default picks:
- PostgreSQL: READ COMMITTED  (per-statement snapshot)
- MySQL:     REPEATABLE READ (per-transaction snapshot)

Use Serializable only when you must -- e.g. double-spend protection
where locking rows is the real fix anyway.

Explain without notes

Give a concrete business scenario where Repeatable Read matters (e.g. reading balance twice in a report).

Practice

For each LLD, note which isolation level you'd set and why — or why default is fine.

Trade-offs

↔
Higher isolation = stronger guarantee, more overhead/contention. Weak levels hide bugs until the money is wrong.

Run it in production

You've designed it. Now build, operate, and break the same idea hands-on in the DevOps courses:

Stateful · PostgreSQL essentials

MVCC, VACUUM, roles, and the SQL vs NoSQL families, from the operator's side.

Completion checklist

I can define the three read phenomena and order the four levels.

Back to phase

Command Palette