PostgreSQL Serializable Stops the Write Skew Repeatable Read Commits

Two concurrent PostgreSQL transactions each saw two doctors on call, changed different rows, and committed under REPEATABLE READ. The final count was zero. Repeating the same history under SERIALIZABLE produced one commit, one SQLSTATE 40001, and one doctor still on call. A complete retry then reread that state and changed nothing.

That result explains the operational choice better than an isolation-level chart. A stable snapshot stops rows from changing underneath one transaction, but it does not make two independent read-then-write decisions equivalent to a serial order. This advanced lab gives application developers a disposable PostgreSQL 17 fixture, deterministic two-session barriers, exact acceptance criteria, and ownership-scoped cleanup.

Start With the Invariant, Not the Isolation Name

Assume a service must keep at least one doctor on call. Alice may leave only if another doctor remains; Bob follows the same rule. Each transaction reads the shared predicate count(*) WHERE on_call, then writes its own row. Either transaction is correct when run alone.

Failure appears only when both transactions read before either commits. Alice sees Bob; Bob sees Alice; their writes touch different rows, so there is no ordinary row-lock conflict. Both can commit and leave a database state that no serial order could produce. PostgreSQL calls this a serialization anomaly, and write skew is its simplest useful form.

Current PostgreSQL transaction-isolation documentation says REPEATABLE READ prevents dirty reads, nonrepeatable reads, and PostgreSQL phantoms, but serialization anomalies remain possible. SERIALIZABLE adds monitoring for read/write dependency patterns and aborts a transaction when the history cannot safely appear serial.

PostgreSQL’s wiki Serializable Snapshot Isolation overview describes the useful contract: prove each transaction correct in isolation, then PostgreSQL either admits a serializable mix or rolls one back. Predicate locks used by SSI are not blocking row locks; adding SELECT FOR UPDATE to arbitrary rows is not a generic substitute for a predicate-level invariant.

Build One Owned PostgreSQL 17 Fixture

Begin with a marker-owned cluster under /tmp that refuses an occupied loopback port, starts only that cluster, and creates two on-call rows. It does not create a system database, edit postgresql.conf, or stop the installed PostgreSQL service.

set -Eeuo pipefail
export LC_ALL=C
pg_bin=$(pg_config --bindir)
lab_root=$(mktemp -d /tmp/voxfor-pg-skew-175.XXXXXX)
marker=$lab_root/.voxfor-owned
port=18577
receipt_copy=$PWD/postgresql-write-skew-receipt-175.txt
cluster_started=no
cleanup() {
  if [[ $cluster_started == yes && -f $marker ]] && grep -Fqx voxfor-pg-skew-175 "$marker"; then
    runuser -u postgres -- "$pg_bin/pg_ctl" -D "$lab_root/data" -m fast -w stop >/dev/null 2>&1 || true
  fi
  if [[ -d $lab_root && -f $marker ]] && grep -Fqx voxfor-pg-skew-175 "$marker" && [[ $lab_root == /tmp/voxfor-pg-skew-175.* ]]; then
    find "$lab_root" -depth -mindepth 1 -delete
    rmdir "$lab_root"
  fi
  rm -f -- "$receipt_copy"
}
trap cleanup EXIT
printf '%s\n' voxfor-pg-skew-175 >"$marker"
test ! -e "$receipt_copy"
! ss -Hln "sport = :$port" | grep -q .
chown -R postgres:postgres "$lab_root"
runuser -u postgres -- "$pg_bin/initdb" -D "$lab_root/data" \
  --no-locale --encoding=UTF8 --auth=trust >"$lab_root/initdb.out"
runuser -u postgres -- "$pg_bin/pg_ctl" -D "$lab_root/data" \
  -l "$lab_root/postgres.log" -o "-h 127.0.0.1 -p $port -k $lab_root" \
  -w start >"$lab_root/start.out"
cluster_started=yes
psql=(runuser -u postgres -- psql -X -v ON_ERROR_STOP=1 \
  -h 127.0.0.1 -p "$port" -d postgres)
"${psql[@]}" <<'SQL'
CREATE TABLE doctors (
  id integer PRIMARY KEY,
  name text NOT NULL UNIQUE,
  on_call boolean NOT NULL
);
INSERT INTO doctors VALUES (1, 'Alice', true), (2, 'Bob', true);
SQL
test "$("${psql[@]}" -Atc 'SELECT count(*) FROM doctors WHERE on_call')" = 2

Use an unprivileged database OS account in a real test environment. Root appears only to create an owned directory and switch to Debian’s postgres account; PostgreSQL itself refuses to initialize or run as root. Port 18577 remains bound to loopback.

Make Repeatable Read Commit the Impossible State

Run the two scripts below as one coherent concurrency test. Each transaction reads the count, creates a readiness file, waits until the peer has also read, updates a different row, and commits. Those files are test barriers, not database locks.

cat >"$lab_root/rr-a.sql" <<SQL
\\set ON_ERROR_STOP on
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT 'rr_a_seen=' || count(*) FROM doctors WHERE on_call;
\\! touch '$lab_root/rr-a-ready'
\\! while [ ! -f '$lab_root/rr-b-ready' ]; do sleep 0.05; done
UPDATE doctors SET on_call=false WHERE id=1;
COMMIT;
\\echo rr_a=committed
SQL
cat >"$lab_root/rr-b.sql" <<SQL
\\set ON_ERROR_STOP on
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT 'rr_b_seen=' || count(*) FROM doctors WHERE on_call;
\\! touch '$lab_root/rr-b-ready'
\\! while [ ! -f '$lab_root/rr-a-ready' ]; do sleep 0.05; done
UPDATE doctors SET on_call=false WHERE id=2;
COMMIT;
\\echo rr_b=committed
SQL
"${psql[@]}" -Atf "$lab_root/rr-a.sql" >"$lab_root/rr-a.out" 2>"$lab_root/rr-a.err" & rr_a_pid=$!
"${psql[@]}" -Atf "$lab_root/rr-b.sql" >"$lab_root/rr-b.out" 2>"$lab_root/rr-b.err" & rr_b_pid=$!
wait "$rr_a_pid"; wait "$rr_b_pid"

Next, refuse to infer success from two zero exit codes. This input checks both snapshot observations, both commit markers, and the violated invariant. Only then does it reset the two fixture rows for the serializable comparison.

grep -Fqx 'rr_a_seen=2' "$lab_root/rr-a.out"
grep -Fqx 'rr_b_seen=2' "$lab_root/rr-b.out"
grep -Fqx 'rr_a=committed' "$lab_root/rr-a.out"
grep -Fqx 'rr_b=committed' "$lab_root/rr-b.out"
rr_remaining=$("${psql[@]}" -Atc 'SELECT count(*) FROM doctors WHERE on_call')
test "$rr_remaining" = 0
"${psql[@]}" -c 'UPDATE doctors SET on_call=true' >/dev/null
test "$("${psql[@]}" -Atc 'SELECT count(*) FROM doctors WHERE on_call')" = 2

pgDash’s PostgreSQL isolation-anomaly walkthrough is the strongest same-intent ranking page because it demonstrates write skew and the serializable abort clearly. The stricter addition here is a fully owned fixture whose barriers, SQLSTATE, retry decision, receipt, and cleanup are machine-checked.

Let Serializable Reject One Complete Attempt

Now change only the isolation level. VERBOSITY verbose makes psql include SQLSTATE 40001, while ON_ERROR_STOP converts the rejected script into a nonzero client result. Both sessions again read two before either writes.

rm -f "$lab_root"/ser-*-ready
for side in a b; do
  id=1; [[ $side == b ]] && id=2
  peer=b; [[ $side == b ]] && peer=a
  cat >"$lab_root/ser-$side.sql" <<SQL
\\set ON_ERROR_STOP on
\\set VERBOSITY verbose
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT 'ser_${side}_seen=' || count(*) FROM doctors WHERE on_call;
\\! touch '$lab_root/ser-$side-ready'
\\! while [ ! -f '$lab_root/ser-$peer-ready' ]; do sleep 0.05; done
UPDATE doctors SET on_call=false WHERE id=$id;
COMMIT;
\\echo ser_${side}=committed
SQL
done
set +e
"${psql[@]}" -Atf "$lab_root/ser-a.sql" >"$lab_root/ser-a.out" 2>"$lab_root/ser-a.err" & ser_a_pid=$!
"${psql[@]}" -Atf "$lab_root/ser-b.sql" >"$lab_root/ser-b.out" 2>"$lab_root/ser-b.err" & ser_b_pid=$!
wait "$ser_a_pid"; ser_a_rc=$?
wait "$ser_b_pid"; ser_b_rc=$?
set -e

Do not assume Alice or Bob will win. The acceptance test classifies the observed results, requires exactly one success and one failure, extracts the losing script’s error, and proves that one row remains on call.

if (( ser_a_rc == 0 && ser_b_rc != 0 )); then
  winner=A; loser=B; retry_id=2; loser_err=$lab_root/ser-b.err
elif (( ser_b_rc == 0 && ser_a_rc != 0 )); then
  winner=B; loser=A; retry_id=1; loser_err=$lab_root/ser-a.err
else
  printf 'unexpected_serializable_results a=%s b=%s\n' "$ser_a_rc" "$ser_b_rc" >&2
  exit 1
fi
grep -Fq 'ERROR:  40001:' "$loser_err"
ser_remaining=$("${psql[@]}" -Atc 'SELECT count(*) FROM doctors WHERE on_call')
test "$ser_remaining" = 1

Vlad Mihalcea’s write-skew explanation across MVCC engines helps distinguish this anomaly from a lost update. Nile’s transaction-isolation explainer adds the important design perspective: serializability lets developers reason about whole transactions in a serial order, not about every possible interleaving.

Retry the Decision, Not the Failed COMMIT

PostgreSQL’s current serialization-failure handling guidance requires retrying the complete transaction, including the logic that decides which statements and values to use. Reissuing only COMMIT, replaying only the UPDATE, or trusting values computed by the aborted attempt is wrong.

This lab’s retry starts a new serializable transaction. It rereads the current count and updates the losing doctor’s row only when more than one doctor remains. Because the winning attempt already left one doctor on call, the retry changes zero rows and preserves the invariant.

retry_out=$("${psql[@]}" -At <<SQL
BEGIN ISOLATION LEVEL SERIALIZABLE;
WITH state AS (SELECT count(*) AS on_call FROM doctors WHERE on_call),
attempt AS (
  UPDATE doctors SET on_call=false
  WHERE id=$retry_id AND (SELECT on_call FROM state) > 1
  RETURNING id
)
SELECT 'retry_seen=' || (SELECT on_call FROM state) ||
       ' retry_changed=' || count(*) FROM attempt;
COMMIT;
SQL
)
grep -Fq 'retry_seen=1 retry_changed=0' <<<"$retry_out"
final_remaining=$("${psql[@]}" -Atc 'SELECT count(*) FROM doctors WHERE on_call')
test "$final_remaining" = 1

Production retry code should match SQLSTATE 40001, cap attempts, add jittered backoff under contention, and instrument attempts plus exhaustion. It must also consider side effects. If a transaction sends email, charges a card, or publishes a message before commit, a database retry can repeat the external action. Voxfor’s AI action retry and reconciliation guide explains the same boundary outside SQL: prove the durable outcome before issuing another real-world action.

Message redelivery has a different acceptance window from a database transaction. Voxfor’s NATS JetStream deduplication-window test shows why a broker’s bounded duplicate suppression cannot replace an application idempotency key or reconciliation record.

Do not collapse 40001 and deadlock SQLSTATE 40P01 into one unexplained metric. Both can be retryable, but their causes differ. Voxfor’s MySQL lock-order deadlock lab shows the wait cycle that SSI write skew does not require. Likewise, PostgreSQL lock and statement timeout ordering addresses blocking admission, not serialization-graph correctness.

Turn the Test Into a Release Receipt

The final input records the two histories, hashes the receipt, stops only the owned cluster, and proves the listener, cluster, fixture, and copied receipt are gone.

{
  printf 'postgresql=%s\n' "$("${psql[@]}" -Atc 'SHOW server_version')"
  printf 'repeatable_read=A:commit,B:commit,on_call:%s\n' "$rr_remaining"
  printf 'serializable=winner:%s,loser:%s,sqlstate:40001,on_call:%s\n' \
    "$winner" "$loser" "$ser_remaining"
  printf '%s\n' "$(grep -o 'retry_seen=[0-9]* retry_changed=[0-9]*' <<<"$retry_out")"
  printf 'invariant=on_call:%s\n' "$final_remaining"
  printf 'system_cluster_changed=no\n'
} | tee "$lab_root/receipt.txt" "$receipt_copy"
grep -Fqx 'repeatable_read=A:commit,B:commit,on_call:0' "$receipt_copy"
grep -Eq '^serializable=winner:[AB],loser:[AB],sqlstate:40001,on_call:1$' "$receipt_copy"
grep -Fqx 'retry_seen=1 retry_changed=0' "$receipt_copy"
grep -Fqx 'invariant=on_call:1' "$receipt_copy"
sha256sum "$receipt_copy"
runuser -u postgres -- "$pg_bin/pg_ctl" -D "$lab_root/data" -m fast -w stop >"$lab_root/stop.out"
cluster_started=no
test ! -e "$lab_root/data/postmaster.pid"
find "$lab_root" -depth -mindepth 1 -delete
rmdir "$lab_root"
rm -f -- "$receipt_copy"
trap - EXIT
test ! -e "$lab_root" && test ! -e "$receipt_copy"
! ss -Hln "sport = :$port" | grep -q .
printf 'cleanup=cluster:1,fixture:1,receipt:1,listener:1 absent=yes\n'

Representative output from PostgreSQL 17.10 on Debian 13:

postgresql=17.10 (Debian 17.10-0+deb13u1)
repeatable_read=A:commit,B:commit,on_call:0
serializable=winner:A,loser:B,sqlstate:40001,on_call:1
retry_seen=1 retry_changed=0
invariant=on_call:1
system_cluster_changed=no
cleanup=cluster:1,fixture:1,receipt:1,listener:1 absent=yes

Accept the production design only when a deterministic concurrency test makes the lower isolation level violate the exact business invariant, SERIALIZABLE returns exactly one 40001 for the same history, the complete retry rereads committed state, the invariant remains true, retry metrics are visible, and external effects are idempotent or reconciled.

If serialization failures exceed the retry budget, preserve the failed SQLSTATE and attempt receipt, stop new work for that invariant, and restore the prior application isolation setting or feature flag. Do not lower isolation while leaving the cross-row rule unprotected. Either keep serializable plus bounded retries or deploy an independently tested serialization mechanism, then rerun the two-session history before reopening traffic.

Peter Grman’s production comparison of repeatable read and serializable is a useful reminder that retry rates depend on real predicates, indexes, and contention. This two-row lab proves correctness, not production capacity; load-test the real transaction shape and graph 40001 attempts before rollout. PgBouncer can change connection ownership but not this transaction’s logical obligation; Voxfor’s PgBouncer connection-pooling guide keeps pooling mode separate from invariant design.