Choose an isolation level from the invariant an operation must preserve. A stable snapshot is useful, but two transactions can each make a locally reasonable decision that becomes invalid when both commit. PostgreSQL Repeatable Read prevents changes to a transaction’s reading snapshot, yet permits some serialization anomalies. Serializable adds detection that rejects combinations of concurrent work inconsistent with any serial execution, requiring the application to handle retries.

Separate a stable view from a valid decision

Consider two on-call doctors and a rule that at least one must remain available. Each doctor may leave only after observing more than one available doctor. Two Repeatable Read transactions can both observe two, then update different rows to leave. Neither overwrites the other’s row. Both can commit, leaving zero doctors even though each transaction used an internally stable snapshot.

This is write skew, not a lost update to one shared row. Row-level checks on each doctor’s boolean state do not express the global availability rule. Protecting only the row being changed also misses the relationship between the two decisions. A suitable serialization protocol must cover the invariant’s complete scope.

Database implementations matter. PostgreSQL maps Read Uncommitted to Read Committed, and its Repeatable Read prevents phantom reads even though the SQL standard allows that level to permit them. Those properties do not remove write skew. Isolation names should lead to the engine’s documented guarantees, rather than an assumption that every database implements them identically.

When it breaks

Treating Serializable as a mode that physically executes every transaction one at a time is misleading. PostgreSQL runs transactions concurrently and tracks dependencies; a transaction may fail during execution or commit with SQLSTATE 40001. The application must retry the complete decision using fresh reads, not resubmit a commit or reuse values computed from the rejected snapshot.

Serializable also cannot repair a transaction that would violate the business rule even when run alone. Every relevant write path must follow the intended contract. A separate unrestricted update can still remove the last doctor. Higher isolation is a concurrency guarantee, not an automatic implementation of missing application logic.

Check it quickly

Open two fresh psql -X -v ON_ERROR_STOP=1 sessions, A and B, in the same disposable PostgreSQL 18 database. Use the same role, normal autocommit outside explicit blocks, and an unused schema name. Follow the order exactly; the overlapping reads are what establish the competing decisions.

  1. In A, create the initial roster.
CREATE SCHEMA isolation_demo;
CREATE TABLE isolation_demo.doctors (id integer PRIMARY KEY,on_call boolean NOT NULL);
INSERT INTO isolation_demo.doctors VALUES (1,true),(2,true);
  1. In A, begin and read; then do the same in B. Both counts must be two.
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) AS available FROM isolation_demo.doctors WHERE on_call;

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) AS available FROM isolation_demo.doctors WHERE on_call;
  1. Update doctor one in A, then doctor two in B. Each update also checks the availability rule within its own snapshot.
UPDATE isolation_demo.doctors SET on_call=false WHERE id=1
  AND (SELECT count(*) FROM isolation_demo.doctors WHERE on_call)>1 RETURNING id;

UPDATE isolation_demo.doctors SET on_call=false WHERE id=2
  AND (SELECT count(*) FROM isolation_demo.doctors WHERE on_call)>1 RETURNING id;
  1. Commit A, then B. In A, inspect the broken invariant and reset the roster after both transactions finish.
COMMIT;

\set ON_ERROR_STOP off
COMMIT;
\echo commit_state :SQLSTATE
\set ON_ERROR_STOP on

SELECT count(*) AS available FROM isolation_demo.doctors WHERE on_call;

UPDATE isolation_demo.doctors SET on_call=true;
  1. Repeat the overlapping decisions with Serializable: begin A, begin B, update A, update B, commit A, then attempt B’s commit.
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) AS available FROM isolation_demo.doctors WHERE on_call;

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) AS available FROM isolation_demo.doctors WHERE on_call;

UPDATE isolation_demo.doctors SET on_call=false WHERE id=1
  AND (SELECT count(*) FROM isolation_demo.doctors WHERE on_call)>1 RETURNING id;

UPDATE isolation_demo.doctors SET on_call=false WHERE id=2
  AND (SELECT count(*) FROM isolation_demo.doctors WHERE on_call)>1 RETURNING id;

COMMIT;

\set ON_ERROR_STOP off
COMMIT;
\echo commit_state :SQLSTATE
\set ON_ERROR_STOP on
  1. In A, check availability. In B, retry the entire decision with a new transaction. Finish by checking and cleaning up in A.
SELECT count(*) AS available FROM isolation_demo.doctors WHERE on_call;

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) AS available FROM isolation_demo.doctors WHERE on_call;
UPDATE isolation_demo.doctors SET on_call=false WHERE id=2
  AND (SELECT count(*) FROM isolation_demo.doctors WHERE on_call)>1 RETURNING id;
COMMIT;

SELECT count(*) AS available FROM isolation_demo.doctors WHERE on_call;
DROP SCHEMA isolation_demo CASCADE;

On PostgreSQL 18.6, the Repeatable Read commits succeeded and availability became zero. With the controlled Serializable ordering, A committed and B’s commit reported 40001; one doctor remained. Error stopping is disabled only around the commit being observed so psql can print its SQLSTATE. A different error is not the expected serialization result.

B’s fresh retry observed one available doctor. Its conditional update returned no row, and it committed without removing that last doctor. The final count stayed one. This verifies both anomaly prevention and recomputation after rejection; merely proving that an error occurred would not establish the recovery behavior.

Account for the real cost

Dependency tracking and repeated attempts consume resources, while explicit locking can introduce waiting and coordination overhead. Neither approach is universally faster. Measure the actual transaction mix, keep transactions short, and bound retries by attempts and the request deadline. Choose the mechanism that preserves the invariant under all participating paths, then verify its contention behavior with representative work.

Sources