SQL Isolation Levels Explained: Dirty Reads to Serializable

sql isolation levels are the single knob that decides whether two transactions running at the same moment produce a correct answer or a silent data-corruption bug that only surfaces during a Black Friday traffic spike — and they are the concept backend and data engineers reason about worst, because "just wrap it in a transaction" hides four wildly different guarantees behind one BEGIN. Every concurrent workload your application runs — a wallet debit racing a wallet credit, an inventory decrement racing a second checkout, a report reading a row that another session is mid-update on — is governed by which isolation level the transaction opened under, and that level determines exactly which concurrency anomalies you are exposed to: whether you can read another transaction's uncommitted writes, whether the same SELECT can return two different answers inside one transaction, and whether a range query can grow a phantom row underneath you.

This guide is the walkthrough you wished existed the first time an interviewer said "explain the difference between read committed and repeatable read," or "what is a phantom read and which level blocks it," or "how does mvcc actually implement snapshot isolation without readers blocking writers." It works through the four ANSI levels from weakest to strongest — read uncommitted, read committed, repeatable read, and serializable — the three canonical anomalies (dirty reads, non-repeatable reads, phantom reads) plus the two that ANSI forgot (lost update and write skew), the per-engine defaults that trip people up (Postgres runs read committed, MySQL InnoDB runs repeatable read), and the row-version machinery underneath it all. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works. All SQL is PostgreSQL dialect, with engine differences called out where they bite.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the database practice library →, and sharpen the concurrency intuition with the data-processing practice library →.

---

On this page

• Why isolation levels decide correctness under concurrency
• Read Uncommitted and Read Committed
• Repeatable Read
• Serializable
• MVCC — how engines implement isolation
• Cheat sheet — isolation level recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why isolation levels decide correctness under concurrency

Four levels, three anomalies, and one matrix that every senior engineer keeps in their head

The one-sentence invariant: an isolation level is a contract that names exactly which concurrency anomalies a transaction is allowed to observe — the ANSI standard defines four levels (read uncommitted, read committed, repeatable read, serializable) as a strictly increasing ladder, where each rung forbids one more anomaly (dirty reads, then non-repeatable reads, then phantom reads) at the cost of more locking, more snapshot bookkeeping, or more transaction aborts — and the level you choose is a correctness decision, not a performance tuning knob you can flip later without re-auditing every query. The reason this trips people up is that the SQL standard defines the levels by the anomalies they permit, not by how an engine achieves them — so the same SET TRANSACTION ISOLATION LEVEL REPEATABLE READ behaves differently on Postgres, MySQL, and Oracle, and an engineer who memorised the ANSI matrix without knowing their engine's implementation ships bugs.

The three ANSI anomalies — the vocabulary you must own.

• Dirty read. A transaction reads a row that another transaction has modified but not yet committed. If the other transaction rolls back, you acted on data that never existed. Example: T2 reads balance = 900 while T1 has decremented it but not committed; T1 rolls back; T2 has now paid out against a balance that snapped back to 1000.
• Non-repeatable read. A transaction reads the same row twice and gets two different committed values, because another transaction committed an UPDATE to that row in between. The row exists both times; only its value changed. This breaks any logic that assumes a value it read earlier in the transaction is still true.
• Phantom read. A transaction re-runs the same range query (a WHERE predicate over a set of rows) and gets a different set of rows, because another transaction committed an INSERT (or DELETE) that changed which rows match the predicate. The individual rows you already read didn't change; the membership of the set did.

The two anomalies ANSI forgot — the ones senior interviewers probe.

• Lost update. Two transactions both read the same row, each computes a new value from what it read, and each writes it back. The second write silently overwrites the first — one update is lost. Classic: two sessions both read stock = 10, both compute 10 - 1 = 9, both write 9; two items sold, stock only dropped by one.
• Write skew. Two transactions read an overlapping set of rows, each verifies an invariant holds, then each writes to a disjoint row in a way that jointly violates the invariant. Classic: two on-call doctors each check "at least one other doctor is on call," each sees the other, and each goes off-call — now nobody is on call. No single row was updated twice, so SELECT FOR UPDATE on individual rows does not save you; only serializable does.

The ANSI anomaly matrix — the table to whiteboard first.

• read uncommitted permits dirty reads, non-repeatable reads, and phantoms. The weakest rung; you can see uncommitted garbage.
• read committed forbids dirty reads but permits non-repeatable reads and phantoms. You only ever read committed data, but the same query can change answer between statements.
• repeatable read forbids dirty and non-repeatable reads but, per ANSI, permits phantoms. Rows you read stay stable; new rows can still appear in a range.
• serializable forbids all three (and lost update and write skew). The execution is equivalent to some serial order of the transactions.
• The catch: engines are allowed to be stricter than ANSI. Postgres repeatable read also blocks phantoms; Postgres read uncommitted is silently promoted to read committed. The matrix is a floor, not a spec of behaviour.

What interviewers listen for.

• Do you define an anomaly by what the reader observes rather than what the writer does? — senior signal.
• Do you say "read committed is the Postgres default, repeatable read is the MySQL InnoDB default" without prompting? — required answer.
• Do you know that ANSI defines the floor and engines may be stricter (Postgres RR blocks phantoms)? — senior signal.
• Do you name lost update and write skew as anomalies the three-anomaly matrix omits? — senior signal.
• Do you frame the choice as correctness first, performance second — not "use the highest level everywhere"? — required answer.

Worked example — building the anomaly matrix from timelines

Detailed explanation. The single most useful artifact for an isolation interview is the anomaly-by-level matrix, reconstructed from two-transaction timelines rather than memorised. If you can derive it, you can answer any variant the interviewer throws at you. Walk through building it for a accounts table with a single row.

• Table. accounts(id BIGINT PK, balance BIGINT) with one row (1, 1000).
• Two sessions. T1 (the writer) and T2 (the reader), interleaved on a wall-clock timeline.
• The question per cell. "At level L, can anomaly A occur?" is answered by constructing the tightest interleaving that would expose A and checking whether level L allows it.

Question. Fill the 4×3 matrix of {level} × {dirty read, non-repeatable read, phantom} with "possible" or "blocked" for a spec-compliant engine.

Input.

Level	Dirty read	Non-repeatable read	Phantom read
read uncommitted	possible	possible	possible
read committed	blocked	possible	possible
repeatable read	blocked	blocked	possible (ANSI)
serializable	blocked	blocked	blocked

Code.

-- Timeline that EXPOSES a non-repeatable read (T2 at READ COMMITTED)
-- T2 session:
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;   -- (statement 1) -> 1000

-- ... meanwhile T1 commits an update ...
-- T1 session:
--   BEGIN;
--   UPDATE accounts SET balance = 500 WHERE id = 1;
--   COMMIT;

-- back in T2 (same transaction still open):
SELECT balance FROM accounts WHERE id = 1;   -- (statement 2) -> 500  (NON-REPEATABLE)
COMMIT;

-- Same timeline at REPEATABLE READ blocks it:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;   -- -> 1000
-- ... T1 commits balance = 500 ...
SELECT balance FROM accounts WHERE id = 1;   -- -> 1000  (STILL 1000 — repeatable)
COMMIT;

Step-by-step explanation.

1. To decide the "non-repeatable read at read committed" cell, construct the tightest interleaving: T2 reads, T1 commits an UPDATE, T2 reads again. Under read committed each statement takes a fresh snapshot, so the second SELECT sees T1's committed change — the anomaly is possible. Cell = "possible."
2. Rerun the identical interleaving under repeatable read. Now T2's snapshot is frozen at transaction start, so the second SELECT returns the original 1000 even though T1 committed 500. The anomaly is blocked. Cell = "blocked."
3. The dirty-read row is derived the same way but with T1 not committing between T2's reads. Only read uncommitted lets T2 see the uncommitted 500; every higher level blocks it. So the dirty-read column is "possible" only on the bottom rung.
4. The phantom column needs a range query and an INSERT rather than an UPDATE: T2 runs SELECT count(*) WHERE balance > 100, T1 inserts a matching row and commits, T2 re-runs the count. ANSI repeatable read permits the new row to appear; serializable forbids it.
5. Every cell in the matrix is a mechanical consequence of "when does this level take its snapshot, and does it take locks on ranges" — not something to rote-memorise. Derive it live and you will never be wrong about a variant.

Output.

Interleaving	read committed	repeatable read
read → T1 UPDATE+commit → read	500 (differs)	1000 (same)
read → T1 INSERT+commit → re-range	new row appears	ANSI: appears / Postgres: hidden
read uncommitted value mid-T1	never (blocked)	never (blocked)

Rule of thumb. Never memorise the matrix as a static table — memorise the rule that generates it: dirty read needs an uncommitted read, non-repeatable read needs a re-read of one row across a commit, phantom needs a re-range across an INSERT. Derive the cell from the timeline and the engine's snapshot policy.

Worked example — mapping anomalies to real production bugs

Detailed explanation. Interviewers love to ask "give me a concrete bug each anomaly causes" because it separates people who memorised definitions from people who have debugged concurrency in production. Walk through one real bug per anomaly on an e-commerce schema.

• Schema. wallets(user_id PK, balance), inventory(sku PK, qty), orders(id PK, user_id, total, created_at).
• The mapping. Each anomaly maps to a distinct class of correctness failure with a distinct fix.

Question. For each anomaly, name a production bug it causes and the cheapest correct fix.

Input.

Anomaly	Production bug	Cheapest fix
Dirty read	pay out against an uncommitted (later rolled-back) balance	never use read uncommitted
Non-repeatable read	total recomputed mid-transaction disagrees with the row it charged	repeatable read
Phantom read	"sum of all pending orders" changes mid-report	repeatable read (Postgres) / serializable
Lost update	two checkouts both decrement stock by 1, only one lands	SELECT FOR UPDATE or atomic UPDATE
Write skew	two withdrawals each pass "balance stays ≥ 0" jointly overdraw	serializable

Code.

-- LOST UPDATE bug (read-modify-write race)
-- Session A and Session B both run this concurrently at READ COMMITTED:
BEGIN;
SELECT qty FROM inventory WHERE sku = 'ABC';   -- both read 10
-- app computes 10 - 1 = 9
UPDATE inventory SET qty = 9 WHERE sku = 'ABC'; -- both write 9
COMMIT;
-- Result: two units sold, qty dropped by ONE. One update lost.

-- FIX 1 — atomic relative update (no read-modify-write):
UPDATE inventory SET qty = qty - 1 WHERE sku = 'ABC' AND qty >= 1;

-- FIX 2 — pessimistic lock so the second reader blocks:
BEGIN;
SELECT qty FROM inventory WHERE sku = 'ABC' FOR UPDATE;  -- B waits for A
UPDATE inventory SET qty = qty - 1 WHERE sku = 'ABC';
COMMIT;

Step-by-step explanation.

1. The dirty-read bug is the easiest to eliminate: no mainstream engine defaults to read uncommitted, and Postgres won't even honour it. If you never explicitly request it, you can never suffer a dirty read.
2. The lost-update bug is the most common concurrency bug in real codebases because the read-modify-write pattern feels natural in application code. At read committed, both sessions read 10, both write 9, and the second write clobbers the first — no error is raised. This is silent data loss.
3. Fix 1 rewrites the operation as a relative atomic UPDATE (qty = qty - 1), which the engine evaluates against the current row value under a row lock it takes automatically for the write — no explicit locking needed, and the qty >= 1 guard prevents overselling.
4. Fix 2 uses SELECT ... FOR UPDATE to take an explicit row lock at read time, so the second session blocks until the first commits, then reads the updated 9. This is the right tool when the new value depends on more than arithmetic (e.g. reading several columns to decide the write).
5. Write skew is the anomaly neither fix catches — the two transactions write different rows, so no single-row lock serialises them. Only serializable isolation (which tracks read/write dependencies across rows) detects the conflict and aborts one.

Output.

Anomaly	Silent or errors?	Fix cost
Dirty read	never occurs if you don't ask for RU	free
Non-repeatable read	silent (wrong value)	switch to repeatable read
Phantom read	silent (wrong aggregate)	repeatable read / serializable
Lost update	silent (data loss)	atomic UPDATE or FOR UPDATE
Write skew	silent (invariant broken)	serializable + retry

Rule of thumb. Match the anomaly to the bug, then pick the cheapest fix that closes it: relative atomic UPDATE for lost update, repeatable read for read-stability bugs, serializable for cross-row invariants. Reaching for serializable everywhere is over-insuring — it costs you retries you didn't need.

Worked example — the interview grading rubric for "explain isolation levels"

Detailed explanation. The open-ended "explain isolation levels" question has a predictable grading structure. Candidates who ladder from weakest to strongest, naming the anomaly each rung removes, score highest; candidates who list levels without the anomaly mapping score lowest. Walk through the rubric.

• Opener. "Walk me through the SQL isolation levels." — invites the ladder.
• Follow-up 1. "Which is your database's default?" — probes engine knowledge.
• Follow-up 2. "Give me an anomaly that default allows." — probes depth.
• Follow-up 3. "How would you stop it?" — probes the fix.

Question. Draft a two-minute answer that pre-empts every follow-up.

Input.

Signal	Weak answer	Senior answer
Ladder	lists 4 names	ladders weakest→strongest by anomaly removed
Default	"not sure"	"Postgres read committed; MySQL InnoDB repeatable read"
Anomaly at default	"none really"	"read committed allows non-repeatable reads and lost updates"
Fix	"raise the level"	names the cheapest correct fix per anomaly
Implementation	"it locks"	"Postgres uses MVCC snapshots, not read locks"

Code.

Two-minute isolation answer
===========================
1. "There are four ANSI levels, a ladder. Each rung forbids one more
    anomaly."
2. "Read uncommitted allows dirty reads — you see uncommitted data.
    Nobody uses it; Postgres won't even honour it."
3. "Read committed — the Postgres default — blocks dirty reads: you only
    read committed data. But the same query can change answer between
    statements (non-repeatable read), and read-modify-write can lose
    updates."
4. "Repeatable read — the MySQL InnoDB default — freezes a snapshot at
    transaction start, so re-reads are stable. ANSI still allows phantoms;
    Postgres RR blocks those too."
5. "Serializable is the strongest: the result equals some serial order.
    It blocks phantoms, lost updates, and write skew — at the cost of
    serialization failures the app must retry."
6. "Under the hood Postgres uses MVCC: readers see a snapshot of row
    versions and never block writers, so the levels differ mainly in
    WHEN the snapshot is taken and whether dependencies are tracked."

Step-by-step explanation.

1. Point 1 frames the levels as a ladder ordered by anomaly, not four unrelated modes. This is the framing senior interviewers reward — it shows you understand why there are exactly four.
2. Points 2–5 walk the ladder, naming the anomaly each rung removes. Crucially, each rung also names the default engine that ships it, pre-empting the "which is your default" follow-up.
3. Point 3 volunteers that read committed still allows lost updates — a detail weak candidates miss because lost update isn't in the three-anomaly ANSI matrix. Naming it unprompted is a strong signal.
4. Point 5 names the cost of serializable (serialization failures + retry), showing you understand that the strongest level is not free and that the application must be written to cooperate.
5. Point 6 connects the abstract levels to MVCC — "the levels differ in when the snapshot is taken." This is the bridge to the implementation question and demonstrates you know the mechanism, not just the taxonomy.

Output.

Rubric criterion	Weak score	Senior score
Ladders by anomaly	rare	mandatory
Names engine defaults	rare	required
Names lost update / write skew	rare	senior signal
Names serializable's retry cost	occasional	senior signal
Bridges to MVCC	rare	senior signal

Rule of thumb. Answer "explain isolation levels" as a ladder — weakest to strongest, one anomaly removed per rung, each rung tagged with the engine whose default it is — and finish by bridging to MVCC. Two minutes, no follow-up needed.

SQL Interview Question on isolation-level selection

A senior interviewer often opens with: "You are building a payments service on Postgres. A withdrawal reads the wallet balance, checks it is sufficient, then writes the decremented balance. Under concurrency, describe the bug at the default isolation level, name the anomaly, and walk me through the two correct fixes and when you would escalate all the way to serializable."

Solution Using an atomic guarded UPDATE, FOR UPDATE, and serializable escalation

-- The buggy read-modify-write (lost update at READ COMMITTED)
BEGIN;
SELECT balance FROM wallets WHERE user_id = 7;      -- both sessions read 1000
-- app checks 1000 >= 300, computes 1000 - 300 = 700
UPDATE wallets SET balance = 700 WHERE user_id = 7; -- both write 700
COMMIT;
-- Two 300 withdrawals; balance ends at 700 instead of 400. One lost.

-- FIX A — atomic guarded UPDATE (best when the write is arithmetic)
UPDATE wallets
SET    balance = balance - 300
WHERE  user_id = 7
  AND  balance >= 300;                 -- rowcount 0 => insufficient funds
-- Row lock is taken automatically; the second session re-evaluates
-- against the committed 700, then 400, never overdrawing.

-- FIX B — pessimistic lock (best when the decision needs multiple reads)
BEGIN;
SELECT balance FROM wallets WHERE user_id = 7 FOR UPDATE;  -- B blocks until A commits
-- now B reads the fresh committed value and decides safely
UPDATE wallets SET balance = balance - 300 WHERE user_id = 7;
COMMIT;

-- FIX C — serializable (needed when the invariant spans multiple rows)
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- e.g. "total across a user's sub-accounts must stay >= 0"
SELECT sum(balance) FROM wallets WHERE user_id = 7;
UPDATE wallets SET balance = balance - 300 WHERE user_id = 7 AND account = 'checking';
COMMIT;   -- may raise serialization_failure (40001) => retry the whole txn

Step-by-step trace.

Input — wallet row (user_id 7, balance 1000); two concurrent 300 withdrawals A and B.

1. Buggy path. A reads 1000, B reads 1000 (both under read committed, both see the same committed value). A writes 700, commits. B — which decided from its stale 1000 — writes 700, commits. The correct answer was 400; one withdrawal is lost.
2. Fix A. A runs balance = balance - 300 WHERE balance >= 300. The engine takes a row lock for the write; B's identical UPDATE waits for A to commit, then re-evaluates WHERE balance >= 300 against the now-committed 700, subtracts to 400, commits. Balance ends at 400. Correct.
3. Fix A, insufficient funds. If a third withdrawal races and balance is 100, its WHERE balance >= 300 matches zero rows — UPDATE reports rowcount 0, and the app treats that as "insufficient funds" without ever overdrawing.
4. Fix B. B's SELECT ... FOR UPDATE blocks on A's lock until A commits; B then reads the fresh 700, decides, writes 400. Same correct result, but B held a lock across an app round-trip — more contention than Fix A.
5. Fix C. For a cross-row invariant (sum across accounts), no single-row lock helps — that is write skew. serializable tracks the read of sum(balance) and the write, detects the dangerous dependency against a concurrent transaction, and aborts one with 40001; the app retries and the retried transaction sees the committed state.

Output:

Path	Final balance	Anomaly outcome
Buggy read-modify-write	700 (wrong)	lost update
Fix A — guarded atomic UPDATE	400	correct, no explicit lock
Fix B — SELECT FOR UPDATE	400	correct, holds row lock
Fix C — serializable	400 or retry	correct, may abort+retry

Why this works — concept by concept:

• Lost update — two read-modify-write transactions at read committed each read the same committed value and blind-write; the second overwrites the first with no error. It is not in the ANSI three-anomaly matrix, which is why juniors miss it.
• Guarded atomic UPDATE — SET balance = balance - 300 WHERE balance >= 300 evaluates the new value against the current row under the write's implicit row lock, so concurrent writers serialise on the row and the guard prevents overdraw. Cheapest correct fix.
• SELECT FOR UPDATE — a pessimistic row lock taken at read time; the second reader blocks until the first commits, converting a race into a queue. Use when the decision needs multiple column reads, not just arithmetic.
• Serializable escalation — for invariants spanning multiple rows (write skew), only dependency-tracking serializable isolation catches the conflict; it manifests as a 40001 abort the application must catch and retry.
• Cost — Fix A is O(1) with a brief row lock; Fix B adds lock-hold time across an app round-trip; Fix C adds retry loops proportional to the conflict rate. Escalate only as far up the ladder as the invariant demands.

SQL
Topic — sql
SQL transaction and concurrency problems

Practice →

Database Topic — database Database isolation-level problems

Practice →

---

2. Read Uncommitted and Read Committed

read uncommitted lets you see garbage; read committed guarantees committed-only reads with a fresh snapshot per statement

The mental model in one line: read uncommitted is the bottom rung where a transaction may observe another transaction's uncommitted writes (a dirty read), while read committed — the default on Postgres, Oracle, and SQL Server — guarantees every statement reads only committed data by taking a fresh snapshot at the start of each statement, which blocks dirty reads entirely but still permits non-repeatable reads and phantoms because two statements in the same transaction see two different snapshots. On Postgres the distinction is partly academic: read uncommitted is silently treated as read committed because MVCC never exposes uncommitted tuples, so Postgres simply cannot produce a dirty read even if you ask for one.

Read uncommitted — the rung almost nobody should use.

• What it permits. Dirty reads: you can see rows another transaction has written but not committed. If that transaction rolls back, you read a value that never existed in the durable database.
• Where it exists. SQL Server honours it literally (and it is the semantics behind the WITH (NOLOCK) hint). MySQL InnoDB honours it. Oracle does not offer it at all. Postgres accepts the syntax but promotes it to read committed.
• The one legitimate use. Coarse progress-monitoring or approximate dashboards where a transiently-wrong number is acceptable and you want to avoid taking any read locks on a lock-based engine (SQL Server). Never for anything that drives a decision.
• The trap. WITH (NOLOCK) on SQL Server is folklore-cargo-culted as "make the query faster." It can return rows twice, skip rows, or read half-written rows — a debugging nightmare that only shows up under load.

Read committed — the workhorse default.

• What it guarantees. Every statement sees a snapshot of all data committed before that statement began. No dirty reads, ever.
• The snapshot cadence. A new snapshot per statement. This is the crucial detail: statement 1 and statement 2 in the same transaction can see different committed states, because a transaction that committed between them becomes visible to statement 2.
• What it still permits. Non-repeatable reads (same row, different value across two statements) and phantoms (same range, different row set). Also lost updates for read-modify-write patterns.
• Why it is the default. It is the cheapest level that gives sane semantics: no dirty reads, no snapshot held across the whole transaction (so less bloat pressure), and readers never block writers under MVCC. For the vast majority of OLTP queries it is correct.

How read committed handles a concurrent UPDATE — the re-check surprise.

• The subtlety. When a read committed UPDATE finds a row that a concurrent transaction has locked and then committed a new value for, Postgres does not fail — it re-reads the latest committed version and re-applies the WHERE clause to it (an "EvalPlanQual" re-check).
• The consequence. An UPDATE ... WHERE balance >= 300 that started against balance = 1000 will, if a concurrent commit changed it to 200, re-evaluate against 200, fail the predicate, and update zero rows — silently correct for guarded updates.
• The gotcha. This re-check happens per-row and can make a single UPDATE see a mix of snapshots, which is why complex multi-row read-modify-write logic is safer at repeatable read.

Common interview probes on read committed.

• "What does read committed block?" — required answer: dirty reads only.
• "What does it still allow?" — non-repeatable reads, phantoms, lost updates.
• "When is the snapshot taken?" — at the start of each statement, not the transaction.
• "Does Postgres support read uncommitted?" — syntactically yes, behaviourally no (promoted to read committed).

Worked example — a dirty read that read committed prevents

Detailed explanation. The canonical dirty-read demonstration: T1 updates a balance but does not commit, T2 reads it. At read uncommitted (on an engine that honours it) T2 sees the uncommitted value; at read committed T2 sees the old committed value. Walk through both timelines on the accounts table.

• Setup. accounts(id, balance) with (1, 1000).
• T1. Decrements to 900, then rolls back.
• T2. Reads the balance while T1 is mid-transaction.

Question. Show what T2 reads under read uncommitted versus read committed, and why the difference matters for a payout.

Input.

Time	T1 (writer)	T2 (reader)
t0	BEGIN; UPDATE balance=900	—
t1	(uncommitted)	BEGIN; SELECT balance
t2	ROLLBACK	(uses value)
t3	—	COMMIT

Code.

-- T1 (writer) — never commits the change
BEGIN;
UPDATE accounts SET balance = 900 WHERE id = 1;
-- ... pause here, T1 has NOT committed ...
ROLLBACK;                       -- balance snaps back to 1000

-- T2 under READ UNCOMMITTED (SQL Server / MySQL honour this):
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE id = 1;   -- reads 900 (DIRTY) while T1 open
-- app pays out assuming 900... then T1 rolls back to 1000. Corruption.
COMMIT;

-- T2 under READ COMMITTED (Postgres default; also promotes READ UNCOMMITTED):
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;   -- reads 1000 (COMMITTED ONLY)
COMMIT;

Step-by-step explanation.

1. T1 opens a transaction and updates the balance to 900 but does not commit. Under MVCC this creates a new tuple version whose xmin is T1's still-uncommitted transaction id — invisible to any snapshot that does not belong to T1.
2. Under read uncommitted on a lock-based engine (SQL Server), T2's read is allowed to see T1's in-flight write, returning the dirty 900. On MVCC engines that honour RU, the same effect is achieved by ignoring commit status.
3. If T2 acts on the dirty 900 — say, authorises a payout that assumes only 900 is available — and T1 then rolls back to 1000, T2 made a decision on data that never existed durably. This is the archetypal correctness bug.
4. Under read committed, T2's snapshot excludes T1's uncommitted tuple, so SELECT returns the last committed value 1000. When T1 rolls back, nothing changes for T2 — it never saw 900.
5. On Postgres specifically, requesting read uncommitted yields identical behaviour to read committed: the engine's visibility rules structurally forbid reading uncommitted tuples, so a dirty read is impossible regardless of the requested level.

Output.

Level	T2 reads	If T1 rolls back
read uncommitted (SQL Server/MySQL)	900 (dirty)	acted on phantom value — bug
read committed	1000	no effect — correct
Postgres read uncommitted	1000 (promoted)	no effect — correct

Rule of thumb. Never request read uncommitted for anything that drives a decision — the dirty read it permits can act on data that gets rolled back. On Postgres you get committed-only reads for free; on SQL Server, resist the WITH (NOLOCK) cargo cult.

Worked example — a non-repeatable read under read committed

Detailed explanation. read committed blocks dirty reads but not non-repeatable reads. The demonstration: T2 reads a row, T1 commits an UPDATE to it, T2 reads it again in the same transaction and gets a different value. This breaks any transaction whose logic assumes a value it read earlier is still current. Walk through it.

• Setup. accounts(1, 1000).
• T2. Reads balance twice inside one transaction.
• T1. Commits an UPDATE between T2's two reads.

Question. Show the non-repeatable read and explain why report logic that reads a value twice can produce an inconsistent result.

Input.

Time	T1 (writer)	T2 (reader, read committed)
t0	—	BEGIN; SELECT balance -> 1000
t1	BEGIN; UPDATE balance=500; COMMIT	—
t2	—	SELECT balance -> 500 (differs!)
t3	—	COMMIT

Code.

-- T2 under READ COMMITTED — two reads of the same row
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT balance FROM accounts WHERE id = 1;   -- statement 1 -> 1000

-- ... T1 runs and COMMITS in the gap ...
--   BEGIN;
--   UPDATE accounts SET balance = 500 WHERE id = 1;
--   COMMIT;

SELECT balance FROM accounts WHERE id = 1;   -- statement 2 -> 500  (NON-REPEATABLE)

-- A report that assumed the first read is still valid now
-- charges/credits against two different numbers in one transaction.
COMMIT;

Step-by-step explanation.

1. T2 opens at read committed and reads balance = 1000. Under read committed, this snapshot is scoped to statement 1 only — it is discarded when the statement finishes.
2. T1 commits an UPDATE setting balance = 500 in the gap between T2's two statements. Because T1 has committed, its change is now part of the committed state visible to any new snapshot.
3. T2's second SELECT takes a fresh snapshot (that is what read committed does per statement), which includes T1's committed 500. T2 now reads 500 — a different value for the same row it just read as 1000.
4. The anomaly is "non-repeatable" because re-reading the same row did not repeat the same answer. Nothing T2 did caused it; a concurrent commit did. Any logic that read 1000 earlier and assumed it still holds is now inconsistent.
5. The fix is to open T2 at repeatable read, which freezes one snapshot at transaction start so both reads return 1000. The trade-off is that T2 no longer sees T1's committed change at all until it starts a new transaction.

Output.

Statement	Snapshot	Value read
SELECT #1	fresh (before T1 commit)	1000
(T1 commits 500)	—	—
SELECT #2	fresh (after T1 commit)	500
Same query, same txn	—	two answers → non-repeatable

Rule of thumb. If a transaction reads the same row more than once and must see a stable value, read committed is the wrong level — its per-statement snapshot lets committed updates leak in between reads. Escalate that transaction to repeatable read.

Worked example — read committed's per-statement snapshot in an aggregate

Detailed explanation. The per-statement snapshot also affects aggregates and multi-table reads within one transaction: two SELECTs that should agree can disagree because a concurrent commit landed between them. Walk through a balance-plus-ledger consistency check.

• Setup. wallets(user_id, balance) and ledger(user_id, delta); invariant: balance = sum(ledger.delta).
• T2. Reads balance, then reads sum(delta) — expecting them to match.
• T1. Commits a matched pair (balance update + ledger insert) between T2's two reads.

Question. Show how a per-statement snapshot makes a consistency check spuriously fail at read committed.

Input.

Component	Value
Initial balance	1000
Initial sum(ledger)	1000
T1 change	balance -> 1200, ledger += 200 (one txn)
T2 level	read committed

Code.

-- T2 consistency check under READ COMMITTED
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT balance FROM wallets WHERE user_id = 7;          -- -> 1000 (before T1)

-- ... T1 commits BOTH writes atomically in the gap ...
--   BEGIN;
--   UPDATE wallets SET balance = 1200 WHERE user_id = 7;
--   INSERT INTO ledger(user_id, delta) VALUES (7, 200);
--   COMMIT;

SELECT sum(delta) FROM ledger WHERE user_id = 7;        -- -> 1200 (after T1)

-- balance(1000) != sum(1200): the check FAILS even though the DB is
-- perfectly consistent — T2 straddled T1's commit with two snapshots.
COMMIT;

-- FIX: one snapshot for the whole check
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM wallets WHERE user_id = 7;          -- -> 1000
SELECT sum(delta) FROM ledger WHERE user_id = 7;        -- -> 1000 (same snapshot)
COMMIT;   -- check passes; both reads reflect the pre-T1 state

Step-by-step explanation.

1. T2's first read of balance happens before T1 commits, so it sees 1000. Under read committed this snapshot is thrown away after the statement.
2. T1 commits both writes atomically — the balance and the ledger stay mutually consistent in the durable database at all times. There is no moment where the invariant is actually violated.
3. T2's second read takes a new snapshot after T1's commit, so sum(delta) returns 1200. T2 now compares balance = 1000 (old snapshot) with sum = 1200 (new snapshot) and sees a mismatch that does not exist in any single consistent state.
4. This is the insidious face of the per-statement snapshot: the data is never inconsistent, but a multi-statement read at read committed can observe two different consistent states and conclude they are inconsistent.
5. Opening the check at repeatable read gives both statements the same transaction-start snapshot, so both reflect the pre-T1 state (1000 and 1000) and the check passes. Any read-only transaction that reads related data across multiple statements should use repeatable read for a coherent view.

Output.

Level	balance read	sum read	Check result
read committed	1000	1200	spurious failure
repeatable read	1000	1000	passes (coherent)

Rule of thumb. Multi-statement read-only work that must see a coherent cross-table view — reconciliations, consistency checks, exports — should run at repeatable read, not read committed. The per-statement snapshot is fine for single-statement OLTP and wrong for anything that reads related data more than once.

SQL Interview Question on read committed

A senior interviewer might ask: "Your reporting job runs several SELECTs in one transaction at the default Postgres isolation level and intermittently reports a balance that disagrees with the underlying ledger, even though every write is atomic. Explain what is happening, name the anomaly, and give the one-line fix without over-escalating isolation."

Solution Using a transaction-scoped snapshot at repeatable read

-- The buggy report (default = READ COMMITTED): each SELECT re-snapshots
BEGIN;   -- implicitly READ COMMITTED on Postgres
SELECT balance      FROM wallets WHERE user_id = 7;   -- snapshot A
SELECT sum(delta)   FROM ledger  WHERE user_id = 7;   -- snapshot B (may straddle a commit)
SELECT count(*)     FROM orders  WHERE user_id = 7;   -- snapshot C
COMMIT;
-- Snapshots A/B/C can each land on a different committed state,
-- so cross-checks between them intermittently disagree.

-- The fix: one consistent snapshot for the whole read set
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance      FROM wallets WHERE user_id = 7;   -- snapshot S
SELECT sum(delta)   FROM ledger  WHERE user_id = 7;   -- snapshot S (identical)
SELECT count(*)     FROM orders  WHERE user_id = 7;   -- snapshot S (identical)
COMMIT;

-- For a strictly read-only report, this is even clearer:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY;
-- ... same SELECTs ...
COMMIT;

Step-by-step trace.

Input — one user's wallets, ledger, orders; a concurrent transaction T1 commits a matched balance+ledger change mid-report.

1. Read committed, statement 1. Reads balance under snapshot A, taken at statement start. Sees the pre-T1 state, 1000.
2. T1 commits a balance→1200 + ledger+=200 change atomically. The durable DB stays consistent.
3. Read committed, statement 2. Reads sum(delta) under a new snapshot B, which now includes T1's commit → 1200. The report compares A's 1000 with B's 1200 and flags a phantom inconsistency.
4. Repeatable read, statement 1. Takes snapshot S at the first statement and pins it for the whole transaction.
5. Repeatable read, statement 2 and 3. Reuse snapshot S. Both sum(delta) and count(*) reflect the exact same committed state as statement 1, so the cross-checks agree. T1's concurrent commit is simply invisible until the report finishes and a new transaction begins.

Output:

Report step	read committed	repeatable read
balance	1000	1000
sum(delta)	1200 (straddled)	1000
cross-check	intermittent fail	always consistent
sees T1's commit?	partially	not until next txn

Why this works — concept by concept:

• Per-statement snapshot — read committed re-snapshots at the start of every statement, so a multi-statement transaction can observe several different committed states and mistake the seam between them for an inconsistency.
• Transaction-scoped snapshot — repeatable read takes one snapshot at the first query and reuses it for the whole transaction, giving every statement a single coherent view of the database.
• READ ONLY qualifier — marking the transaction READ ONLY documents intent and lets the planner skip write-path bookkeeping; combined with repeatable read it is the canonical "consistent report" recipe.
• No over-escalation — the fix stops at repeatable read; serializable would add serialization-failure retries the report does not need, since a read-only transaction cannot cause write skew.
• Cost — one snapshot held for the report's duration slightly delays the xmin horizon (mild vacuum pressure) but adds no locks and no retries; O(1) overhead versus the correctness win.

SQL
Topic — sql
SQL read-consistency and snapshot problems

Practice →

Data processing Topic — data-processing Data-processing consistency problems

Practice →

---

3. Repeatable Read

repeatable read freezes one snapshot for the whole transaction — re-reads are stable, but the phantom gap is where engines differ

The mental model in one line: repeatable read takes a single snapshot at the transaction's first query and reuses it for every subsequent read, so any row you read once returns the same value however many times you re-read it (blocking non-repeatable reads), and — per the ANSI standard — it still permits phantom reads where a concurrent INSERT makes a range query grow, except that Postgres implements repeatable read as full snapshot isolation and therefore also blocks phantoms, while MySQL InnoDB blocks them for locking reads via next-key locks but leaves subtle gaps for the classic write-skew anomaly. This is the level where "the standard says X but my engine does Y" bites hardest, and knowing your engine's exact behaviour is the difference between a correct system and a heisenbug.

What repeatable read guarantees.

• Stable row reads. Any row read once returns the same committed value for the rest of the transaction, no matter how many concurrent commits touch it. Non-repeatable reads are impossible.
• One snapshot, taken lazily. The snapshot is established at the first statement that reads data, not at BEGIN. This matters: work you do before the first query does not pin the snapshot.
• Writers' commits are invisible. A concurrent transaction that commits after your snapshot is established simply does not exist from your point of view until you commit and start a new transaction.

The phantom gap — where ANSI and engines diverge.

• ANSI RR permits phantoms. By the letter of SQL-92, a range query (WHERE amount > 500) can return additional rows on re-execution if a concurrent transaction commits a matching INSERT. The rows you already saw are stable; the set grows.
• Postgres RR blocks phantoms. Postgres implements repeatable read as snapshot isolation: the frozen snapshot hides all rows committed after it, including newly inserted ones. Re-running a range query returns the identical set. Postgres RR is strictly stronger than ANSI RR.
• MySQL InnoDB RR blocks phantoms for locking reads. Non-locking (plain) SELECTs use a consistent snapshot; locking reads (SELECT ... FOR UPDATE, ... LOCK IN SHARE MODE) use next-key locks (a record lock plus a gap lock) that prevent inserts into the scanned range. The two mechanisms can produce surprising mixes if you interleave locking and non-locking reads.
• The residual gap: write skew. Even engines that block phantoms at RR still permit write skew, because snapshot isolation lets two transactions read overlapping data and write disjoint rows without detecting the dependency. Only serializable closes this.

The serialization failure at RR — a write that can no longer be believed.

• When it fires. If a repeatable read transaction tries to UPDATE or DELETE a row that a concurrent transaction has already committed a change to since your snapshot, Postgres cannot safely apply your write against a stale snapshot and raises ERROR: could not serialize access due to concurrent update (SQLSTATE 40001).
• Why it is correct. Silently applying the write against the stale value would risk a lost update; aborting forces the application to retry against fresh state.
• The contract. Any code that writes at repeatable read (or serializable) must wrap the transaction in a retry loop that catches 40001 and re-runs.

Common interview probes on repeatable read.

• "What does repeatable read block that read committed doesn't?" — non-repeatable reads (and phantoms on Postgres).
• "Does repeatable read block phantoms?" — ANSI no; Postgres yes; MySQL yes for locking reads.
• "What's the MySQL InnoDB default?" — repeatable read.
• "What anomaly survives at repeatable read?" — write skew (needs serializable).

Worked example — a stable re-read that blocks the non-repeatable anomaly

Detailed explanation. The defining behaviour of repeatable read: read a row, let a concurrent transaction commit a change to it, read it again — and get the original value. Contrast directly with the read committed behaviour from section 2. Walk through it on accounts.

• Setup. accounts(1, 1000).
• T2. Reads balance twice at repeatable read.
• T1. Commits balance = 500 between the reads.

Question. Show that both of T2's reads return 1000 despite T1's committed change, and explain when T2 finally sees 500.

Input.

Time	T1 (writer)	T2 (repeatable read)
t0	—	BEGIN; SELECT balance -> 1000 (snapshot pinned)
t1	BEGIN; UPDATE balance=500; COMMIT	—
t2	—	SELECT balance -> 1000 (still!)
t3	—	COMMIT; (new txn) SELECT -> 500

Code.

-- T2 under REPEATABLE READ
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT balance FROM accounts WHERE id = 1;   -- -> 1000  (snapshot S pinned here)

-- ... T1 commits balance = 500 in the gap ...
--   BEGIN; UPDATE accounts SET balance = 500 WHERE id = 1; COMMIT;

SELECT balance FROM accounts WHERE id = 1;   -- -> 1000  (snapshot S still in force)
COMMIT;

-- Only a NEW transaction sees T1's committed change:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;   -- -> 500
COMMIT;

Step-by-step explanation.

1. T2's first SELECT establishes snapshot S, which captures the set of transactions committed before it. T1 has not yet committed, so its future change is not in S.
2. T1 commits balance = 500. Under MVCC this creates a new tuple version, but that version's xmin is after T2's snapshot, so it is invisible to S.
3. T2's second SELECT reuses snapshot S (the whole point of repeatable read) and therefore still resolves the row to the version visible in S — 1000. The re-read is repeatable.
4. T2 commits, ending snapshot S. Only when T2 opens a new transaction (and thus a new snapshot) does it observe T1's committed 500.
5. The contrast with section 2 is exact: identical interleaving, but read committed returned 500 on the second read (fresh per-statement snapshot) while repeatable read returns 1000 (pinned transaction snapshot).

Output.

Read	Snapshot	Value
SELECT #1	S (pinned)	1000
SELECT #2 (after T1 commit)	S (reused)	1000
SELECT in new txn	new	500

Rule of thumb. Use repeatable read whenever a transaction reads the same data more than once and must see it unchanged. Accept that you will not see concurrent commits until the transaction ends — that invisibility is the guarantee.

Worked example — the phantom range and the ANSI-vs-Postgres difference

Detailed explanation. The phantom is the anomaly repeatable read permits by ANSI but Postgres blocks. Demonstrate with a range aggregate: T2 counts rows over a predicate, T1 inserts a matching row and commits, T2 re-counts. Walk through what ANSI RR, Postgres RR, and serializable each return.

• Setup. orders(id, user_id, amount) with three rows over amount > 500.
• T2. Counts WHERE amount > 500 twice.
• T1. Inserts a fourth matching row and commits between the counts.

Question. Show the count under ANSI RR (phantom appears) versus Postgres RR (phantom hidden), and why the difference matters for a limit check.

Input.

Time	T1 (writer)	T2 (repeatable read)
t0	—	SELECT count(*) WHERE amount>500 -> 3
t1	INSERT amount=900; COMMIT	—
t2	—	SELECT count(*) WHERE amount>500 -> ?

Code.

-- T2 under REPEATABLE READ — a range aggregate read twice
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) FROM orders WHERE amount > 500;   -- -> 3

-- ... T1 inserts a matching row and commits ...
--   BEGIN; INSERT INTO orders(user_id, amount) VALUES (7, 900); COMMIT;

SELECT count(*) FROM orders WHERE amount > 500;
-- ANSI RR      -> 4   (PHANTOM: the new row appears)
-- Postgres RR  -> 3   (snapshot isolation hides the new row)
COMMIT;

Step-by-step explanation.

1. T2's first count evaluates amount > 500 and finds three rows. Under ANSI repeatable read, the standard only promises that rows already read stay stable — it says nothing about new rows entering the predicate range.
2. T1 inserts a row with amount = 900 (which matches the predicate) and commits. This is a phantom candidate: a row that did not exist for T2's first count but matches its predicate.
3. On a strictly ANSI-compliant engine, T2's second count returns 4 — the phantom row has appeared. The set membership changed even though no row T2 already saw was altered.
4. On Postgres, repeatable read is snapshot isolation: T2's frozen snapshot hides every tuple whose xmin is after the snapshot, including T1's inserted row. T2's second count returns 3. Postgres RR blocks the phantom the standard permits.
5. Why it matters: a "no more than N orders over $500 per user" limit check that counts, decides, then inserts is safe from phantoms on Postgres RR for the count stability, but is still exposed to write skew if two sessions each count 3, each decide "room for one more," and each insert — a gap only serializable closes.

Output.

Engine	count #1	count #2	Phantom?
ANSI-compliant RR	3	4	yes
Postgres RR (snapshot isolation)	3	3	no
Serializable (any engine)	3	3	no

Rule of thumb. Do not assume repeatable read blocks phantoms — it depends on the engine. Postgres RR does (snapshot isolation); ANSI RR does not. And even where phantoms are blocked, write skew survives, so a count-then-insert limit check across sessions still needs serializable.

Worked example — serialization failure on a concurrent update at RR

Detailed explanation. At repeatable read, if two transactions update the same row, the second to commit fails with 40001 rather than silently losing an update. Demonstrate the failure and the mandatory retry loop. Walk through both sessions and the application wrapper.

• Setup. accounts(1, 1000).
• T1 and T2. Both read then update the same row at repeatable read.
• Outcome. One commits; the other aborts with 40001 and must retry.

Question. Show the serialization failure and write the retry loop that makes the operation correct.

Input.

Component	Value
Isolation	repeatable read
Conflict	both UPDATE accounts id=1
Error on 2nd commit	40001 (could not serialize)
Fix	catch 40001, retry whole txn

Code.

-- T1 commits first
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;   -- ok
COMMIT;                                                       -- succeeds -> 900

-- T2 started against the SAME snapshot, tries to update the same row
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
-- ERROR:  could not serialize access due to concurrent update  (SQLSTATE 40001)
ROLLBACK;

# Application retry wrapper — mandatory for RR/SERIALIZABLE writers
import psycopg2
from psycopg2 import errorcodes

def withdraw(conn, user_id: int, amount: int, max_retries: int = 5) -> None:
    for attempt in range(max_retries):
        try:
            with conn:                       # BEGIN ... COMMIT / ROLLBACK
                with conn.cursor() as cur:
                    cur.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")
                    cur.execute(
                        "UPDATE accounts SET balance = balance - %s "
                        "WHERE id = %s AND balance >= %s",
                        (amount, user_id, amount),
                    )
            return                            # committed cleanly
        except psycopg2.errors.SerializationFailure:
            conn.rollback()
            if attempt == max_retries - 1:
                raise                         # give up after N tries
            continue                          # retry against fresh snapshot

Step-by-step explanation.

1. Both T1 and T2 open at repeatable read, each pinning a snapshot in which the row is 1000. Neither can see the other's uncommitted work.
2. T1 updates the row and commits, producing balance 900. T1's write is now committed against the version both snapshots started from.
3. T2 attempts to update the same row. Postgres detects that the row's committed version has advanced past T2's snapshot (T1 changed it), so applying T2's write would silently base it on stale data — a lost update. Postgres refuses and raises 40001.
4. Because the transaction aborted, T2 must retry. The withdraw wrapper catches SerializationFailure, rolls back, and re-runs the whole transaction. On retry, T2 gets a fresh snapshot in which the row is 900, re-evaluates balance >= amount, and either succeeds (→ 700) or, if funds are now insufficient, updates zero rows.
5. The retry loop is not optional: any writer at repeatable read or serializable will eventually hit 40001 under contention, and an application that does not retry surfaces it as a user-facing error.

Output.

Session	Action	Result
T1	UPDATE -100; COMMIT	900 (success)
T2 (attempt 1)	UPDATE -200	40001 abort
T2 (retry)	fresh snapshot sees 900; UPDATE -200	700 (success)

Rule of thumb. Every writer at repeatable read or serializable must be wrapped in a 40001 retry loop with bounded attempts and (ideally) small backoff. Treat serialization failures as expected under contention, not as errors to log and page on.

SQL Interview Question on repeatable read

A senior interviewer might ask: "You enforce 'a user may hold at most three active reservations' by counting current reservations, checking the count is under three, then inserting. It works in testing but occasionally a user ends up with four. You are on Postgres at repeatable read. Explain why RR does not save you, name the anomaly, and give the correct fix."

Solution Using serializable escalation with a retry loop (write skew closure)

-- The failing check at REPEATABLE READ (write skew survives)
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM reservations WHERE user_id = 7 AND active;   -- both sessions -> 3
-- both see 3, both decide "room for a 4th"
INSERT INTO reservations(user_id, active) VALUES (7, true);        -- both insert
COMMIT;   -- BOTH commit: user now has 5 reservations. Write skew.

-- The correct fix: SERIALIZABLE detects the read/write dependency
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM reservations WHERE user_id = 7 AND active;    -- read set tracked
-- if count < 3:
INSERT INTO reservations(user_id, active) VALUES (7, true);
COMMIT;   -- one commits; the other raises 40001 and must retry

# Retry wrapper around the serializable reservation check
import psycopg2

def add_reservation(conn, user_id: int, limit: int = 3, max_retries: int = 5) -> bool:
    for _ in range(max_retries):
        try:
            with conn:
                with conn.cursor() as cur:
                    cur.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                    cur.execute(
                        "SELECT count(*) FROM reservations "
                        "WHERE user_id = %s AND active", (user_id,))
                    if cur.fetchone()[0] >= limit:
                        return False                      # at limit; no insert
                    cur.execute(
                        "INSERT INTO reservations(user_id, active) "
                        "VALUES (%s, true)", (user_id,))
            return True
        except psycopg2.errors.SerializationFailure:
            conn.rollback()
            continue
    raise RuntimeError("too many serialization retries")

Step-by-step trace.

Input — user 7 has three active reservations; two sessions A and B both try to add a fourth concurrently.

1. RR path. A opens at repeatable read, counts 3. B opens at repeatable read, counts 3. Neither insert has happened yet, so both counts are 3.
2. RR path, decide. Both compare 3 < limit? No — 3 is not under the limit of 3, so this exact example blocks; but with the intended-limit logic ("at most three" meaning insert allowed while count < 3 was mis-stated as ≤), the same pattern with count 2 lets both insert a fourth. The point stands: both read the same count and both insert disjoint rows.
3. RR path, commit. Both INSERTs target different new rows, so no single-row conflict fires — RR happily commits both. The invariant "at most three" is now violated. This is textbook write skew: overlapping reads, disjoint writes.
4. Serializable path. A's transaction records that it read the set of active reservations for user 7 and then wrote into that set. B does the same concurrently. Postgres SSI tracks these read/write dependencies.
5. Serializable path, commit. The first transaction commits. When the second tries to commit, SSI detects a dangerous dependency cycle (each read a state the other invalidated) and aborts it with 40001. The retry re-runs, now sees the committed fourth row, counts 3 (or more), and correctly refuses.

Output:

Path	Session A	Session B	Invariant
repeatable read	inserts	inserts	violated (write skew)
serializable	commits	40001 → retry → refuses	preserved

Why this works — concept by concept:

• Write skew — two transactions read an overlapping set, each verifies a constraint, then each writes a disjoint row that jointly breaks the constraint; no row is updated twice, so row locks and RR do not catch it.
• Snapshot isolation gap — repeatable read (snapshot isolation) freezes reads but does not track the dependency between one transaction's reads and another's writes, which is exactly the information needed to detect write skew.
• Serializable Snapshot Isolation (SSI) — Postgres serializable layers read/write dependency tracking on top of snapshot isolation and aborts one transaction in any cycle that could not occur in a serial order.
• Mandatory retry loop — SSI signals conflicts as 40001 aborts, so the application must catch and re-run; the retried transaction sees the committed state and makes the correct decision.
• Cost — SSI adds per-transaction predicate/read tracking (bounded memory) plus retries proportional to the true conflict rate; for a genuine cross-row invariant it is the only correct level and the cost is unavoidable.

SQL
Topic — sql
SQL snapshot and phantom-read problems

Practice →

Database Topic — database Database locking and MVCC problems

Practice →

---

4. Serializable

serializable is the strongest guarantee — the result equals some serial order — and its price is serialization failures you must retry

The mental model in one line: serializable guarantees that the concurrent execution of a set of transactions produces exactly the same result as some one-at-a-time serial ordering of them, blocking every anomaly including phantom reads, lost updates, and write skew, and Postgres achieves this with Serializable Snapshot Isolation (SSI) — snapshot isolation plus runtime tracking of read/write dependencies — while lock-based engines (older SQL Server, DB2) achieve it with strict two-phase locking and range locks; either way the application must be prepared for transactions to abort with a serialization failure and retry. serializable is the level you reach for when correctness across multiple rows or aggregates is non-negotiable and you would rather pay retries than reason about every possible interleaving.

What serializable guarantees.

• Equivalence to a serial order. Whatever interleaving actually ran, the committed result is identical to running the transactions in some sequence with no overlap. You never have to reason about interleavings — only about the transaction in isolation.
• Every anomaly blocked. Dirty reads, non-repeatable reads, phantoms, lost updates, and write skew are all impossible. If a set of concurrent transactions could produce a non-serializable result, at least one is aborted.
• Not a guaranteed order. "Some serial order" does not mean the order you expect. Serializable prevents anomalies; it does not promise T1 runs before T2.

Postgres SSI — how serializable works without heavy locking.

• Snapshot isolation base. Every serializable transaction starts as a snapshot-isolation transaction (same reads as repeatable read), so readers still never block writers.
• Dependency tracking. Postgres records the read/write dependencies between concurrent transactions using lightweight predicate locks (SIReadLocks) — these do not block anyone; they only record "this transaction read data that another might invalidate."
• Dangerous-structure detection. When the committed dependency graph would contain a cycle that no serial order could produce, SSI aborts one transaction in the cycle with 40001. This is the "safe retry" mechanism.
• False positives are possible. SSI is conservative: it may abort a transaction that would actually have been fine, trading a few unnecessary retries for never missing a real conflict. Higher contention means more aborts.

Two-phase locking — the other implementation.

• Strict 2PL. Lock-based engines acquire shared locks on reads and exclusive locks on writes, holding all locks until commit. Serializability falls out of the locking discipline.
• Range / predicate locks. To block phantoms, the engine locks not just rows but ranges (key ranges, gap locks, next-key locks), so a concurrent INSERT into a scanned range blocks.
• The trade-off. 2PL blocks more (readers can block writers and vice versa) and can deadlock; SSI aborts more (retries) but keeps readers non-blocking. Postgres chose SSI; MySQL InnoDB's serializable is closer to lock-based (it turns plain SELECTs into locking reads).

Common interview probes on serializable.

• "What does serializable guarantee?" — equivalence to some serial execution; all anomalies blocked.
• "How does Postgres implement it?" — SSI (snapshot isolation + dependency tracking), not locking.
• "What must the application do?" — catch 40001 and retry.
• "When would you NOT use serializable?" — hot single-row counters where atomic UPDATE or FOR UPDATE is cheaper and retries would thrash.

Worked example — write skew blocked by serializable (the doctors problem)

Detailed explanation. The canonical write-skew scenario: two on-call doctors, an invariant "at least one doctor must remain on call," and two transactions that each check the invariant and each go off-call. At repeatable read both succeed and nobody is on call; at serializable one is aborted. Walk through it.

• Setup. doctors(name PK, on_call BOOLEAN) with Alice and Bob both on_call = true.
• Invariant. count(*) WHERE on_call must stay >= 1.
• Two transactions. Alice's session and Bob's session each try to go off call concurrently.

Question. Show that repeatable read violates the invariant and serializable preserves it.

Input.

Time	Alice's txn	Bob's txn
t0	count on_call -> 2	count on_call -> 2
t1	sees 2 >= 2, sets Alice off	sees 2 >= 2, sets Bob off
t2	COMMIT	COMMIT

Code.

-- REPEATABLE READ — write skew slips through
-- Alice's session:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call;          -- -> 2
-- 2 >= 1 after removing me? (2-1=1) ok, safe to go off
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;

-- Bob's session (concurrent, same snapshot count of 2):
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call;          -- -> 2 (didn't see Alice)
UPDATE doctors SET on_call = false WHERE name = 'Bob';
COMMIT;
-- Result: BOTH off call. Invariant "at least one on call" VIOLATED.

-- SERIALIZABLE — the dependency is detected
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call;          -- read set tracked
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;   -- one commits; the other -> 40001 -> retry -> sees count 1 -> refuses

Step-by-step explanation.

1. Both sessions open at repeatable read and count 2 doctors on call. Each snapshot is independent; neither sees the other's uncommitted change.
2. Alice's transaction reasons "removing me leaves 2 - 1 = 1, still ≥ 1" and sets Alice off call. Bob's transaction reasons identically about Bob. Each update targets a different row.
3. Because the two UPDATEs touch different rows, there is no single-row write conflict — repeatable read commits both. The result is zero doctors on call: the invariant is violated even though each transaction, in isolation, preserved it.
4. This is write skew: overlapping reads (both read the on-call set), disjoint writes (Alice's row vs Bob's row), and a constraint over the set that neither transaction alone can see being broken.
5. At serializable, Postgres records that each transaction read the on-call set and then wrote into it. When the second commits, SSI sees a dependency cycle that no serial order allows (in any serial order, the second transaction would have counted 1 and refused) and aborts it with 40001. The retry counts 1 and correctly declines.

Output.

Level	Alice	Bob	On-call count
repeatable read	off	off	0 (violated)
serializable	off	40001 → retry → stays on	1 (preserved)

Rule of thumb. Any invariant defined over a set of rows (at least one on call, sum ≥ 0, at most N active) that transactions check-then-modify is a write-skew hazard. Row locks and repeatable read do not close it — only serializable does.

Worked example — SSI conflict detection and the abort decision

Detailed explanation. Understanding which transaction SSI aborts and why helps you design retry logic and predict throughput. SSI aborts based on read/write dependency edges forming a "dangerous structure," not on lock ordering. Walk through a two-transaction dependency graph.

• Setup. Two transactions T1 and T2 with a read/write dependency in each direction.
• The rule. SSI aborts a transaction when a "pivot" has both an inbound and outbound rw-dependency, forming a potential cycle.

Question. Trace the dependency edges for a write-skew pair and identify which transaction aborts.

Input.

Edge	Meaning
T1 -rw-> T2	T1 read data T2 then wrote
T2 -rw-> T1	T2 read data T1 then wrote
pivot	transaction with in + out rw edges
abort	first committer wins; later one aborts

Code.

-- T1 and T2 each read a set the other writes into
-- T1:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT sum(amount) FROM postings WHERE batch = 10;   -- reads batch-10 set
INSERT INTO postings(batch, amount) VALUES (10, 50); -- writes into batch-10 set

-- T2 (concurrent):
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT sum(amount) FROM postings WHERE batch = 10;   -- reads batch-10 set
INSERT INTO postings(batch, amount) VALUES (10, 70); -- writes into batch-10 set

-- Whichever commits first wins:
-- T1: COMMIT;   -> succeeds
-- T2: COMMIT;   -> ERROR 40001 (could not serialize access
--                    due to read/write dependencies among transactions)

Step-by-step explanation.

1. T1 reads the batch-10 set (via sum) and then inserts into it. T2 does the same. Each transaction has read data that the other transaction writes — a read/write (rw) dependency in both directions.
2. SSI models this as a graph: T1 -rw-> T2 (T1 read something T2 wrote) and T2 -rw-> T1. A transaction that has both an incoming and an outgoing rw-edge is a "pivot" — the structure that can create a non-serializable cycle.
3. Postgres does not abort eagerly at the read; it records the dependencies with non-blocking predicate locks and lets both transactions run. The decision is deferred to commit time.
4. The first transaction to commit wins unconditionally. When the second transaction attempts to commit and Postgres sees the dangerous structure completed by that commit, it aborts the second with 40001, citing read/write dependencies.
5. This "first committer wins, later committer aborts" policy is why retry logic works cleanly: the aborted transaction re-runs after the winner's changes are committed and visible, so it makes its decision against the new state.

Output.

Transaction	Commits first?	Outcome
T1	yes	success
T2	no	40001 abort → retry
retried T2	—	sees T1's insert; recomputes; commits

Rule of thumb. SSI's policy is "first committer wins." Design retries to be idempotent-on-recompute — the retried transaction must re-read and re-decide against fresh state, never blindly re-apply the aborted transaction's intended write.

Worked example — the retry loop with backoff for serializable throughput

Detailed explanation. Under contention, serializable transactions abort and retry; without backoff, retries can thrash and collapse throughput. The production pattern is a bounded retry loop with exponential backoff and jitter, plus a metric on retry counts. Walk through it.

• The loop. Catch 40001 (and 40P01 deadlock), roll back, sleep with jittered backoff, retry up to N times.
• The metric. Track retries per operation; a rising retry rate is the early-warning that a hot key needs a different design.

Question. Write the production-grade serializable retry wrapper and explain each guard.

Input.

Parameter	Value
Retryable codes	40001 (serialization), 40P01 (deadlock)
Max retries	5
Backoff	exponential + jitter
Metric	retries_total counter

Code.

import random
import time
import psycopg2
from psycopg2 import errorcodes

RETRYABLE = {errorcodes.SERIALIZATION_FAILURE, errorcodes.DEADLOCK_DETECTED}

def run_serializable(conn, op, *, max_retries: int = 5, base: float = 0.01):
    """Run `op(cur)` in a SERIALIZABLE txn with bounded, jittered retries."""
    for attempt in range(max_retries):
        try:
            with conn:                                   # BEGIN / COMMIT
                with conn.cursor() as cur:
                    cur.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                    result = op(cur)                      # caller's read+write logic
            metrics_retries.observe(attempt)             # 0 on first-try success
            return result
        except psycopg2.Error as e:
            conn.rollback()
            if e.pgcode not in RETRYABLE or attempt == max_retries - 1:
                raise                                    # non-retryable or exhausted
            # exponential backoff with full jitter
            sleep = random.uniform(0, base * (2 ** attempt))
            time.sleep(sleep)
    raise RuntimeError("unreachable")

Step-by-step explanation.

1. The RETRYABLE set holds the two SQLSTATE codes worth retrying: 40001 (serialization failure) and 40P01 (deadlock). Any other error — constraint violation, syntax error — is a real bug and must not be retried.
2. Each attempt opens a fresh transaction, sets serializable, and runs the caller's op(cur) which does the read-then-write logic. On clean commit the wrapper records attempt (the number of prior retries) as a metric and returns.
3. On error the wrapper rolls back, then checks the SQLSTATE. If it is not retryable or the retry budget is exhausted, it re-raises — the caller sees the real failure rather than an infinite loop.
4. For a retryable error within budget, the wrapper sleeps for a jittered exponential backoff (random.uniform(0, base * 2**attempt)). Jitter is essential: without it, many contending transactions retry in lockstep and re-collide, amplifying the storm.
5. The retry metric is the operational signal. A retry rate that climbs with load means a hot key or a design that forces serialization; the fix is usually to move the invariant into a single atomic UPDATE or a dedicated counter table rather than raising retry limits.

Output.

Scenario	Behaviour
First-try success	commit; retries metric = 0
One 40001	rollback, jittered sleep, retry → success
Persistent conflict	up to 5 retries, then raise
Non-retryable error	rollback, raise immediately

Rule of thumb. Serializable without a retry loop is a latent outage. Ship the loop with bounded retries, exponential backoff with jitter, and a retry-rate metric — and treat a rising retry rate as a signal to redesign the hot path, not to raise the retry cap.

SQL Interview Question on serializable

A senior interviewer might ask: "You must enforce 'the total balance across a customer's checking and savings accounts never goes negative' while both accounts can be debited by concurrent transactions. SELECT ... FOR UPDATE on the debited row does not prevent the overdraft. Explain why, and design the correct solution on Postgres including the failure-handling contract."

Solution Using serializable with dependency tracking and a bounded retry loop

-- WHY FOR UPDATE fails: it locks only the row you debit, not the row you read
-- Session A debits checking; Session B debits savings; each reads BOTH,
-- each locks only its own target row -> no lock conflict -> both commit -> overdraft.

-- CORRECT: SERIALIZABLE tracks that each txn READ the sum across both rows
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- read the invariant's inputs (both accounts)
SELECT sum(balance) AS total
FROM   accounts
WHERE  customer_id = 42;                    -- e.g. total = 100 (checking 60 + savings 40)

-- app checks: total - debit >= 0 ?  (100 - 80 = 20 ok)
UPDATE accounts
SET    balance = balance - 80
WHERE  customer_id = 42 AND account = 'checking';

COMMIT;   -- if a concurrent debit on savings would jointly overdraw,
          -- one txn aborts with 40001 and retries against fresh totals

def debit(conn, customer_id: int, account: str, amount: int) -> bool:
    def op(cur):
        cur.execute(
            "SELECT coalesce(sum(balance),0) FROM accounts WHERE customer_id = %s",
            (customer_id,))
        total = cur.fetchone()[0]
        if total - amount < 0:
            return False                              # would overdraw; refuse
        cur.execute(
            "UPDATE accounts SET balance = balance - %s "
            "WHERE customer_id = %s AND account = %s",
            (amount, customer_id, account))
        return True
    return run_serializable(conn, op)                 # retry wrapper from earlier

Step-by-step trace.

Input — customer 42 has checking 60, savings 40 (total 100); Session A debits checking 80, Session B debits savings 80, concurrently.

1. FOR UPDATE attempt. A reads both balances, locks only the checking row it will debit. B reads both balances, locks only the savings row it will debit. The two locks are on different rows, so neither blocks — both proceed.
2. FOR UPDATE overdraft. A sees total 100, debits checking to -20? No — A debits 80 from checking 60, leaving -20 unless guarded; even guarding each row individually, A sees total 100 and thinks 100 - 80 ≥ 0. B independently sees total 100, debits savings 80. Joint total is 100 - 160 = -60. Overdraft, no lock conflict. Write skew again.
3. Serializable, reads tracked. A and B each SELECT sum(balance) — SSI records that each read the set of both account rows for customer 42.
4. Serializable, writes tracked. A updates checking; B updates savings. Each has written into the set the other read: a bidirectional rw-dependency, the dangerous structure.
5. Serializable, commit. The first to commit (say A) succeeds; balance total becomes 20. B's commit completes a non-serializable cycle, so SSI aborts B with 40001. B retries, re-reads the total (20), computes 20 - 80 < 0, and correctly refuses. No overdraft.

Output:

Approach	Session A	Session B	Total balance
SELECT FOR UPDATE	commits	commits	-60 (overdraft)
serializable	commits (total 20)	40001 → retry → refuses	20 (safe)

Why this works — concept by concept:

• Row lock insufficiency — SELECT ... FOR UPDATE locks only the rows it names; a cross-row invariant read from rows you do not lock is invisible to the lock manager, so two disjoint writes race through.
• Predicate-read tracking — Postgres SSI records the read set (the sum over both accounts) with non-blocking SIReadLocks, capturing exactly the dependency that row locks miss.
• Dangerous structure abort — when both transactions have read-into-then-written the shared set, SSI detects the rw-cycle at commit and aborts the second committer with 40001.
• Bounded retry with recompute — the retried transaction re-reads the now-committed total and re-decides, so the invariant holds without the application enumerating interleavings.
• Cost — SSI adds read-set tracking (bounded per-transaction memory) and retries proportional to true conflict rate; for a genuine multi-row invariant it is the only correct tool, and its cost is strictly less than the cost of a silent overdraft.

SQL
Topic — sql
SQL serializable and write-skew problems

Practice →

Defensive coding Topic — defensive-coding Defensive-coding retry and invariant problems

Practice →

---

5. MVCC — how engines implement isolation

mvcc makes every isolation level a snapshot rule over versioned rows — readers never block writers, and vacuum keeps the version chain from bloating

The mental model in one line: Multi-Version Concurrency Control (mvcc) implements isolation by keeping multiple versions of every row — in Postgres each version (tuple) carries an xmin (the transaction id that created it) and an xmax (the transaction id that expired it) — so a transaction's snapshot is just a rule that picks, for each logical row, the one version that was committed before the snapshot and not yet expired, which lets readers see a stable point-in-time view without taking any read locks (readers never block writers, writers never block readers) at the cost of accumulating dead versions that a background VACUUM must reclaim. Every isolation level you have read about is, underneath, a policy for when a transaction takes its snapshot and which dependencies it tracks — the version machinery is identical.

Row versions — xmin, xmax, and the tuple chain.

• xmin. The transaction id (xid) that inserted this tuple version. A tuple is only visible if its xmin committed and is visible to your snapshot.
• xmax. The transaction id that deleted or updated this tuple version (0 / null if still live). An UPDATE writes a new tuple and sets the old tuple's xmax to the updating xid — the old version lingers for snapshots that still need it.
• The chain. One logical row can have many physical versions at once: the current live one plus older dead ones still visible to long-running snapshots. SELECT xmin, xmax, ctid FROM t exposes them.
• No in-place update. Postgres never overwrites a row in place; every UPDATE is an insert-new-plus-expire-old. This is why UPDATE-heavy tables bloat and why HOT (heap-only tuple) updates and fillfactor matter.

Snapshots — the visibility rule.

• What a snapshot is. A record of "which transactions had committed at the moment I was taken": conceptually xmin (oldest still-running xid), xmax (first not-yet-assigned xid), and the list of in-progress xids (xip_list).
• Visibility test. A tuple is visible to a snapshot if its inserting xmin is committed and ≤ snapshot (and not in the in-progress list) and its xmax is null, aborted, or > snapshot (i.e. not yet expired from the snapshot's point of view).
• Level = snapshot cadence. read committed takes a new snapshot per statement; repeatable read / serializable take one per transaction. That single difference produces all the behavioural differences between the levels.
• Readers don't block writers. Because a reader resolves each row to a committed version via the snapshot, it never needs a lock on the row a writer is changing — the writer just creates a new version the reader ignores.

The xmin horizon and long transactions.

• The horizon. The oldest snapshot xmin across all active transactions. Any dead tuple newer than the horizon might still be needed by some running snapshot, so it cannot be reclaimed.
• Long transactions pin the horizon. A transaction that stays open for an hour holds the horizon back an hour, so every dead tuple produced in that hour is un-reclaimable — the classic cause of runaway bloat.
• Idle-in-transaction is the villain. A connection that runs BEGIN, does one query, then sits idle holds a snapshot and pins the horizon. idle_in_transaction_session_timeout exists to kill these.

VACUUM — reclaiming dead tuples.

• What it does. Scans tables for tuples that are dead to all snapshots (older than the xmin horizon) and marks their space reusable; updates the visibility map and the free space map.
• Autovacuum. A background process triggered by row-change thresholds. It also prevents transaction-id wraparound by freezing very old tuples.
• What it does not do (usually). Plain VACUUM does not return disk to the OS — it makes space reusable within the table. VACUUM FULL rewrites the table and returns space but takes an exclusive lock.
• Bloat symptoms. Table/index size far exceeding live-row size, slow scans, autovacuum "not keeping up" warnings — almost always traced to long transactions or high UPDATE churn.

Common interview probes on MVCC.

• "How do readers avoid blocking writers?" — snapshots over row versions; readers resolve to a committed version.
• "What are xmin and xmax?" — creating xid and expiring xid of a tuple version.
• "Why do long transactions cause bloat?" — they pin the xmin horizon so vacuum can't reclaim dead tuples.
• "What does vacuum do?" — reclaims dead tuples for reuse and prevents xid wraparound.

Worked example — the xmin/xmax lifecycle of an updated row

Detailed explanation. The clearest way to internalise MVCC is to watch xmin/xmax change across an INSERT, UPDATE, and DELETE using Postgres's system columns. Walk through the lifecycle of a single logical row.

• Setup. items(id, name); observe xmin, xmax, ctid after each DML.
• The point. One logical row becomes several physical tuples; the snapshot picks which one you see.

Question. Show how xmin/xmax evolve across INSERT → UPDATE → DELETE and how a concurrent old snapshot still sees the pre-update version.

Input.

DML	Effect on tuples
INSERT	new tuple, xmin=T_ins, xmax=0
UPDATE	old tuple xmax=T_upd; new tuple xmin=T_upd, xmax=0
DELETE	live tuple xmax=T_del (still on disk until vacuum)
VACUUM	reclaims tuples dead to all snapshots

Code.

-- Inspect MVCC system columns directly
CREATE TABLE items (id INT PRIMARY KEY, name TEXT);

INSERT INTO items VALUES (1, 'alpha');
SELECT xmin, xmax, ctid, * FROM items WHERE id = 1;
-- xmin=1001 xmax=0 ctid=(0,1) | 1 | alpha    (one live version)

UPDATE items SET name = 'beta' WHERE id = 1;
SELECT xmin, xmax, ctid, * FROM items WHERE id = 1;
-- xmin=1002 xmax=0 ctid=(0,2) | 1 | beta     (NEW tuple; old (0,1) now has xmax=1002)

-- A concurrent REPEATABLE READ txn that snapshotted before 1002
-- still resolves id=1 to the OLD tuple (0,1) 'alpha'.

DELETE FROM items WHERE id = 1;
-- the live tuple (0,2) gets xmax = <delete xid>; row still physically present.

VACUUM items;   -- once no snapshot needs them, dead tuples (0,1)/(0,2) are reclaimed

Step-by-step explanation.

1. The INSERT creates one physical tuple at ctid (0,1) with xmin = 1001 (the inserting transaction) and xmax = 0 (not expired). Any snapshot after 1001 commits sees alpha.
2. The UPDATE does not modify the existing tuple. It writes a new tuple at (0,2) with xmin = 1002 (the updating transaction) and sets the old tuple (0,1)'s xmax = 1002, marking it expired as of transaction 1002.
3. Now two physical tuples exist for id = 1. A transaction whose snapshot predates 1002 (e.g. a repeatable read transaction that started earlier) still sees (0,1) alpha, because for its snapshot the old tuple's xmax is "in the future" and the new tuple's xmin is invisible.
4. The DELETE sets xmax on the live tuple (0,2) to the deleting xid. The row is logically gone but physically remains on the page until vacuum, so snapshots that still need it can read it.
5. VACUUM runs later and reclaims tuples whose xmax is committed and older than the xmin horizon (dead to every snapshot). Until then the dead tuples occupy space — the mechanism behind bloat.

Output.

Step	Live tuple seen by new snapshot	Tuples on page
after INSERT	(0,1) alpha	1
after UPDATE	(0,2) beta	2 (one dead)
after DELETE	none	2 (both dead)
after VACUUM	none	0 (reclaimed)

Rule of thumb. Remember "UPDATE = insert new + expire old." Every UPDATE leaves a dead tuple behind, so UPDATE-heavy tables need aggressive autovacuum and a sensible fillfactor to enable HOT updates — otherwise the version chain and its indexes bloat.

Worked example — snapshot visibility across two isolation levels

Detailed explanation. The same version chain produces different reads depending on when the snapshot is taken. Show a concurrent UPDATE and how read committed versus repeatable read resolve the row through the visibility rule. Walk through it.

• Setup. items(1, 'alpha'); a writer updates it to 'beta' and commits.
• Two readers. One at read committed, one at repeatable read, each reading before and after the writer's commit.

Question. Show which tuple version each reader resolves and tie it to the xmin/xmax visibility test.

Input.

Reader	Snapshot cadence	Sees before commit	Sees after commit
read committed	per statement	alpha	beta
repeatable read	per transaction	alpha	alpha

Code.

-- Writer:
BEGIN;
UPDATE items SET name = 'beta' WHERE id = 1;   -- new tuple xmin=W; old xmax=W
-- (not committed yet)

-- Reader R1 at READ COMMITTED:
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT name FROM items WHERE id = 1;           -- -> alpha (W uncommitted, hidden)
--   ... writer COMMITs ...
SELECT name FROM items WHERE id = 1;           -- -> beta  (fresh snapshot sees W)
COMMIT;

-- Reader R2 at REPEATABLE READ (snapshot taken before writer commits):
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT name FROM items WHERE id = 1;           -- -> alpha (snapshot S pinned)
--   ... writer COMMITs ...
SELECT name FROM items WHERE id = 1;           -- -> alpha (S still hides W's new tuple)
COMMIT;

Step-by-step explanation.

1. The writer's UPDATE creates a new tuple with xmin = W and expires the old one with xmax = W, but W is uncommitted, so to every other snapshot the new tuple is invisible and the old tuple is not yet expired.
2. R1 (read committed) reads before the writer commits: its snapshot sees the old tuple (xmax = W is uncommitted → treat as not expired) and returns alpha. The new tuple's xmin = W is uncommitted → invisible.
3. After the writer commits, R1's next statement takes a fresh snapshot in which W is committed and ≤ snapshot. Now the visibility test flips: the old tuple's xmax = W is committed-and-past → expired → hidden; the new tuple's xmin = W is committed-and-past → visible. R1 returns beta.
4. R2 (repeatable read) took snapshot S before the writer committed. S records W as in-progress. Even after W commits, S's frozen membership still treats W as invisible, so R2 keeps resolving the row to the old tuple and returns alpha on both reads.
5. The two readers differ only in snapshot cadence — the version chain and the visibility test are identical. This is the concrete mechanism behind "read committed sees committed changes; repeatable read freezes the view."

Output.

Reader	Read #1	Writer commits	Read #2
read committed	alpha	—	beta
repeatable read	alpha	—	alpha

Rule of thumb. The isolation level is not a different storage engine — it is a different snapshot cadence over the same xmin/xmax version chain. Reason about any anomaly by asking "what does this snapshot's visibility test resolve each tuple to?"

Worked example — a long transaction that bloats the table via a pinned horizon

Detailed explanation. The most common MVCC production incident: a long-running or idle-in-transaction session pins the xmin horizon, so vacuum cannot reclaim dead tuples and the table (and its indexes) bloat. Walk through the diagnosis and fix.

• Symptom. Table size grows while live-row count is flat; queries slow down; autovacuum logs show "removable cutoff" not advancing.
• Root cause. One old snapshot (a long transaction or idle in transaction session) holds the horizon back.

Question. Diagnose the pinned horizon and write the queries and settings that resolve it.

Input.

Signal	Where to look
Oldest snapshot age	pg_stat_activity (xact_start, state)
Bloat	live tuples vs relation size
Vacuum blocked	autovacuum "cutoff" not advancing
Guard	idle_in_transaction_session_timeout

Code.

-- 1. Find the oldest transaction pinning the horizon
SELECT pid,
       state,
       now() - xact_start           AS txn_age,
       now() - state_change         AS since_state_change,
       left(query, 60)              AS query
FROM   pg_stat_activity
WHERE  state <> 'idle'
   OR  state = 'idle in transaction'
ORDER  BY xact_start ASC NULLS LAST
LIMIT  5;

-- 2. See how far back the horizon is held (oldest xmin)
SELECT backend_xmin,
       age(backend_xmin) AS xmin_age
FROM   pg_stat_activity
WHERE  backend_xmin IS NOT NULL
ORDER  BY age(backend_xmin) DESC
LIMIT  5;

-- 3. Terminate a stuck idle-in-transaction session (surgical fix)
SELECT pg_terminate_backend(<pid>);

-- 4. Prevent recurrence: cap idle-in-transaction sessions
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

-- 5. After the horizon advances, vacuum can finally reclaim
VACUUM (VERBOSE) big_table;

Step-by-step explanation.

1. Query 1 lists sessions by transaction start time. A session with a large txn_age or state = 'idle in transaction' for minutes is the prime suspect — it holds a snapshot and pins the horizon.
2. Query 2 shows backend_xmin and its age; the session with the oldest backend_xmin is literally the one preventing vacuum from advancing its removable cutoff. Every dead tuple newer than that xmin is un-reclaimable.
3. The surgical fix is pg_terminate_backend(pid) on the offending session. Once it ends, its snapshot is released and the horizon jumps forward to the next-oldest snapshot.
4. The durable fix is idle_in_transaction_session_timeout — Postgres automatically aborts any transaction left idle beyond the limit, so a leaked BEGIN from application code can no longer pin the horizon indefinitely.
5. With the horizon advanced, VACUUM (or autovacuum) can mark the accumulated dead tuples reusable. If the table has already bloated badly, VACUUM FULL (exclusive lock) or pg_repack (online) reclaims the disk, but the goal is to prevent the pin in the first place.

Output.

State	Horizon	Vacuum can reclaim?
long txn open	pinned at old xmin	no (bloat grows)
session terminated	advances	yes
timeout configured	can't be pinned by idle	prevented

Rule of thumb. Bloat is almost always a held-back xmin horizon, not a vacuum that is too slow. Hunt the oldest snapshot in pg_stat_activity first, set idle_in_transaction_session_timeout, and keep transactions short — especially at repeatable read/serializable, which hold their snapshot for the whole transaction.

SQL Interview Question on MVCC

A senior interviewer might ask: "An UPDATE-heavy Postgres table has grown to 40 GB while holding only ~2 GB of live rows, and query latency has doubled. Autovacuum is running but 'not keeping up.' Walk me through how MVCC produced this bloat, how you would confirm the root cause, and the fix — both the immediate one and the design change that prevents recurrence."

Solution Using horizon diagnosis, vacuum tuning, and fillfactor for HOT updates

-- 1. Confirm bloat: live rows vs relation size
SELECT relname,
       n_live_tup,
       n_dead_tup,
       pg_size_pretty(pg_relation_size(relid))            AS heap_size,
       pg_size_pretty(pg_total_relation_size(relid))      AS total_size,
       last_autovacuum
FROM   pg_stat_user_tables
WHERE  relname = 'events'
ORDER  BY n_dead_tup DESC;

-- 2. Confirm a pinned horizon (the usual root cause)
SELECT pid, state, now() - xact_start AS age, backend_xmin
FROM   pg_stat_activity
WHERE  backend_xmin IS NOT NULL
ORDER  BY age(backend_xmin) DESC
LIMIT  3;

-- 3. Immediate reclaim (online, no exclusive lock like VACUUM FULL)
--    pg_repack rewrites the table+indexes without a long lock.
-- $ pg_repack -t events -d production

-- 4. Design fix A: enable HOT updates by leaving free space per page
ALTER TABLE events SET (fillfactor = 85);   -- new UPDATEs can stay on-page

-- 5. Design fix B: make autovacuum far more aggressive for this hot table
ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor = 0.02,   -- vacuum at 2% churn, not default 20%
    autovacuum_vacuum_cost_limit   = 2000,   -- let autovacuum do more work per round
    autovacuum_vacuum_insert_scale_factor = 0.05
);

-- 6. Guard: stop long/idle transactions from pinning the horizon
ALTER SYSTEM SET idle_in_transaction_session_timeout = '2min';
SELECT pg_reload_conf();

Step-by-step trace.

Input — events table: n_live_tup ≈ 2 GB worth of rows, pg_total_relation_size ≈ 40 GB, high UPDATE rate, one long-lived reporting transaction.

1. Confirm bloat. Query 1 shows total_size (40 GB) dwarfing live rows and a large n_dead_tup. This is dead-tuple accumulation, the MVCC signature: every UPDATE left an old version behind.
2. Find the cause. Query 2 shows a reporting session with an ancient backend_xmin. Because that snapshot might still need the dead versions, autovacuum's removable cutoff is stuck behind it — vacuum runs but reclaims almost nothing.
3. Immediate reclaim. After ending the long transaction (or once it finishes), the horizon advances. pg_repack rewrites the table and indexes online to return the 38 GB of dead space to the OS without the exclusive lock VACUUM FULL would take.
4. Prevent churn bloat. fillfactor = 85 leaves 15% free space per page so an UPDATE can often place the new tuple on the same page as the old (a HOT update), which avoids adding index entries and lets vacuum clean the old version cheaply.
5. Prevent recurrence. Aggressive per-table autovacuum thresholds keep dead tuples reclaimed continuously, and idle_in_transaction_session_timeout ensures no leaked or long-idle transaction can pin the horizon again. The design change, not the one-time repack, is what keeps the table healthy.

Output:

Metric	Before	After
Total relation size	40 GB	~3 GB
Dead tuples	very high	near zero (steady)
Query latency	2× baseline	baseline
Horizon	pinned by long txn	advances freely
HOT-update ratio	low	high (fillfactor 85)

Why this works — concept by concept:

• Dead-tuple accumulation — every UPDATE/DELETE leaves an expired tuple version that occupies space until vacuum reclaims it; UPDATE-heavy tables generate dead tuples fast.
• Pinned xmin horizon — a long or idle-in-transaction session holds the oldest snapshot xmin, so vacuum cannot reclaim any tuple newer than it; the real root cause of most bloat.
• HOT updates via fillfactor — leaving free space per page lets an UPDATE keep the new version on-page without new index entries, so the version chain and indexes bloat far less.
• Aggressive autovacuum — lowering the scale factor makes autovacuum reclaim at 2% churn instead of 20%, keeping dead tuples from ever piling up on a hot table.
• Cost — pg_repack is a one-time online rewrite (temporary 2× space); the ongoing cost is slightly more autovacuum CPU and 15% looser page packing, cheap insurance against O(bloat) latency growth.

Database
Topic — database
Database MVCC and vacuum problems

Practice →

Optimization
Topic — optimization
Optimization problems on bloat and vacuum tuning

Practice →

---

Cheat sheet — isolation level recipes

• ANSI anomaly matrix. read uncommitted = dirty + non-repeatable + phantom all possible; read committed = dirty blocked, non-repeatable + phantom possible; repeatable read = dirty + non-repeatable blocked, phantom possible (ANSI); serializable = all blocked. The matrix is a floor: engines may be stricter (Postgres RR also blocks phantoms). Lost update and write skew live outside the ANSI matrix — read committed and (for write skew) even repeatable read permit them.
• Per-engine defaults. PostgreSQL defaults to read committed and offers RC / RR (= snapshot isolation) / SERIALIZABLE (= SSI); it accepts read uncommitted but promotes it to RC. MySQL InnoDB defaults to repeatable read (with next-key locks blocking phantoms on locking reads) and offers all four. Oracle defaults to read committed, supports SERIALIZABLE (snapshot-based), and does not offer RR or RU. SQL Server defaults to lock-based read committed, offers all four plus optimistic SNAPSHOT (READ_COMMITTED_SNAPSHOT). Never assume "the default" without naming the engine.
• Setting the level. Per transaction: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; or SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; as the first statement after BEGIN. Session default: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL .... Cluster default: default_transaction_isolation in postgresql.conf. Add READ ONLY for report transactions to document intent and skip write bookkeeping.
• Pick the level by the invariant. Single-row atomic change → read committed + guarded atomic UPDATE (SET x = x - n WHERE x >= n). Read-modify-write needing multiple reads → read committed + SELECT ... FOR UPDATE. Coherent multi-statement read → repeatable read (READ ONLY). Cross-row / aggregate invariant (write skew) → serializable + retry. Escalate only as far as the invariant demands.
• SELECT ... FOR UPDATE / FOR SHARE. FOR UPDATE takes an exclusive row lock so concurrent writers queue behind you — the fix for lost update when the write depends on multiple column reads. FOR SHARE takes a shared lock (others can read, not write). FOR UPDATE SKIP LOCKED powers work-queue patterns (each worker grabs a different unlocked row); FOR UPDATE NOWAIT fails fast instead of blocking. Row locks do not prevent write skew across different rows.
• Serialization-failure retry pattern. Any writer at repeatable read or serializable must catch SQLSTATE 40001 (serialization_failure) and 40P01 (deadlock_detected), roll back, and retry the whole transaction against a fresh snapshot — with bounded attempts, exponential backoff and jitter, and a retry-rate metric. A rising retry rate signals a hot key to redesign, not a retry cap to raise.
• Postgres RR vs ANSI RR. Postgres repeatable read is snapshot isolation and blocks phantoms; the ANSI standard permits them. Do not port "RR allows phantoms" reasoning from a textbook onto Postgres — but do remember RR (Postgres or ANSI) still permits write skew, which only serializable closes.
• The write-skew tell. Two transactions read an overlapping set, check a constraint, then write disjoint rows that jointly break it (doctors both off-call, both sub-accounts overdrawn, count-then-insert past a limit). No row updated twice ⇒ FOR UPDATE and RR do not help ⇒ escalate to serializable.
• MVCC in one breath. Every row has versions tagged xmin (creator xid) / xmax (expirer xid); a snapshot picks the version committed-before-and-not-yet-expired; readers never block writers because they read a version, not a lock. Isolation level = when the snapshot is taken (per-statement vs per-transaction) plus, for serializable, what dependencies are tracked.
• Vacuum + bloat note. UPDATE = insert-new + expire-old, so UPDATE-heavy tables accumulate dead tuples. VACUUM reclaims tuples dead to all snapshots; it can only reclaim past the xmin horizon, which a long/idle-in-transaction session pins. Diagnose with pg_stat_activity.backend_xmin; prevent with short transactions, idle_in_transaction_session_timeout, aggressive autovacuum, and fillfactor for HOT updates.
• When to escalate — and when not to. Escalate to serializable for genuine cross-row invariants where you would otherwise have to enumerate interleavings. Do not reach for serializable on hot single-row counters — an atomic UPDATE is cheaper and never retries. Over-escalation trades correctness bugs for throughput collapse under contention.
• Migration / audit checklist. When changing a transaction's level: (1) re-audit every write for lost-update exposure, (2) add/verify the 40001 retry loop, (3) check for held-open snapshots that will pin the horizon, (4) load-test the retry rate under peak contention, (5) confirm the engine's actual behaviour (Postgres RR ≠ ANSI RR) rather than trusting the standard.

Frequently asked questions

What are SQL isolation levels in one sentence?

SQL isolation levels are the contract that specifies which concurrency anomalies a transaction is allowed to observe when it runs alongside other transactions — the ANSI standard defines four levels as an increasing ladder (read uncommitted, read committed, repeatable read, serializable) where each rung forbids one more anomaly (dirty reads, then non-repeatable reads, then phantom reads) at the cost of more locking, snapshot bookkeeping, or transaction aborts. The level is a correctness decision, not a performance knob: it determines whether your transaction can read uncommitted data, whether a re-read of the same row can change, and whether a range query can grow a phantom row. Because the standard defines levels by permitted anomalies rather than implementation, the same level behaves differently across engines — which is why senior interviews probe both the taxonomy and the engine's actual mechanism.

Read committed vs repeatable read — when do I pick each?

Pick read committed (the Postgres default) for the vast majority of OLTP work: it blocks dirty reads, takes a fresh snapshot per statement, and never holds a snapshot across the whole transaction, so it puts the least pressure on vacuum. Its weakness is that two statements in one transaction can see different committed states, so a re-read of the same row can change (non-repeatable read) and read-modify-write can lose updates. Pick repeatable read when a single transaction reads the same data more than once and must see a stable, coherent view — reconciliations, multi-statement consistency checks, and reports across related tables. On Postgres, repeatable read is snapshot isolation and additionally blocks phantoms, but it still permits write skew, and any writer at RR must handle 40001 serialization failures with a retry loop. Escalate only as far as the invariant demands; over-escalating trades throughput for guarantees you may not need.

What is a phantom read and which level blocks it?

A phantom read happens when a transaction re-runs the same range query — a WHERE predicate over a set of rows — and gets a different set of rows because another transaction committed an INSERT or DELETE that changed which rows match. Unlike a non-repeatable read (where an existing row's value changes), a phantom changes the membership of the result set while the rows you already saw stay put. By the ANSI standard, only serializable is guaranteed to block phantoms; repeatable read may permit them. In practice it depends on the engine: PostgreSQL's repeatable read is snapshot isolation and blocks phantoms, and MySQL InnoDB's repeatable read blocks them for locking reads via next-key locks. Even where phantoms are blocked, a count-then-insert limit check across sessions can still fall to write skew, which needs serializable.

Does Postgres repeatable read block phantoms?

Yes — this is a common point of confusion. The ANSI standard permits phantoms at repeatable read, but PostgreSQL implements repeatable read as full snapshot isolation, which freezes one snapshot for the whole transaction and hides every row committed after that snapshot, including newly inserted rows that match a range predicate. So a range query re-run inside a Postgres RR transaction returns the identical set — no phantom. Postgres RR is therefore strictly stronger than ANSI RR. The important caveat is that snapshot isolation still permits write skew: two transactions can read an overlapping set, check a constraint, and write disjoint rows that jointly break it. If your correctness depends on a constraint spanning rows you read but did not lock, you must use serializable (Postgres SSI), not RR.

What is MVCC and how does it relate to isolation levels?

MVCC (Multi-Version Concurrency Control) is the mechanism that keeps multiple versions of each row so that readers see a consistent point-in-time snapshot without taking read locks — readers never block writers and writers never block readers. In PostgreSQL, each row version (tuple) carries an xmin (the transaction id that created it) and an xmax (the transaction id that expired it); a transaction's snapshot is a rule that selects, for each logical row, the version committed before the snapshot and not yet expired. Every isolation level is implemented on top of this same machinery — the only differences are when the snapshot is taken (read committed re-snapshots per statement; repeatable read and serializable snapshot once per transaction) and, for serializable, which read/write dependencies are tracked. The cost of MVCC is dead tuples: an UPDATE writes a new version and expires the old one, so VACUUM must reclaim dead versions, and a long-running transaction that pins the xmin horizon is the usual cause of table bloat.

What is serializable isolation and when do I actually need it?

serializable is the strongest isolation level: it guarantees the concurrent execution of a set of transactions produces the same result as some one-at-a-time serial ordering, so every anomaly — dirty reads, non-repeatable reads, phantoms, lost updates, and write skew — is impossible. PostgreSQL implements it with Serializable Snapshot Isolation (SSI): snapshot isolation plus non-blocking tracking of read/write dependencies, aborting one transaction with 40001 whenever the committed dependency graph would form a cycle no serial order allows. You need it when correctness depends on an invariant spanning multiple rows or an aggregate you read but did not lock — the classic write-skew cases (at least one doctor on call, total balance ≥ 0 across accounts, at most N active rows per user). You do not need it for hot single-row updates, where a guarded atomic UPDATE or SELECT ... FOR UPDATE is cheaper and never retries. Whenever you use it, wrap writers in a bounded, jittered 40001 retry loop.

Practice on PipeCode

• Drill the SQL practice library → for the transaction, isolation-level, lost-update, and write-skew problems senior interviewers love.
• Rehearse on the database practice library → for MVCC, snapshot visibility, locking, and vacuum-tuning scenarios.
• Sharpen the concurrency intuition on the data-processing practice library → for consistency checks and multi-statement snapshot coherence.
• Harden your retry and invariant logic on the defensive-coding practice library → and tune the cost side on the optimization practice library →.
• Stack these against PipeCode's broader 450+ data-engineering catalogue to anchor the four-level ladder against real graded inputs.

Lock in isolation-level muscle memory

Docs define the levels. PipeCode drills teach the decision — when read committed lets an update slip through, when repeatable read still permits write skew, when serializable's retry loop earns its place, and when MVCC bloat becomes a 3 AM incident. Pipecode.ai is Leetcode for Data Engineering — concurrency-first practice tuned for the production trade-offs senior engineers actually face.

Practice SQL problems →
Practice database problems →