Concurrency anomalies — dirty read, non-repeatable read, phantom read: Worked examples

Figure — Concurrency anomalies — dirty read, non-repeatable read, phantom read

Before symbols, the vocabulary — all borrowed straight from the parent, nothing new invented:

The scenario matrix

Every case this topic can throw at you is one row here. Read it as: " $T_{1}$ observes X because $T_{2}$ did Y at time Z." The last three columns are the verdict — which anomaly, if any. Where a cell needs extra narrative, the numbered example (E-number in column 1) carries it, so the columns stay clean.

#	Case class	What $T_{2}$ does	Did $T_{2}$ commit before $T_{1}$ acts?	Same row's value changed ("moved")?	Set of matching rows changed?	Verdict
C1	Uncommitted value read	UPDATE, no commit yet	✗	—	—	Dirty read
C2	Uncommitted read that "survives"	UPDATE, no commit, then COMMIT	✗ then ✓	—	—	still a dirty read
C3	Committed re-read moved	UPDATE + COMMIT between two reads	✓	✓	✗	Non-repeatable read
C4	Committed range grew	INSERT + COMMIT into the range	✓	✗	✓	Phantom read
C5	Committed range shrank	DELETE + COMMIT from the range	✓	✗	✓	Phantom read
C6	Range membership via UPDATE	UPDATE that moves a row across the predicate	✓	✗ (for the query's set)	✓	Phantom read
C7	Degenerate: no overlap	writes a different row	✓	✗	✗	No anomaly
C8	Limiting: same value written	UPDATE to the identical value + COMMIT	✓	✗ (value unchanged)	✗	No anomaly
C9	Real-world word problem	UPDATE, no commit at read, then ROLLBACK	✗ then ROLLBACK	—	—	Dirty read → wrong action
C10	Exam twist: aggregate vs row	UPDATE that flips an aggregate	✓	✓ on one row	✗	Non-repeatable (subtle)
C11	Zero-input degenerate	$T_{1}$ reads an empty set, $T_{2}$ INSERTs a match	✓	✗	✓ (0 → 1)	Phantom read

The worked examples below hit every cell C1–C11.

E1 — Dirty read (cell C1)

Worked example The classic dirty read

-- initial state: accounts(id=1).balance = 100
T2: UPDATE accounts SET balance = 0 WHERE id = 1;   -- NOT committed
T1: SELECT balance FROM accounts WHERE id = 1;       -- returns ?
T2: ROLLBACK;

Forecast: what does $T_{1}$ 's SELECT return, and is that number ever "real"?

Start value. balance was $100$ before anything ran (stated in the code block). Why this step? We must fix the "before" world, or "did it change?" is meaningless.
$T_{2}$ writes 0 but does not commit. The row now provisionally holds $0$ . Why this step? At Read Uncommitted there is no barrier hiding uncommitted writes, so $T_{1}$ can see this $0$ .
$T_{1}$ reads $0$ . It reads a value that only exists inside $T_{2}$ 's unfinished work. Why this step? This is the definition of a dirty read — reading uncommitted data.
$T_{2}$ rolls back. The true balance snaps back to $100$ . $T_{1}$ 's $0$ was a lie. Why this step? Rollback proves the read value never officially existed.

Verify: final committed balance $= 100$ , but $T_{1}$ believed $0$ . Discrepancy $= 100 - 0 = 100 \neq = 0$ ⇒ $T_{1}$ acted on phantom money. Cure: Read Committed (only ever read committed values). ✓

E2 — A dirty read that "commits" is still wrong (cell C2)

Worked example "But it committed in the end!"

-- initial state: accounts(id=1).balance = 100
T2: UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- 100 -> -400, NOT committed
T1: SELECT balance FROM accounts WHERE id = 1;   -- returns ?
T1: -- decision: "balance negative, freeze account"
T2: COMMIT;   -- this time it DOES commit

Forecast: since $T_{2}$ eventually commits, was $T_{1}$ 's read safe?

$T_{1}$ reads $- 400$ — the uncommitted intermediate. Why this step? The read still happened before commit, so it is still a dirty read by definition.
$T_{1}$ makes a decision on $- 400$ . Why this step? The danger of dirty reads is not only rollback — it is reading a state the DB never guaranteed at read time.
$T_{2}$ commits $- 400$ . By luck the final value matches. Why this step? Correctness cannot depend on luck: $T_{2}$ could equally have rolled back.

Verify: the read value $- 400$ equals the eventual committed value $- 400$ here, but $Δ = - 400 - (- 400) = 0$ only for this timeline. Read Committed would have made $T_{1}$ wait, guaranteeing correctness regardless. ✓

E3 — Non-repeatable read (cell C3)

Worked example Same row, two answers

-- initial state: accounts(id=1).balance = 100
T1: SELECT balance FROM accounts WHERE id = 1;   -- read A = ?
T2: UPDATE accounts SET balance = 150 WHERE id = 1; COMMIT;
T1: SELECT balance FROM accounts WHERE id = 1;   -- read B = ?

Forecast: what are read A and read B, and why isn't this a dirty read?

Read A = 100 (the committed start value declared in the code block). Why this step? We are at Read Committed, so both reads see committed data — no dirtiness possible.
$T_{2}$ commits 150. Now the committed value has moved. Why this step? The write is committed between $T_{1}$ 's two reads — that "between" is the whole story.
Read B = 150. Same query, same row, different number. Why this step? $T_{1}$ can no longer trust "I already read this row." That instability is the anomaly.

Verify: $A = 100$ , $B = 150$ , $A \neq = B$ ⇒ non-repeatable. Both are committed ⇒ not dirty. Same single row ⇒ not phantom. Cure: Repeatable Read — hold a read-lock/snapshot on row id=1 so read B $= 100$ again. ✓

E4 — Phantom read via INSERT (cell C4)

Intuition What the figure shows (read this first)

A large black box outlines the range the query scans: every row where dept = 'Sales'. Inside it sit 10 white boxes labelled "Sales" — the rows that already exist; those are the ones a Repeatable-Read lock would pin down. Below the box, a single red box labelled "Sales" is the brand-new row $T_{2}$ inserts, and a red arrow points up into the box — the row is entering the range from outside, where no existing lock was watching. To the right, the counts change from count A = 10 (black) to count B = 11 (red). The red object is the phantom; everything else is the unchanged background.

[!example] A new row joins the party

-- initial state: exactly 10 rows have dept = 'Sales'
T1: SELECT COUNT(*) FROM employees WHERE dept = 'Sales';   -- count A = ?
T2: INSERT INTO employees(dept) VALUES ('Sales'); COMMIT;
T1: SELECT COUNT(*) FROM employees WHERE dept = 'Sales';   -- count B = ?

Forecast: if there were 10 Sales rows, what are A and B — and why can't a row lock save you?

Count A = 10. Look at the figure: 10 white rows sit inside the dept='Sales' range. Why this step? We fix the initial membership of the set the query scans.
$T_{2}$ inserts a brand-new Sales row and commits. The red row appears inside the range — follow the red arrow. Why this step? No existing row changed value, so it is not non-repeatable. The set membership grew.
Count B = 11. Why this step? The range now contains one more row. A lock on the 10 existing rows never touched the newcomer — that's why row locks fail here.

Verify: $A = 10$ , $B = 11$ , $B - A = 1$ ⇒ one phantom. Cure: Serializable via range/predicate (next-key) locks that lock the gap where the new row would land. ✓

E5 — Phantom read via DELETE (cell C5)

Worked example The range shrinks

-- initial state: exactly 10 rows have dept = 'Sales' (one of them is id = 7)
T1: SELECT COUNT(*) FROM employees WHERE dept = 'Sales';   -- count A = ?
T2: DELETE FROM employees WHERE dept = 'Sales' AND id = 7; COMMIT;
T1: SELECT COUNT(*) FROM employees WHERE dept = 'Sales';   -- count B = ?

Forecast: phantoms are usually "new rows" — can a deletion be a phantom too?

Count A = 10. Why this step? Fix the starting set again (same 10 Sales rows).
$T_{2}$ deletes one matching row and commits. Why this step? The set of rows matching dept='Sales' changed membership — a row left the range.
Count B = 9. Why this step? Same query, different set size, and no surviving row changed value ⇒ this is a phantom (disappearing kind), not non-repeatable.

Verify: $A = 10$ , $B = 9$ , $A - B = 1$ ⇒ one (vanishing) phantom. Phantoms are about the set changing, in either direction. Cure: Serializable. ✓

E6 — Phantom read via UPDATE across the predicate (cell C6)

Worked example No INSERT, no DELETE — an UPDATE still makes a phantom

-- initial state: exactly 10 rows have dept = 'Sales'; one row id = 5 has dept = 'Ops'
T1: SELECT COUNT(*) FROM employees WHERE dept = 'Sales';   -- count A = ?
T2: UPDATE employees SET dept = 'Sales' WHERE id = 5; COMMIT;   -- 'Ops' -> 'Sales'
T1: SELECT COUNT(*) FROM employees WHERE dept = 'Sales';   -- count B = ?

Forecast: an UPDATE usually causes a non-repeatable read — how is this a phantom instead?

Count A = 10. Why this step? Row id=5 starts as 'Ops', so it is outside the dept='Sales' range and does not contribute to A.
$T_{2}$ updates row 5's dept from 'Ops' to 'Sales' and commits. Why this step? This UPDATE moves the row across the predicate boundary — it slides into the query's range from outside, exactly like an INSERT does for that range.
Count B = 11. Why this step? The set matching dept='Sales' gained a member. $T_{1}$ never read row 5 in query A (it wasn't in range), so no existing member's value changed — the membership changed. That is the phantom signature.

Verify: $A = 10$ , $B = 11$ , $B - A = 1$ ⇒ phantom by predicate-crossing UPDATE. Cure: Serializable / predicate locks (a mere row-lock on the 10 originals misses row 5 entirely). ✓

E7 — Degenerate: no overlap, no anomaly (cell C7)

Worked example

$T_{2}$ touches a different row

-- initial state: accounts(id=1).balance = 100 ; accounts(id=2).balance = 500
T1: SELECT balance FROM accounts WHERE id = 1;   -- read A = ?
T2: UPDATE accounts SET balance = 999 WHERE id = 2; COMMIT;   -- note: id 2, not 1
T1: SELECT balance FROM accounts WHERE id = 1;   -- read B = ?

Forecast: $T_{2}$ committed a write between $T_{1}$ 's reads — anomaly or not?

Read A = 100 (row id=1). Why this step? Establish the row $T_{1}$ actually cares about.
$T_{2}$ writes row id=2. $T_{1}$ never reads row 2. Why this step? An anomaly requires overlap between what $T_{1}$ reads and what $T_{2}$ writes. Here there is none.
Read B = 100. Unchanged. Why this step? Row 1 was never touched ⇒ nothing moved under $T_{1}$ .

Verify: $A = B = 100$ ⇒ no non-repeatable read; the query range id=1 gained/lost no rows ⇒ no phantom; both committed ⇒ no dirty. Verdict: no anomaly, safe even at Read Committed. ✓

E8 — Limiting case: same value written (cell C8)

Worked example

$T_{2}$ "changes" the value to what it already was

-- initial state: accounts(id=1).balance = 100
T1: SELECT balance FROM accounts WHERE id = 1;   -- read A = ?
T2: UPDATE accounts SET balance = 100 WHERE id = 1; COMMIT;   -- was already 100
T1: SELECT balance FROM accounts WHERE id = 1;   -- read B = ?

Forecast: a committed UPDATE fired between the reads — surely that's non-repeatable?

Read A = 100.
$T_{2}$ commits balance = 100 — a write, but to the identical value. Why this step? Non-repeatable read is defined by getting a different value, not by "a write happened."
Read B = 100. Why this step? $A = B$ , so $T_{1}$ 's two reads agree ⇒ the definition is not satisfied.

Verify: $A = 100$ , $B = 100$ , $A = B$ ⇒ not non-repeatable. Verdict: no anomaly (limiting boundary). ✓

E9 — Real-world word problem (cell C9)

Worked example Card-fraud logic

A fraud service reads a card's balance to decide whether to freeze the card ("freeze if balance $< 0$ "). Concurrently a refund transaction is in flight.

-- initial state: cards(id=9).balance = 20
T2 (refund): UPDATE cards SET balance = balance - 50 WHERE id = 9;  -- 20 -> -30, NOT committed
T1 (fraud):  SELECT balance FROM cards WHERE id = 9;   -- reads ?
T1: -- balance < 0  => FREEZE CARD
T2: ROLLBACK;   -- refund failed, revert

Forecast: which anomaly bites, what wrong action results, and what isolation level stops it?

This is cell C9: the "commit before $T_{1}$ acts?" answer is ✗ — $T_{1}$ reads before $T_{2}$ decides, and $T_{2}$ then rolls back. So it is the dirty-read pattern (C1) dressed as a real system.

$T_{2}$ pushes balance to $- 30$ uncommitted. Why this step? The provisional value crosses the " $< 0$ " threshold the fraud logic watches.
$T_{1}$ reads $- 30$ (dirty read). Why this step? $T_{1}$ reads uncommitted data — exactly cell C1's pattern, now in the wild.
$T_{1}$ freezes the card. Why this step? The wrong action is triggered by a value that is about to vanish.
$T_{2}$ rolls back to $20$ . Card is now frozen for no reason. Why this step? Rollback restores the only value the DB ever guaranteed ( $20$ ), exposing the freeze as a decision built on data that never officially existed — the concrete cost of the dirty read.

Verify: committed balance $= 20 \geq 0$ , so the correct decision is do not freeze, yet $T_{1}$ froze; the harm equals one wrongly frozen customer. Cure: run the fraud check at Read Committed (or use MVCC snapshots) so $T_{1}$ reads $20$ , not $- 30$ , and never freezes. ✓

E10 — Exam twist: aggregate that hides a non-repeatable read (cell C10)

Worked example SUM changes but it's still row-level

-- initial state: accounts(id=1).balance = 100 ; accounts(id=2).balance = 100
T1: SELECT SUM(balance) FROM accounts WHERE id IN (1, 2);   -- sum A = ?
T2: UPDATE accounts SET balance = 300 WHERE id = 1; COMMIT;
T1: SELECT SUM(balance) FROM accounts WHERE id IN (1, 2);   -- sum B = ?

Forecast: the number changed — is this phantom (an aggregate over a range) or non-repeatable?

Sum A = $100 + 100 = 200$ . Why this step? Fix the aggregate's inputs — two existing rows, no new rows possible (fixed IN (1,2) list).
$T_{2}$ updates row 1's value to 300 and commits. Why this step? The set of contributing rows is unchanged (still rows 1 and 2). Only a value moved ⇒ this is the non-repeatable signature, not phantom.
Sum B = $300 + 100 = 400$ . Why this step? The changed aggregate is caused by an existing row's value, so Repeatable Read (row locks/snapshot) already fixes it.

Verify: $A = 200$ , $B = 400$ , $A \neq = B$ ; membership fixed (rows 1 and 2 both times) ⇒ non-repeatable read, cured at Repeatable Read — not Serializable-required. ✓

E11 — Zero-input degenerate: phantom out of nothing (cell C11)

Worked example Empty result grows to one

-- initial state: zero rows have dept = 'Legal'
T1: SELECT COUNT(*) FROM employees WHERE dept = 'Legal';   -- count A = ?
T2: INSERT INTO employees(dept) VALUES ('Legal'); COMMIT;
T1: SELECT COUNT(*) FROM employees WHERE dept = 'Legal';   -- count B = ?

Forecast: if the first query returns 0, can there still be a phantom?

Count A = 0. Why this step? The starting set is empty — the degenerate boundary of a phantom read.
$T_{2}$ inserts the first-ever Legal row and commits. Why this step? Even with zero existing rows, the predicate dept='Legal' gains a member — the set membership went $0 \to 1$ .
Count B = 1. Why this step? There was no row to lock at read A, so row-level locking is powerless — you must lock the predicate/gap.

Recall Why the empty case matters

What must Serializable lock when the initial result set is empty? ::: The predicate range / gap (there is no existing row to lock), e.g. a next-key / gap lock covering dept='Legal'.

Verify: $A = 0$ , $B = 1$ , $B - A = 1$ ⇒ phantom born from an empty set. Row locks impossible ⇒ needs predicate locking / Serializable. ✓