Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Postgres: could not serialize access due to concurrent update

At REPEATABLE READ and SERIALIZABLE a conflicting write aborts with SQLSTATE 40001. Retry the whole transaction with backoff, then remove the conflict instead of retrying it.

~5 min readSQL
#PostgreSQL#Transactions#Isolation Levels#Retries
3D diagram of two transaction arrows colliding over one glowing row with a cyan retry loop below, headline 40001

Introduction & Quick Summary

Symptom. A transaction fails at commit or on a write, under load only:

code
ERROR: could not serialize access due to concurrent update SQLSTATE: 40001

Through JDBC or an ORM it arrives as org.postgresql.util.PSQLException, psycopg.errors.SerializationFailure, or Django's OperationalError — and often as a 500 on one request in a hundred.

Cause. At REPEATABLE READ and SERIALIZABLE, a transaction works on a snapshot. If it tries to update a row that another transaction committed after that snapshot was taken, PostgreSQL cannot apply the write to a version the transaction never saw, so it aborts with 40001 (serialization_failure) and expects the client to retry. SERIALIZABLE additionally raises the same code for read/write dependency cycles (could not serialize access due to read/write dependencies among transactions).

Fix. Retry the whole transaction on 40001 with backoff. That is not a workaround; it is the contract of snapshot isolation. Then reduce how often it happens: single-statement updates for counters, advisory locks or SELECT FOR UPDATE for read-modify-write, and the lowest isolation level that still gives you the guarantee you need.

Core Mechanics, Signatures & Reproducible Setup

PostgreSQL's REPEATABLE READ and SERIALIZABLE refuse to overwrite a row that changed after the snapshot instead of silently merging, which is the difference from READ COMMITTED — there, the second writer waits and then re-evaluates against the new row version, no error.

A two-session reproduction, no application code:

-- session 1 BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT balance FROM account WHERE id = 1; -- 100 -- session 2 BEGIN; UPDATE account SET balance = balance - 10 WHERE id = 1; COMMIT; -- session 1 UPDATE account SET balance = balance + 5 WHERE id = 1; -- ERROR: could not serialize access due to concurrent update ROLLBACK;

The transaction is now aborted: every further statement returns 25P02 current transaction is aborted, commands ignored until end of transaction block until rollback. That is why a retry has to restart at BEGIN, and why catching the error inside a try/except around a single statement leaves the session unusable.

Confirm the isolation level you are actually running, since drivers and frameworks set it:

SHOW transaction_isolation; -- read committed (server default) SELECT current_setting('transaction_isolation');

Key Comparison Table

Isolation levelConcurrent update by another transactionError code
READ COMMITTED (default)Waits, then re-evaluates on the new rownone, or 40P01 on deadlock
REPEATABLE READAborts: snapshot cannot see the new version40001
SERIALIZABLEAborts on write conflicts and on SSI dependency cycles40001
REPEATABLE READ READ ONLY DEFERRABLENever conflicts, waits for a safe snapshotnone

Ways to reduce the conflict rate, in order of preference:

ApproachConverts 40001 intoUse when
Single-statement UPDATE ... SET n = n + 1Nothing to conflict onCounters, balances, stock decrements
INSERT ... ON CONFLICT DO UPDATEA single atomic statementUpserts keyed by a unique column
pg_advisory_xact_lock(key)A queue on one keyBusiness logic that must serialize per entity
SELECT ... FOR UPDATEA wait, at the cost of lock orderingRead-modify-write inside one transaction
Retry with backoffA later successWhatever remains; always needed
Lower isolation to READ COMMITTEDLoses the snapshot guaranteeReporting reads that tolerate re-reads

Practical Edge Cases, Error Fixes & Deep Dive Code Examples

1. A retry loop that restarts the transaction

The unit of retry is the transaction, not the statement, and the check must use the SQLSTATE rather than the message text:

python
import random, time import psycopg RETRYABLE = {"40001", "40P01"} # serialization_failure, deadlock_detected def run_txn(dsn: str, work, attempts: int = 5): for attempt in range(attempts): try: with psycopg.connect(dsn) as conn: with conn.transaction(): return work(conn) except psycopg.errors.Error as exc: if exc.sqlstate not in RETRYABLE or attempt == attempts - 1: raise delay = min(0.05 * 2 ** attempt, 1.0) * (0.5 + random.random()) time.sleep(delay)

Backoff with jitter matters: a fixed sleep makes every failed request retry in lockstep and reproduces the collision. Five attempts with a one-second cap is enough for ordinary contention.

2. Retrying on the same connection

After 40001 the transaction is aborted; a retry on the same connection must begin with ROLLBACK, and any statement issued before that fails with 25P02. Drivers differ in whether they roll back for you:

python
# Wrong: statement-level retry inside an open transaction try: cur.execute("UPDATE account SET balance = balance + 5 WHERE id = 1") except psycopg.errors.SerializationFailure: cur.execute("UPDATE account SET balance = balance + 5 WHERE id = 1") # 25P02 # Right: abandon the transaction, then reconnect or roll back conn.rollback()

With pooled connections, returning a connection in idle in transaction state also holds a snapshot and blocks vacuum; pool_pre_ping does not detect it.

3. Spring and JPA: the retry has to be outside the transaction

@Retryable on the same method as @Transactional does not retry a transaction that has already been marked rollback-only. Put the retry on the caller:

java
@Service public class TransferService { @Retryable(retryFor = CannotSerializeAccessException.class, maxAttempts = 5, backoff = @Backoff(delay = 50, multiplier = 2, random = true)) public void transfer(long from, long to, BigDecimal amount) { txService.transferInTransaction(from, to, amount); // @Transactional method } }

CannotSerializeAccessException is Spring's translation of SQLSTATE 40001. A self-invocation (this.transferInTransaction(...)) bypasses the proxy and runs without a new transaction, which is the usual reason the retry appears to do nothing.

4. Hot rows: stop conflicting instead of retrying

A counter updated by every request conflicts at snapshot isolation because every transaction reads it first. Do the update as one statement and there is no read to invalidate:

-- fragile under REPEATABLE READ: read, then write UPDATE account SET balance = (SELECT balance FROM account WHERE id = 1) + 5 WHERE id = 1; -- atomic: one statement, no lost update, no 40001 UPDATE account SET balance = balance + 5 WHERE id = 1; -- upsert path INSERT INTO daily_count (day, hits) VALUES (CURRENT_DATE, 1) ON CONFLICT (day) DO UPDATE SET hits = daily_count.hits + 1;

Row-level lock contention still serializes the statements, but waiting is not an error. If the wait itself becomes the bottleneck, shard the counter (day, shard) and sum on read.

5. SELECT ... FOR UPDATE and the deadlock it invites

Locking what you are about to change converts a serialization failure into a wait, which is often what you want for short transactions:

BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT balance FROM account WHERE id = 1 FOR UPDATE; UPDATE account SET balance = balance + 5 WHERE id = 1; COMMIT;

Two transactions locking rows in different orders then deadlock instead, and 40P01 deadlock detected is retryable for exactly the same reason. Lock rows in a deterministic order (ORDER BY id) whenever a transaction touches more than one.

6. Choosing the isolation level deliberately

Many 40001s come from SERIALIZABLE applied to work that does not need it — a dashboard read, a report, an idempotent insert. Downgrading the read path is a legitimate fix:

sql
-- consistent snapshot for a report, without SSI write conflicts BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;

For a long read-only report under SERIALIZABLE, READ ONLY DEFERRABLE waits for a snapshot that cannot conflict at all, trading latency for a guarantee of never failing. Both are worth setting per transaction rather than per database, so the write path keeps its protection.

Common Compilation/Runtime Mistakes & Version Gotchas

  • Catching the error as a generic database error. Retrying every OperationalError turns a syntax error into five attempts, a delay, and the same failure. Match 40001 and 40P01 only.
  • Retrying the statement, not the transaction. The session is aborted; the second attempt fails with 25P02 and the real cause disappears from the logs.
  • Fixed-interval retries. All failed requests retry together, which recreates the conflict. Exponential backoff with jitter is the whole fix.
  • Unbounded retries. Under sustained contention they amplify load; five attempts with a cap, then fail loudly with the original SQLSTATE in the message.
  • REPEATABLE READ on an ORM session that reads once and writes much later. The snapshot ages while the request waits on an external API, so the write always conflicts. Keep the transaction short, or lower the isolation.
  • Confusing 40001 with 40P01. Both are retryable and for different reasons: one is a snapshot conflict, the other is lock ordering. The fix for the second is in the query order, not in the retry count.
  • Migrations under SERIALIZABLE. A DDL-heavy migration that also writes rows conflicts with live traffic; migrations belong in READ COMMITTED with explicit locks.
  • Connection pool sized for the original latency. With retries, the same request can hold a connection twice as long; a pool at the edge of its capacity turns a transient conflict into a timeout.

Related tools. Format the transaction and the conflicting statements with the SQL formatter so the read and the write sit in one view, and check the table's keys in the SQL schema viewer — a retry loop is rarely necessary on rows that are updated through a unique key in a single statement.

Official references

primary documentation

The SQL documentation that defines the behaviour this guide explains.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.