PostgreSQL 40001: Fix Serialization Failure in REPEATABLE READ

You're running a transaction at REPEATABLE READ (or SERIALIZABLE) and Postgres throws:

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

Don't panic. This isn't corruption. It's Postgres enforcing snapshot isolation: two transactions tried to write the same row, and the snapshot your transaction started with is no longer valid. The database picks a winner and aborts you.

I've seen this fire in production on Black Friday traffic, on batch jobs that overlap with user writes, and on long-running migration scripts that touch hot rows. The trigger is basically always the same pattern — a read, some app logic, then a write, all inside one transaction.

The fix depends on why you're in an aborted state. Three causes cover about 95% of cases.

Cause 1: Classic read-modify-write race (the most common)

You've seen this code a hundred times. Two sessions, same row, overlapping transactions.

-- Session A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 42;   -- reads 100
-- app logic decides: balance = balance - 30
UPDATE accounts SET balance = 70 WHERE id = 42;

-- Session B (started before A committed)
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 42;   -- still sees 100 (snapshot)
UPDATE accounts SET balance = 90 WHERE id = 42;
-- ERROR: could not serialize access due to concurrent update

Session B's snapshot is stale. Postgres refuses to apply the write because it can't reconcile B's view with A's commit.

Fix: lock the row you're about to modify

Add FOR UPDATE to the SELECT. That takes a row-level lock at read time, so session B blocks until A commits, then re-reads the fresh value.

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
-- blocks here if another tx holds the lock, then returns fresh data
UPDATE accounts SET balance = balance - 30 WHERE id = 42;
COMMIT;

Yes, you lose some concurrency. That's the trade-off. For hot rows (counters, balances, inventory), it's the right one.

Fix alt: atomic UPDATE, no read needed

If your logic is arithmetic, skip the SELECT entirely:

UPDATE accounts SET balance = balance - 30 WHERE id = 42 AND balance >= 30;

That single statement is atomic. No snapshot conflict possible. Check rowcount to know if it succeeded. I use this for inventory decrements everywhere.

Cause 2: SERIALIZABLE isolation doing what it's supposed to

If you set default_transaction_isolation = 'serializable' in postgresql.conf, you signed up for this. SERIALIZABLE uses Serializable Snapshot Isolation (SSI) and will abort transactions that couldn't possibly be serialized. Postgres 9.1+ has this built in — it's not the old lock-based mess.

The error is not a bug. It's the isolation level telling you the truth: your transaction can't be linearized against a concurrent one.

The trigger shows up in write skew scenarios. Classic example: two doctors on call, both check "is another doctor on call?", both see yes, both go off call. Each transaction is fine in isolation. Together they violate the invariant.

Fix: retry. That's not a cop-out.

Under SERIALIZABLE, you're expected to catch 40001 and retry the whole transaction. Postgres does the detection; your app does the retry. Write a bounded loop:

for attempt in range(5):
    try:
        with conn.transaction(isolation='serializable'):
            do_work(conn)
        break
    except SerializationFailure:
        if attempt == 4:
            raise
        time.sleep(0.05 * (2 ** attempt))  # exponential backoff with jitter

Keep the transaction short. If your retry loop can't succeed in a few tries, your transaction is holding locks or taking too long.

Fix alt: drop to REPEATABLE READ if you don't need true serializability

Half the teams I audit enable SERIALIZABLE because it sounds safer, then spend weeks fighting 40001. If your writes are on distinct rows or you already use FOR UPDATE, REPEATABLE READ gives you snapshot consistency without SSI aborts on read-only conflicts.

Check current setting:

SHOW default_transaction_isolation;

Cause 3: Long-running transactions touching hot rows

This one bites batch jobs and admin scripts. A report that runs for six minutes, then updates an audit table that user sessions also write to. The report's snapshot from minute zero is ancient by the time it writes. Every update conflicts.

Watch for it with:

SELECT pid, now() - xact_start AS age, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY age DESC;

Anything over a few seconds in REPEATABLE READ is a suspect.

Fix: split the transaction

Read in one transaction, compute in app memory, write in a second short transaction. Each write transaction starts fresh with a current snapshot.

-- tx 1: read only, can run long
SELECT * FROM big_table WHERE ...;

-- app computes results

-- tx 2: write, short and sweet
BEGIN;
INSERT INTO audit_results ...;
COMMIT;

Fix alt: use a lower isolation for the report

Read-committed reports don't get 40001. They just see committed data as of each statement. For most dashboards that's fine and it's what I'd pick unless the report needs a consistent point-in-time view.

What NOT to do

• Don't just swallow the error. If you catch 40001 and continue, you've silently dropped the write. Users will notice missing orders.
• Don't bump the timeout. statement_timeout doesn't touch snapshot conflicts. It just delays the failure.
• Don't disable MVCC or switch to READ UNCOMMITTED. Postgres treats READ UNCOMMITTED as READ COMMITTED anyway. It won't help.
• Don't set serializable globally "just in case." It's a per-workload decision, not a default.

Quick reference

The bottom line: 40001 is Postgres telling you your snapshot is stale. Either lock earlier, retry, or rethink the transaction boundary. Retrying without understanding why is how you end up with duplicate charges and missing inventory.