MVCC & Isolation Levels

PostgreSQL's Multi-Version Concurrency Control (MVCC) gives readers a transactionally consistent snapshot instead of forcing them to wait behind ordinary writers. An update creates a new tuple version; snapshots decide which version each statement may see. This is why a plain SELECT normally neither blocks an UPDATE nor is blocked by it.

MVCC does not eliminate concurrency problems. It changes them. Readers can see older committed versions while writers still contend on row versions, unique indexes, foreign keys, and explicit locks. Correct design requires knowing which snapshot the isolation level provides and which anomalies it still allows.

Version chains and snapshot visibility #

Conceptually, every tuple version has transaction metadata:

• xmin: the transaction that created the version;
• xmax: the transaction that deleted it or replaced it with a newer version;
• status information, derived from transaction state and hint bits, saying whether those transactions committed, aborted, or are still in progress.

A snapshot summarizes which transactions were complete and which were still in progress when the snapshot was taken. A tuple version is visible only when its creating transaction is visible and no visible deleting/replacing transaction has removed it.

The actual implementation is more nuanced than a simple linked list, but this mental model explains the important behavior:

1. transaction A reads version v1;
2. transaction B updates the row, producing v2, then commits;
3. A can continue seeing v1 if its isolation level retains the old snapshot;
4. a later snapshot sees v2;
5. VACUUM can remove v1 only after no active or otherwise retained snapshot can still need it.

Long-running transactions therefore have a storage consequence: their old snapshots can prevent cleanup, increase dead tuples and index work, and make transaction-ID wraparound management harder.

SQL isolation phenomena and PostgreSQL's implementations #

The SQL standard names four levels by phenomena they may permit. PostgreSQL 18 implements only three distinct behaviors: READ UNCOMMITTED behaves exactly like READ COMMITTED because dirty reads are incompatible with PostgreSQL's MVCC design.

The terms mean:

• dirty read: reading a value written by a transaction that has not committed;
• nonrepeatable read: rereading one row and observing a committed change;
• phantom read: rerunning a predicate and observing a changed matching set;
• serialization anomaly: a committed result inconsistent with every possible serial ordering, even if the simpler phenomena above did not occur.

PostgreSQL Repeatable Read is stronger than the minimum SQL requirement because it prevents phantoms. It is still snapshot isolation, not full serializability.

Read Committed: one snapshot per command #

Read Committed is PostgreSQL's default. Each command sees rows committed before that command began, plus the transaction's own earlier writes. Two successive SELECT statements in one transaction can therefore see different committed states.

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT status FROM orders WHERE order_id = 10; -- 'pending'
-- Another transaction commits status = 'paid'.
SELECT status FROM orders WHERE order_id = 10; -- may now be 'paid'

COMMIT;

This behavior is often ideal for short, independent OLTP statements. It is not a guarantee that a multi-query business decision used one coherent database snapshot.

Updating commands wait and then re-evaluate #

UPDATE, DELETE, MERGE, and locking SELECT clauses find candidate rows using their command-start snapshot. If another transaction already modified a candidate, the command waits. After that transaction ends, PostgreSQL follows the row to its current version and re-evaluates the command's WHERE condition.

UPDATE jobs
SET state = 'claimed', worker_id = $worker
WHERE job_id = $id AND state = 'ready'
RETURNING *;

If a concurrent transaction changed the state from ready, the waiter wakes, rechecks, and returns no row. This is why a conditional update plus its row count is safer than a separate SELECT followed by an unconditional update.

The updated row can be newer than other rows observed by the same command. For complex commands, Read Committed can therefore produce a view that does not correspond to one instant across all rows. Use it when each command can make sense independently, not when reasoning requires one global snapshot.

Lost update depends on the statement shape #

Two relative updates are serialized on the row:

UPDATE counters SET value = value + 1 WHERE counter_id = 7;

The waiter applies + 1 to the current row version. But application-side read–modify–write can lose a decision:

T1 reads version=5 and body=A
T2 reads version=5 and body=A
T1 writes body=B
T2 writes body=C without checking version

The final write can overwrite the first. Prevent this with a version predicate, SELECT ... FOR UPDATE, or a higher isolation level with correct retry logic.

Repeatable Read: one snapshot, possible write skew #

At Repeatable Read, the transaction uses one snapshot established by its first ordinary query or data-modification command. Later statements continue seeing that database state plus their own writes. Changes committed afterward remain invisible.

If the transaction tries to update a row changed by another transaction since its snapshot began, PostgreSQL aborts it with 40001 serialization_failure. The application must retry the complete transaction.

Stable snapshot does not imply serial execution #

Consider a rule: at least one doctor must remain on call.

Initial state: Alice=on, Bob=on

T1 snapshot: counts 2 on-call doctors
T2 snapshot: counts 2 on-call doctors
T1 updates Alice to off
T2 updates Bob to off
Both commit under snapshot isolation
Final state: nobody is on call

The transactions update different rows, so no row-level write conflict forces one to abort. Each decision was valid in its own snapshot, but the combined result violates the cross-row invariant. This is write skew, a serialization anomaly.

Locking both doctor rows can serialize this particular decision, and a schema redesign may turn the invariant into a directly constrained row. When the predicate is broad or dynamic, Serializable isolation is the general database mechanism.

Serializable: equivalent to some serial order #

PostgreSQL Serializable uses Serializable Snapshot Isolation (SSI). It starts with Repeatable Read's stable snapshot and tracks dependencies among concurrent transactions. When a dependency pattern could form a serialization anomaly, PostgreSQL aborts one transaction rather than allowing all of them to commit.

Successful transactions have effects equivalent to some serial execution, even though they actually overlapped. The price is not that every read blocks every writer; it is that applications must treat 40001 as a normal control path under contention.

Predicate locks are observations, not blocking row locks #

SSI records SIReadLock predicate locks representing data a serializable transaction has read. Depending on the plan, they can cover tuple, page, or relation ranges. They:

• appear in pg_locks;
• do not block writers;
• cannot themselves participate in a traditional blocking deadlock;
• let PostgreSQL detect dangerous read/write dependency structures.

An index that narrows the accessed range can reduce false-positive dependency tracking, but correctness must not depend on a particular plan.

Whole-transaction retry is mandatory #

attempt transaction:
  BEGIN ISOLATION LEVEL SERIALIZABLE
  perform every read, decision, and write
  COMMIT

if SQLSTATE = 40001:
  rerun from BEGIN with a fresh snapshot

Do not reuse values computed from the failed attempt. Do not retry only the last statement. External side effects need idempotency because PostgreSQL cannot roll them back.

PostgreSQL does not automatically retry: it cannot know whether replaying the application's surrounding logic is safe. Retrying 40P01 deadlock_detected may also be appropriate, but the deadlock's lock-order cause should still be removed when possible.

Read-only deferrable transactions #

A SERIALIZABLE READ ONLY DEFERRABLE transaction may wait before its first snapshot until PostgreSQL can choose one that is safe from serialization failure. This is useful for long reports that require a serializable view and would rather wait up front than risk expensive work being aborted.

BEGIN TRANSACTION
ISOLATION LEVEL SERIALIZABLE
READ ONLY
DEFERRABLE;

-- long consistent report
COMMIT;

Choosing the mechanism by invariant #

Do not choose isolation by folklore such as “Serializable is always safest” or “Read Committed is always fastest.” State the invariant, access pattern, contention, and retry behavior. A stronger level cannot fix an external side effect that is not idempotent, and a weaker level can be correct when an atomic statement or constraint fully enforces the rule.

Snapshot and isolation traps #

A missing row cannot be row-locked #

SELECT ... FOR UPDATE locks rows that exist and are selected. If “no row with this key exists” is the fact that must remain true, use a unique constraint, lock a stable parent/key-range surrogate, or use Serializable. A row lock on an empty result protects nothing.

Sequence values are not transactional #

nextval() effects are immediately visible and are not rolled back. Gaps from aborted transactions, crashes, and caching are normal. Use sequences for unique identifiers, not gapless business numbering or evidence that every smaller transaction committed.

Isolation level must be selected before work begins #

Set the transaction characteristics in BEGIN or before the first query or data-changing statement. Changing the isolation level after the transaction has acquired a snapshot is not allowed.

“Idle in transaction” is active operational state #

A client that begins, queries, and then waits can retain a snapshot, hold locks, block schema changes, and delay vacuum cleanup. Transactions should surround only database work. Use idle_in_transaction_session_timeout as a guardrail, not as a substitute for correct request lifecycle management.

Interview and design-review questions #

An experienced answer connects each question to a mechanism:

• Why do readers not normally block writers? They read visible older tuple versions from a snapshot.
• Why can writers still block? Competing modifications need exclusive rights to row versions and may contend on indexes, foreign keys, and explicit locks.
• Does Repeatable Read prevent every anomaly? No; PostgreSQL prevents phantoms but snapshot isolation can admit write skew.
• Does Serializable take a giant global lock? No; SSI monitors dependencies and aborts unsafe transactions.
• What must be retried? The whole transaction, including its reads and business decisions.
• Can SELECT FOR UPDATE protect nonexistence? No; an empty result contains no row to lock.
• Why do long transactions cause bloat? Their snapshots can keep old versions potentially visible, so vacuum cannot reclaim them.
• Is READ UNCOMMITTED useful in PostgreSQL? It maps to Read Committed and does not permit dirty reads.

Primary PostgreSQL references #

• Introduction to concurrency control
• Transaction isolation
• Serialization failure handling
• Transaction snapshot information
• Routine vacuuming
• SET TRANSACTION