MVCC — multi-version concurrency control — Hinglish notes

4.4.28 · Coding › Databases

MVCC exist kyun karta hai?

WHAT hum trade karte hain: thodi extra storage (old versions) + background cleanup (garbage collection) ke badle mein massively higher concurrency milti hai.

Ek transaction decide kaise karta hai ki wo kya dekhega?

Har clause kyun? Chalo ise yaad karne ki jagah derive karte hain.

$x min$ committed kyun hona chahiye? Agar jo transaction ne yeh version create kiya wo commit nahi hua, toh uska data baaki sabke liye uncommitted garbage hai → invisible (warna hum dirty data padhte).
$x min$ ko snapshot ke active set se exclude kyun karein? Chahe wo baad mein commit kare, jis pal maine apna snapshot liya wo tab bhi run kar raha tha → ek consistent picture ke liye mujhe pretend karna hoga ki yeh kabhi hua hi nahi. Yahi repeatable reads deta hai.
$x min < x_{ma x}^{s na p}$ kyun? Koi bhi txid $\geq x_{ma x}^{s na p}$ mere snapshot ke baad shuru hua — yeh mera future hai, toh invisible hai.
Deleter logic mirror image kyun hai? Ek deletion mere liye tab "effect" leti hai jab deleting transaction khud mere liye unhi teen rules se visible ho. Agar deleter invisible hai, toh row abhi bhi alive dikhti hai.

Worked Example 1 — Reader vs Writer (koi blocking nahi)

Setup: row balance = 100. txid clock abhi 50 par hai.

Step	Event
t1	T100 (reader) shuru hota hai, snapshot: active set = {} , $x_{ma x}^{s na p} = 100$
t2	T101 (writer) balance → 200 update karta hai. Naya version: `(xmin=101, xmax=∞)`, purana version ab `(xmin=…, xmax=101)`
t3	T101 commit karta hai
t4	T100 balance padhta hai

T100 kya padhega?

Naya version xmin=101: kya $101 < x_{ma x}^{s na p} = 100$ ? Nahi → invisible. Yeh step kyun? T101 T100 ke snapshot ke baad shuru hua, toh yeh T100 ke future mein hai.
Purana version xmax=101: deleter 101 invisible (same reason) → purana version abhi bhi alive → 100 padha. ✅

T100 ne 100 padha bina T101 ka kabhi wait kiye. Reader ne writer ko block nahi kiya; writer ne reader ko block nahi kiya.

Worked Example 2 — Two writers conflict

Setup: row stock = 5. T200 aur T201 dono isse decrement karna chahte hain.

T200 stock=5 padhta hai, → 4 update karta hai (xmin=200 create karta hai).
T201 bhi same row update karna chahta hai. Wo ek naya version likhne ki koshish karta hai.

Yeh step kyun? MVCC read–write blocking hata deta hai lekin write–write same row par abhi bhi conflict karta hai (do "current" versions nahi ho sakte jo dono ek hi ko supersede karne ka claim karein). Toh:

T201 ko T200 ke commit/abort hone ka wait karna hoga (latest version par row-level write lock).
Agar T200 commit karta hai, toh T201 (Snapshot Isolation ke under) re-check karta hai aur serialization error milti hai ("could not serialize access due to concurrent update") ya re-reads karta hai, isolation level par depend karta hai.

Worked Example 3 — Snapshot Isolation anomaly (Write Skew)

Worked example SI ≠ Serializable kyun hai

Constraint: "kam se kam ek doctor on-call hona chahiye." Abhi Alice & Bob dono on-call hain.

T1: snapshot Alice+Bob ko on dekhta hai. Check karta hai "≥1 aur on-call?" → Bob hai → Alice off ho jaati hai.
T2 (concurrent): same snapshot Alice+Bob ko on dekhta hai. Check karta hai → Alice hai → Bob off ho jaata hai.
Dono commit karte hain. Unhone alag rows likhi (koi write-write conflict nahi!) → dono succeed karte hain.
Result: zero doctors on-call. Constraint violate ho gayi.

Yeh step kyun important hai: Snapshot Isolation ek frozen past padhta hai, toh koi bhi transaction doosre ka update nahi dekhta. Alag rows → koi conflict detect nahi hoti. Yeh write skew hai, ek anomaly jo SI allow karta hai lekin true serializability forbid karta hai. Fix: SSI (Serializable Snapshot Isolation) ya explicit SELECT ... FOR UPDATE.

Garbage Collection (hidden cost)

Forecast-then-Verify

Flashcards

MVCC ka full form kya hai?

Multi-Version Concurrency Control.

MVCC mein har row version ko kaun se do metadata fields tag karte hain?

xmin (creating txid) aur xmax (deleting/superseding txid).

MVCC ka readers aur writers ke baare mein core promise kya hai?

Readers writers ko block nahi karte aur writers readers ko block nahi karte (different access ke liye).

Ek reader concurrent writer ka wait kyun avoid kar sakta hai?

Row ka purana version rakha jata hai, toh reader write se pehle ka consistent snapshot padhta hai.

MVCC mein snapshot kya hota hai?

Ek chosen instant par kaunse transactions commit ho chuke the uska frozen record — (xmin_snap, xmax_snap, active set).

Ek version visible hota hai iff kaun si do conditions hoon?

Uska xmin visible ho (committed, active set mein nahi, < xmax_snap) AUR uska xmax abhi visible NA ho.

Kya MVCC sari locking khatam kar deta hai?

Nahi — same row par write-write conflicts abhi bhi serialize hoti hain.

Snapshot Isolation abhi bhi kaun sa anomaly allow karta hai?

Write skew (concurrent txns overlapping data padhti hain, disjoint rows likhti hain, cross-row constraint violate hoti hai).

MVCC mein table bloat kya hota hai?

Dead old row versions ka accumulation jo garbage-collect nahi hui hain (VACUUM).

MVCC ke under long-running transactions harmful kyun hain?

Ye GC horizon rok ke rakhti hain, old versions clean hone se rokti hain → bloat aur slow scans.

Snapshot ko txid >= xmax_snap kyun invisible hota hai?

Yeh snapshot lene ke baad shuru hua, toh transaction ke future mein hai.

Recall Feynman: 12-saal ke bachche ko explain karo

Socho ek shared diary jo bahut log ek saath padhte aur likhte hain. Ek person diary lock karne ki bajay taaki koi aur touch na kar sake, diary har purane page ki photocopies rakhti hai. Jab tum padhna shuru karte ho, tumhe apni photocopy milti hai diary kaisi dikhti thi us moment. Koi aur usi time ek naya page scribble kar sakta hai — tumhe koi fark nahi, tum apni photo padh rahe ho. Koi line mein nahi wait karta. Jhagda tab hota hai jab do log bilkul same page rewrite karne ki koshish karte hain — tab ek ko wait karna padta hai. Baad mein, ek cleaner woh photocopies phek deta hai jise koi nahi dekh raha.

Connections

Snapshot Isolation — isolation level jo MVCC sabse naturally implement karta hai.
Serializability — jo SI fully provide karne mein fail karta hai (write skew).
Two-Phase Locking (2PL) — pessimistic alternative jisse MVCC compete karta hai.
ACID Properties — MVCC ek mechanism hai Isolation ke liye.
Write-Ahead Logging (WAL) — versioned storage ke neeche durability layer.
VACUUM and Garbage Collection — dead versions ki cleanup.
Transaction IDs and Wraparound — txid clock jo xmin/xmax power karta hai.
Optimistic vs Pessimistic Concurrency Control.

MVCC — multi-version concurrency control