The invariant chooses the isolation level, not the database default

heuristic
established
fragment

Scope. That snapshot isolation admits write skew is a result about histories (Berenson et al.); that PostgreSQL SERIALIZABLE aborts such interleavings is documented behaviour and was reproduced on PostgreSQL 18.6 for the example shown. The decision rule itself — pick the level from the shape of the invariant, prefer constraints, then row locks, then SERIALIZABLE — is engineering judgment, not a theorem. The SQL comments describe one observed interleaving: which transaction SSI aborts, and on which statement, depends on timing, and SSI can also abort transactions that were in fact serializable.

Versions. Behaviour verified on PostgreSQL 18.6 (postgres:18 image). PostgreSQL has shipped SSI as its SERIALIZABLE level since 9.1; REPEATABLE READ there is snapshot isolation, and READ UNCOMMITTED behaves as READ COMMITTED.

Conflicts with: Active/active availability ↔ multi-row transactional invariants

Primary sources: Berenson, Bernstein, Gray, Melton, O’Neil, O’Neil — A Critique of ANSI SQL Isolation Levels (SIGMOD 1995), Ports, Grittner — Serializable Snapshot Isolation in PostgreSQL (VLDB 2012)

References: Adya, Liskov, O’Neil — Generalized Isolation Level Definitions (ICDE 2000), PostgreSQL 18 documentation — Transaction Isolation, PostgreSQL 18 documentation — Serialization Failure Handling

Other claims on this card, typed separately:

- theorem
- established
Snapshot isolation permits write skew (A5B) and is incomparable with locking REPEATABLE READ; the ANSI phenomena-based definitions do not characterize it.
Primary sources: Berenson et al. — A Critique of ANSI SQL Isolation Levels (SIGMOD 1995)
- theorem
- established
Aborting a transaction whenever two consecutive rw-dependencies between concurrent transactions are detected makes snapshot isolation serializable, at the cost of some unnecessary aborts.
Primary sources: Cahill, Röhm, Fekete — Serializable Isolation for Snapshot Databases (SIGMOD 2008)
- empirical law
- context-dependent
On the authors’ transaction-processing and web-application benchmarks, PostgreSQL’s SSI cost under 7% relative to snapshot isolation and outperformed two-phase locking on some workloads.
Primary sources: Ports, Grittner — Serializable Snapshot Isolation in PostgreSQL (VLDB 2012)

An isolation level is a list of anomalies you agree to handle yourself. Snapshot isolation (PostgreSQL’s REPEATABLE READ) stops lost updates on the same row, but not write skew: two transactions read an overlapping set, each checks the invariant against its own snapshot, then each writes a different row — no write-write conflict, both commit, and the invariant that spans the rows is gone. So the question at review is not “which level do we use” but “which invariant does this transaction protect, and is it a single-row fact, a multi-row fact, or a predicate over rows that may not exist yet”.

fragment

PostgreSQL 18 · two sessions

-- Invariant: shift 1 always has at least one doctor on call.
CREATE TABLE on_call (doctor text PRIMARY KEY, shift_id int NOT NULL, on_call bool NOT NULL);
INSERT INTO on_call VALUES ('alice', 1, true), ('bob', 1, true);

-- Sessions A and B interleave; each step runs in time order.
BEGIN ISOLATION LEVEL REPEATABLE READ;                             -- A
BEGIN ISOLATION LEVEL REPEATABLE READ;                             -- B
SELECT count(*) FROM on_call WHERE shift_id = 1 AND on_call;       -- A: 2, "safe to leave"
SELECT count(*) FROM on_call WHERE shift_id = 1 AND on_call;       -- B: 2, "safe to leave"
UPDATE on_call SET on_call = false WHERE doctor = 'alice';         -- A
UPDATE on_call SET on_call = false WHERE doctor = 'bob';           -- B: different row, no wait
COMMIT;                                                            -- A: ok
COMMIT;                                                            -- B: ok
-- Result: nobody on call. Both transactions were correct in isolation.

-- Same interleaving with BEGIN ISOLATION LEVEL SERIALIZABLE:
-- A commits; B's COMMIT fails and bob stays on call:
--   ERROR: could not serialize access due to read/write dependencies among transactions
--   SQLSTATE 40001, "Canceled on identification as a pivot, during commit attempt."
-- The caller re-runs B from BEGIN, including the SELECT: the retry now sees count = 1.

-- Alternative at READ COMMITTED: lock the rows the decision reads.
SELECT doctor FROM on_call WHERE shift_id = 1 AND on_call FOR UPDATE;
-- B blocks until A commits, then gets only 'bob' back and must refuse.
-- At REPEATABLE READ the same statement fails B with 40001 instead.
-- Neither variant stops a concurrent INSERT of a new matching row (a phantom).

Decision rule. A single-row invariant is covered by READ COMMITTED plus an atomic statement (UPDATE … SET x = x - 1 WHERE x > 0) or a version check (66). An invariant expressible as a constraint belongs in the schema: UNIQUE, CHECK, EXCLUDE — the database enforces it at every level. A multi-row invariant over rows that already exist can be protected by SELECT … FOR UPDATE on exactly those rows; the lock must cover everything the decision read, and it blocks. A predicate over rows that may be inserted concurrently (“no overlapping booking”, “at most N active sessions”) needs SERIALIZABLE, a materialized conflict row that every writer updates, or a coarser lock. SERIALIZABLE is the only option that stays correct when a new transaction type appears that nobody reviewed against the old ones.

What SERIALIZABLE costs in PostgreSQL. It is SSI: snapshot isolation plus tracking of read/write dependencies through non-blocking SIReadLock predicate locks; when two consecutive dependencies between concurrent transactions appear, one of them is aborted (the pivot, if it has not committed yet). The detection is conservative, so some aborted transactions would have been serializable. The price is paid in retries, and the retry contract is strict: SQLSTATE 40001 can arrive on any statement including COMMIT; the whole transaction, including the reads and the logic that chose what to write, is re-run (any side effect outside the database inside that code needs 22, and the loop needs a bound, 24); nothing read inside it counts as true until it commits. Sequential scans take relation-level predicate locks and raise the failure rate, so the queries need indexes. Read-only reports can use SERIALIZABLE READ ONLY DEFERRABLE, which waits for a safe snapshot and then cannot be rolled back by a serialization failure.

Level names are not portable. ANSI defines levels by phenomena, and Berenson et al. showed those definitions are ambiguous and that snapshot isolation is incomparable with locking REPEATABLE READ: each allows an anomaly the other forbids. The same keyword means different guarantees in different engines, and read replicas or caches in front of the database change them again (65, 70). Specify the anomaly, not the keyword.