4.4.17
Intuition Ek-sentence coreIsolation levels ek dial hain jo correctness aur speed ke beech trade karta hai: jitna zyada aap concurrent transactions ko akele hone ka natak karne do, utne zyada "ghosts" (anomalies) woh dekh sakte hain — lekin utna hi zyada concurrency aap allow karte ho.
Definition Problem kya haiEk database kaafi saari transactions ko ek saath serve karta hai. Agar do transactions ek hi rows ko touch karein, toh naïve interleaving aisa result de sakta hai jo kisi bhi serial (ek-ek karke) order se match na kare. Isolation levels SQL standard ka jawab hai is sawaal ka: "Throughput ke badle main kitni interleaving weirdness tolerate karne ko tayyar hoon?"
"Gold standard" hai serializability : concurrent result kisi na kisi serial execution order ke barabar hona chahiye. Lekin sab kuch lock karke yeh guarantee karna slow hai. Toh SQL standard 4 levels define karta hai, har ek ek specific set of read anomalies ko ban karta hai.
Transaction T1 ek aisi row padhta hai jo T2 ne likhi toh hai par commit nahi ki . Agar T2 rollback kar le, toh T1 ne aisa data padha jo officially kabhi exist hi nahi kiya .
Definition Non-repeatable readT1 ek row padhta hai, T2 usi row pe UPDATE commit karta hai, T1 use dobara padhta hai aur alag value milti hai. Same query, same row, do alag answers.
T1 ek query run karta hai WHERE condition ke saath (jaise WHERE age > 30), T2 usi condition se match karne wali row ko INSERT/DELETE commit karta hai, T1 dobara query run karta hai aur alag set of rows milti hai. Ek nayi "phantom" row appear ho jaati hai.
Intuition Non-repeatable vs phantom — WHAT ka farqNon-repeatable read = ek existing row ki value change ho jaati hai.
Phantom read = kisi predicate se match karne wala rows ka set change ho jaata hai (rows appear/disappear hote hain).
Dono "maine do baar poocha aur alag answers mile" waali baat hai, lekin ek content ke baare mein hai, doosra membership ke baare mein.
Intuition Do mental modelsLock-based: zyada isolation = locks zyada der tak hold karo.
READ COMMITTED: read locks immediately read ke baad release ho jaate hain → values agle read pe change ho sakti hain.
REPEATABLE READ: commit tak read locks hold karo → jo rows aapne padhi hain unhe update nahi kiya ja sakta.
SERIALIZABLE: range/predicate locks add karo → koi bhi aisi range mein insert nahi kar sakta jise aapne query kiya (phantoms khatam).
MVCC (snapshot-based, jaise PostgreSQL): har transaction ek version snapshot se padhta hai.
READ COMMITTED: har statement pe naaya snapshot lo.
REPEATABLE READ: transaction start pe ek snapshot lo, hamesha reuse karo → PG mein repeatable bhi aur koi phantom bhi nahi.
Worked example Dirty read, step by stepT2: UPDATE accounts SET bal = 0 WHERE id=1; -- not committed
T1: SELECT bal FROM accounts WHERE id=1; -- reads 0 (DIRTY)
T2: ROLLBACK; -- bal is actually back to old value
T1: acts on 0 that never existed → BUG
Yeh step kyun? READ UNCOMMITTED pe, T1 ke read ko T2 ka lock release hone ki zaroorat nahi, toh woh uncommitted state peek kar leta hai. READ COMMITTED pe raise karo → T1 block hota ya committed purani value padhta , kabhi 0 nahi.
Worked example Non-repeatable read at READ COMMITTEDT1: SELECT bal FROM accounts WHERE id=1; -- 100
T2: UPDATE accounts SET bal=50 WHERE id=1; COMMIT;
T1: SELECT bal FROM accounts WHERE id=1; -- 50 (DIFFERENT!)
Yeh step kyun? READ COMMITTED har statement pe fresh snapshot leta hai, toh T1 ka doosra read T2 ki committed change dekh leta hai. Fix: REPEATABLE READ → T1 apna transaction-start-ka snapshot rakhta hai, dono reads 100 return karte hain.
Worked example Phantom read at REPEATABLE READ (standard behavior)T1: SELECT count(*) FROM users WHERE age>30; -- 5
T2: INSERT INTO users(age) VALUES (40); COMMIT;
T1: SELECT count(*) FROM users WHERE age>30; -- 6 (PHANTOM)
Yeh step kyun? REPEATABLE READ (standard ke hisaab se) unhi rows ko protect karta hai jo aapne already padhi hain , predicate ki membership ko nahi. Ek brand-new row WHERE se match karti hai aur appear ho jaati hai. Fix: SERIALIZABLE → age>30 pe range/predicate lock T1 ke khatam hone tak T2 ka insert block karta hai.
Recall Padhne se pehle: outputs predict karoDo transactions READ COMMITTED pe. T1 bal padhta hai (=100). T2 bal=200 commit karta hai. T1 dobara padhta hai.
Forecast: doosra read = ? · Kya yeh ek anomaly hai, aur kaun si?
Verify: doosra read = 200 . Yeh ek non-repeatable read hai — READ COMMITTED pe allowed, REPEATABLE READ pe banned.
Common mistake "MySQL mein REPEATABLE READ phantoms allow karta hai, toh standard galat hai."Kyun sahi lagta hai: SQL standard table kehta hai phantoms REPEATABLE READ pe allowed hain.
Fix: Standard ek minimum guarantee set karta hai; engines isse exceed kar sakte hain. MySQL InnoDB next-key locking use karta hai aur PostgreSQL snapshots use karta hai, toh dono REPEATABLE READ pe kaafi phantoms block karte hain. "Standard dwara allowed" ≠ "aapke DB mein hona zaroori hai".
Common mistake "SERIALIZABLE ka matlab transactions literally ek-ek karke run hoti hain."Kyun sahi lagta hai: serial shabd.
Fix: SERIALIZABLE sirf guarantee karta hai ki result kisi serial order ke barabar hai . Engines phir bhi concurrently run karte hain; SERIALIZABLE Serializable Snapshot Isolation (SSI) use kar sakta hai jo optimistically run karta hai aur un transactions ko abort karta hai jo serializability break karein. Aapko yeh serialization_failure errors ke roop mein dikhai deta hai jinhein retry karna padta hai.
Common mistake "Zyada isolation = hamesha safer, toh bas SERIALIZABLE har jagah use karo."Kyun sahi lagta hai: correctness achhi cheez hai.
Fix: SERIALIZABLE zyada blocking/deadlocks/retries cause karta hai, throughput hurt hoti hai. Sabse low level choose karo jo un anomalies ko rokta ho jo aapki app ko actually matter karti hain . (PostgreSQL/Oracle mein default READ COMMITTED hai; MySQL InnoDB REPEATABLE READ default karta hai.)
Common mistake "Dirty reads aur non-repeatable reads ek hi cheez hain."Kyun sahi lagta hai: dono "changing data padhna" involve karte hain.
Fix: Dirty read = uncommitted data padhna (jo rollback ho sakta hai). Non-repeatable read = committed data padhna jo aapke do reads ke beech change ho gaya. Alag cause, alag level har ek ko rokta hai.
Mnemonic Order aur guarantees yaad rakho"U-C-R-S" = U ncommitted → C ommitted → R epeatable → S erializable, weakest se strongest.
Anomalies ek ek karke hatti hain : D irty, N on-repeatable, P hantom → "DNP" har step neeche pe ek drop karta hai (pehle level ke baad).
"Read U ncommitted reads U ncommitted; Read C ommitted reads C ommitted; R epeatable R epeats; S erializable is S afe."
Recall Feynman: 12-saal ke bacche ko explain karoSocho database ek shared notebook hai aur log ek saath usme likhte hain.
READ UNCOMMITTED: tum kisi ki pencil scribble pehle hi padh lete ho jab usne decide bhi nahi kiya ki rakhni hai ya nahi — woh erase kar sakta hai. Risky.READ COMMITTED: tum sirf woh cheezein padhte ho jo logon ne sach mein ink se likh di hain . Lekin agar tum ek hi line do baar dekho, kisine beech mein dobara ink kar di ho sakti hai, toh woh change ho jaati hai.REPEATABLE READ: tum shuru mein apne page ki ek photo lo aur photo se padhte ho, toh jo lines tum dekh chuke ho woh tumhare neeche kabhi nahi badlengi.SERIALIZABLE: aisa hai jaise sabki strict baari hoti hai — final notebook bilkul waisi dikhti hai jaise log ek ek karke gaye hon. Koi surprise nahi, lekin tum zyada wait karte ho.
Teen classic read anomalies kaun si hain? Dirty read, non-repeatable read, phantom read.
Kaun sa isolation level dirty reads allow karta hai? Sirf READ UNCOMMITTED.
Non-repeatable read aur phantom read mein kya farq hai? Non-repeatable = do reads ke beech existing row ki value change ho jaati hai; phantom = ek predicate se match karne wala rows ka set change ho jaata hai (rows insert/delete hote hain).
Kaun sa level non-repeatable reads rokta hai lekin (standard ke hisaab se) phantoms allow karta hai? REPEATABLE READ.
4 levels ko weakest → strongest order karo. READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.
SERIALIZABLE actually kya guarantee karta hai? Concurrent result kisi na kisi serial (ek-ek karke) execution order ke barabar hai — literal serial execution nahi.
MVCC mein READ COMMITTED aur REPEATABLE READ snapshots mein kya farq hai? READ COMMITTED har statement pe fresh snapshot leta hai; REPEATABLE READ transaction start pe ek snapshot leta hai aur reuse karta hai.
Locking ke under phantoms kaise rokein jaate hain? Range/predicate (next-key) locks queried range mein inserts block karte hain.
PostgreSQL vs MySQL InnoDB mein default isolation level? PostgreSQL = READ COMMITTED; MySQL InnoDB = REPEATABLE READ.
SERIALIZABLE har jagah kyun nahi use karte? Zyada blocking, deadlocks, aur serialization-failure retries → kam throughput; sabse low level use karo jo tumhari app ke anomalies rokta ho.
ACID properties — ACID mein I exactly yahi isolation hai.MVCC (Multi-Version Concurrency Control) — READ COMMITTED / REPEATABLE READ ke peeche snapshot mechanism.Two-Phase Locking (2PL) — serializability tak lock-based raasta.Serializable Snapshot Isolation (SSI) — PostgreSQL SERIALIZABLE optimistically kaise karta hai.Deadlocks — zyada levels pe locks hold karne ka side effect.Write skew anomaly — anomaly jo snapshot isolation allow karta hai lekin SERIALIZABLE rokta hai.
trade correctness for speed
existing row value changes
row set membership changes
