40001ERRORTier 2 — Caution✅ HIGH confidence

could not serialize access due to concurrent update

Category: Transaction RollbackVersions: All Postgres versions

What this means

Under SERIALIZABLE or REPEATABLE READ isolation, Postgres detected that the transaction's view of the data has been invalidated by a concurrent committed update. The transaction is aborted to preserve the illusion of serial execution.

Why it happens

1Two SERIALIZABLE transactions read and then write the same rows in an order that cannot be mapped to any serial execution
2A REPEATABLE READ transaction attempted to update a row that another transaction modified and committed after the snapshot was taken
3Write skew anomaly: two transactions each read a set of rows, make a decision based on them, and write non-overlapping rows that together violate a constraint

How to reproduce

Two concurrent SERIALIZABLE transactions both read a balance then attempt an update.

trigger — this will ERROR

CREATE TABLE accounts (id INT PRIMARY KEY, balance NUMERIC);
INSERT INTO accounts VALUES (1, 100);

-- Session 1:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 1; -- returns 100

-- Session 2 (concurrent):
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
COMMIT;

-- Session 1 continues:
UPDATE accounts SET balance = balance - 20 WHERE id = 1; -- triggers 40001

ERROR: could not serialize access due to concurrent update

Fix 1: Retry the transaction on 40001

Always — 40001 is an expected outcome of SERIALIZABLE isolation and the application must retry.

fix

-- Catch SQLSTATE 40001 in application code and retry the full transaction:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 20 WHERE id = 1;
COMMIT;

Why this works

Postgres SSI (Serializable Snapshot Isolation) tracks read/write dependencies between concurrent transactions. When a cycle is detected that would produce a non-serializable history, the transaction with fewer dependencies is chosen as the victim and rolled back. The retry will execute after the conflicting transaction commits, reading the updated snapshot.

Fix 2: Use SELECT FOR UPDATE for pessimistic locking

When retries are expensive and conflict rate is high.

fix

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 20 WHERE id = 1;
COMMIT;

Why this works

FOR UPDATE acquires a row-level exclusive lock immediately on SELECT. Concurrent transactions block at the SELECT rather than proceeding optimistically, eliminating the serialization conflict. The tradeoff is reduced throughput under high concurrency.

What not to do

✗

Downgrade to READ COMMITTED to avoid 40001

Why it's wrong: READ COMMITTED does not prevent write skew or non-repeatable reads; data anomalies become silent correctness bugs rather than explicit errors.

Version notes

Postgres 9.1+Full Serializable Snapshot Isolation (SSI) implemented. Earlier versions had snapshot isolation only, which prevented fewer anomalies.

Dangerous variant

⚠️ Warning

Silencing 40001 by catching it and continuing without retry will cause the transaction's writes to be silently discarded, leading to data loss.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

📚 Feature docs: https://www.postgresql.org/docs/current/transaction-iso.html

🔧 Source ref: src/backend/storage/lmgr/predicate.c — CheckForSerializationFailure()

📖 Further reading: Transaction Isolation

📖 Further reading: SSI in PostgreSQL

Confidence assessment

✅ HIGH confidence

Well-documented since Postgres 9.1. The retry requirement is universally agreed upon in the Postgres community. Edge case: 40001 can also appear in READ COMMITTED transactions if a row being updated was deleted by a concurrent transaction; the retry approach is the same.