Multiversion concurrency control

Why it matters

With plain locking, a long report blocks every writer it touches and every writer blocks the report. Multiversion concurrency control removes that conflict by keeping old versions of rows so that readers see a consistent snapshot while writers create new versions. Nearly every modern relational database uses it, so it decides what your queries actually see, which anomalies can still happen, why "vacuum" and "undo" exist, and why a forgotten open transaction can slowly degrade a whole database.

The idea

Instead of overwriting a row in place, an update creates a new version and keeps the old one. Each version is tagged with the transaction that created it (and, once superseded or deleted, the transaction that ended it). Each transaction or statement reads from a snapshot: the set of transactions that had committed when the snapshot was taken. A version is visible to a snapshot if its creator committed before the snapshot and it was not deleted by a transaction that committed before the snapshot.

How engines store versions

Snapshot isolation

Under snapshot isolation (SI), each transaction reads from one snapshot taken at its start and may commit only if no other transaction committed a write to the same rows since that snapshot (first-committer-wins). SI prevents dirty reads, unrepeatable reads and lost updates. PostgreSQL's REPEATABLE READ is snapshot isolation, and its READ COMMITTED takes a fresh snapshot for each statement.

SI is not serializable. It allows write skew: two transactions read overlapping data, make decisions based on it, and write different rows, so neither conflicts with the other's write.

1. 1

   The rule

   At least one doctor must be on call.
2. 2

   T1

   Reads: Alice and Bob are on call (2). Sets Alice off call.
3. 3

   T2, concurrently from the same snapshot

   Reads: 2 on call. Sets Bob off call.
4. 4

   Both commit

   They wrote different rows, so first-committer-wins sees no conflict. Now nobody is on call.

Fixes: lock the rows you based the decision on (SELECT ... FOR UPDATE on all on-call rows), materialise the conflict (update a shared counter row both must touch), enforce the rule with a constraint where possible, or use serializable isolation.

Serializable snapshot isolation

SSI (PostgreSQL's SERIALIZABLE level) keeps snapshot reads but also tracks read-write dependencies between concurrent transactions. Write skew always involves a "dangerous structure" of two consecutive read-write dependencies among concurrent transactions; when SSI detects one, it aborts a transaction with a serialization failure. Applications must retry such errors. SSI gives true serializability with far less blocking than 2PL, at the cost of some false-positive aborts.

The cost: old versions

Versions accumulate until no snapshot can see them. A long-running transaction (an analytics query, or a session left idle in a transaction) holds its snapshot open, so nothing newer than its start can be cleaned up anywhere it matters. Tables and indexes bloat, scans slow down, undo logs grow, and in PostgreSQL transaction-ID wraparound becomes a risk if vacuum cannot keep up. Monitoring the oldest open transaction and bounding transaction duration are part of running any MVCC database.

Trade-offs

MVCC lets readers and writers proceed without blocking each other and makes consistent backups and long reports cheap, at the cost of storage for versions, background cleanup work, and weaker default guarantees (snapshot isolation) that allow write skew unless you use serializable mode or explicit locks. Write-write conflicts still need locks or first-committer-wins aborts.

In an interview

Explain both the benefit and the trap: "Under MVCC the nightly report reads a snapshot and never blocks checkout writes. But snapshot isolation allows write skew, so the 'at least one doctor on call' rule needs SELECT FOR UPDATE on the on-call rows or SERIALIZABLE with retries. And we alert on transactions open longer than a few minutes, because they stop vacuum and bloat the tables."