MVCC — multi-version concurrency control
WHY does MVCC exist?
WHAT we trade: a little extra storage (old versions) + background cleanup (garbage collection) in exchange for massively higher concurrency.
The mental model
HOW does a transaction decide what it sees?
Why each clause? Let's derive it instead of memorizing.
- Why must be committed? If the transaction that created this version hasn't committed, its data is uncommitted garbage to everyone else → invisible (otherwise we'd read dirty data).
- Why exclude in the snapshot's active set? Even if it commits later, at the instant I took my snapshot it was still running → for a consistent picture I must pretend it never happened. This is what gives repeatable reads.
- Why ? Any txid started after my snapshot — it's in my future, so invisible.
- Why the deleter logic is the mirror image? A deletion only "takes effect" for me if the deleting transaction is itself visible to me by the same three rules. If the deleter is invisible, the row still looks alive.
Worked Example 1 — Reader vs Writer (no blocking)
Setup: row balance = 100. txid clock currently at 50.
| Step | Event |
|---|---|
| t1 | T100 (reader) starts, snapshot: active set = {} , |
| t2 | T101 (writer) updates balance → 200. New version: (xmin=101, xmax=∞), old version now (xmin=…, xmax=101) |
| t3 | T101 commits |
| t4 | T100 reads balance |
What does T100 read?
- New version
xmin=101: is ? No → invisible. Why this step? T101 started after T100's snapshot, so it's in T100's future. - Old version
xmax=101: deleter 101 invisible (same reason) → old version still alive → read 100. ✅
T100 read 100 without ever waiting for T101. Reader didn't block writer; writer didn't block reader.
Worked Example 2 — Two writers conflict
Setup: row stock = 5. T200 and T201 both want to decrement it.
- T200 reads stock=5, updates → 4 (creates
xmin=200). - T201 also wants to update the same row. It tries to write a new version.
Why this step? MVCC removes read–write blocking but write–write on the same row still conflicts (you can't have two "current" versions both claiming to supersede the same one). So:
- T201 must wait for T200 to commit/abort (row-level write lock on the latest version).
- If T200 commits, T201 (under Snapshot Isolation) re-checks and gets a serialization error ("could not serialize access due to concurrent update") or re-reads, depending on isolation level.
Worked Example 3 — Snapshot Isolation anomaly (Write Skew)
Garbage Collection (the hidden cost)
Forecast-then-Verify
Flashcards
What does MVCC stand for?
In MVCC, what two metadata fields tag each row version?
Core promise of MVCC about readers and writers?
Why can a reader avoid waiting for a concurrent writer?
What is a snapshot in MVCC?
A version is visible iff what two conditions hold?
Does MVCC eliminate ALL locking?
What anomaly does Snapshot Isolation still allow?
What is table bloat in MVCC?
Why are long-running transactions harmful under MVCC?
Why is a txid >= xmax_snap invisible to a snapshot?
Recall Feynman: explain to a 12-year-old
Imagine a shared diary that lots of people read and write at once. Instead of one person locking the diary so nobody else can touch it, the diary keeps photocopies of every old page. When you start reading, you get your own photocopy of how the diary looked right then. Someone else can scribble a new page at the same time — you don't care, you're reading your photo. Nobody waits in line. The only fight is when two people try to rewrite the exact same page — then one has to wait. Later, a cleaner throws away photocopies nobody is looking at anymore.
Connections
- Snapshot Isolation — the isolation level MVCC most naturally implements.
- Serializability — what SI fails to fully provide (write skew).
- Two-Phase Locking (2PL) — the pessimistic alternative MVCC competes with.
- ACID Properties — MVCC is a mechanism for Isolation.
- Write-Ahead Logging (WAL) — durability layer underneath versioned storage.
- VACUUM and Garbage Collection — cleanup of dead versions.
- Transaction IDs and Wraparound — txid clock that powers xmin/xmax.
- Optimistic vs Pessimistic Concurrency Control.
Concept Map
Hinglish (regional understanding)
Intuition Hinglish mein samjho
Dekho, MVCC ka core idea bahut simple hai: database har row ki multiple versions rakhta hai. Jab koi writer row ko update karta hai, woh purani value ko delete nahi karta — woh ek nayi version bana deta hai aur purani ko mark kar deta hai "isko txid X ne supersede kiya". Iska fayda? Jo log read kar rahe hain, woh purani version padh lete hain bina kisi wait ke. Isliye famous line hai: readers never block writers, writers never block readers.
Har version pe do tags hote hain — xmin (kis transaction ne banaya) aur xmax (kis transaction ne hataya). Jab aap transaction start karte ho, aapko ek snapshot milta hai — yaani "us time pe kaun kaun commit ho chuka tha" ka frozen photo. Visibility ka rule yaad rakho: version tabhi dikhega jab uska janam dikhe (xmin committed aur snapshot se pehle) aur uski maut na dikhe (xmax abhi tak invisible). Mnemonic: "born before, not yet buried".
Lekin ek trap hai — log sochte hain MVCC matlab koi lock nahi. Galat. Reader-writer free hai, par do writer agar same row pe likhna chahein to ek ko wait karna padega ya serialization error aayega ("RW free, WW fee"). Aur ek aur ghotala: write skew — Snapshot Isolation me do transactions alag-alag rows likhke ek cross-row constraint tod sakte hain (jaise dono doctors off-call ho jaayein). Isliye SI poori tarah serializable nahi hoti.
Last important baat: purani versions jamaa hoti rehti hain, isliye VACUUM / garbage collection chahiye, warna table bloat ho jaata hai. Aur agar koi transaction ghanto open rehta hai (long reporting query), to GC ruk jaata hai kyunki shayad woh purani version dekh raha ho — isliye long-open transactions MVCC me anti-pattern hain. Bas itna samajh lo to exam aur real DB dono me strong ho.