Transaction isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE: Worked examples

This page is the drill floor for the parent topic. We will hit every kind of case the isolation dial can throw at you: each anomaly, each level, the "nothing happens" baseline, the degenerate rollback case, the limiting case (SERIALIZABLE aborts), a real-world word problem, and an exam twist (write skew, which the standard table does not cover).

If a word here is unfamiliar (dirty read, snapshot, predicate lock), it is fully built in the parent note — this page assumes you read that first.

The scenario matrix

Think of a scenario as a point in a grid. The rows are what T2 does and when; the columns are which isolation level T1 runs at. A cell is either "anomaly happens" (bad) or "prevented" (safe). We must show at least one worked example touching every kind of cell.

Formula The scenario matrix (each cell → an example below)

Case class (what T2 does)	RU	RC	RR	SER	Example
T2 writes then ROLLBACK (dirty)	❌ dirty	✅ safe	✅	✅	Ex 1, Ex 2
T2 UPDATE + COMMIT a read row (non-repeatable)	❌	❌	✅	✅	Ex 3a, Ex 3, Ex 4
T2 INSERT + COMMIT matching a predicate (phantom)	❌	❌	⚠️	✅	Ex 5a, Ex 5
Baseline: T2 touches different rows (no conflict)	✅	✅	✅	✅	Ex 6
Degenerate: T1 reads, T2 does nothing, T1 re-reads	✅	✅	✅	✅	Ex 6
Limiting: SER must abort one transaction	—	—	—	🔴 abort	Ex 7
Word problem: bank overdraft race	❌	❌	❌	✅	Ex 8
Exam twist: write skew (not in standard table)	❌	❌	❌	✅	Ex 9

Each ✅/❌ below is earned by a worked example. Nothing is asserted without a trace.

Ex 1 — Dirty read at READ UNCOMMITTED · (dirty row, RU column)

Worked example Statement

accounts(id=1) starts at bal = 100.

T2: UPDATE accounts SET bal = 0 WHERE id=1;   -- NOT committed yet
T1 (READ UNCOMMITTED): SELECT bal WHERE id=1; -- reads ??
T2: ROLLBACK;                                  -- bal snaps back to 100

Forecast: What does T1 read? Does that value survive T2's rollback? What is the final committed bal?

Steps.

T1 reads bal. Why this step? RU imposes no rule that T2's write-lock be released first, so T1 peeks at the in-progress value → T1 reads 0.
T2 rolls back. Why this step? A ROLLBACK undoes T2's uncommitted write, so the row's committed value returns to 100. The 0 T1 saw never officially existed.
Final committed bal = 100, but T1 already acted on 0. Why this step? This is the definition of a dirty read — T1's logic is now based on a value the database will disown.

The timeline below traces this exactly: the white dashed arrow shows T1's read reaching up into T2's uncommitted lane to grab the 0, and the red ROLLBACK marker on T2's lane shows the value snapping back to 100 afterwards.

Figure — Transaction isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

Recall Verify

T1 read = 0 (uncommitted). Final committed bal = 100. The read value ≠ final value ⇒ dirty read confirmed. See =VERIFY= dirty_read_value.

Ex 2 — Same scenario, raised to READ COMMITTED · (dirty row, RC column = safe)

Steps.

T1 issues its SELECT while T2's write is uncommitted. Why this step? RC's contract: only committed data is visible. Under MVCC, T1 reads the last committed version = 100. (Under locking, T1 would block until T2 commits/rolls back, then read 100.)
T2 rolls back. Why this step? No effect on T1 — T1 never looked at the uncommitted 0.

Recall Verify

T1 read at RC = 100 = final committed value. No dirty read. This is exactly the ✅ (prevented) cell in the RC column. See =VERIFY= rc_prevents_dirty.

Ex 3a — Non-repeatable read at READ UNCOMMITTED · (update row, RU column = anomaly)

Worked example Statement

T1 (READ UNCOMMITTED): SELECT bal WHERE id=1;  -- read A
T2: UPDATE accounts SET bal=50 WHERE id=1; COMMIT;
T1: SELECT bal WHERE id=1;                      -- read B

Start bal = 100. Forecast: read A = ? read B = ? Are they equal? (This is the RU cell of the non-repeatable row.)

Steps.

Read A = 100. Why this step? No writer is active yet, so T1 reads the current committed value.
T2 commits bal=50. Why this step? 50 is now the committed value.
Read B = 50. Why this step? RU has no snapshot stability at all — it always sees the newest value (committed or not). So the second read certainly reflects T2's committed 50.
Read A ≠ Read B. Why this step? Two answers to the same query ⇒ non-repeatable read. RU never prevents it — this fills the ❌ RU cell.

Recall Verify

Read A = 100, Read B = 50, unequal ⇒ non-repeatable read at RU. See =VERIFY= nonrepeatable_ru.

Ex 3 — Non-repeatable read at READ COMMITTED · (update row, RC column = anomaly)

Worked example Statement

T1 (READ COMMITTED): SELECT bal WHERE id=1;   -- read A
T2: UPDATE accounts SET bal=50 WHERE id=1; COMMIT;
T1: SELECT bal WHERE id=1;                     -- read B

Start bal = 100. Forecast: read A = ? read B = ? Are they equal? Which anomaly is this?

Steps.

Read A = 100. Why this step? T1's first statement takes a fresh snapshot; committed value is 100.
T2 commits bal=50. Why this step? Now 50 is the committed value.
Read B. Why this step? RC takes a new snapshot per statement, so the second SELECT sees T2's freshly committed 50. Read B = 50.
Read A ≠ Read B. Why this step? Same query, same row, two answers → non-repeatable read.

Recall Verify

Read A = 100, Read B = 50, 100 ≠ 50 ⇒ non-repeatable read. See =VERIFY= nonrepeatable_rc.

Ex 4 — Same scenario, raised to REPEATABLE READ · (update row, RR column = safe)

Steps.

Read A = 100. Why this step? T1's first statement takes the transaction's one and only snapshot, timestamped before T2 committed.
T2 commits bal=50. Why this step? This creates a new version of the row, but it is newer than T1's frozen snapshot.
Read B. Why this step? RR reuses the start-of-transaction snapshot forever, so T1 ignores T2's newer version. Read B = 100.
Read A = Read B. Why this step? The read is now repeatable — this is the ✅ (prevented) cell.

Recall Verify

Read A = Read B = 100. Difference = 0 ⇒ repeatable. See =VERIFY= rr_prevents_nonrepeatable.

The figure below contrasts Ex 3 and Ex 4 on two timelines. Top (red) lane = RC: the two blue read-dots return 100 then 50 — different. Bottom (green) lane = RR: both read-dots return 100 because the white double-arrow bracket shows one frozen snapshot spanning the whole transaction, so T2's commit-marker in the middle is ignored.

This snapshot machinery is MVCC (Multi-Version Concurrency Control); the lock-based alternative is Two-Phase Locking (2PL).

Ex 5a — Phantom read at READ UNCOMMITTED and READ COMMITTED · (insert-matching-predicate, RU + RC columns = anomaly)

Worked example Statement

T1: SELECT count(*) FROM users WHERE age>30;  -- count A
T2: INSERT INTO users(age) VALUES (40); COMMIT;
T1: SELECT count(*) FROM users WHERE age>30;  -- count B

Suppose 5 users already have age>30. Run T1 at READ UNCOMMITTED, then at READ COMMITTED. Forecast: count A = ? count B = ? Does the new row appear at both levels?

Steps.

Count A = 5. Why this step? Five existing rows match the predicate at each level's first read.
T2 inserts age=40 and commits. Why this step? 40 > 30, so this new row joins the matching set.
At RU, count B = 6. Why this step? RU offers no read stability whatsoever, so the newest matching set (now 6 rows) is visible immediately — phantom appears. This fills the ❌ RU phantom cell.
At RC, count B = 6. Why this step? RC takes a fresh snapshot per statement, so the second SELECT sees T2's committed insert. RC prevents dirty reads but not phantoms — this fills the ❌ RC phantom cell.
Count A ≠ count B at both levels ⇒ phantom read confirmed for RU and RC.

Recall Verify

RU: count A = 5, count B = 6. RC: count A = 5, count B = 6. Both show one phantom. See =VERIFY= phantom_ru_rc.

Ex 5 — Phantom read at REPEATABLE READ · (insert-matching-predicate, RR column = ⚠️)

Worked example Statement

T1 (REPEATABLE READ): SELECT count(*) FROM users WHERE age>30;  -- count A
T2: INSERT INTO users(age) VALUES (40); COMMIT;
T1: SELECT count(*) FROM users WHERE age>30;                    -- count B

Suppose 5 users already have age>30. Forecast: count A = ? count B = ? Which anomaly? Would PostgreSQL actually show it?

Steps.

Count A = 5. Why this step? Five existing rows satisfy the predicate.
T2 inserts age=40 and commits. Why this step? 40 > 30, so this brand-new row matches T1's WHERE.
Count B — by the SQL standard = 6. Why this step? Standard RR protects rows you already read, not predicate membership. A new matching row is a phantom and may appear.
But in PostgreSQL RR reuses the start snapshot, so the new row (committed after the snapshot) is invisible → PG returns 5. Why this step? Engines may exceed the standard's minimum. This is the ⚠️ in the matrix.
Fix to guarantee prevention everywhere: SERIALIZABLE places a predicate/range lock on age>30, blocking T2's insert until T1 finishes.

Recall Verify

Standard-behaviour count A = 5, count B = 6, difference = 1 phantom row. See =VERIFY= phantom_count.

Ex 6 — Baseline & degenerate at EVERY level · (no-conflict + degenerate cells, all columns)

Steps.

Case (a): read A = 100 at every level. Why this step? committed value; all four levels agree on the first read.
T2 updates id=99 — an unrelated row. Why this step? No overlap with T1's read set ⇒ no interference is possible, so the level cannot matter.
Case (a): read B = 100 at RU, RC, RR, and SER. Why this step? Even RU (which sees everything) has nothing to see about id=1; and RR/SER's snapshots trivially still show 100. Nobody touched T1's row.
Case (b): both reads = 100 at every level. Why this step? With no concurrent writer at all, there is no version to diverge to — this is the degenerate "isolation has nothing to protect against" case, true for all four columns.

Recall Verify

(a) reads (100, 100) equal at all four levels; (b) reads (100, 100) equal at all four levels. No anomaly in any cell. This fills the two "always ✅" rows across every column. See =VERIFY= baseline_no_conflict.

Ex 7 — Limiting case: SERIALIZABLE aborts a transaction · (SER limiting cell)

Worked example Statement

Both T1 and T2 run at SERIALIZABLE under Serializable Snapshot Isolation. Each reads a shared counter x=10, then writes based on what it read:

T1: r=SELECT x;  -- 10        T2: r=SELECT x;  -- 10
T1: UPDATE x=r+1;             T2: UPDATE x=r+5;
T1: COMMIT;                   T2: COMMIT?

Forecast: Both read 10. If both committed naively, x = ? What must SER do to stay serializable?

Steps.

Both read x = 10. Why this step? Each takes its own snapshot; concurrent reads are fine.
Naive concurrent commit: whichever writes last wins → x = 15 or 11, and the other update is lost. Why this step? Neither result equals any serial order: serial T1→T2 gives x = 10+1 then +5 = 16; serial T2→T1 gives 10+5 then +1 = 16. Both serial orders give 16, but the concurrent write-based-on-stale-read gives 11 or 15 — impossible serially.
SER detects the read-write dependency cycle and aborts one transaction with serialization_failure. Why this step? SER guarantees result = some serial order; since no serial order yields 11 or 15, it refuses to let both through. The app retries the aborted one.
On retry, the loser re-reads the now-committed value and produces x = 16. Why this step? After retry the two runs are effectively serial, giving the correct 16.

Recall Verify

Serial T1→T2 = 16, serial T2→T1 = 16; naive concurrent = 11 or 15 (≠ 16). SER must abort to force 16. See =VERIFY= ser_abort_forces_serial.

This abort-and-retry mechanism is Serializable Snapshot Isolation (SSI); forcing serial order can also produce Deadlocks under locking implementations.

Ex 8 — Word problem: bank overdraft race · (real-world, needs SER)

Worked example Statement

A checking account has bal = 100. Two ATM withdrawals run at the same time, each checking "is there enough?" before subtracting from the value it read:

T1: r=SELECT bal;  -- 100      T2: r=SELECT bal;  -- 100
T1: if r>=80: UPDATE bal=r-80  T2: if r>=80: UPDATE bal=r-80
T1: COMMIT                     T2: COMMIT

Each withdrawal is 80; the rule is "never let balance go negative." Note each UPDATE writes r - 80 where r is that transaction's own read, not the current row value. Forecast: At READ COMMITTED / REPEATABLE READ, what final bal can occur? How much total money left the account? Is that legal? What does SER do?

Steps.

Both read bal = 100 into their own variable r. Why this step? Under RC/RR, concurrent reads both see the committed 100; neither sees the other's pending withdrawal.
Both pass the check 100 >= 80. Why this step? Each transaction independently believes the money is there.
Both compute r - 80 = 20 and write bal = 20. Why this step? Crucially, each writes an absolute value computed from its stale read of 100, so the second commit overwrites the first with the same 20 — a lost update. Final bal = 20, yet two withdrawals of 80 each were "approved" and handed out as cash. Money that left the account (cash dispensed) = 80 + 80 = 160, but the ledger only dropped by 100 - 20 = 80. The account is short by 80 — an accounting hole no serial order allows.
Fix — SERIALIZABLE. Why this step? SER must equal a serial order. In serial T1→T2, T2 reads the committed 20, fails 20 >= 80, and dispenses nothing — only one withdrawal of 80 happens and bal = 20 matches the 80 dispensed. SER detects the read-write conflict, aborts one transaction, and on retry the loser correctly rejects. This closes the accounting hole.

Recall Verify

Anomalous (RC/RR) outcome: cash dispensed = 160 while ledger dropped only 80 (illegal short of 80). Serial outcome: cash dispensed = 80, ledger drop = 80, final bal = 20 (consistent). See =VERIFY= overdraft_serial_safe.

Ex 9 — Exam twist: write skew · (anomaly outside the standard table)

Worked example Statement

Hospital rule: at least one doctor must stay on-call. Currently Alice and Bob are both on-call (on_call = TRUE each). Both request leave simultaneously at REPEATABLE READ:

T1: n = SELECT count(*) WHERE on_call;  -- 2    T2: n = SELECT count(*) WHERE on_call;  -- 2
T1: if n>=2: UPDATE alice.on_call=FALSE T2: if n>=2: UPDATE bob.on_call=FALSE
T1: COMMIT                              T2: COMMIT

Forecast: How many doctors remain on-call after both commit at RR? Is the rule violated? Which level saves you?

Steps.

Both read count = 2. Why this step? Each RR snapshot sees both doctors on-call.
Both pass 2 >= 2. Why this step? Each independently believes it's safe to leave — the classic write skew setup.
T1 sets Alice off; T2 sets Bob off. Why this step? They write to different rows, so there's no update conflict — RR (and even next-key locks) see nothing to block.
Final on-call count = 0 → rule violated. Why this step? This is Write skew anomaly: two transactions read an overlapping set, then each writes a disjoint part, together breaking an invariant. The SQL standard's table (dirty / non-repeatable / phantom) does not name it, which is the exam trap.
Fix: SERIALIZABLE. Under SSI it detects the read-write dependency (each read the set the other modified) and aborts one, leaving count = 1.

The figure shows the trap: both blue reads see 2, the yellow box (T1→Alice) and red box (T2→Bob) write different rows so no conflict is detected, and the red result banner shows on-call collapsing to 0.

Recall Full matrix recap (self-test)

Which example proved RC prevents dirty reads? ::: Ex 2 (read = 100, not 0). Which anomaly appears at RC but not RR? ::: Non-repeatable read (Ex 3 vs Ex 4). At which levels does a non-repeatable read happen? ::: RU (Ex 3a) and RC (Ex 3); RR and SER prevent it. At which levels does a phantom happen per the standard? ::: RU and RC (Ex 5a), and RR (Ex 5, ⚠️ blocked in practice); SER prevents it. Why does REPEATABLE READ fail the overdraft (Ex 8) and write-skew (Ex 9) cases? ::: Because both writers act on stale snapshots / disjoint rows; RR stabilises reads but not cross-row write validity. What does SERIALIZABLE do when it can't serialise? ::: Aborts one transaction with a serialization failure to retry (Ex 7).