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

4.4.18 · Coding › Databases

Transaction kya hota hai (taaki anomalies samajh aayein)

Woh key insight jo teeno anomalies unlock karti hai:

Teen anomalies (first principles se nikali gayi)

Hum classify karte hain kya change hua aur kab ke basis pe. Socho transaction $T_{1}$ read kar raha hai, aur $T_{2}$ concurrently kuch kar raha hai.

1. Dirty Read — uncommitted ko padhna

WHY yeh bura hai: Aapne ek aisi baat pe act kiya jo un-done ho gayi. Jaise kisi price tag par trust karna jo koi hatane hi wala tha.

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

Yeh step kyun? $T_{1}$ ne $T_{2}$ ki provisional write padhi. Rollback ke baad, $T_{1}$ ki knowledge ek jhooth hai. Koi bhi decision jo $T_{1}$ ne liya (jaise "card block karo, balance 0 hai") galat hai.

2. Non-Repeatable Read — wahi row aapke neeche change ho jaati hai

WHY yeh bura hai: Aapka transaction internally inconsistent hai — usne ek row ke baare mein do alag sach dekhe.

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!)

Yeh step kyun? $T_{1}$ ke dono reads committed data ke hain (toh dirty nahi hai), lekin committed value unke beech mein move ho gayi. $T_{1}$ yeh trust nahi kar sakta ki "yeh toh main pehle hi padh chuka hoon."

3. Phantom Read — rows ka set aapke neeche change ho jaata hai

WHY yeh non-repeatable se alag hai: Non-repeatable = ek existing row ki value change hui. Phantom = result set ki membership change hui (naye "phantom" rows appear hote hain).

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!)

Yeh step kyun? Koi ek existing row nahi badli — ek bilkul nai row us range mein ghus gayi jo query scan karti hai. Toh existing rows par row-level locks kaam nahi aate; aapko range/predicate locks chahiye.

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

HOW isolation levels anomalies se map karte hain (80/20 table)

Formula Anomaly-to-isolation-level matrix

SQL standard levels ko define karta hai kaunsi anomalies woh forbid karte hain ke basis pe (✓ = prevented, ✗ = allowed):

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

*SQL standard Repeatable Read par phantoms allow karta hai; kuch engines (jaise InnoDB) actually inhe bhi next-key locks ke through prevent karte hain.

Common mistakes (Steel-manned)

Recall Feynman: ek 12-saal ke bachche ko explain karo

Socho tum apne dost ka homework answer copy kar rahe ho.

Dirty read: tumne ek aisa answer copy kiya jo unhone pencil se likha tha aur phir erase kar diya — ab tumhara answer kisi aisi cheez par based hai jo mita di gayi. (uncommitted data, rolled back)
Non-repeatable read: tum question 3 ka answer dekhte ho, nazar hatate ho, phir dekhte ho, aur unhone use badal diya. Same question, naya answer. (ek row ki value change hui)
Phantom read: tum "page par 5 questions hain" count karte ho, phir dobara dekhte ho, aur ek naya question jaadu se aa gaya jisse 6 ho gaye. (ek nai row tumhare result mein aa gayi) Librarian (database) strict ho sakta hai (poora page lock karo taaki kuch change na ho — slow but safe) ya chill (cheezein change hone do — fast but glitchy). Aap choose karte ho kitna strict hona hai.

Recall drill

Flashcards

Dirty read kya hota hai?

T_{1}

ek aisi row padhta hai jo

T_{2}

ne modify ki hai lekin commit nahi ki; agar

T_{2}

roll back kare, toh

T_{1}

ne ek aisi value padhi jo kabhi officially exist hi nahi ki.

Non-repeatable read kya hota hai?

T_{1}

wahi row do baar padhta hai aur alag values milti hain kyunki

T_{2}

ne beech mein ek UPDATE commit kar diya.

Phantom read kya hota hai?

T_{1}

wahi range query do baar chalata hai aur alag set of rows milti hai kyunki

T_{2}

ne condition se match karne wali INSERT/DELETE commit kar di.

Key difference: non-repeatable vs phantom?

Non-repeatable = ek existing row ki value change hui (UPDATE). Phantom = matching rows ka set change hua (INSERT/DELETE).

Kaunsa isolation level dirty reads prevent karta hai lekin non-repeatable reads allow karta hai?

Read Committed.

Kaunsa isolation level non-repeatable reads prevent karta hai lekin (SQL standard ke hisaab se) phantoms allow kar sakta hai?

Repeatable Read.

Kaunsa isolation level teeno anomalies prevent karta hai?

Serializable.

Serializable har jagah kyun use nahi karte?

Yeh locking/aborts maximize karta hai, concurrency aur throughput kam karta hai aur deadlocks ka risk badhata hai; woh lowest level choose karo jo un anomalies ko forbid kare jinka tumhe care hai.

Phantoms rokne ke liye kis tarah ka lock chahiye?

Range / predicate (jaise next-key) locks, sirf row locks nahi.

Kya Read Committed guarantee karta hai ki ek hi row ke do reads match karenge?

Nahi — yeh sirf guarantee karta hai ki har read committed data dekhe, reads ke beech stability nahi.

Connections

ACID Properties — Isolation (woh "I") exactly yahi hai jo yeh anomalies violate karti hain.
Isolation Levels — woh four-level ladder jo har anomaly fix karta hai.
Locking — Shared and Exclusive Locks — dirty/non-repeatable reads prevent karne ka mechanism.
MVCC — Multiversion Concurrency Control — Postgres/InnoDB dwara use kiya jaane wala snapshot approach jo locks avoid karta hai.
Two-Phase Locking (2PL) — serializability guarantee karne wala protocol.
Deadlocks — stronger isolation ki cost.