🚨 Postgres Error: 'could not serialize access due to concurrent update' — Cause, Fix, and Troubleshooting Guide

Overview

PostgreSQL raises this error when a transaction running at REPEATABLE READ or SERIALIZABLE isolation tries to update or lock a row that a different transaction has already updated or deleted and committed since your transaction took its snapshot. Rather than silently overwrite the concurrent change, Postgres aborts your transaction with SQLSTATE 40001.

ERROR:  could not serialize access due to concurrent update

This is not a bug and it is not corruption — it is the isolation level doing its job. At READ COMMITTED (the default) Postgres would re-read the updated row and proceed; at the stricter levels it refuses, because letting you continue would violate the snapshot you were promised. The correct response is almost always to roll back and retry the whole transaction, not to “fix” the query.

Symptoms

• Transactions fail with 40001 under load, but the same statements succeed when run alone.
• The failure clusters on a few hot rows (a counter, an inventory balance, an account).
• You recently switched a session or the whole database to REPEATABLE READ or SERIALIZABLE.
• SELECT ... FOR UPDATE on a contended row throws instead of blocking.

SHOW transaction_isolation;

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

If this returns read committed, you will not see this specific error — check for a SET TRANSACTION ISOLATION LEVEL or a pool/ORM default that raises it.

Common Root Causes

1. High isolation level on a write-heavy path

The error can only occur at REPEATABLE READ or SERIALIZABLE. A framework, a default_transaction_isolation setting, or an explicit statement may be raising it cluster-wide or per-session.

SHOW default_transaction_isolation;

 default_transaction_isolation
-------------------------------
 read committed
(1 row)

A value other than read committed here means every transaction starts stricter than you may expect.

2. Concurrent writes to the same hot row

Two transactions read the same row, then both try to update it. The first to commit wins; the second gets 40001.

-- Session A and Session B both run, concurrently, at REPEATABLE READ:
BEGIN ISOLATION LEVEL REPEATABLE READ;
UPDATE customers SET balance = balance - 10 WHERE id = 42;
-- whichever session commits second fails with 40001

3. SELECT … FOR UPDATE contention

Explicit row locks under REPEATABLE READ surface the same conflict: if the target row changed after your snapshot, the lock attempt aborts instead of blocking to completion.

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM customers WHERE id = 42 FOR UPDATE;  -- may raise 40001

4. Long-running transactions widen the window

The longer a transaction holds its snapshot, the more likely a concurrent commit lands on a row it later touches. Slow transactions turn a rare race into a frequent one.

How to diagnose

Step 1: Confirm the isolation level in effect

SHOW transaction_isolation;
SHOW default_transaction_isolation;

If either is repeatable read or serializable, this error is expected under contention. Decide whether that strictness is actually required for the path that is failing.

Step 2: Find the concurrent activity on the hot rows

SELECT pid, usename, state, now() - xact_start AS xact_age,
       left(query, 60) AS current_query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY xact_start
LIMIT 10;

Overlapping transactions touching the same table — especially long xact_age values — are the ones colliding.

Step 3: Identify the hot rows being contended

SELECT id, updated_at
FROM orders
WHERE status = 'processing'
ORDER BY updated_at DESC
LIMIT 20;

A tight cluster of very recent updated_at values on the same handful of ids confirms a write hot spot that will keep producing 40001.

Fixes

Retry the whole transaction on SQLSTATE 40001

This is the primary, expected fix. A serialization failure means “try again on a fresh snapshot.” Wrap the transaction in a retry loop with capped exponential backoff, and retry the entire transaction — re-running a single statement inside the aborted one will only get current transaction is aborted.

import time, psycopg2
from psycopg2 import errorcodes

def run_txn(conn, work, attempts=5):
    for i in range(attempts):
        try:
            with conn:                      # commits on success, rolls back on error
                with conn.cursor() as cur:
                    work(cur)
            return
        except psycopg2.errors.SerializationFailure:  # SQLSTATE 40001
            if i == attempts - 1:
                raise
            time.sleep((2 ** i) * 0.05)     # 50ms, 100ms, 200ms, ...

Keep transactions short

Read, compute, and write in the smallest possible transaction. Do not hold a REPEATABLE READ snapshot open across a network call or user think-time — that is what turns rare conflicts into constant ones.

Use a lower isolation level where it is safe

If the failing path does not actually need a stable multi-statement snapshot, run it at READ COMMITTED, where Postgres re-reads updated rows instead of aborting.

BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE customers SET balance = balance - 10 WHERE id = 42;
COMMIT;

Order locks consistently or update in one statement

Collapse read-then-write into a single atomic UPDATE (e.g., SET balance = balance - 10) so there is no snapshot gap, and always acquire locks on multiple rows in a consistent order to avoid compounding this with a deadlock.

What to watch out for

• Do not retry a single statement — retry the entire transaction from BEGIN; the failed transaction is already aborted.
• Add a retry cap and backoff; unbounded immediate retries on a hot row make the contention worse, not better.
• Distinguish this from could not serialize access due to read/write dependencies among transactions — that message is SERIALIZABLE-only and comes from Postgres’s predicate locking (SSI), whereas this one occurs at both REPEATABLE READ and SERIALIZABLE on a direct row conflict.
• Both are SQLSTATE 40001, so a retry-on-40001 policy correctly handles either.
• Make retried transactions idempotent — a transaction that runs twice must not double-apply its effect.

Related

• Postgres Error: ‘deadlock detected’
• Postgres Error: ‘canceling statement due to lock timeout’
• Postgres Error: ‘canceling statement due to statement timeout’