Transaction isolation is not a property you can infer from the word “transaction,” from the use of MVCC, or even from the absence of dirty and phantom reads. The useful question is narrower: which concurrent histories may commit, and are their effects equivalent to some serial execution?

That distinction explains a common surprise in PostgreSQL. Its REPEATABLE READ level gives every transaction a stable snapshot and prevents the dirty-read, nonrepeatable-read, and phantom-read phenomena named by the SQL standard. It still permits some results that no one-at-a-time ordering could produce. PostgreSQL SERIALIZABLE closes that gap with Serializable Snapshot Isolation (SSI): it keeps snapshot reads, tracks read/write dependencies, and aborts transactions when a non-serializable execution could form.

This article works from schedules rather than isolation-level slogans.

A small notation for concurrent histories

Let:

  • r1(x) mean transaction T1 reads item x;
  • w1(x = v) mean T1 writes value v to x;
  • c1 and a1 mean T1 commits or aborts;
  • operations on the same line proceed from left to right.

A serial history completes one transaction before the next:

r1(x) w1(x = 11) c1   r2(x) w2(x = 12) c2

A concurrent history interleaves them:

r1(x)   r2(x)   w1(x = 11) c1   w2(x = 11) c2

If x began at 10 and each transaction meant “increment x,” the latter history loses an update: both read 10, and the final value is 11, not 12. Whether a particular database permits this exact schedule depends on its concurrency-control rules and the SQL used. PostgreSQL, for example, can make an atomic UPDATE counters SET x = x + 1 behave differently from an application-side read followed by a write. An anomaly name is therefore not a substitute for testing the actual statement pattern and isolation level.

The standard phenomena are useful but incomplete

The familiar anomalies describe observations that weaker isolation can permit.

Dirty read

T2 observes data written by a transaction that later aborts:

w1(balance = -50)   r2(balance = -50)   a1   c2

T2 has acted on a value that never becomes committed state. PostgreSQL does not permit dirty reads: requesting READ UNCOMMITTED behaves like READ COMMITTED.

Nonrepeatable read

The same row changes between two reads in one transaction:

r1(status = 'open')
    w2(status = 'closed') c2
r1(status = 'closed') c1

Under PostgreSQL READ COMMITTED, each statement receives a snapshot taken when that statement begins, so both reads are individually committed and valid even though they disagree.

Phantom read

A predicate returns a different set of rows after another transaction inserts or deletes a matching row:

T1: SELECT count(*) FROM jobs WHERE state = 'queued';  -- 4
T2: INSERT INTO jobs(state) VALUES ('queued'); COMMIT;
T1: SELECT count(*) FROM jobs WHERE state = 'queued';  -- 5

Preventing these three phenomena does not prove serializability. Berenson and co-authors’ critique of the ANSI SQL-92 definitions made this exact point and formally described Snapshot Isolation (SI), an important multiversion level that does not fit cleanly on a simple “stronger than” ladder.

PostgreSQL demonstrates the gap directly. Its REPEATABLE READ implementation prevents all three phenomena above, including phantoms, but its documentation still marks serialization anomalies as possible.

MVCC is machinery, not an isolation guarantee

Multiversion concurrency control keeps multiple row versions so a reader can select the version visible to its snapshot rather than waiting for an in-progress writer. Conceptually, a snapshot separates transactions whose effects are visible from transactions that were still in progress or began later.

PostgreSQL exposes a snapshot as xmin:xmax:xip_list:

  • transaction IDs below xmin are old enough to be either committed-and-visible or aborted-and-dead;
  • IDs at or above xmax had not completed when the snapshot was taken;
  • xip_list records transactions that were in progress within that range.

Tuple visibility also depends on transaction status and the tuple’s creating and deleting/updating transaction IDs. The full implementation includes details such as subtransactions, freezing, and vacuum; the three snapshot fields alone are not a complete visibility algorithm.

The isolation level determines when snapshots are acquired and what conflicts are rejected:

  • PostgreSQL READ COMMITTED normally gives each statement a fresh snapshot.
  • REPEATABLE READ uses a transaction-level snapshot, starting with the first non-transaction-control statement.
  • SERIALIZABLE uses snapshot behavior plus dependency monitoring.

Therefore “this database uses MVCC” says how versions can support concurrency, not whether a multi-statement invariant is safe. MVCC can implement multiple isolation levels, including serializable ones.

Snapshot Isolation prevents some conflicts, not every bad history

Under the standard SI model, a transaction reads from a consistent snapshot and concurrent transactions cannot both commit writes to the same item. This “first-committer-wins” rule prevents a basic lost update between overlapping writers of the same item.

The remaining hole is that two transactions may read overlapping state but write different rows. Row-level write conflict detection sees no collision.

Suppose this table initially contains two doctors:

doctors
+-------+---------+
| name  | on_call |
+-------+---------+
| Alice | true    |
| Bob   | true    |
+-------+---------+

The invariant is:

count(doctors where on_call) >= 1

Each doctor may leave call only if at least two doctors are currently on call:

T1                                              T2
BEGIN ISOLATION LEVEL REPEATABLE READ;          BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*)                                 SELECT count(*)
FROM doctors                                    FROM doctors
WHERE on_call;  -- 2                            WHERE on_call;  -- 2

UPDATE doctors                                  UPDATE doctors
SET on_call = false                             SET on_call = false
WHERE name = 'Alice';                           WHERE name = 'Bob';

COMMIT;                                         COMMIT;

Both transactions make a correct decision against their own snapshot. They update disjoint rows, so SI’s same-item write check does not force a conflict. Both may commit, leaving zero doctors on call.

No serial order explains the result. If T1 ran first, T2 would see only Bob on call and would not remove Bob. Reversing the order gives the symmetric outcome. This is write skew.

The defect is not a “stale read” in the ordinary sense: each transaction reads a stable, internally consistent database version. The defect is that the pair of snapshots and writes cannot be placed into one serial history.

Serializability is a graph property

A multiversion serialization graph makes the missing serial order explicit. Each committed transaction is a vertex. Dependencies constrain which transaction must appear first:

  • wr: T1 writes a version that T2 reads, so T1 → T2;
  • ww: T1 writes a version and T2 writes the next version, so T1 → T2;
  • rw anti-dependency: T1 reads a version and concurrent T2 creates a later version that T1 did not see, so T1 → T2.

The arrow means “must precede in the apparent serial order,” not necessarily “executed first in wall-clock time.”

For the doctors schedule:

T1 reads Bob.on_call = true
T2 later writes Bob.on_call = false
T1 --rw--> T2

T2 reads Alice.on_call = true
T1 later writes Alice.on_call = false
T2 --rw--> T1

The graph is a cycle:

T1 --rw--> T2 --rw--> T1

Acyclic dependency graphs can be topologically sorted into an equivalent serial order. A cycle means there is no such order.

Predicate reads count too. If T1 reads WHERE state = 'queued' and concurrent T2 inserts a matching row invisible to T1, that can create an rw anti-dependency even though the new tuple did not exist when T1 scanned. Correct serializable implementations must reason about the logical read set, not only primary keys that happened to exist.

SSI detects the shape every SI anomaly must contain

Tracking the complete dependency graph and checking it for cycles would be expensive. SSI relies on a more specific result: every non-serializable SI history contains two adjacent rw anti-dependencies:

T1 --rw--> T2 --rw--> T3

T2 is the pivot. In the stronger result used by PostgreSQL’s implementation, T3 is the first transaction in the cycle to commit. T1 and T3 can be the same transaction, as in the two-transaction write-skew cycle.

This pair of edges is called a dangerous structure. PostgreSQL does not need to retain every wr and ww dependency. It detects the rw edges that could participate in the dangerous structure and aborts a participant before all transactions in a non-serializable cycle can commit.

This detection is conservative. Every SI serialization anomaly has the dangerous structure, but not every dangerous structure completes a cycle. SSI can therefore produce a serialization failure for an execution that would ultimately have been serializable. That is a deliberate false positive: rejecting some safe histories is cheaper than maintaining and testing the full graph, while committed histories remain serializable.

PostgreSQL predicate locks observe; they do not block

PostgreSQL records Serializable reads with SIReadLock predicate locks. The word “lock” is easy to misread here. These locks do not block writers and do not create deadlocks. They preserve enough information to notice that a concurrent write would have changed a prior read’s result if the writer had been ordered first.

The tracked granularity depends on the query plan and available predicate-lock memory. PostgreSQL can track tuples, index pages or ranges, pages, and whole relations; finer locks may be promoted to coarser locks. A sequential scan requires a relation-level predicate lock. Coarser tracking is still safe, but it can report more conflicts and increase aborts.

You can inspect these records:

SELECT locktype, mode, relation::regclass, page, tuple
FROM pg_locks
WHERE mode = 'SIReadLock';

Do not use that view as an application correctness test. Locks may outlive the transaction that acquired them while overlapping transactions finish, and their granularity is an implementation decision. The public contract is serializability for successfully committed Serializable transactions, not a particular set of lock rows.

On the doctors schedule, changing both sessions to:

BEGIN ISOLATION LEVEL SERIALIZABLE;

allows the reads and writes to proceed without the read locks blocking either update. When PostgreSQL detects the dangerous structure, one transaction fails:

ERROR: could not serialize access due to read/write dependencies among transactions
SQLSTATE: 40001

The abort is part of the isolation protocol, not an infrastructure incident.

Retrying means rerunning the decision

A Serializable application is incomplete without transaction retry handling. PostgreSQL explicitly requires retrying the entire transaction, including application logic that selected SQL statements or calculated values.

The safe boundary looks like:

repeat with bounded attempts:
    begin SERIALIZABLE
    read current database state
    derive the decision from those reads
    issue writes
    commit
    if commit succeeds: return
    if SQLSTATE == 40001:
        rollback
        back off with jitter
        continue
    otherwise:
        rollback
        raise

Replaying only the failed UPDATE would reuse a decision derived from an invalidated snapshot. Retrying forever without a limit can also turn sustained contention into unbounded latency.

Keep irreversible external effects outside the retried body or make them idempotent. A payment request or email sent before COMMIT is not rolled back when PostgreSQL aborts the transaction. A transactional outbox can record the intent atomically, then let a separate idempotent worker perform the external action after commit.

For long read-only jobs, SERIALIZABLE READ ONLY DEFERRABLE offers a different tradeoff. PostgreSQL may wait before the first query until it can obtain a snapshot known to be safe, after which the transaction can avoid SSI overhead and its reads are not later invalidated by a serialization failure. This is useful for reports and backups that can tolerate startup delay; it is not a general setting for read-write requests.

What serializable does—and does not—promise

PostgreSQL Serializable guarantees that the effects of successfully committed Serializable transactions are equivalent to some serial execution. Several caveats matter:

  1. All relevant transactions must participate. A weaker-isolation transaction can make application decisions outside the serializable history you are relying on. Use Serializable consistently for transactions that maintain the same invariant, or enforce the invariant with database constraints or deliberate locking.
  1. Serializability does not repair incorrect transaction logic. If a transaction violates an invariant when run alone, serial scheduling preserves the bug. The guarantee composes individually correct transactions; it does not prove them correct.
  1. Declared constraints are preferable when the rule is expressible. Unique, foreign-key, check, and exclusion constraints give the database direct knowledge of many invariants and are enforced independently of this application-level reasoning. The “at least one doctor” rule is not a simple per-row check constraint, which is why it is a useful write-skew example.
  1. Not every database object is transactional in the same way. PostgreSQL sequence changes are immediately visible and are not rolled back on transaction abort. Do not use a gapless sequence as evidence of serial execution.
  1. Serializable can still surface errors other than 40001. PostgreSQL documents cases where overlapping Serializable transactions can produce a unique-constraint violation that would not arise in a pure serial execution, particularly when transactions do not follow the same key-selection protocol. Retry policy must be based on the operation and SQLSTATE, not on the assumption that every concurrency symptom is 40001.
  1. Distributed boundaries need separate analysis. PostgreSQL SSI governs transactions inside the database. It does not make a database transaction atomic with a cache, message broker, remote API, or another database. Those boundaries need protocols such as idempotency, an outbox, or distributed coordination.

A practical choice

Use READ COMMITTED when each statement can safely make progress against the latest committed state and the transaction does not rely on a multi-row read/write invariant. It remains PostgreSQL’s default and is often the right choice.

Use explicit row or advisory locks when the conflict is narrow, known, and intentionally materialized—but confirm that every code path takes the same lock in the same order.

Use SERIALIZABLE when correctness is easiest to state as “these transactions must behave as if run one at a time,” especially when predicates or changing application code make a complete conflict analysis fragile. Budget for retries, keep transactions short, declare read-only work, limit excessive concurrency, and measure serialization-failure rates under a representative workload.

The essential mental model is simple: a stable snapshot is not necessarily a serializable history. MVCC gives transactions coherent versions to read. SSI adds the graph-level check that prevents those individually coherent views from committing into a collectively impossible result.

References