Isolation Levels & Anomalies
Why isolation levels exist
Run transactions concurrently and they can corrupt each other's view of the data. SQL defines four isolation levels — each a trade between correctness and concurrency. Knowing your engine's default (it is usually not the safest) is a real production skill.
Two taxonomies (do not mix them)
Interviewers and docs use “anomaly” for two different buckets. Keep them separate.
A — ANSI read phenomena (what the isolation-level table is about)
These three are the classic SQL-standard read phenomena (Berenson et al. / ANSI framing). The level table below maps only these.
- Dirty read — you read another transaction's uncommitted change (which may roll back).
- Non-repeatable read — you read a row twice and get different values (someone committed an update between).
- Phantom read — you re-run a range query and new rows appear (or disappear) that change the set.
B — Write / multi-row anomalies (still real under common defaults)
These are not extra columns on the ANSI three-phenomena table. They are the production bugs teams hit after they memorize that table — especially under Read Committed / Repeatable Read / snapshot isolation.
- Lost update — two read-modify-writes on the same row clobber each other (the
counter++race, in SQL form). Same-row write/write; fix withFOR UPDATE, SQL-side arithmetic under a row lock, or a version CAS. - Write skew — each transaction reads an overlapping constraint, each writes a different row, both commit, the invariant breaks (e.g. “≥1 doctor on call”). No single-row write/write conflict, so RR/SI often miss it; needs Serializable/SSI, explicit locks on the invariant rows, or a DB constraint.
Level → which ANSI read phenomena it prevents
This table is only bucket A. Lost update and write skew are handled in the sections after it (bucket B).
| Isolation level | Dirty read | Non-repeatable | Phantom |
|---|---|---|---|
| Read Uncommitted | possible | possible | possible |
| Read Committed (Postgres default) | prevented | possible | possible |
| Repeatable Read (MySQL/InnoDB default) | prevented | prevented | possible* |
| Serializable | prevented | prevented | prevented |
*In practice both major engines prevent phantom reads at Repeatable Read — InnoDB via next-key/gap locks, Postgres via its MVCC snapshot (a repeated range query returns the same rows). What neither prevents at this level is write skew; that needs Serializable. Higher isolation = fewer anomalies but more blocking/aborts. Most apps run at Read Committed and handle the rest explicitly.
Fixing a lost update (the common one)
-- pessimistic: lock the row for the duration
BEGIN;
SELECT balance FROM accounts WHERE id='A' FOR UPDATE; -- others block here
UPDATE accounts SET balance = balance - 100 WHERE id='A';
COMMIT;
-- optimistic: detect the conflict with a version column
UPDATE accounts SET balance=?, version=version+1 WHERE id='A' AND version=?;
-- if 0 rows updated, someone else changed it -> re-read and retry (this is CAS, in SQL)Pitfalls
- Assuming Serializable: your default is almost certainly weaker — reason about the anomalies it allows.
- Write skew at Repeatable Read: two transactions each read a constraint, both pass, both write, constraint now violated (e.g. "at least one doctor on call"). Needs Serializable or explicit locks.
- Plain read-modify-write in app code is a lost update waiting to happen — use
FOR UPDATEor a version check.
Takeaways
- Bucket A: three ANSI read phenomena (dirty / non-repeatable / phantom) map to four isolation levels.
- Bucket B: lost update (same row) and write skew (multi-row invariant) are the production holes under common defaults — not extra ANSI table columns.
- Know your engine default (Postgres = Read Committed, InnoDB = Repeatable Read).
- Lost update →
SELECT … FOR UPDATE(pessimistic) or a version check (optimistic = CAS); write skew → Serializable/SSI, locks on invariant rows, or a constraint.
L0 · An isolation level forbids a specific set of read anomalies; higher levels forbid more, but buy that safety with blocking or aborts.
L1 · ① Concurrency — “You’re at Repeatable Read. Two doctors each check ‘≥1 other is still on call’, both see it’s true, both go off-call, both commit. Now nobody’s on call. How?”
Trap: “Repeatable Read gives each transaction a stable snapshot, so it’s safe.” — RR/snapshot isolation only guarantees the rows you read stay stable and blocks two writers clobbering the same row. Here each writes a different row, so there’s no write-write conflict to detect — nothing aborts.
Bar: This is write skew, and it is invisible to snapshot isolation by construction (each writes a distinct row, so there is no lost update to arbitrate — snapshot isolation blocks concurrent writers of the same row via first-updater-wins, but this isn’t that). Fix it by materializing the conflict: SELECT … FOR UPDATE on the rows the invariant depends on (so the second reader blocks), promote that transaction to Serializable (SSI aborts on a detected dangerous structure — chained read-write conflicts that could form a cycle), or push the invariant into a DB constraint. MVCC & snapshot isolation
L2 · ⑤ Adversary/Edge — “You move a range-scan-then-insert from Postgres to MySQL, both at Repeatable Read. It ran clean on Postgres; on MySQL it starts deadlocking. Same level name — why?”
Trap: “Both are Repeatable Read, so the behavior is identical — must be an app bug.” — The level name is standardized; the mechanism is not. InnoDB enforces RR with next-key/gap locks that lock the gaps a range scan touched, so a concurrent insert into that gap blocks and can deadlock. Postgres enforces the same level with a pure MVCC snapshot that takes no gap locks — the identical code just proceeds.
Bar: The SQL standard defines the maximum anomalies a level may allow, not the implementation. Both engines actually prevent phantom reads at RR, but by different mechanisms with different concurrency and failure behavior — InnoDB can gap-lock and deadlock; Postgres uses a pure snapshot (no gap locks on plain range reads). Separately, under Postgres Repeatable Read, a concurrent update to a row you also try to update can fail with could not serialize access due to concurrent update — that is an RR same-row write conflict, not full SSI. True SSI dangerous-structure aborts and the app-wide 40001 retry discipline belong to Serializable. Reason from the engine’s mechanism and version, never the level name — and note that neither prevents write skew at RR; that needs Serializable. MVCC & locking internals
L3 · ② Failure — “Fine, you move that transaction to Serializable to be safe. Under load, half of them start failing with ‘could not serialize access’. Now what?”
Trap: “Serializable is the safe level, so I’ll just leave it — or the database is broken.” — Neither. Serializable (SSI) achieves correctness precisely by aborting transactions when it detects a dangerous structure (chained read-write conflicts that could form a cycle); those aborts are the mechanism working, not an error — and because the check is conservative, some aborts are false positives you must simply retry.
Bar: Serializable pushes the cost onto the application: every transaction must sit inside a retry loop on serialization failure (SQLSTATE 40001), and it must defer external side effects until after commit so a retry is safe. A flood of aborts means the transaction’s read/write footprint is too wide — shrink it — not that Serializable was the wrong call. Transactions & ACID
L4 · ③ Scale — “Your optimistic version-check lost-update fix passed every test. In prod it collapses on one hot row. Why, and what do you switch to?”
Trap: “Optimistic locking is always better — it holds no locks.” — Optimistic CAS on a version column is great when conflicts are rare. On a single hot row, nearly every writer’s version check fails, so they all retry into the same contention → a retry storm, wasted work, collapsing throughput.
Bar: Contention picks the strategy, and the deciding variable is conflict probability per write. A hot single row wants pessimistic FOR UPDATE (writers take one clean serialized wait instead of N failed retries) or removal of the hotspot entirely (sharded/batched counter). Optimistic wins only in the low-conflict regime. optimistic vs pessimistic locking
L5 · ⑥ Cost/Simplicity — “If Serializable is correct, why does anyone run Read Committed by default?”
Trap: “Just always run Serializable and stop worrying about anomalies.” — Correctness-maximal, but you pay abort/retry overhead, dependency-tracking cost, and reduced concurrency on every transaction — including the read-heavy majority that has no dangerous cycle to catch.
Bar: Read Committed is the default because it kills the one anomaly that bites every app (dirty reads) at near-zero cost, and the rarer, more expensive dangers (lost update, write skew) are handled surgically on the few statements that need them — FOR UPDATE, a version check, or Serializable on that transaction alone. You buy isolation per-transaction, not globally. (And “default” is itself engine-specific: Postgres defaults to Read Committed, MySQL/InnoDB to the stronger Repeatable Read — so even this baseline isn’t portable.)
The floor keeps dropping: now hold that on-call invariant across two databases with no shared transaction — you’ve left isolation levels entirely and entered distributed consistency (2PC vs. sagas + compensation).
Self-locate: didn’t see the write skew at L1 → mid-level; defended the retry-loop (L3), the contention regime (L4) and per-transaction cost (L5) → staff signal.
Facing any new concept? Hit it with the six: concurrent? failing? at 100×? over time? adversarial? worth the cost? — that’s the interviewer’s whole playbook.
Re-authored for this guide; non-repeatable-read timeline hand-authored as SVG. Follows DDIA ch. 7 and PostgreSQL transaction-isolation docs. See also: Transactions & ACID, MVCC & Locking, and (Concurrency) Race Conditions.
🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — Isolation Levels & Anomalies
Why this concept exists (judgment chain)
Isolation levels trade anomaly prevention for concurrency. Defaults are not Serializable (PG=RC, InnoDB=RR). Write skew and lost updates are the anomalies teams hit in production after they memorize the ANSI table. Mechanism differs by engine even when the level name matches.
Worked example with numbers or traced steps
ANSI read map (bucket A): RU allows dirty; RC blocks dirty; RR blocks non-repeatable;
Serializable blocks phantoms (and more). Bucket B (lost update / write skew) is separate from that table.
*RR phantom reads: InnoDB next-key locks / PG snapshot often prevent re-read phantoms;
write skew still allowed at RR.
Lost update fix: SELECT … FOR UPDATE (pessimistic) or version CAS (optimistic).
Write skew: two doctors each leave; RR snapshot both OK → need Serializable/SSI or locks.
Hot row: optimistic retry storm → switch pessimistic or shard counter.When NOT to use / named alternative
Do not run global Serializable for all traffic — use it surgically; handle 40001 retries. Do not assume RR means the same on MySQL and Postgres. Prefer RC default + explicit locks on critical invariants when abort rates matter. Skip FOR UPDATE on read-only reporting queries.
Failure / ops fingerprint
Fingerprint: balance wrong after concurrent withdraw; on-call roster empty (write skew); serialization failure flood without retry loop; MySQL deadlocks after porting PG RR range code. Ops: set default consciously; metric on serialization_failure; runbooks for retry-safe transactions (no side effects before commit).
Hostile-panel drills (defend the decision)
Q1. Postgres vs InnoDB default isolation?
Model answer: Postgres Read Committed; InnoDB Repeatable Read.
Q2. What is write skew?
Model answer: Two transactions read overlapping constraint state, each write different rows, both commit, invariant broken — allowed under snapshot/RR; needs Serializable or explicit locks.
Q3. Optimistic vs pessimistic for a hot counter?
Model answer: Hot row → pessimistic FOR UPDATE or redesign; optimistic CAS thrash-retries under high conflict probability.
Production judgment
Isolation levels are a menu of which lies concurrent readers/writers may see. Read Committed is not “safe for money.” Serializable is not free. Production chooses a level, then designs constraints (unique keys, conditional updates) for the anomalies left over.
Flash-sale / ledger angle: lost update on stock and write skew on “two seats left” are not interview trivia — they are oversell classes.
Private labs: Flash-sale inventory · Ledger concurrent posts · Payment gateway under load.
Staff drill: For your DB defaults (e.g. Postgres RC), list two money bugs still possible and the app-level guard for each.
🤖 Don't fully get this? Learn it with Claude
Stuck on Isolation Levels & Anomalies? Open Claude, copy a block below, and it'll teach you this exact concept — visually and interactively.
Build the mental picture, not memorization.
I just read a lesson on **Isolation Levels & Anomalies** (Databases) and want to truly understand it. Explain Isolation Levels & Anomalies from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.
Socratic — adapts to where you're stuck.
Teach me **Isolation Levels & Anomalies** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.
Active recall exposes what you missed.
Quiz me on **Isolation Levels & Anomalies** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.
Intuition + hook + flashcards for long-term memory.
Help me remember **Isolation Levels & Anomalies** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.