MVCC, Snapshot Isolation & Write Skew

1. 1

   simple CRUD → fresh statement snapshot

   Stay on READ COMMITTED

   Cheap. Dirty reads are gone. Non-repeatable reads and write skew are allowed. Fine for catalog browsing, wrong for multi-row money invariants.

2. 2

   first statement → one snapshot

   SI (Postgres REPEATABLE READ)

   Same-row lost updates abort. Multi-row invariants still write-skew. This is the hub's default mental model.

3. ?

   Winner for multi-row rules: SSI, FOR UPDATE, or a constraint

   SERIALIZABLE aborts the dangerous rw-cycle (40001 → retry whole txn). SELECT … FOR UPDATE serializes the read set. A CHECK / EXCLUDE / guard row makes the invariant physical. Pick the cheapest that actually closes the hole — details on the sibling pages.

Overview

Almost every modern OLTP database (PostgreSQL, MySQL InnoDB, Oracle, SQL Server row-versioning, Cockroach, TiDB) uses Multi-Version Concurrency Control. Senior interviews love asking how readers avoid blocking writers, what isolation you actually get, and why REPEATABLE READ still lets write skew through.

If you only memorize "ACID" without MVCC mechanics, you will miss production bugs and interview depth questions.

By the end of this lesson you should be able to:

• Explain how xmin / xmax decide visibility under a snapshot
• Contrast READ COMMITTED vs SI vs SSI
• Reproduce the classic write-skew anomaly and fix it
• Reason about VACUUM / GC as the cost of MVCC
• Write application-level retry loops for serialization failures (40001)

Versions, not locks (for reads)

Under lock-based 2PL, a writer holds an exclusive lock and readers wait. Under MVCC:

• A write creates a new physical version of a logical row (or marks the old one deleted)
• A read picks the newest version that was committed as of the transaction's snapshot
• Result: readers do not block writers; writers do not block readers
• Writers may still block other writers on the same row (first-writer-wins / row locks)

This is why analytics queries on hot OLTP tables often "just work" without stopping the write path — they read older committed versions.

PostgreSQL visibility (xmin / xmax)

Each heap tuple carries system columns:

Visibility is not "xmin less than my xid". The engine consults the transaction's snapshot: which XIDs were committed, in-progress, or aborted at snapshot time.

• Your own in-progress writes are visible to you
• Uncommitted writes from others are not

UPDATE = insert a new version + set xmax on the old one. DELETE = set xmax (no physical remove yet). VACUUM later reclaims versions no longer visible to any snapshot.

A simplified teaching rule: a version is visible to snapshot S if its inserter is committed as of S (or is you), and its deleter is not committed as of S (and is not you deleting it).

flowchart TB
  a["Txn A SI"]
  b["Txn B SI"]
  d["doctors"]
  a -->|read Alice on,| d
  b -->|read Alice on,| d
  a -->|Alice off| d
  b -->|Bob off| d
  a -->|COMMIT| d
  b -->|COMMIT| d

ctid is not a stable row id — it changes on update and rewrite.

Snapshots and isolation levels

READ COMMITTED (Postgres default)

Each statement takes a new snapshot. You never see dirty (uncommitted) data, but two SELECTs in the same txn can disagree (non-repeatable reads). Fine for simple CRUD when that is acceptable.

Snapshot Isolation (SI)

When a transaction starts (or, depending on engine, on first statement), it gets a consistent snapshot:

• Sees only data committed before the snapshot
• Never sees partial results of concurrent writers
• Concurrent updates to the same row: first committer wins; the loser aborts or retries (lost-update prevention on that row)

PostgreSQL's REPEATABLE READ implements SI (stronger than the weak ANSI RR definition). MySQL InnoDB's RR is closer to SI for many workloads but has different gap-lock behavior — do not assume engines match ANSI labels 1:1.

SI prevents dirty reads, non-repeatable reads on the same row, and lost updates on the same row. It does not guarantee a schedule equivalent to some serial order of transactions. The classic hole is write skew.

Serializable Snapshot Isolation (SSI)

PostgreSQL SERIALIZABLE runs SI plus tracking of read-write dependencies (SIREAD / predicate locks). If the dependency graph contains a dangerous structure (potential anomaly), one txn aborts with SQLSTATE 40001 (serialization_failure). You must retry the whole transaction from the start.

Tradeoffs:

• Near-SI performance for many workloads; some false positives
• Coarse predicate locks (page/table) increase abort rates
• READ ONLY DEFERRABLE can wait for a conflict-free snapshot (great for reports)

Write skew: doctors on call

Invariant: at least one of Alice or Bob must remain on_call = true.

1. t1: Txn A and Txn B both BEGIN and read both rows (Alice on, Bob on)
2. t2: Each sees at least one on-call; A sets Alice off; B sets Bob off
3. t3: Both COMMIT

Each txn updated a different row, so no write-write conflict. Final state: both off. No serial order produces that. That is write skew.

Other shapes: two transfers checking "sum greater than or equal to 0" then debiting different accounts; two inventory adjusters each checking "total stock greater than or equal to N".

Fixes (pick one):

• SELECT ... FOR UPDATE on the read set (serialize the decision)
• SERIALIZABLE (SSI) + retry on 40001
• A constraint that materializes the invariant (guard row / CHECK)

Architecture

The txn manager assigns an XID and a snapshot (xmin horizon, xmax, in-progress XID list). WAL is orthogonal but paired with MVCC. Vacuum reclaims dead versions and freezes old XIDs.

Failure modes and ops

• Table bloat: heavy UPDATE/DELETE without vacuum means dead versions pile up, seq scans slow, disk grows
• Long-running transactions / idle-in-transaction: hold back the vacuum horizon; everyone pays
• XID wraparound: 32-bit XIDs must be frozen; autovacuum can force aggressive vacuum or refuse writes if ignored
• Index bloat: indexes also retain dead entries until vacuumed
• Hot rows: writers still serialize on the same logical row; MVCC does not magically make contended counters scale

Scaling levers: shorter transactions, SELECT ... FOR UPDATE / advisory locks where invariants span rows, SERIALIZABLE + retry for correctness-critical paths, partitioning to shrink vacuum scope, read replicas for stale-ok reads (never money invariants).

Choosing isolation in production

• Simple CRUD, tolerate non-repeatable reads: READ COMMITTED (Postgres default)
• Multi-statement consistency, protect same-row lost updates: REPEATABLE READ (SI)
• Multi-row invariants (balances, quotas, "at least one"): SERIALIZABLE or explicit locks / constraints
• Long read-only report: REPEATABLE READ / snapshot + READ ONLY; or a replica

Senior answer pattern: prefer the weakest level that preserves the invariant. If the invariant spans multiple rows and contention is low, SERIALIZABLE + retry is often cleaner than hand-rolled locking. If contention is high on a small key set, use row locks or a single "constraint row" / SELECT ... FOR UPDATE.

Full ANSI-vs-engine matrix: Isolation levels deep dive. Dangerous rw-cycles and 40001 retries: SSI vs Snapshot Isolation. Lock wait policies (NOWAIT, SKIP LOCKED): Row locks SELECT FOR UPDATE. Vacuum, freeze, wraparound: VACUUM, bloat & XID wraparound. Phantoms vs write skew as two different holes: Phantoms vs write skew.

Application patterns

A. Explicit lock to prevent write skew

# Sketch — psycopg. Lock both doctors before deciding.
def go_off_call(conn, doctor_id, peer_id):
    with conn.transaction():
        rows = conn.execute(
            """
            SELECT id, on_call FROM doctors
            WHERE id IN (%s, %s)
            FOR UPDATE
            """,
            (doctor_id, peer_id),
        ).fetchall()
        by_id = {r["id"]: r["on_call"] for r in rows}
        others_on = any(on for i, on in by_id.items() if i != doctor_id)
        if not others_on:
            raise RuntimeError("cannot leave: peer not on call")
        conn.execute(
            "UPDATE doctors SET on_call = false WHERE id = %s",
            (doctor_id,),
        )

// Sketch — node-postgres. BEGIN, FOR UPDATE, then UPDATE, then COMMIT.
await client.query("BEGIN");
const { rows } = await client.query(
  `SELECT id, on_call FROM doctors
   WHERE id = ANY($1::int[])
   FOR UPDATE`,
  [[doctorId, peerId]]
);
const peerOn = rows.some((r) => r.id === peerId && r.on_call);
if (!peerOn) throw new Error("cannot leave: peer not on call");
await client.query(`UPDATE doctors SET on_call = false WHERE id = $1`, [doctorId]);
await client.query("COMMIT");

B. SERIALIZABLE + retry (preferred when many multi-row invariants)

Retry must re-run all reads and writes of the logical unit of work. Never commit partial work and retry only the failing statement. Add jitter in production.

# Sketch — catch SerializationFailure, backoff, retry the whole txn.
def with_serializable_retry(conn, fn, *, max_attempts=8):
    delay = 0.01
    for attempt in range(1, max_attempts + 1):
        try:
            with conn.transaction():
                conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                return fn(conn)
        except SerializationFailure:
            if attempt == max_attempts:
                raise
            time.sleep(delay)
            delay = min(delay * 2, 0.5)

// Sketch — SQLSTATE 40001: ROLLBACK, backoff, new BEGIN ISOLATION LEVEL SERIALIZABLE.
for (let attempt = 1; attempt <= maxAttempts; attempt++) {
  try {
    await client.query("BEGIN ISOLATION LEVEL SERIALIZABLE");
    const result = await fn(client);
    await client.query("COMMIT");
    return result;
  } catch (err) {
    await client.query("ROLLBACK").catch(() => undefined);
    if (err?.code !== "40001" || attempt === maxAttempts) throw err;
    await new Promise((r) => setTimeout(r, delayMs));
    delayMs = Math.min(delayMs * 2, 500);
  }
}

C. Constraint-enforced invariant (often best)

-- Materialize count on call and reject zero with a CHECK,
-- or use a singleton guard row.
CREATE TABLE on_call_guard (
  id int PRIMARY KEY CHECK (id = 1),
  on_call_count int NOT NULL CHECK (on_call_count >= 1)
);
-- Each go-off-call decrements under row lock; CHECK fails if it would hit 0.

Inventory "never sell below zero": prefer a single-row atomic predicate, not check-then-act:

UPDATE stock SET qty = qty - $1 WHERE id = $2 AND qty >= $1 RETURNING qty;

For multi-SKU carts: one transaction with locks on ordered SKU ids, or SERIALIZABLE + retry.

In-memory snapshots (run this)

A tiny heap + snapshot visibility function. SI lets both doctors commit; locking / running B after A preserves the invariant; SSI detects a dangerous rw-cycle.

Press Run. Snippets must be self-contained — no network, files, or native modules.

Interview Q&A

Pitfalls

Go Deeper

Official docs

• PostgreSQL — Transaction Isolation
• PostgreSQL — System Columns (xmin / xmax)
• PostgreSQL — Routine Vacuuming
• PostgreSQL Wiki — SSI
• InterDB — Concurrency Control (SSI, rw-conflicts)

Papers and lectures

• Ports and Grittner, Serializable Snapshot Isolation in PostgreSQL (VLDB 2012)
• Berenson et al., A Critique of ANSI SQL Isolation Levels (SIGMOD 1995) — why write skew sits outside the ANSI phenomena list
• CMU 15-445 — Multi-Version Concurrency Control (Andy Pavlo); course hub: 15445.courses.cs.cmu.edu

One-line takeaway: MVCC buys non-blocking reads via versions; Snapshot Isolation still allows write skew on multi-row invariants — close that gap with SSI retries, precise locks, or constraints, and keep transactions short so vacuum can keep up.