Optimistic vs pessimistic concurrency control

4.4.20 · Coding › Databases

WHAT is concurrency control?

Woh dushman jisse hum defend kar rahe hain: lost updates, dirty reads, write skew — yeh woh anomalies hain jo tab appear hoti hain jab concurrent transactions interleave karte hain. Note karo yeh shape mein differ karte hain: lost update tab hota hai jab do transactions ek same row ko read-modify-write karte hain; write skew thoda subtle hai — do transactions ==overlapping data read karte hain aur phir alag-alag rows mein write karte hain==, har ek akele valid hota hai lekin milke ek invariant violate karte hain (jaise dono doctors "2 on-call" read karte hain aur har ek shift se baahar chala jaata hai, zero bachta hai). Toh haari defense sirf single-row contention se zyada cover karni chahiye.

WHY do hain do alag strategies?

Poora decision ek number par aata hai: kisi conflict ke actually hone ki probability.

Isse hum precise bana sakte hain. Maano:

$p$ = probability ki koi given transaction conflict kare,
$L$ = locking ka overhead (acquire + hold + release) per transaction,
$R$ = ek failed optimistic validation ke baad transaction dobara karne ki cost.

Yeh single inequality poore topic ka 80/20 hai: agar conflicts lock-vs-retry cost ratio ke relative rare hain, toh optimistic jao.

Figure — Optimistic vs pessimistic concurrency control

HOW kaam karta hai har ek

Pessimistic Concurrency Control (PCC)

Locks se implement hota hai. Sabse common scheme hai Two-Phase Locking (2PL):

Reads ke liye Shared (S) lock, writes ke liye Exclusive (X) lock.
Cost: deadlock ka risk (T1 A hold karta hai B ka wait, T2 B hold karta hai A ka wait) → detection/timeout zaroori.
SQL mein SELECT ... FOR UPDATE explicit pessimistic locking hai.
Write skew ke liye predicate/range locking chahiye (ya serializable isolation), sirf row locks nahi, kyunki conflicting writes alag-alag rows ko touch karte hain.

Optimistic Concurrency Control (OCC)

Teen phases, kaam ke dauran koi locks nahi:

Classic implementation hai version checking:

-- read
SELECT balance, version FROM accounts WHERE id = 1;  -- balance=100, version=7
-- ... naya balance compute karo = 100 - 30 = 70 ...
-- WHERE mein validation bake karke write karo:
UPDATE accounts SET balance = 70, version = 8
WHERE id = 1 AND version = 7;     -- 0 rows affect hote hain agar kisi ne version bump kiya → retry!

Worked Examples

Common Mistakes (Steel-manned)

Recall Feynman: ek 12-saal ke bacche ko samjhao

Socho ek library ki kitaab jise har koi chahta hai. Pessimistic tarika: jo pehle le jaata hai woh use ek box mein lock kar deta hai — baaki sab line mein wait karte hain. Koi kabhi fight nahi karta, lekin line slow ho sakti hai, aur do careful log ek doosre ke boxes par hamesha ke liye wait kar sakte hain (woh deadlock hai). Optimistic tarika: hum kitaab ki photocopy karte hain taaki sab ek saath padh sakein. Jab aap apni edited copy waapis dena chahte ho, librarian check karta hai: "kya kisi ne yeh page badla jab aapke paas tha?" Agar haan, sorry — apni edits naye page par dobara karo. Bahut accha jab log rarely same page edit karte hain, bura jab sab page 1 par likhte hain.

Flashcards

Pessimistic concurrency control ki core assumption kya hai?

Ki conflicts likely hain, isliye woh unhe rokne ke liye data ko access karne se pehle lock karta hai.

Optimistic concurrency control ki core assumption kya hai?

Ki conflicts rare hain, isliye transactions freely chalte hain aur conflicts sirf commit time par check kiye jaate hain.

OCC ke teen phases kya hain?

Read (private copy par kaam karo + versions record karo), Validate (check karo ki kuch jo padha tha woh badla toh nahi), Write (commit karo ya abort+retry).

OCC mein validation usually SQL mein kaise implement hoti hai?

Ek conditional UPDATE ... WHERE version = <old>; agar woh 0 rows affect kare, toh conflict hua aur aap retry karte ho.

Derive karo kab optimistic beats pessimistic.

Jab

R \frac{p}{1 - p} < L

, yaani conflict probability

p < \frac{L}{L + R}

Expected optimistic cost mein

\frac{p}{1 - p}

factor kyun hota hai?

Retries ek geometric series banate hain (har retry prob

1 - p

se succeed karti hai), mean retries

\frac{p}{1 - p}

deta hai.

Pessimistic 2PL mein serializability kya guarantee karta hai?

Two-phase rule: growing phase (sirf locks acquire karo) phir shrinking phase (sirf release karo), kisi bhi release ke baad acquire nahi.

Kaun sa scheme deadlock kar sakta hai, aur kaun sa abort karta hai?

Pessimistic 2PL deadlock kar sakta hai (detection/timeout chahiye); OCC kabhi deadlock nahi karta — woh abort aur retry karta hai.

Kya MVCC aur optimistic concurrency control same hain?

Nahi. MVCC ek storage technique hai (snapshot reads ke liye multiple versions) jo optimistic ya pessimistic dono control ko underpin kar sakti hai.

Flash-sale hot row ke liye jisme p→1 ho, kaun sa control appropriate hai aur kyun?

Pessimistic (ya queue/atomic), kyunki OCC ka

\frac{p}{1 - p}

retry cost retry storms/livelock mein explode ho jaata hai.

Write skew ek lost update se kaise differ karta hai?

Lost update do read-modify-writes same row par hain; write skew mein do transactions overlapping data read karte hain aur phir alag-alag rows mein write karte hain, jointly ek invariant break karte hain.

Write skew rokne ke liye kya chahiye?

Predicate/range locking ya serializable isolation — plain row locks aur single-row version checks isse nahi pakad paate kyunki writes alag rows ko hit karti hain.

Connections

ACID properties — isolation (I) wahi hai jo concurrency control enforce karta hai.
Two-Phase Locking (2PL) — canonical pessimistic implementation.
MVCC — version storage jo snapshot isolation power karta hai.
Serializability and Isolation Levels — correctness ka target.
Write Skew and Phantoms — anomaly jiske liye predicate locking / serializable chahiye.
Deadlocks — ek hazard jo sirf pessimistic locking mein hota hai.
Compare-and-Swap (CAS) — optimistic version checking ka hardware-level analogue.