Concurrency anomalies — dirty read, non-repeatable read, phantom read: Exercises

Before we start, one picture fixes the vocabulary we will reuse in every solution.

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

Level 1 — Recognition

Here you only decide which anomaly (if any) a scenario shows. No isolation levels yet.

L1.1 — Name the anomaly (rollback)

Worked example Problem

T2: UPDATE stock SET qty = 5 WHERE item='pen';   -- not committed
T1: SELECT qty FROM stock WHERE item='pen';        -- reads 5
T2: ROLLBACK;

Which anomaly did $T_{1}$ suffer?

Recall Solution

$T_{1}$ read a value (qty = 5) that $T_{2}$ had written but not committed, and $T_{2}$ then undid it. $T_{1}$ 's read is based on data that never officially existed. Answer: dirty read. The give-away is the ROLLBACK — only a dirty read can be invalidated by an abort, because only a dirty read looks at uncommitted data.

L1.2 — Name the anomaly (same row, two values)

Worked example Problem

T1: SELECT price FROM books WHERE id=7;   -- reads 300
T2: UPDATE books SET price=350 WHERE id=7; COMMIT;
T1: SELECT price FROM books WHERE id=7;   -- reads 350

Which anomaly?

Recall Solution

Both of $T_{1}$ 's reads see committed data, so it is not dirty. But the same row (id 7) gave two different values because $T_{2}$ committed an UPDATE in between. Answer: non-repeatable read.

L1.3 — Name the anomaly (result set grew)

Worked example Problem

T1: SELECT COUNT(*) FROM orders WHERE city='Pune';   -- 12
T2: INSERT INTO orders(city) VALUES('Pune'); COMMIT;
T1: SELECT COUNT(*) FROM orders WHERE city='Pune';   -- 13

Which anomaly?

Recall Solution

No existing row changed value; a new row entered the city='Pune' range. The membership of the result set changed. Answer: phantom read.

L1.4 — Name the anomaly (trick)

Worked example Problem

T1: SELECT balance FROM acc WHERE id=1;   -- reads 100
T1: SELECT balance FROM acc WHERE id=1;   -- reads 100  (no T2 in between)

Which anomaly?

Recall Solution

None. Nothing else touched the data between the two reads, so both reads agree. An anomaly requires another transaction's interference — here there is no $T_{2}$ . This is a normal, consistent read.

Level 2 — Application

Now apply the anomaly→isolation-level rules from the parent's matrix.

L2.1 — Minimum level to stop a dirty read

Recall Solution

Dirty reads = reading uncommitted data. The lowest level that forbids reading uncommitted data is Read Committed. Answer: Read Committed. (Read Uncommitted still allows it; Repeatable Read and Serializable also work but are stronger than needed — the question asks for the lowest.)

L2.2 — Which anomalies remain at Read Committed?

Recall Solution

From the matrix: Read Committed prevents dirty reads (✓) but allows non-repeatable (✗) and phantom (✗). Answer: non-repeatable read and phantom read can still occur.

L2.3 — Cheapest level for a stable re-read of one row

Recall Solution

Same row, must be identical on re-read ⇒ we must forbid non-repeatable reads. The lowest level that does so is Repeatable Read. Since there is no range query, phantoms are irrelevant, so Serializable would be overkill. Answer: Repeatable Read.

L2.4 — Count anomalies allowed per level

Recall Solution

Read the matrix rows and count the ✗ (allowed):

Read Uncommitted: dirty ✗, non-rep ✗, phantom ✗ → 3 allowed
Read Committed: non-rep ✗, phantom ✗ → 2 allowed
Repeatable Read: phantom ✗ (per SQL standard) → 1 allowed
Serializable: none → 0 allowed Answer: 3, 2, 1, 0.

Level 3 — Analysis

Now reason about specific interleavings and locking behaviour.

L3.1 — Classify each line of an interleaving

Worked example Problem

t1  T1: SELECT bal FROM a WHERE id=1;            -- reads 100
t2  T2: UPDATE a SET bal=200 WHERE id=1;          -- not committed
t3  T1: SELECT bal FROM a WHERE id=1;            -- reads ???
t4  T2: COMMIT;
t5  T1: SELECT bal FROM a WHERE id=1;            -- reads ???

Assume Read Uncommitted. Fill in the two ??? values and label any anomaly at each read.

Recall Solution

Under Read Uncommitted, reads see the latest written value, committed or not.

t3 reads 200. At this point 200 is uncommitted → this is a dirty read.
t5 reads 200. Now 200 is committed (t4). Compared with t1's 100 for the same row, the value changed across reads → this is a non-repeatable read. Answer: t3 = 200 (dirty), t5 = 200 (non-repeatable vs the t1 read of 100).

L3.2 — Why row locks don't stop phantoms

Recall Solution

$T_{1}$ 's locks are on rows that already existed when it read. A brand-new row was never locked — you cannot lock a row that does not exist yet. So the INSERT succeeds, and $T_{1}$ 's second range query returns the extra row: a phantom. To stop it you must lock the predicate / range itself (e.g. a range or "next-key" lock on dept='Sales'), which blocks any insert into that range. That is what Serializable does. Answer: row locks cover existing rows only; a predicate/range lock is required to block phantom inserts.

L3.3 — Does MVCC change the story?

Recall Solution

Under snapshot isolation $T_{1}$ keeps reading from its frozen snapshot taken at start. It sees the old version of the row, not $T_{2}$ 's new committed value. Both reads return the same value. Answer: no non-repeatable read — MVCC serves the old version, so the two reads agree. (This is why many engines' "Repeatable Read" is implemented with MVCC rather than long-held read locks.)

L3.4 — Spot the deadlock risk

Worked example Problem

T1: lock row X (write);        T2: lock row Y (write);
T1: request row Y (write);     T2: request row X (write);

Both requests block. What is this, and how do stricter isolation levels relate to it?

Recall Solution

$T_{1}$ holds X and waits for Y; $T_{2}$ holds Y and waits for X — a circular wait. This is a deadlock. Stricter isolation (more/longer locks) increases the chance of deadlocks, so the engine must detect and abort one victim. Answer: a deadlock; higher isolation levels make deadlocks more likely, which is part of their performance cost.

Level 4 — Synthesis

Design and justify — combine multiple ideas.

L4.1 — Pick the level for a banking transfer

Recall Solution

The danger: $T_{1}$ reads balance = 100, $T_{2}$ also reads 100, both think "enough for 100", both debit → account goes to –100. The read of 100 became stale after the other's committed write: this is a non-repeatable-read–class hazard on the decision row. The lowest level guaranteeing the read stays valid until commit is Repeatable Read (or equivalently take a SELECT ... FOR UPDATE write-lock). No range query is involved, so Serializable is not required. Answer: Repeatable Read (or explicit row write-lock).

L4.2 — Pick the level for an inventory audit

Recall Solution

New matching rows can appear between the two range queries → phantom hazard. Only Serializable (predicate/range locking or serialization checks) forbids phantoms per the SQL standard. Answer: Serializable.

L4.3 — Cheapest level satisfying a set of requirements

Recall Solution

(a) needs at least Read Committed.
(b) needs at least Repeatable Read (kills non-repeatable reads).
(c) there are no range queries, so phantoms cannot arise — Serializable is unnecessary. The lowest level meeting (a) and (b) is Repeatable Read, and it trivially satisfies (c). Answer: Repeatable Read.

Level 5 — Mastery

Multi-part, subtle — the full toolbox at once.

L5.1 — Build the full matrix from scratch

Recall Solution

Level	Dirty	Non-Repeatable	Phantom
Read Uncommitted	✗	✗	✗
Read Committed	✓	✗	✗
Repeatable Read	✓	✓	✗
Serializable	✓	✓	✓

Generating rule: each step down the ladder adds a ✓ from left to right — first stop reading uncommitted (dirty), then freeze rows you read (non-repeatable), then freeze the predicate/range (phantom). The ✓ region grows monotonically down and to the right.

L5.2 — Match mechanism to anomaly

Recall Solution

(iv) reading only committed versions → removes dirty read.
(ii) hold read-lock on read rows until commit → removes non-repeatable read (those rows can't be updated).
(iii) predicate/next-key lock → removes phantom read (no inserts into the range).
(i) release read-lock immediately → this is Read Committed behaviour: removes dirty reads but still allows non-repeatable and phantom. Answer: iv→dirty, ii→non-repeatable, iii→phantom, i→(Read Committed: dirty only).

L5.3 — Trace an interleaving and count anomalies

Worked example Problem

Under Read Committed, count how many distinct anomalies $T_{1}$ experiences:

t1 T1: SELECT bal FROM a WHERE id=1;               -- reads 100
t2 T2: UPDATE a SET bal=100 WHERE id=1; (uncommitted)
t3 T1: SELECT bal FROM a WHERE id=1;               -- ?
t4 T2: COMMIT;
t5 T1: SELECT COUNT(*) FROM a WHERE bal=100;        -- ?
t6 T3: INSERT INTO a(id,bal) VALUES(9,100); COMMIT;
t7 T1: SELECT COUNT(*) FROM a WHERE bal=100;        -- ?

How many anomalies, and which?

Recall Solution

Under Read Committed, uncommitted writes are invisible.

t3: the t2 write is uncommitted → invisible → reads 100 (unchanged). No dirty read (Read Committed forbids it). And the value equals the t1 value, so no non-repeatable read yet.
t5: counts rows with bal=100. Say there is 1 such row (id 1) → COUNT = 1.
t6: $T_{3}$ inserts a new bal=100 row and commits.
t7: same range query now returns COUNT = 2 — a phantom read (a new matching row appeared). So exactly 1 anomaly: a phantom read. The dirty read was blocked by Read Committed, and no committed UPDATE hit an existing row $T_{1}$ re-read, so there is no non-repeatable read. Answer: 1 anomaly (phantom). (Values: t3 = 100, t5 = 1, t7 = 2.)

L5.4 — Severity ordering and the mnemonic

Recall Solution

Weakest cure → strongest: Dirty (Read Committed) → Non-repeatable (Repeatable Read) → Phantom (Serializable). Mnemonic DNP / RRS: Dirty, Non-repeatable, Phantom fixed by Read Committed, Repeatable Read, Serializable.

Recall Final self-check (answers hidden)

Fastest cure for a dirty read? ::: Read Committed Cure that stops non-repeatable but standard-allows phantom? ::: Repeatable Read Only standard level with zero anomalies? ::: Serializable Why don't row locks stop phantoms? ::: The new row didn't exist to be locked; you need a predicate/range lock Does MVCC snapshot isolation give non-repeatable reads on re-read? ::: No — it serves the old snapshot version