Diagnosing and handling Postgres serialization failures (40001)

Symptoms

• A query or COMMIT fails with `ERROR: could not serialize access due to concurrent update`
• The error appears intermittently under concurrent load and the same transaction succeeds on retry
• The driver reports SQLSTATE 40001

The diagnostic path

8 steps, exactly as this revision published them.

1. Step 1 · StartYou're seeing SQLSTATE 40001

   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

   • passed → step 2, Confirm the transaction is running above READ COMMITTED

2. Step 2 · TestConfirm the transaction is running above READ COMMITTED

   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).

   Read-onlysql

   Copy

   SHOW transaction_isolation;

   Expected result

    transaction_isolation 
   ------------------------
    repeatable read
   (1 row)

   What happens next

   • passed → step 3, This is Postgres's conflict detection working as designed
   • failed → step 4, 40001 without elevated isolation is unusual
   • unknown → step 4, 40001 without elevated isolation is unusual

3. Step 3 · Root causeThis is Postgres's conflict detection working as designed

   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

   • passed → step 5, Retry the whole transaction on 40001

4. Step 4 · End40001 without elevated isolation is unusual

   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.

5. Step 5 · FixRetry the whole transaction on 40001

   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

   • passed → step 6, Confirm the retry logic is actually working

6. Step 6 · Verify the fixConfirm the retry logic is actually working

   Watch the rollback trend for the database over a period that includes normal concurrent load.

   Read-onlysql

   Copy

   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

   • passed → step 7, Resolved
   • failed → step 8, Still surfacing to users
   • unknown → step 8, Still surfacing to users

7. Step 7 · EndResolved

   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.

8. Step 8 · EndStill surfacing to users

   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.

Sources

• 13.2. Transaction Isolation— PostgreSQL
• Appendix A. PostgreSQL Error Codes— PostgreSQL