EC
40001
PostgreSQLERRORNotableTransaction RollbackHIGH confidence

could not serialize access due to concurrent update

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
  1. 1Two SERIALIZABLE transactions read and then write the same rows in an order that cannot be mapped to any serial execution
  2. 2A REPEATABLE READ transaction attempted to update a row that another transaction modified and committed after the snapshot was taken
  3. 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
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

expected output

ERROR:  could not serialize access due to concurrent update

Fix 1

Retry the transaction on 40001

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

Retry the transaction on 40001
-- 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 When retries are expensive and conflict rate is high.

Use SELECT FOR UPDATE for pessimistic locking
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

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.

Sources
Official documentation ↗

src/backend/storage/lmgr/predicate.c — CheckForSerializationFailure()

Transaction Isolation ↗SSI in PostgreSQL ↗

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors