Covers confirming this is a genuine repeatable-read/serializable conflict and adding the application-level retry that Postgres's own documentation prescribes. It does not cover deadlocks (SQLSTATE 40P01 — a different mechanism entirely, see the deadlock playbook) and does not attempt to tune away contention at the schema level.
Unverifiedno reproductions yetWhy this confidence?8 steps. Every step is written out below in full — the interactive version simply follows the branches for you.
A transaction failed with could not serialize access due to concurrent update, SQLSTATE 40001. This only happens under REPEATABLE READ or SERIALIZABLE isolation — Postgres detected that letting both this transaction and a concurrent one commit could not have arisen from any serial execution, so it aborted one of them.
What happens next
Run this inside the same session/transaction that failed, or check the application/ORM config for an explicit isolation level (e.g. Prisma's isolationLevel, a driver's SET TRANSACTION ISOLATION LEVEL, or a framework default).
SHOW transaction_isolation;Expected result
transaction_isolation
------------------------
repeatable read
(1 row)What happens next
Under REPEATABLE READ and SERIALIZABLE, Postgres aborts one of two conflicting concurrent transactions rather than allow a result that no serial ordering of them could have produced. This is not corruption and not a bug — the aborted transaction is expected to be retried from its first statement. A high rate of 40001 aborts means the same rows (or, under SERIALIZABLE, the same predicates) are being contended by many concurrent transactions.
What happens next
This playbook covers only the standard REPEATABLE READ / SERIALIZABLE write-conflict. If the session is genuinely READ COMMITTED, re-check that the SQLSTATE and message text were captured correctly, and look for a SET TRANSACTION ISOLATION LEVEL issued inside a function, trigger, or ORM interceptor that elevates isolation for just this one statement. Diagnosing that further is outside what this playbook prescribes.
Catch SQLSTATE 40001 specifically and re-run the transaction from its very first statement (not just the last query) with a short backoff — the transaction's snapshot is invalid after the abort, so replaying only the failing statement is not sufficient. Most Postgres client libraries and ORMs surface the SQLSTATE on the thrown error object (e.g. error.code === '40001' in node-postgres/Prisma). You can also reduce how often this happens by taking an explicit SELECT ... FOR UPDATE lock earlier in the transaction to serialise access to hot rows, but that changes concurrency behaviour and is a separate decision from adding the retry.
What happens next
Watch the rollback trend for the database over a period that includes normal concurrent load.
SELECT datname, xact_commit, xact_rollback
FROM pg_stat_database
WHERE datname = current_database();Expected result
datname | xact_commit | xact_rollback
---------+-------------+---------------
appdb | 184213 | 412
(1 row)What happens next
40001 errors are being retried transparently and no longer reach users. xact_rollback will still include the aborted attempts — that's expected, not a regression.
If retries are implemented but 40001 still propagates, either the retry isn't restarting from the transaction's first statement, the configured retry count is too low for the current abort rate, or contention is high enough that reducing it (shorter transactions, narrower row ranges per transaction, an explicit lock to serialise the hottest rows) is needed alongside the retry. Diagnosing which is outside this playbook's scope.
What would strengthen it: 6 more independent reproductions. Reproductions from 3 more distinct environments.