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

What is a transaction (so the anomalies make sense)

The key insight that unlocks all three anomalies:

The three anomalies (derived from first principles)

We classify by what changed and when. Imagine transaction $T_1$ reading, and $T_2$ doing something concurrently.

1. Dirty Read — reading the uncommitted

WHY it's bad: You acted on a fact that got un-done. Like trusting a price tag someone was about to remove.

Worked example Dirty read in action

T2: UPDATE accounts SET balance = 0 WHERE id=1;   -- not committed yet
T1: SELECT balance FROM accounts WHERE id=1;       -- reads 0  (DIRTY!)
T2: ROLLBACK;                                       -- balance was never really 0

Why this step? $T_1$ read $T_2$ 's provisional write. After rollback, $T_1$ 's knowledge is a lie. Any decision $T_1$ made (e.g., "block the card, balance is 0") is wrong.

2. Non-Repeatable Read — the same row changes under you

WHY it's bad: Your transaction is internally inconsistent — it saw two truths about one row.

Worked example Non-repeatable read

T1: SELECT balance FROM accounts WHERE id=1;   -- reads 100
T2: UPDATE accounts SET balance=150 WHERE id=1; COMMIT;
T1: SELECT balance FROM accounts WHERE id=1;   -- reads 150  (NON-REPEATABLE!)

Why this step? Both of $T_1$ 's reads are of committed data (so it's not dirty), but the committed value moved between them. $T_1$ cannot trust "I already read this."

3. Phantom Read — the set of rows changes under you

WHY it differs from non-repeatable: Non-repeatable = an existing row changed value. Phantom = the membership of the result set changed (new "phantom" rows appear).

Worked example Phantom read

T1: SELECT COUNT(*) FROM employees WHERE dept='Sales';   -- 10
T2: INSERT INTO employees(dept) VALUES('Sales'); COMMIT;
T1: SELECT COUNT(*) FROM employees WHERE dept='Sales';   -- 11  (PHANTOM!)

Why this step? No single existing row changed — a brand-new row entered the range the query scans. So row-level locks on existing rows don't help; you need range/predicate locks.

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

HOW isolation levels map to anomalies (the 80/20 table)

Formula Anomaly-to-isolation-level matrix

The SQL standard defines levels by which anomalies they forbid (✓ = prevented, ✗ = allowed):

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

*The SQL standard allows phantoms at Repeatable Read; some engines (e.g. InnoDB) actually prevent them too via next-key locks.

Common mistakes (Steel-manned)

Recall Feynman: explain to a 12-year-old

Imagine you're copying a friend's homework answer.

Dirty read: you copy an answer they wrote in pencil and then erased — now your answer is based on something that got rubbed out. (uncommitted data, rolled back)
Non-repeatable read: you look at question 3's answer, look away, look back, and they've changed it. Same question, new answer. (a row's value changed)
Phantom read: you count "5 questions on the page", look back, and a new question magically appeared making it 6. (a new row joined your result) The librarian (database) can be strict (lock the whole page so nothing changes — slow but safe) or chill (let things change — fast but glitchy). You choose how strict.

Recall drill

Flashcards

What is a dirty read?

T_1

reads a row that

T_2

modified but has not committed; if

T_2

rolls back,

T_1

read a value that never officially existed.

What is a non-repeatable read?

T_1

reads the same row twice and gets different values because

T_2

committed an UPDATE in between.

What is a phantom read?

T_1

runs the same range query twice and gets a different set of rows because

T_2

committed an INSERT/DELETE matching the condition.

Key difference: non-repeatable vs phantom?

Non-repeatable = an existing row's value changed (UPDATE). Phantom = the set of matching rows changed (INSERT/DELETE).

Which isolation level prevents dirty reads but allows non-repeatable reads?

Read Committed.

Which isolation level prevents non-repeatable reads but (per SQL standard) may allow phantoms?

Repeatable Read.

Which isolation level prevents all three anomalies?

Serializable.

Why not just use Serializable everywhere?

It maximizes locking/aborts, reducing concurrency and throughput and risking deadlocks; pick the lowest level that forbids the anomalies you care about.

What kind of lock is needed to stop phantoms?

Range / predicate (e.g. next-key) locks, not just row locks.

Does Read Committed guarantee two reads of the same row match?

No — it only guarantees each read sees committed data, not stability across reads.

Connections

ACID Properties — Isolation (the "I") is exactly what these anomalies violate.
Isolation Levels — the four-level ladder that fixes each anomaly.
Locking — Shared and Exclusive Locks — the mechanism behind preventing dirty/non-repeatable reads.
MVCC — Multiversion Concurrency Control — snapshot approach used by Postgres/InnoDB to avoid locks.
Two-Phase Locking (2PL) — protocol guaranteeing serializability.
Deadlocks — the cost of stronger isolation.

Hinglish (regional understanding)

Intuition Hinglish mein samjho

Dekho, database mein bahut saari transactions ek saath chalti hain taaki speed fast rahe. Lekin jab do transactions same data ko touch karti hain, toh ek transaction doosri ka aadha-adhura ya badalta hua kaam dekh leti hai. Yehi "glitch" ko hum concurrency anomaly kehte hain. Teen famous anomalies hain — yaad rakhne ka asaan tareeka: Dirty, Non-repeatable, Phantom.

Dirty read: T2 ne value change ki par abhi commit nahi ki (pencil se likha, abhi mitane wala hai). T1 ne wahi padh li. Phir T2 ne ROLLBACK kar diya — matlab jo T1 ne padha tha woh exist hi nahi karta tha. Galat data pe decision. Non-repeatable read: T1 ne ek row padhi (balance 100), beech mein T2 ne UPDATE karke commit kar diya (150), T1 ne dobara padha toh 150 mil gaya. Same row, do alag answer — yani read repeatable nahi raha. Phantom read: T1 ne ek range query chalayi (WHERE dept='Sales'), count aaya 10. T2 ne ek nayi row INSERT karke commit kar di. T1 ne dobara count kiya toh 11 — koi purani row nahi badli, balki ek nayi row aa gayi range mein. Yeh hi farq hai: non-repeatable mein value badalti hai, phantom mein result ka set badalta hai.

Inko theek karne ke liye isolation levels hote hain — ek seedhi (ladder): Read Uncommitted (sab allowed), Read Committed (dirty gone), Repeatable Read (non-repeatable bhi gone), Serializable (sab gone, including phantom). Jitna upar jaoge utni safety, par utna hi locking zyada aur speed kam. 80/20 funda: hamesha Serializable mat lagao — apne logic ke hisaab se sabse neeche wala level chuno jo aapki zaroori anomaly ko rok de. Interview aur real projects dono mein yeh table yaad rakhna game-changer hai.

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

What is a transaction (so the anomalies make sense)

The three anomalies (derived from first principles)

1. Dirty Read — reading the uncommitted

2. Non-Repeatable Read — the same row changes under you

3. Phantom Read — the set of rows changes under you

HOW isolation levels map to anomalies (the 80/20 table)

Common mistakes (Steel-manned)

Recall drill

Flashcards

Connections

Concept Map

Hinglish (regional understanding)

Go deeper — visual, from zero

Test yourself — Databases

Connections