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.

The four anomalies

• 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.
• Lost update — two read-modify-writes clobber each other (the counter++ race, in SQL form).

Level → which anomalies it prevents

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

*InnoDB's next-key locks actually stop most phantoms at Repeatable Read; Postgres 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 UPDATE or a version check.

Takeaways

• Four anomalies (dirty / non-repeatable / phantom / lost update); four levels trade safety for concurrency.
• Know your engine default (Postgres = Read Committed, InnoDB = Repeatable Read).
• Lost update → SELECT … FOR UPDATE (pessimistic) or a version check (optimistic = CAS).

---

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.

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.

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.

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.

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.