MVCC & Isolation Levels#

Problem statement (interviewer prompt)

Explain MVCC and the ANSI isolation levels (Read Uncommitted → Serializable). For a banking app, pick the right isolation level for transfers, statement reads, and a daily reconciliation job, and explain the anomalies each level prevents.

flowchart LR
  T1[Txn 1<br/>reads snapshot]
  T2[Txn 2<br/>writes new version]
  R[(Row v1, v2, v3 ...<br/>versioned by tx id)]
  T1 --> R
  T2 --> R
  R -. GC old versions .-> GC[Vacuum / GC]

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class T1,T2 service;
    class R datastore;

Each transaction sees a consistent snapshot; writers don't block readers.

flowchart TB
  subgraph MVCC[Multi-Version Concurrency Control]
    direction TB
    XID[Tx id / commit seqno]
    SS[Snapshot = set of visible XIDs]
    ROW[(Row chain<br/>v1 → v2 → v3)]
    HOTUP[HOT updates / undo log]
    GC[VACUUM / GC<br/>old tuples removed]
  end

  subgraph Levels[Isolation Levels - ANSI + extensions]
    direction TB
    RU[Read Uncommitted<br/>allows dirty reads]
    RC[Read Committed<br/>no dirty reads]
    RR[Repeatable Read<br/>same row -> same value]
    SI[Snapshot Isolation<br/>own snapshot, write skew possible]
    SR([Serializable<br/>equivalent to serial schedule])
    SSI[Serializable Snapshot Isolation<br/>SI + detect dangerous structures]
  end

  subgraph Anomalies[Anomalies]
    DR[Dirty read]
    NRR[Non-repeatable read]
    PR[Phantom read]
    LU[Lost update]
    WS[Write skew]
    CR[Cycle / serializability]
  end

  subgraph Mechanisms[Mechanisms]
    LOCK[2PL pessimistic locks]
    OCC[OCC: validate at commit]
    TS[Timestamp ordering]
    LK[Predicate locks]
  end

  subgraph Engines
    PG[Postgres<br/>MVCC undo via dead tuples + VACUUM]
    INNO[InnoDB<br/>MVCC via undo log]
    SPN[Spanner / CRDB<br/>TS via TrueTime / HLC]
    ORA[Oracle<br/>MVCC via UNDO segments]
  end

  RU -. fixes DR .-> RC
  RC -. fixes NRR .-> RR
  RR -. fixes PR / WS .-> SR
  SR -. impl as SI+detect .-> SSI
  MVCC --- Engines
  Mechanisms --- Levels
  Anomalies --- Levels

    classDef client fill:#dbeafe,stroke:#1e40af,stroke-width:1px,color:#0f172a;
    classDef edge fill:#cffafe,stroke:#0e7490,stroke-width:1px,color:#0f172a;
    classDef service fill:#fef3c7,stroke:#92400e,stroke-width:1px,color:#0f172a;
    classDef datastore fill:#fee2e2,stroke:#991b1b,stroke-width:1px,color:#0f172a;
    classDef cache fill:#fed7aa,stroke:#9a3412,stroke-width:1px,color:#0f172a;
    classDef queue fill:#ede9fe,stroke:#5b21b6,stroke-width:1px,color:#0f172a;
    classDef compute fill:#d1fae5,stroke:#065f46,stroke-width:1px,color:#0f172a;
    classDef storage fill:#e5e7eb,stroke:#374151,stroke-width:1px,color:#0f172a;
    classDef external fill:#fce7f3,stroke:#9d174d,stroke-width:1px,color:#0f172a;
    classDef obs fill:#f3e8ff,stroke:#6b21a8,stroke-width:1px,color:#0f172a;
    class XID,SS,HOTUP,GC,RU,RC,SI,SSI,DR,PR,LU,WS,CR,LOCK,OCC,TS,LK,INNO,ORA service;
    class ROW,RR,NRR,PG,SPN datastore;
    class SR compute;

Anomalies matrix (ANSI + Berenson)#

Level	Dirty read	Non-rep read	Phantom	Write skew
RU	✗	✗	✗	✗
RC	✓	✗	✗	✗
RR	✓	✓	depends	✗
SI	✓	✓	✓ for snapshot reads	✗
Serializable	✓	✓	✓	✓

(✓ = prevented)

How MVCC works in Postgres#

Each row tuple has xmin (creating tx) and xmax (deleting tx).
A snapshot is { xmin, xmax, xip[] } - visible if xmin committed and xmax not committed/in snapshot.
Updates write a new tuple; old becomes dead → VACUUM removes.
Long-running tx → bloat → autovacuum cannot reclaim → table grows.

Choosing a level#

Most apps: Read Committed is fine.
Bank transfer / inventory: Serializable or SSI.
Reports: Repeatable Read (consistent snapshot).
Beware lost updates in RC - use SELECT ... FOR UPDATE or compare-and-set.

Optimistic vs pessimistic#

OCC: validate at commit (Postgres SSI, CRDB). Great for low contention.
2PL: lock rows. Predictable but deadlocks. MySQL default.

Glossary & fundamentals#

Concepts referenced in this design. Each row links to its canonical page; the tag column shows whether it is a high-level (HLD) or low-level (LLD) concept.

Tag	Concept	What it is	Page
`HLD`	LSM vs B-Tree engines	WAL, memtable, SSTables, compaction	storage-engines-lsm-btree
`HLD`	MVCC & isolation levels	snapshot isolation, serializability, vacuum	mvcc-isolation-levels
`HLD`	Logical clocks	Lamport, vector clocks, HLC, TrueTime	logical-clocks

Quick reference#

Why MVCC#

Readers don't block writers; writers don't block readers.
Enables consistent snapshots for backups, analytics, long reads.

Storage cost#

Each update creates a new tuple → bloat.
Postgres VACUUM, InnoDB undo log purge, Oracle UNDO tablespace.
Long open transactions block GC.

Write skew (the classic SI gotcha)#

Two surgeons on call; each checks the other is on call (true), then both go off.
Both txns see the same snapshot, both commit, system ends up with zero on call.
Fix: Serializable / SSI / explicit predicate lock / SELECT FOR UPDATE on both rows.

Cross-DB semantics#

DB	Default isolation
Postgres	Read Committed (SI on request)
MySQL InnoDB	Repeatable Read (non-strict)
Oracle	Read Committed (SI on request)
SQL Server	Read Committed (snapshot opt)
CockroachDB	Serializable (always)
Spanner	External consistency (Serializable)

API discipline#

Wrap business invariants in DB constraints (FKs, CHECK, UNIQUE).
Use SELECT ... FOR UPDATE for read-then-write under RC.
Retry on serialization failure (40001) - every modern driver supports this.

Refs#

Berenson et al.: "A Critique of ANSI SQL Isolation Levels" (SIGMOD '95).
Postgres concurrency control chapter.
CockroachDB engineering blog on SSI.
Jepsen analyses (PostgreSQL, MySQL, MongoDB) on jepsen.io.

FAQ#

What is MVCC?#

Multi-version concurrency control keeps multiple versions of each row. Readers see a consistent snapshot and never block writers, while writers create new versions atomically.

Read Committed vs Repeatable Read?#

Read Committed sees each statement's own snapshot. Repeatable Read pins the snapshot at transaction start, so the same query returns the same rows every time inside the txn.

What anomalies does Serializable prevent?#

Serializable prevents dirty reads, non-repeatable reads, phantoms, and write skew. The cost is more aborts or wider locks compared to Snapshot or Repeatable Read.

What is write skew?#

Two transactions each read a value, see it is safe, and write based on that read. Snapshot isolation allows it; Serializable Snapshot Isolation or explicit locks prevent it.

Which isolation level should I pick?#

Default to Read Committed for OLTP. Pick Repeatable Read or Snapshot for analytical reads. Use Serializable when correctness matters more than throughput, like for ledger transfers.

Database Sharding: MVCC isolation must be maintained across shards in distributed databases
Distributed Transactions: distributed transactions must coordinate MVCC snapshots across nodes to provide serializable isolation
CAP and PACELC Theorems: isolation levels directly map to the consistency guarantees a system offers within the CAP/PACELC framework