Isolation Anomaly Lab (Interactive)
Interleave two transactions and trigger each anomaly at every isolation level. Advance interleaved transactions one statement at a time and watch which anomalies each isolation level permits, how MVCC lets readers never block writers, and when SSI aborts.
Isolation Level Anomaly Lab (MVCC Edition)
Step two transactions through each classic anomaly and see exactly which isolation level stops it.
- TX1: BEGIN
- TX1: UPDATE balance $500 → $350 (NO COMMIT)
- TX2: SELECT balance
- TX1: ROLLBACK
- Verdict
The matrix from the topic: READ UNCOMMITTED allows everything · READ COMMITTED (PostgreSQL default) stops dirty reads per-statement · REPEATABLE READ gives one snapshot for the whole transaction (stops non-repeatables + phantoms) · SERIALIZABLE adds predicate tracking and aborts the write-skew loser. Blocked readers is always 0 — that is the MVCC promise.
How It Works Under the Hood
Isolation is not one switch but a ladder of four guarantees. READ UNCOMMITTED permits dirty reads of uncommitted bytes; READ COMMITTED takes a fresh snapshot per statement, allowing non-repeatable reads; REPEATABLE READ pins one snapshot, preventing those but permitting phantoms in some engines; SERIALIZABLE forbids all four anomalies — in PostgreSQL via SSI, which aborts one transaction when it detects a dangerous read-write cycle. Underneath sits MVCC: row versions tagged with xmin, snapshot visibility rules, and dead tuples awaiting autovacuum.
Core Architectural Principles
- Dirty read, non-repeatable read, phantom read, and write skew map precisely onto the four ANSI isolation levels.
- MVCC row versions with xmin visibility rules let readers never block writers and writers never block readers.
- Serializable Snapshot Isolation detects dangerous read-write dependencies and aborts one transaction instead of locking rows.
Do not just recite four levels — demonstrate one anomaly per level with a concrete interleaving, then explain what an MVCC snapshot actually sees and when a transaction aborts instead of blocking. Mention that production bugs often come from assuming REPEATABLE READ is serializable, or from write skew slipping through check-then-act business logic.
Stronger isolation removes anomalies but raises abort rates and version overhead; weaker isolation shifts the burden onto application locks and retries.