Multi-Version Concurrency Control

Multi-Version Concurrency Control (MVCC) is a concurrency-control discipline in which the database keeps multiple versions of every row, and concurrent transactions read a consistent snapshot of the data taken at their start time, while writes create new versions rather than overwriting. The slogan that explains its appeal — “writers don’t block readers; readers don’t block writers” — is what makes MVCC the dominant concurrency design in modern OLTP databases (PostgreSQL, MySQL InnoDB, Oracle, SQL Server’s RCSI mode, Spanner, CockroachDB, YugabyteDB). The mechanism dates to David Reed’s 1978 MIT thesis and was formalized in Bernstein and Goodman’s 1981 survey; PostgreSQL adopted it from version 6.5 (1999) and has carried it as its only concurrency model ever since. Understanding MVCC means understanding three things — the snapshot/visibility rules, the relationship to ANSI SQL isolation levels (especially the difference between Snapshot Isolation and true Serializability), and the operational cost of garbage-collecting old versions (vacuum, undo log, compaction). In interviews, MVCC is the entry point to almost every isolation, anomaly, and replication question that involves transactional databases.

1. Plain-Language Definition

In a single-version database, a row is a single cell of storage. When transaction T2 wants to write the row, it must wait until any in-flight transaction T1 that’s reading the row releases its read lock; conversely, if T1 is writing, T2 cannot read until T1 commits. Locking-based concurrency control trades throughput for safety this way — readers and writers contend.

MVCC observes that reading and writing only conflict if they’re looking at the same version of the row. So instead of overwriting, a write creates a new version of the row, tagged with the transaction’s identifier. Old versions stay around. A reader sees the version of the row that was committed before its own transaction started — its consistent snapshot — and never has to wait for any writer.

The mechanical consequence is dramatic: long-running analytical queries (which read many rows) and short OLTP writes can run concurrently without blocking each other. A 30-second report querying yesterday’s data sees a consistent snapshot from when it started; meanwhile, hundreds of concurrent updates are creating new row versions. The report finishes and sees no half-applied transactions. Later, a garbage collector reclaims versions that are no longer visible to any transaction.

The cost is paid in storage, in the garbage-collection mechanism itself, and in the fact that MVCC alone does not give you Serializability — the strongest isolation level. The default under MVCC is Snapshot Isolation, which permits a specific anomaly called write skew that true Serializability forbids. Modern systems layer Serializable Snapshot Isolation (SSI) on top of MVCC to close that gap (Cahill et al. 2008).

2. Mechanism

The cleanest way to understand MVCC is via PostgreSQL’s implementation, because it makes versioning visible at the row level. Every row carries hidden columns:

• xmin — the transaction ID (XID) that inserted this version.
• xmax — the XID that deleted (or replaced) this version, or 0 if still live.
• cmin, cmax — command IDs within those transactions, for visibility within the same transaction.
• A pointer ctid to the next version of the row, if any (forming a version chain).

A write operation creates a new tuple (heap row) with xmin set to the writer’s XID and xmax of the replaced tuple set to the writer’s XID. The old tuple is not deleted; it now has a non-zero xmax, marking it as superseded.

flowchart LR
    subgraph T1["Transaction T1 (snapshot at xid=100)"]
      R1[reads row id=42]
    end
    subgraph T2["Transaction T2 (xid=110)"]
      W2[updates row id=42<br/>creates new version]
    end
    subgraph HEAP["Heap row id=42"]
      V1["v1: xmin=50, xmax=0<br/>(state pre-T2)"]
      V2["v2: xmin=110, xmax=0<br/>(state post-T2)"]
      V1 -.->|ctid pointer| V2
    end
    R1 --> V1
    W2 --> V2

Diagram: T1 reads row 42 at snapshot time 100. T2, running at xid 110, updates row 42 by creating a new version; the old version’s xmax is set to 110. T1 still sees v1 because xmax (110) was not yet committed when T1 took its snapshot. The version chain is visible at the storage layer.

Visibility is determined by comparing each candidate version’s xmin/xmax against the reader’s snapshot, which is captured at transaction start (or at each statement under Read Committed). The snapshot consists of:

• xmin of the snapshot — the smallest XID still considered “in progress” or “committed after my snapshot.”
• xmax of the snapshot — one past the largest XID that had been assigned at snapshot time.
• A list of XIDs that were in progress (not yet committed) at snapshot time.

A row version is visible to the reader if and only if its xmin is committed and not in the snapshot’s in-progress list, and either its xmax is 0, uncommitted, or in the snapshot’s in-progress list. Concretely: “the inserter committed before me, and the deleter (if any) had not committed before me.” Every read consults pg_xact (the transaction-status SLRU) for the commit/abort status of each candidate XID; the result is the consistent snapshot.

2.05 The Visibility Algorithm in Detail

The single most important MVCC mechanism is the visibility check: given a row version with xmin (inserter XID), xmax (deleter XID), and the reader’s snapshot, does the reader see this version? The answer is computed by a small, branch-heavy function that runs on every tuple touched by every query.

The algorithm in PostgreSQL (heapam_visibility.c) for the MVCC snapshot type, simplified:

function is_visible(tuple, snapshot):
  xmin = tuple.xmin
  xmax = tuple.xmax

  # Check the inserter
  if xmin is in snapshot.in_progress:
    return false  # inserter not yet committed at our snapshot
  if xmin >= snapshot.xmax:
    return false  # inserter started after our snapshot
  if xmin's status in pg_xact is ABORTED:
    return false  # inserter rolled back; tuple never logically existed
  if xmin's status is COMMITTED:
    inserter_visible = true
  else:
    return false

  # Check the deleter
  if xmax == 0:
    return inserter_visible  # tuple still live
  if xmax is in snapshot.in_progress:
    return inserter_visible  # deleter not yet committed at our snapshot, so still see it
  if xmax >= snapshot.xmax:
    return inserter_visible  # deleter started after our snapshot
  if xmax's status is ABORTED:
    return inserter_visible  # deleter rolled back, tuple still live
  if xmax's status is COMMITTED:
    return false  # tuple is logically deleted as of our snapshot

Several things are worth noting. First, every read may consult pg_xact (the transaction-status SLRU on disk) for both xmin and xmax; in pathological cases this is two random reads per tuple, which is why Postgres caches transaction status aggressively. Second, the algorithm requires the row’s full version history to be physically present — a reader at snapshot 100 examining a row last updated by xid 200 needs to find the version that was current as of snapshot 100, which means walking the version chain. Third, “visibility” depends on both timestamp ordering and commit status, which is why aborted transactions don’t leave permanent trace despite having created tuples.

The performance implication: tables with many concurrent transactions accumulate version chains, and reads must walk them. If a single hot row is being updated 1000 times per second by concurrent transactions, every read of that row pays an O(n) cost in version-chain traversal until vacuum reclaims old versions. This is the genesis of “hot-row contention” pathologies in MVCC databases — the antithesis of locking-based “hot-row contention” which manifests as lock waits rather than slow reads.

2.1 Garbage Collection — Vacuum

Old versions accumulate. In Postgres, the autovacuum daemon periodically scans tables, identifies versions with xmax smaller than the oldest snapshot still active in the system, and marks their slots as reusable. The VACUUM command can also be run manually. VACUUM FULL rewrites the table compacting it (locks the table); ordinary VACUUM reclaims slots in place without locking.

Vacuum is what keeps MVCC’s storage cost bounded. Without vacuum, every update or delete grows the heap forever. The interaction with long-running transactions is the central operational pitfall: a single transaction that stays open for hours pins its snapshot’s xmin, preventing vacuum from reclaiming any version newer than that snapshot. Hot-updated tables can grow many-fold during a long-running query, a phenomenon known as table bloat.

2.15 HOT Updates — A Bloat Mitigation

Postgres’s normal update behavior creates a new tuple in the heap and a new index entry pointing to it; both the old tuple and old index entry remain until vacuum. For tables with many indexes, the index-bloat cost dominates: a row with 5 indexes generates 5 index updates per row update, and 5 obsolete index entries per row update.

Heap-Only Tuple (HOT) updates are an optimization where, if the update does not modify any indexed columns and the new tuple fits on the same page as the old one, Postgres updates the heap in place (creating a new version on the same page) and skips updating the indexes entirely. The old indexes still point to the old heap tuple; following the page’s version chain locally finds the updated tuple. This dramatically reduces both heap and index bloat for non-indexed-column updates.

HOT was added in Postgres 8.3 (2008) and is automatic — operators don’t typically configure it. The condition “all updates are non-indexed-column” is more common than people realize: status flags, counters, last-modified-timestamps. Realizing that adding an index to a frequently-updated column kills HOT for that table is a useful operational insight; the index’s value must justify the bloat cost.

2.2 InnoDB’s Approach

MySQL InnoDB takes a different mechanical approach but achieves the same semantic. The current row in the clustered B+ tree is always the latest version; older versions live in the undo log. When a reader needs an older version, it walks the undo chain backward, applying inverse changes until it reaches the version visible at its snapshot (MySQL docs). Garbage collection works by truncating the undo log when no transaction’s snapshot needs the older versions any more (the purge thread).

InnoDB stores DB_TRX_ID and DB_ROLL_PTR per row instead of Postgres’s xmin/xmax/ctid. The roll-back pointer leads to the prior version in the undo log, building the version chain in a different physical structure but with the same logical effect. This design has different trade-offs: index structures stay slim because old versions live in the undo log, not the main heap, but the undo log itself becomes the bottleneck under heavy long-transaction load.

2.3 Distributed MVCC

In a single-node database, the XID space is a 32-bit counter, monotonically increasing within the system, providing a total order on transactions for free. In distributed systems with multiple nodes that may write concurrently, the XID counter is no longer enough; you need a global ordering on transactions to define snapshots correctly. Two prominent approaches:

Spanner (Google, 2012) uses TrueTime: GPS- and atomic-clock-synchronized hardware in every datacenter exposes a now() API that returns an interval [earliest, latest] bounding the true real-world time. Transactions get timestamps from TrueTime and wait out the uncertainty interval before committing, which guarantees that any transaction that started after a committed transaction sees a strictly larger timestamp. MVCC versions are stamped with TrueTime-derived commit timestamps, and snapshots are real-time-coherent across the entire fleet (Corbett et al. 2012).

CockroachDB uses Hybrid Logical Clocks (HLC) — a logical clock combined with a wall-clock timestamp — to approximate Spanner’s guarantees without needing atomic clocks. Every node tracks its own HLC; every message bumps the receiver’s HLC to be >= the sender’s. Transactions get HLC commit timestamps, but because clocks can drift, certain conflict cases require explicit uncertainty restarts — a transaction reading a value committed within its uncertainty window must restart with a later timestamp (CockroachDB blog). The mechanism is rougher than Spanner’s but works on commodity hardware.

In both designs, MVCC’s per-row structure (multiple versions tagged by commit timestamp) is unchanged; only the source of timestamps differs. The logic for reading at a snapshot is structurally identical to single-node MVCC, just stamped with global timestamps.

2.4 Read Committed vs Repeatable Read in MVCC

PostgreSQL’s two main isolation levels both use MVCC but differ in when the snapshot is taken.

Read Committed takes a fresh snapshot at the start of each statement. Two SELECT statements within the same transaction may see different data if a concurrent transaction commits between them. This is the SQL standard’s “non-repeatable read” — and Postgres’s RC freely admits to it. The advantage is that long-running RC transactions do not pin xmin: each statement re-snapshots, so vacuum can reclaim versions that were live at any earlier statement’s snapshot but not the current one. RC is Postgres’s default isolation level for this reason.

Repeatable Read takes a single snapshot at the first statement of the transaction (technically, the first command that requires a snapshot). All subsequent statements see that one snapshot. Non-repeatable reads and phantom reads are eliminated. The cost: the snapshot pins xmin for the entire transaction’s duration, which under MVCC means accumulating dead tuples that vacuum cannot touch.

For most application code, RC is the right default: short transactions don’t need anomaly prevention, and the lower bloat overhead is significant. RR is correct when a transaction makes multiple decisions that must be consistent with one another (e.g., reading two related rows and computing something based on both). Promoting from RR to SERIALIZABLE adds SSI’s conflict-detection bookkeeping but doesn’t change the snapshot semantics.

A subtle point that catches many developers: “Repeatable Read” in PostgreSQL is strictly stronger than the SQL standard’s RR (which permits phantoms; Postgres SI does not). “Repeatable Read” in MySQL InnoDB is almost the same as the SQL standard’s RR (allows phantoms in non-locking SELECTs). Cross-database porting must account for this difference.

3. Origins

The earliest formulation of MVCC is David Reed’s 1978 MIT Ph.D. thesis Naming and Synchronization in a Decentralized Computer System. Reed introduced multi-version timestamp ordering as a concurrency-control protocol for a distributed system: every transaction has a timestamp, every object has a list of versions tagged with the timestamps of the transactions that wrote them, and reads return the version with the largest write-timestamp ≤ the reader’s timestamp. Reed’s design is the conceptual ancestor of every MVCC system since.

Bernstein and Goodman’s 1981 ACM Computing Surveys article (Concurrency Control in Distributed Database Systems) systematized MVCC alongside locking, certification, and timestamp-ordering as the canonical concurrency-control families. Their 1987 textbook Concurrency Control and Recovery in Database Systems (free PDF available at Microsoft Research) is still the standard reference.

The first widely-deployed commercial MVCC was Oracle Read Consistency in Oracle 6 (1988), which used the undo log to materialize old versions on demand. InterBase (Borland, 1985) implemented MVCC even earlier, using a record-versioning approach pioneered by Jim Starkey; this lineage continues today in Firebird (the open-source descendant of InterBase).

PostgreSQL introduced MVCC in version 6.5, released in June 1999 — replacing an earlier locking-based concurrency design that had been inherited from POSTGRES’s research roots at Berkeley. The implementation written by Vadim Mikheev established the xmin/xmax/vacuum design that has been Postgres’s hallmark ever since. MySQL InnoDB has been MVCC since Innobase Oy’s first release in 2000 (later acquired by Oracle in 2005 along with InnoDB).

The theoretical treatment of MVCC anomalies — the gap between Snapshot Isolation and true Serializability — was formalized in Berenson et al.’s 1995 paper A Critique of ANSI SQL Isolation Levels (Berenson et al. 1995). The paper coined the term Snapshot Isolation, defined the write skew anomaly that distinguishes SI from Serializability, and showed that the ANSI SQL standard’s isolation-level definitions are too imprecise to be used as a specification. The closing of the gap — making MVCC truly serializable without giving up its read-write concurrency wins — was achieved by Cahill, Röhm, and Fekete’s 2008 SIGMOD paper Serializable Isolation for Snapshot Databases, which introduced Serializable Snapshot Isolation (SSI). SSI was implemented in PostgreSQL 9.1 (2011) by Dan Ports and Kevin Grittner (Ports & Grittner 2012 VLDB).

4. Worked Example

Consider a small bank with two accounts, Alice (A=$100)andBob(B=$100). Two concurrent transactions:

• T1: read both balances, transfer $20 from A to B. Steps: read A, read B, write A=80, write B=120, commit.
• T2: read both balances, transfer $30 from B to A. Steps: read B, read A, write B=70, write A=130, commit.

Under a serial schedule, the final balances must be either {A=110, B=90} (T1 then T2) or {A=110, B=90} (T2 then T1) — coincidentally the same, because addition commutes.

Now run them concurrently under MVCC with Snapshot Isolation. Suppose:

t=0   T1 starts (xid=100, snapshot at xid=99)
t=1   T2 starts (xid=110, snapshot at xid=99)
t=2   T1 reads A=100, B=100 (at its snapshot)
t=3   T2 reads A=100, B=100 (at its snapshot)
t=4   T1 writes A=80 → creates new version (xmin=100); old A still has xmax=100
t=5   T1 writes B=120 → creates new version (xmin=100); old B still has xmax=100
t=6   T1 commits (xid=100 marked committed in pg_xact)
t=7   T2 writes B=70 → conflict?

At step 7, T2 is about to update B. The current visible-to-T2 version of B is the old B=100 (because T1’s update at t=5 was not yet committed at T2’s snapshot start, t=1). When T2 writes B, it creates a new version with xmin=110. Now the question is where in the version chain does T2’s new version go.

The answer depends on the isolation level. Under Postgres’s Read Committed, T2’s write succeeds; the old B’s xmax becomes 110, and T2 commits cleanly with B=70. The two transactions ran concurrently; both committed; the final state is A=80, B=70. Total money: $150.Westartedwith$200. $50 vanished.

This is lost update, an anomaly that Read Committed does not prevent under MVCC.

Under Repeatable Read (Snapshot Isolation), Postgres detects the write conflict via the version chain: when T2 tries to write B, it sees that B’s current version was modified by a concurrent transaction (T1, committed at t=6, after T2’s snapshot start) — Postgres aborts T2 with a serialization-failure error. Application is expected to retry. After retry, T2 sees the post-T1 state, transfers $30, and the final state is A=110, B=90. Money preserved.

Under Read Committed, the application would need explicit row locking (SELECT ... FOR UPDATE) to get the same protection. Without it, lost updates can silently happen.

Now consider a different anomaly. Two doctors are on call; the rule is “at least one doctor must be on call at all times.” Both want to take the day off. Each transaction reads the other doctor’s status, sees they’re on call, and decides “great, I’ll go off-call.”

T1: SELECT count(*) FROM doctors WHERE on_call=true → returns 2
    UPDATE doctors SET on_call=false WHERE name='Alice'
    COMMIT

T2: SELECT count(*) FROM doctors WHERE on_call=true → returns 2
    UPDATE doctors SET on_call=false WHERE name='Bob'
    COMMIT

Both transactions ran on disjoint snapshots. Neither overwrote the other’s row. From each transaction’s local perspective, the invariant “at least one doctor on call” was respected. But the post-commit state has zero doctors on call.

This is write skew, and it is not detected by Snapshot Isolation — neither T1 nor T2 wrote a row the other read; both wrote different rows. Yet a serial execution of these transactions would have detected that the second transaction’s invariant check is violated.

Detecting write skew requires Serializable Snapshot Isolation (SSI). Postgres’s SERIALIZABLE isolation level enables SSI, which tracks read-write dependencies between concurrent transactions and aborts one if it detects a “dangerous structure” (a cycle of read-write conflicts). Under SERIALIZABLE, one of T1 or T2 above would be aborted with a serialization failure; the application retries; the final state has one doctor on call. SSI achieves true serializability without giving up MVCC’s reader-writer concurrency.

4.1 Phantom Reads Under MVCC

Phantoms are the third anomaly in the SQL standard’s hierarchy: a transaction reads “all rows where X” once, returning N rows; later it reads the same predicate and returns N+1 rows because a concurrent transaction inserted a row that matches.

Standard MVCC (Snapshot Isolation) prevents phantoms naturally for plain reads: the snapshot is fixed at transaction start, so any rows inserted by other transactions after that point are invisible regardless of the predicate. A SELECT count(*) FROM orders WHERE status='pending' returns the same count every time within the transaction.

But phantoms reappear when MVCC interacts with SELECT ... FOR UPDATE (range locking) or with serializability. Under InnoDB Repeatable Read, SELECT ... FOR UPDATE takes gap locks to prevent phantoms in locked reads, but plain SELECTs see the consistent snapshot. Under Postgres SERIALIZABLE (SSI), phantom-causing concurrent inserts are detected as predicate-conflict edges in the dependency graph, and the offending transaction is aborted. The mechanism that catches phantoms under SSI is exactly the mechanism that catches write skew, because both are “predicate read intersected with concurrent write” anomalies.

4.2 Lost Updates and Their Solutions

The lost-update anomaly was illustrated in the worked example: two transactions read the same value, both compute new values, and the second commit’s new value silently overwrites the first’s. Under MVCC, this happens because the second transaction’s update was based on a stale snapshot of the row.

Three solutions exist:

1. Pessimistic locking: SELECT ... FOR UPDATE takes a row lock; the second transaction blocks on the lock until the first commits, then re-reads the now-current value. Simple, prevents the anomaly, costs concurrency.

2. Optimistic concurrency control: include a version number in every row; on update, write UPDATE ... SET ..., version = version + 1 WHERE id = ? AND version = ?. The conditional WHERE clause means the update succeeds only if the row’s version matches what the transaction read; if it doesn’t, the update affects 0 rows and the application retries. This is the application-level analog of MVCC’s conflict detection, and is what most ORMs (Hibernate, Django) use under the hood for entities marked with optimistic locking.

3. MVCC-level conflict detection: under Repeatable Read in Postgres, the engine detects that the row was modified by a concurrent committed transaction and aborts the second transaction with 40001 serialization_failure. The application catches the error and retries. This is what plain MVCC gives you for free if you use RR.

The choice depends on the application’s contention level. Under low contention, optimistic CC and MVCC conflict detection are nearly free; under high contention, pessimistic locking avoids the retry storms that come from constant aborts. Production systems often use both, picking per-operation based on workload.

5. Variants and Implementations

System	Versioning storage	Garbage collection	Default isolation	Notes
PostgreSQL	Old versions in heap (xmin/xmax); ctid version chain	autovacuum sweeps	Read Committed	Repeatable Read = Snapshot Isolation; SERIALIZABLE = SSI (since 9.1)
MySQL InnoDB	Latest in clustered B+ tree; old versions in undo log	purge thread	Repeatable Read	Repeatable Read uses snapshots but also locks; SERIALIZABLE adds gap locks
Oracle	Undo log (UNDO segments)	Auto-managed	Read Committed	Read Committed uses statement-level snapshots; SERIALIZABLE is SI
SQL Server (RCSI)	Tempdb version store	Background	Read Committed (RCSI optional)	Default uses locks; RCSI mode opts into MVCC
Spanner	Per-key version chain in Bigtable-like store	Async	External Consistency (linearizable)	TrueTime-stamped commits
CockroachDB	Per-key MVCC values in RocksDB	Async GC	Serializable	HLC timestamps; uncertainty restarts
YugabyteDB	Per-key MVCC values in DocDB	Compaction-driven	Serializable / Snapshot	Spanner-inspired
TiDB	MVCC in TiKV (RocksDB-based)	Compaction GC	Snapshot Isolation	Optimistic and pessimistic transactions
Firebird (InterBase descendant)	Old versions in record chain	Sweep	Snapshot	The original commercial MVCC

The unifying observation: every MVCC system stores multiple versions of each row, indexes them by some kind of transaction-time stamp, materializes consistent snapshots for readers, and reclaims old versions in the background. The differences are in where old versions live (in-place heap vs separate undo log), how timestamps are sourced (local XID counter vs HLC vs TrueTime), and what isolation level is layered on top (Read Committed, Snapshot Isolation, or Serializable).

A particularly important distinction is the interaction with isolation levels. PostgreSQL maps the four ANSI SQL levels onto MVCC like this:

• Read Uncommitted → treated as Read Committed (Postgres has no dirty-read mechanism — reads always see committed snapshots).
• Read Committed → snapshot taken at each statement, not the whole transaction. A statement re-runs against fresh data if a row was concurrently changed.
• Repeatable Read → snapshot taken at transaction start. Equivalent to Snapshot Isolation.
• Serializable → SSI on top of RR, detecting and aborting transactions that would create non-serializable schedules.

InnoDB’s mapping is different: Repeatable Read is the default, uses snapshots, but also takes range (gap) locks for SELECT ... FOR UPDATE to prevent phantom reads. The two systems’ “Repeatable Read” are not the same thing — a recurring footgun in cross-database porting.

5.1 SSI in More Detail

Serializable Snapshot Isolation (SSI), as implemented in Postgres 9.1+ and CockroachDB, deserves a dedicated section because it is the modern answer to “MVCC + true serializability.”

The key idea (Cahill et al. 2008) is that SI’s anomalies all involve a specific structure: a cycle of read-write conflicts in the conflict graph. Specifically, if two concurrent transactions T1 and T2 form a structure where T1 read something T2 wrote, and T2 read something T1 wrote (in either temporal order), and a third transaction’s commit creates a “dangerous” closing of the cycle, the resulting schedule is not serializable.

SSI tracks two relationships at runtime: which transactions have read which rows (predicates, more precisely), and which have written which rows. When a transaction commits, the engine checks for the dangerous structure. If found, one of the participating transactions is aborted with 40001. The application retries; on retry, the snapshots have advanced and the conflict typically goes away.

SSI’s appeal is that it preserves MVCC’s read-write concurrency. Readers never block; writers never block readers; only at commit time do conflicts manifest, and even then only in the specific dangerous-structure case. For workloads with few real conflicts, SSI is barely more expensive than plain SI. For workloads with many real conflicts, SSI’s abort rate goes up; tuning becomes “use FOR UPDATE to serialize hot rows explicitly” or “design the application to avoid the conflict.”

Postgres’s SSI implementation (Ports & Grittner VLDB 2012) uses predicate locks stored in a hash table; index scans, sequential scans, and explicit predicates all populate it. The implementation has been refined over many Postgres releases; today’s SSI is robust enough to be a viable default for applications that need true serializability.

CockroachDB took the more aggressive design: SSI is the only isolation level. Application developers cannot accidentally fall into SI’s anomalies because SI is not exposed. The cost is somewhat higher commit-time conflict rates, but the developer-facing guarantees are stronger. This design choice is one of the more interesting modern statements about the SI/Serializable tradeoff: CockroachDB’s authors believe the bug-prevention benefit outweighs the throughput cost.

6. Real-World Examples

Postgres long-running transactions and table bloat. A famous production failure mode: an analyst opens a psql session, runs BEGIN; SELECT ... FROM huge_table;, gets distracted, leaves the connection open. The transaction’s snapshot pins xmin for hours. Autovacuum cannot reclaim any version newer than that snapshot. A high-write OLTP table (orders, sessions) bloats by gigabytes. When the analyst eventually disconnects, autovacuum finally runs but now must process the accumulated bloat in one expensive pass. Mitigation: monitor pg_stat_activity.xact_start for old transactions; alert; kill them automatically (statement_timeout, idle_in_transaction_session_timeout).

Postgres XID wraparound. Postgres’s XID is 32 bits. After ~2 billion transactions, it wraps. Old rows’ xmin values are reinterpreted as “in the future” relative to current XIDs and become invisible — silent data loss. Autovacuum’s freeze operation rewrites old rows to mark them as “frozen” (visible to all), preventing wraparound. If autovacuum falls behind, Postgres goes into emergency mode where it refuses new writes until vacuum catches up. The Mailchimp 2018 outage and several others trace to XID wraparound. (Postgres docs — Preventing Transaction ID Wraparound Failures.)

Spanner at Google. Powers AdWords, Google Photos metadata, and many other Google services. Spanner is the case study for “MVCC + globally-consistent timestamps” at planetary scale: the same row can be read at exactly the same logical time from a database serving Tokyo and one serving New York, with the system guaranteeing that the snapshots are mutually consistent. The TrueTime infrastructure (GPS + atomic clocks in every datacenter) is what makes the design tractable; absent that, distributed MVCC requires the kind of bookkeeping CockroachDB does with HLCs.

CockroachDB’s serialization design. CockroachDB took the more aggressive design choice: SERIALIZABLE is the only isolation level (no Snapshot Isolation default). Every read records its read intervals in a timestamp cache; every write checks the cache and pushes its timestamp forward if a later read would create a conflict. The rationale: write skew is a real bug source in production, and exposing it as a knob causes real data corruption. The cost is more frequent transaction restarts than systems that default to SI, but the correctness story is unambiguous.

YugabyteDB’s two transaction modes. YugabyteDB exposes both Snapshot Isolation and Serializable, mapped through their distributed transaction manager. For workloads that do not care about write skew, SI gives lower-latency commits; for workloads that do, Serializable runs the same SSI-style conflict detection. The system shows that the two modes can coexist within one database.

InnoDB undo-log explosion. A workload with many long-running queries and many concurrent updates can grow the InnoDB undo log to tens of gigabytes — old row versions are kept until the oldest snapshot no longer needs them. The undo log uses tablespace, contends for I/O with the data files, and slows down everything. Mitigation: short transactions, kill long queries, increase innodb_purge_threads, monitor INNODB_TRX and undo-segment usage.

6.1 Snapshot-Based Backups

A practical use of MVCC: backups. pg_dump opens a long-running transaction in REPEATABLE READ, taking a snapshot at start; reads through the snapshot are consistent across all tables for the duration of the dump, even though the database is being actively modified. The result is a transactionally-consistent point-in-time backup, with no need to lock any tables.

The cost is exactly the cost discussed in pitfalls — the long-running snapshot pins xmin and bloats heavily-updated tables for the duration of the dump. Production databases run pg_dump on replicas (which can be configured with hot_standby_feedback to push the cost there) or use physical-level backups (pg_basebackup + WAL) to avoid the snapshot duration entirely.

The same MVCC-snapshot mechanism is what enables Debezium’s bootstrap snapshot for Change Data Capture — Debezium opens a transaction, records the WAL position, reads all rows under the snapshot, then resumes from the recorded position with no gap. The trick is identical to pg_dump’s; the consumer is different.

6.2 Time-Travel Queries

Some MVCC systems expose the version history directly as a time-travel query feature. Snowflake’s AT (TIMESTAMP => '...') clause and BigQuery’s FOR SYSTEM_TIME AS OF '...' let users query the state of the data at a past timestamp, transparently materializing the appropriate row versions. The mechanism is exactly MVCC; the API exposes what was previously an internal capability.

CockroachDB exposes AS OF SYSTEM TIME for the same purpose — offering bounded-staleness reads that intentionally return slightly older snapshots in exchange for going to the nearest replica rather than the leader. This is a deliberate use of MVCC to trade consistency for latency, and it scales beautifully because all replicas can serve the same historical snapshot without coordination.

7. Tradeoffs

The headline trade is storage and GC overhead in exchange for read-write concurrency. MVCC keeps multiple versions of every updated row; the database must reclaim them; under load, GC competes with foreground work for I/O and CPU. The reward is that long readers do not block writers and vice versa, which on real OLTP/OLAP-mixed workloads is night and day for throughput.

A second trade is default Snapshot Isolation versus true Serializability. SI is fast (no extra bookkeeping beyond the version chain) but admits write skew. SSI catches write skew but adds tracking overhead — every read records its predicate; every write checks for conflicts; conflicts trigger transaction aborts that the application must retry. For workloads where serializability is required, SSI is the right default; for read-heavy workloads where the application can tolerate or programmatically prevent the SI anomalies, plain SI is faster.

A third trade is the operational cost of long transactions. Under MVCC, a single long-running transaction is a system-wide problem: it pins xmin, prevents vacuum, grows undo log, increases bloat. Under locking-based concurrency control, a long transaction is a local problem (it holds its locks and blocks others) but does not cause global storage growth. Production MVCC systems develop a culture of “transactions are short; long-running queries use replicas or read snapshots” specifically because of this trade.

8. Pitfalls

1. Long-running transactions cause table bloat. Already covered in §6, but worth restating: any open transaction pins xmin and prevents vacuum on tables modified concurrently. Production teams must monitor and limit transaction duration aggressively. idle_in_transaction_session_timeout (Postgres) and equivalent settings should always be set in OLTP systems.

2. Snapshot Isolation does not prevent write skew. The doctor-on-call example above is the canonical illustration. Applications that assume “Repeatable Read prevents anomalies” miss the SI ≠ Serializable distinction. Mitigation: use SERIALIZABLE for invariant-critical transactions, or use explicit row locks (SELECT ... FOR UPDATE) to escalate to a serializable schedule.

3. pg_repack and VACUUM FULL lock the table. Recovering from severe bloat requires tools that rewrite the table. VACUUM FULL takes an ACCESS EXCLUSIVE lock — no concurrent reads or writes — for the duration of the rewrite. pg_repack (a third-party tool) uses a more clever approach with triggers that takes only short locks, but is itself not built into Postgres core. The lesson: prevention beats remediation.

4. Postgres XID wraparound (mentioned above). A 32-bit XID means after 2^31 transactions, vacuum must freeze old rows or the database stops accepting writes. This has caused multiple high-profile outages. Postgres 13+ added vacuum_failsafe_age to make autovacuum more aggressive as wraparound approaches; Postgres 14 added 64-bit transaction IDs (logical, mapped to 32-bit on disk) reducing pressure further.

5. SELECT ... FOR UPDATE does not promote SI to Serializable. It locks rows; subsequent reads in the same transaction see those locks, which prevents concurrent updates to the same rows. But it does not prevent write skew on rows that are predicate-overlap-but-not-row-overlap. A common pitfall is “I added FOR UPDATE so this is now serializable” — only true if the FOR UPDATE locked every row that any concurrent transaction would have read or written. SSI is the only general solution.

6. Phantom reads in Repeatable Read. Under ANSI SQL, “Repeatable Read” forbids non-repeatable reads but allows phantoms. Under Postgres’s Repeatable Read (SI), phantoms are also prevented (the snapshot is fixed, so a re-execution of the same query returns the same rows). Under InnoDB’s Repeatable Read, gap locks prevent phantoms in SELECT ... FOR UPDATE queries but not in plain SELECTs. The cross-system inconsistency is a real interoperability headache.

7. Index visibility checks force heap fetches in Postgres. Because old row versions live in the heap (not separated like InnoDB’s undo log), Postgres index entries cannot determine row visibility on their own — they must fetch the heap tuple to check xmin/xmax. This is why Postgres added the visibility map and index-only scans: a bit per heap page indicating “all tuples here are visible to all snapshots, so the index entry alone is enough.” Without the visibility map, every index lookup is two random reads (index + heap).

8. Vacuum and replication interact. A standby that is replaying WAL and serving read queries can have its replayed version of vacuum collide with a long-running query: the standby attempts to remove a tuple that the query still needs. Postgres has hot_standby_feedback to keep the primary aware of standby snapshots, preventing the conflict, but this re-introduces the long-transaction-pinning-xmin problem on the primary. The trade is between bloat on the primary and query cancellations on the standby.

9. Common Interview Discussion Points

“What is MVCC and why does Postgres use it?” The expected one-paragraph answer: each row has multiple versions; readers see a snapshot; writers create new versions; vacuum reclaims old ones; the win is non-blocking concurrency. Mentioning xmin/xmax and the Reed/Bernstein/Goodman lineage signals depth.

“Snapshot Isolation vs Serializability.” Strong answers cite the Berenson et al. 1995 paper, define write skew with the doctor-on-call example, and explain how SSI (Cahill et al. 2008) closes the gap. Bonus credit for naming Postgres’s SERIALIZABLE as SSI implementation versus InnoDB’s lock-based serializable.

“What happens with a long-running transaction?” Pins xmin, prevents vacuum, causes bloat, stresses undo log. The interviewer may follow up with “how do you mitigate” — the answer is idle_in_transaction_session_timeout, monitoring pg_stat_activity, separating analytics workloads onto replicas with hot_standby_feedback configured carefully.

“How does MVCC differ from locking?” Locking blocks readers when there’s a writer (and vice versa); MVCC does not. Locking gives serializability for free; MVCC needs SSI on top to be serializable. Locking pays no storage cost for old versions; MVCC does. Locking can deadlock; MVCC restarts transactions on conflict but does not deadlock in the lock-graph sense.

“How does this work in a distributed database?” Spanner uses TrueTime-stamped commits and waits out clock uncertainty. CockroachDB uses HLCs and uncertainty restarts. The per-row data structures look like single-node MVCC; the timestamp source is what scales out. Mentioning external consistency (linearizable across the entire fleet) versus serializability earns extra credit.

“What’s the alternative?” Two-Phase Locking (2PL) for serializability; Optimistic Concurrency Control (OCC) for low-contention workloads; multi-version 2PL (MV2PL) as a hybrid. Modern HTAP systems sometimes mix MVCC for the OLTP path and copy-on-write snapshotting for the analytical path.

“Why is Postgres Repeatable Read called SI internally?” Because Postgres’s RR uses a single snapshot for the whole transaction, exactly the SI definition. The ANSI SQL Repeatable Read level forbids non-repeatable reads but allows phantoms; Postgres’s SI forbids both. The naming is technically wrong by the ANSI spec but historically entrenched.

10. See Also

• Write-Ahead Log — durability layer underneath; MVCC writes are logged in the WAL like any other writes
• B+ Tree — storage structure that holds the row versions in InnoDB; visibility-map optimizations atop it in Postgres
• LSM Tree — alternative storage structure with its own version-keeping (write timestamps in SSTables, compaction reclaims old versions)
• Distributed SQL Database System Design — Spanner, CockroachDB, YugabyteDB all build on distributed MVCC
• Distributed Key Value Store System Design — most modern KV stores expose MVCC reads at a key
• ACID Transactions — MVCC delivers Isolation (the I); the others (A, C, D) come from different mechanisms
• Two-Phase Commit — distributed MVCC needs 2PC (or its descendants) to coordinate cross-shard transaction commits
• Raft — distributed MVCC systems use Raft to durably replicate the per-shard write log on which MVCC is built
• Change Data Capture — CDC bootstrap snapshots use MVCC to take a consistent point-in-time copy without locking
• Time To Live — old MVCC versions are conceptually expired by transaction-visibility rules, structurally similar to TTL-based expiration
• Leader-Follower Replication Architecture — MVCC on the follower interacts with replication via hot_standby_feedback
• SWE Interview Preparation MOC
• Major System Designs MOC