Databases / transaction isolation / PostgreSQL · 2026-10-19

PostgreSQL Serializable: detecting write skew, not just dirty reads

Two transactions can each read a perfectly consistent database snapshot and still jointly violate a rule. This is write skew: the classic reason “repeatable reads” is not always enough. PostgreSQL's Serializable isolation level addresses this by detecting dangerous dependency patterns and aborting one transaction, rather than forcing every reader to block every writer.

A rule that looks safe in each transaction

Suppose a clinic must always have at least one doctor on call. Rows start as Alice=true and Ben=true. Two concurrent transactions each check that two doctors are on call, then each turns one doctor off. Each transaction sees the other's original row, so both conclude the invariant remains true. Their writes touch different rows, so ordinary row-write conflict checks do not stop the result: nobody is on call.

-- Session A                         -- Session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call; -- 2
                                     BEGIN ISOLATION LEVEL REPEATABLE READ;
                                     SELECT count(*) FROM doctors WHERE on_call; -- 2
UPDATE doctors SET on_call=false WHERE name='Alice';
                                     UPDATE doctors SET on_call=false WHERE name='Ben';
COMMIT;                               COMMIT;

Why snapshot isolation permits it

Snapshot isolation gives each transaction a stable view and typically prevents two concurrent transactions from overwriting the same row. But the invariant is about a predicate—“at least one matching row”—not one specific row. Transaction A reads Ben and writes Alice; B reads Alice and writes Ben. This read-write cycle, sometimes called a dangerous structure, is invisible if you look only for same-row write conflicts.

Serializable means equivalent to some serial order

A serializable execution must have the same effect as running the transactions one at a time in some order. PostgreSQL implements Serializable Snapshot Isolation (SSI): it tracks read-write dependencies, including predicate reads, and detects structures that could produce a non-serializable cycle. It usually allows concurrent work to proceed; if the conflict pattern becomes unsafe, it aborts a transaction with SQLSTATE 40001 (serialization_failure). The application must retry the whole transaction.

Reproducible lab

Install PostgreSQL locally, create the table, and open two psql sessions. Run the interleaved commands above first at REPEATABLE READ; both commits can succeed. Reset the rows, repeat using SERIALIZABLE, and expect one commit to fail with a serialization error. Exact victim and timing can vary; retry the entire failed transaction, including its reads and decision logic.

CREATE TABLE doctors(name text PRIMARY KEY, on_call boolean NOT NULL);
INSERT INTO doctors VALUES ('Alice', true), ('Ben', true);
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call;
-- In the other session, run the same transaction before either commits.
-- Each session updates a different doctor, then commits.

After both sessions finish, verify the invariant:

SELECT count(*) FROM doctors WHERE on_call;
-- Correct result must be at least 1.

For production code, implement bounded retries with jitter for SQLSTATE 40001. Never retry only the final UPDATE: the earlier read and decision belong to the same logical transaction. Keep external side effects (email, payment capture) outside a retryable transaction or make them idempotent, because the database may rerun the transaction body.

Trade-offs and operational clues

Aborts are normal: serialization failures are concurrency control, not necessarily database faults.
Retry whole unit: all reads and writes must be repeated against a fresh snapshot.
Long transactions increase conflict risk: keep transactions short and avoid user interaction inside them.
Indexes and predicates matter: SSI monitors read dependencies, not just explicit row locks.
Serializable is not a substitute for external idempotency: SQL commit cannot atomically include a third-party API call.

Takeaway

Snapshot consistency answers “what did this transaction see?” Serializability answers the stronger question “could all committed work have happened in a safe one-at-a-time order?” Write skew demonstrates why the distinction matters. Choose Serializable when correctness depends on cross-row predicates, and make serialization retries an ordinary, tested application path.