Exercises — Transaction isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE
The whole game rests on four levels and three anomalies. Keep this chart in view:
Read it as a staircase: each step down removes one more anomaly (a filled cell = "this ghost can still appear"). We use U-C-R-S for the level order and D-N-P for the anomalies that peel off. Both mnemonics come straight from the parent note.
Level 1 — Recognition
You must name the anomaly. Nothing to compute yet — just match the story to a label.
Exercise 1.1
T1 reads bal = 0 from a row while T2 is still mid-transaction. T2 then does ROLLBACK, so the real balance is 100. What anomaly did T1 hit?
Recall Solution 1.1
Dirty read. T1 read uncommitted data (0) that never officially existed once T2 rolled back. The tell-tale sign: the source transaction rolled back, so the value T1 saw was never real. Only READ UNCOMMITTED permits this.
Exercise 1.2
T1: SELECT bal WHERE id=1 → 100. T2: UPDATE ... SET bal=50 WHERE id=1; COMMIT. T1 repeats the same SELECT → 50. Name it.
Recall Solution 1.2
Non-repeatable read. Same row, same query, two different committed values. Not "dirty" because T2 committed — the data was real, it just changed under T1. The key signature: an existing row's value changed between two reads.
Exercise 1.3
T1: SELECT count(*) WHERE age>30 → 5. T2: INSERT (age=40); COMMIT. T1 repeats → 6. Name it.
Recall Solution 1.3
Phantom read. The set of rows matching the predicate grew — a brand-new row appeared. Distinguish from non-repeatable read: nothing about an existing row changed; the membership of the result set changed.
Level 2 — Application
Now predict the actual output, given a stated isolation level.
Exercise 2.1
At READ COMMITTED: T1 reads bal=100, then T2 commits bal=200, then T1 reads again. What is the second read, and is it an anomaly?
Recall Solution 2.1
Second read = 200. READ COMMITTED takes a fresh snapshot per statement, so T1's second read sees T2's committed change. This is a non-repeatable read — allowed at READ COMMITTED, banned only at REPEATABLE READ and above.
Exercise 2.2
Same script as 2.1 but at REPEATABLE READ (PostgreSQL/MySQL-InnoDB style). What is the second read?
Recall Solution 2.2
Second read = 100. REPEATABLE READ pins one snapshot at transaction start and reuses it, so T1 keeps seeing its original value regardless of T2's commit. Both reads return 100.
Exercise 2.3
At READ UNCOMMITTED: T2 sets bal=0 (uncommitted), T1 reads, then T2 rolls back, then T1 reads again. Give both of T1's reads (assume original bal=100).
Recall Solution 2.3
First read = 0 (dirty read of uncommitted 0). Second read = 100 (after T2's rollback the real value is restored). So T1 sees 0 then 100 — it acted on a value that never truly existed.
Exercise 2.4
Count how many of the three anomalies are possible at each level, then sum across all four levels.
Recall Solution 2.4
- READ UNCOMMITTED: 3 possible (D, N, P).
- READ COMMITTED: 2 possible (N, P).
- REPEATABLE READ: 1 possible (P, per the standard).
- SERIALIZABLE: 0 possible. Total = . This "6" is a handy checksum for the staircase in the figure.
Level 3 — Analysis
Explain why the engine behaves as it does.
Exercise 3.1
Under MVCC, explain in one line why READ COMMITTED can give a non-repeatable read but REPEATABLE READ cannot.
Recall Solution 3.1
READ COMMITTED takes a new snapshot per statement, so a later statement sees newly-committed versions. REPEATABLE READ takes one snapshot at transaction start and reuses it, so every read sees the same version — the value cannot change under it.
Exercise 3.2
Under a lock-based engine, which lock does SERIALIZABLE add that REPEATABLE READ lacks, and which anomaly does it kill?
Recall Solution 3.2
SERIALIZABLE adds range / predicate (next-key) locks on top of the row read-locks that REPEATABLE READ already holds. These lock the gap covered by a WHERE range, so nobody can INSERT a row into that range — this kills the phantom read. Row locks alone protect only rows that already exist, which is why REPEATABLE READ (lock-based, standard) still allows phantoms.
Exercise 3.3
A developer sets SERIALIZABLE in PostgreSQL and is surprised to see two transactions running at the same time in pg_stat_activity. Is the engine broken? Explain.
Recall Solution 3.3
Not broken. SERIALIZABLE guarantees the result equals some serial order, not that transactions run one-at-a-time. PostgreSQL implements it with Serializable Snapshot Isolation (SSI): transactions run concurrently and optimistically, and the engine aborts one with a serialization_failure if their interleaving would violate serializability. Concurrency is real; only unsafe outcomes are prevented.
Exercise 3.4
Under lock-based SERIALIZABLE, T1 range-locks age>30 and T2 range-locks salary>90000; each then tries to insert into the other's range. What happens?
Recall Solution 3.4
A deadlock: T1 waits for T2's lock while T2 waits for T1's — a cycle. The engine's deadlock detector breaks it by aborting one transaction (a victim), which the app must retry. This is the throughput cost the parent note warns about for high isolation.
Level 4 — Synthesis
Given a bug, prescribe the lowest level that fixes it.
Exercise 4.1
An app double-charges customers because T1 reads a balance, T2 commits a change to that same row, and T1 re-reads a different balance mid-logic. Cheapest level that prevents this? Assume it starts at READ COMMITTED.
Recall Solution 4.1
This is a non-repeatable read. The cheapest level that bans it is REPEATABLE READ — it pins the snapshot so T1's re-read matches its first read. Going to SERIALIZABLE would also work but adds needless blocking (the parent's "don't over-isolate" rule).
Exercise 4.2
A report counts rows WHERE age>30 twice and gets different counts because another transaction inserted a matching row. Cheapest level that guarantees identical counts by the SQL standard alone (don't rely on engine extras)?
Recall Solution 4.2
This is a phantom read. By the standard, only SERIALIZABLE guarantees no phantoms (via range/predicate locks). REPEATABLE READ is not enough per the standard — engines like PostgreSQL happen to prevent it, but the question restricts us to standard guarantees.
Exercise 4.3
You see occasional serialization_failure errors after raising to SERIALIZABLE. Is this a bug to fix at the DB, or app-side work? What's the correct handling?
Recall Solution 4.3
It is expected behaviour of SSI, not a DB bug. The correct handling is app-side: catch the serialization_failure and retry the whole transaction (usually with a small backoff). The DB traded blocking for optimistic abort-and-retry; retries are the contract you accept for SERIALIZABLE-via-SSI.
Level 5 — Mastery
Reason about serializability itself and its famous edge case.
Exercise 5.1
Two transactions read a shared invariant (on_call_doctors >= 1), each sees 2 on call, each decides "safe to go off-call", and each commits a change to a different row. The invariant is now violated (0 on call). Under REPEATABLE READ / snapshot isolation, no anomaly from the classic table (D/N/P) occurred. Name this anomaly and the only level that prevents it.
Recall Solution 5.1
This is the write skew anomaly. It slips past REPEATABLE READ / snapshot isolation because no single row was read twice (no non-repeatable read) and no matching row was inserted (no phantom) — the two transactions wrote disjoint rows based on a shared read. Only SERIALIZABLE prevents it: PostgreSQL's Serializable Snapshot Isolation (SSI) detects the dangerous read-write dependency cycle and aborts one transaction.
Exercise 5.2
Explain why write skew proves that "no dirty/non-repeatable/phantom read" is not the same as "serializable".
Recall Solution 5.2
The three classic anomalies are a necessary but not sufficient checklist. A schedule can avoid all three yet still be non-serializable, because serializability also forbids read-write dependency cycles across disjoint rows (write skew). Banning D, N, and P gives you snapshot isolation, which is strictly weaker than true serializability. This is exactly why Serializable Snapshot Isolation (SSI) exists: to close that remaining gap on top of MVCC snapshots.
Exercise 5.3
Order READ COMMITTED, REPEATABLE READ, SERIALIZABLE, READ UNCOMMITTED from weakest to strongest, and state how many anomalies each bans (0–3).
Recall Solution 5.3
Weakest → strongest = READ UNCOMMITTED (bans 0) → READ COMMITTED (bans 1: dirty) → REPEATABLE READ (bans 2: dirty + non-repeatable) → SERIALIZABLE (bans 3: dirty + non-repeatable + phantom). The counts are ; summed that's — the same checksum as Exercise 2.4 (each anomaly is either "possible" or "banned", and cells total).
Recall One-line summary of the whole ladder
Anomalies peel off in order Dirty → Non-repeatable → Phantom → Write skew, and the levels that ban successively more are READ UNCOMMITTED → READ COMMITTED → REPEATABLE READ → SERIALIZABLE.