MVCC & Locking — Snapshots, Row Locks & Deadlocks

How databases give isolation without everyone blocking

If every read took a lock, a long report would freeze all writers. Modern databases (Postgres, InnoDB, Oracle) use MVCC — Multi-Version Concurrency Control. A write doesn't overwrite a row; it creates a new version. Each transaction reads from a snapshot — the set of versions committed as of its start. So readers never block writers, and writers never block readers.

How it works (Postgres model)

Every row version carries xmin (the transaction that created it) and xmax (the one that deleted/superseded it). A version is visible to a transaction if its xmin is committed-and-before-my-snapshot and its xmax is not. An UPDATE is really "mark the old version dead (set xmax) + insert a new version" — which is why this is also how Repeatable Read gives you a stable snapshot: you keep reading the versions visible at your start.

Locking still exists — for writes

Two transactions writing the same row must serialize: the second waits on a row lock. Cross-row, opposite-order writes can deadlock — and here the DB does what your app can't: it detects the wait-for cycle and aborts one transaction as the victim (you get a deadlock error; retry). Same cycle as the Dining Philosophers; same fix — consistent lock ordering.

-- deadlock: two sessions, opposite update order
-- S1: UPDATE a; UPDATE b;     S2: UPDATE b; UPDATE a;
-- Postgres: "ERROR: deadlock detected" -> one is rolled back. Fix: always update by ascending id.

The MVCC tax: bloat & vacuum

Dead versions pile up; VACUUM reclaims them. A long-running transaction holds a snapshot that keeps old versions "maybe still needed," so vacuum can't clean them → table bloat and slow scans. This is the concrete reason "keep transactions short" matters.

Takeaways

• MVCC = versioned rows + per-transaction snapshots → readers and writers don't block each other.
• Writes to the same row still serialize via row locks; cross-row cycles deadlock and the DB aborts a victim.
• Long transactions cause bloat (vacuum can't reclaim) — keep them short; retry on deadlock.

---

Re-authored for this guide; version-chain diagram hand-authored as SVG. Follows the PostgreSQL MVCC docs and CMU 15-445. See also: Isolation Levels & Anomalies, Transactions & ACID, and (Concurrency) Deadlock.

I just read a lesson on **MVCC & Locking — Snapshots, Row Locks & Deadlocks** (Databases) and want to truly understand it. Explain MVCC & Locking — Snapshots, Row Locks & Deadlocks from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **MVCC & Locking — Snapshots, Row Locks & Deadlocks** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **MVCC & Locking — Snapshots, Row Locks & Deadlocks** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **MVCC & Locking — Snapshots, Row Locks & Deadlocks** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.