Fix: ERROR: could not serialize access due to concurrent update

This error is PostgreSQL doing exactly what you asked it to do. Under REPEATABLE READ or SERIALIZABLE isolation, a transaction must behave as if it ran alone. When another transaction commits a change to a row your transaction read or is trying to update, PostgreSQL cannot honor that guarantee anymore, so it aborts your transaction with SQLSTATE 40001. The fix is almost never a server setting. It is catching the error and retrying the transaction.

ERROR: could not serialize access due to concurrent update

What this error means

At READ COMMITTED, PostgreSQL quietly re-reads the latest committed version of a row and carries on. At REPEATABLE READ and SERIALIZABLE, it must not do that, because your transaction has already made decisions based on the snapshot it started with. So when an UPDATE or DELETE targets a row that a concurrent transaction has changed and committed since your snapshot began, PostgreSQL aborts your transaction instead of producing a result no serial ordering could explain. The whole transaction rolls back, nothing was partially applied, and it is safe (and expected) to run it again from the top.

Common causes

• Two transactions updating the same row at the same time under REPEATABLE READ or SERIALIZABLE, such as counters, balances, or inventory rows.
• A hot row that many requests contend for: a single account, a single stock level, a shared settings row.
• Long running transactions, which widen the window in which someone else can commit a conflicting change.
• Batch jobs or backfills sweeping tables that live traffic is updating concurrently.
• An ORM or pooler that set the isolation level to SERIALIZABLE without the application having any retry handling.
• Retry logic that reruns only the failed statement instead of the whole transaction, which cannot succeed because the transaction is already aborted.

How to fix it

STABILIZE first. If a critical job is failing, rerun it during a quiet window or pause the conflicting workload. Serialization failures leave no partial state behind, so rerunning is safe.

1. DIAGNOSE: confirm the isolation level your sessions actually use, since drivers and poolers sometimes set it for you.

SHOW default_transaction_isolation;
SELECT pid, query, state FROM pg_stat_activity WHERE state != 'idle';

2. DIAGNOSE: find what is colliding. Enable logging briefly if the failing statement is unknown, then look for the pairs of queries hitting the same tables.

grep -B2 "could not serialize" /var/log/postgresql/postgresql-*.log | tail -40

3. FIX the application: wrap the transaction in a retry loop keyed on SQLSTATE 40001. Retry the entire transaction, not just the statement.

import psycopg2, time, random

for attempt in range(5):
    try:
        with conn:
            with conn.cursor() as cur:
                cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amt, src))
                cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amt, dst))
        break
    except psycopg2.errors.SerializationFailure:
        conn.rollback()
        time.sleep(random.uniform(0.05, 0.2) * (attempt + 1))

4. FIX the contention where retries are not enough. Keep transactions short, and take the row lock up front when you know you will update it, so conflicts become brief waits instead of aborts:

BEGIN;
SELECT * FROM inventory WHERE sku = 'A100' FOR UPDATE;
UPDATE inventory SET qty = qty - 1 WHERE sku = 'A100';
COMMIT;

5. FIX hot rows structurally if one row absorbs most writes: split a global counter into shards that are summed on read, or move high frequency increments to a queue that applies them in batches.

How to prevent it

• Treat SQLSTATE 40001 as retryable everywhere you use REPEATABLE READ or SERIALIZABLE; bake the retry loop into your transaction helper so no code path forgets it.
• Keep transactions short and do not hold them open across network calls or user think time.
• Schedule bulk backfills and sweeps away from peak write traffic, and batch them in small chunks.
• Use SELECT ... FOR UPDATE when a read is always followed by a write to the same row.
• Avoid designing hot single rows; shard counters and aggregate on read.
• Monitor the rate of serialization failures so a slow climb shows up before users notice retries exhausting.

When to call a senior engineer

Call for help when retries are exhausting under normal load, when you cannot identify which transactions are colliding, or when you are unsure whether your workload actually needs SERIALIZABLE or could run correctly at a weaker level with explicit locking. Erzon engineers can map the conflict graph in your workload, choose the right isolation and locking strategy per transaction, and restructure the hot paths so the failures drop to a level retries absorb silently.