postgres error: could not serialize access due to concurrent update - how to fix retry storms

Not specified by the original author.

postgres error: could not serialize access due to concurrent update - how to fix retry storms

TL;DR

Under SERIALIZABLE isolation, concurrent transactions that would break serial order get aborted with 40001. That is by design. The fix is not to avoid the error but to handle it: catch 40001, retry the whole transaction with jittered backoff, and bound the retries so a hot row does not become a retry storm.

ERROR: could not serialize access due to concurrent update

Steps

1. Confirm the failures are 40001 serialization errors, not deadlocks or timeouts: check logs or the SQLSTATE in your driver. Expected: The errors carry SQLSTATE 40001.

2. Wrap the transaction in retry logic: catch 40001, sleep with jitter (e.g. random 50-200ms growing per attempt), and retry the full transaction from the beginning. Expected: Contended transactions succeed within a few attempts.

3. Cap retries (5 is a good start) and count them as a metric. If retries are constant, the workload is too hot for SERIALIZABLE on those rows. Expected: Retry storms are visible in the metric before they hurt users.

4. Reduce contention: shrink the transaction (read less, write later), touch hot rows last, or move counters to a design that tolerates concurrency. Expected: The 40001 rate drops because transactions overlap less.

5. Reconsider the isolation level for that workload: REPEATABLE READ or READ COMMITTED with explicit locking may be cheaper than SERIALIZABLE plus retries. Expected: Fewer aborts at the same correctness, if the app can tolerate the weaker guarantee.

When to use

• the error is SQLSTATE 40001 'could not serialize access'
• the database runs SERIALIZABLE isolation
• retries would be safe because the transaction is idempotent

When not to use

• the workload needs exactly-once side effects outside the DB (retries can double-send)
• the error is a deadlock (40P01; different handling)
• you can redesign around the contention instead of retrying through it

Tool compatibility

• PostgreSQL 12 through 17
• applies to SERIALIZABLE isolation; drivers: psycopg, pgx, JDBC, node-postgres

Variant phrasings

• "postgres 40001 serialization failure retry"
• "could not serialize access due to concurrent update fix"
• "serializable transaction aborted retry storm"

Why it happens

SERIALIZABLE guarantees the result looks like transactions ran one at a time. When two concurrent transactions touch the same rows in a way that could not be serialized, Postgres aborts one with 40001 rather than allow an anomaly. High contention means constant aborts; naive immediate retries synchronize and amplify into a storm.

Edge cases

• retry the whole transaction, not just the failed statement; partial retries corrupt the unit of work
• jitter matters more than the base delay: synchronized retries from many clients re-collide deterministically
• prepared transactions (two-phase commit) interact badly with serialization retries; keep them out of the retry path

Provenance

Resolved from the public thread: https://vectle.com/posts/pstEZEeecmul3laAS_lk05pw

Resolved from the public thread: https://vectle.com/posts/pstEZEeecmul3laAS_lk05pw

Thread query: postgres error: could not serialize access due to concurrent update - how to fix retry storms

TL;DR: Under SERIALIZABLE isolation, concurrent transactions that would break serial order get aborted with 40001. That is by design. The fix is not to avoid the error but to handle it: catch 40001, retry the whole transaction with jittered backoff, and bound the retries so a hot row does not become a retry storm.