PostgreSQL "could not serialize access due to concurrent update": Why It Happens and How to Handle It

PostgreSQL "could not serialize access due to concurrent update" means your transaction tried to change a row that another transaction changed and committed after yours had taken its snapshot. PostgreSQL can either let you overwrite a change you never saw or abort you. At Repeatable Read and Serializable it aborts. The fix is almost never to make that error disappear. You make the application retry correctly, and you make collisions rare.

The messages and what they have in common

You will see one of these, usually from an ORM wrapped in its own exception type:

ERROR:  could not serialize access due to concurrent update

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

There is also a sibling, could not serialize access due to concurrent delete, for a row that was deleted underneath you. All of these carry SQLSTATE 40001 (serialization_failure). That code is what your retry logic should match on, not the message text.

When each one appears

• Read Committed (the default). You will not get this error here. If two transactions update the same row, the second one waits; when the first commits, the second re-checks its WHERE clause against the new row version and applies its change to that version. If your application suddenly sees 40001, something switched the isolation level: a framework default, ALTER ROLE ... SET default_transaction_isolation, or an explicit BEGIN ISOLATION LEVEL.
• Repeatable Read. A transaction sees one snapshot from its first query onwards. The documentation says it cannot modify or lock rows changed by other transactions after it began. So UPDATE, DELETE, SELECT ... FOR UPDATE or FOR SHARE on such a row raises "concurrent update" (or "concurrent delete"). The error appears only if the other transaction committed. If it rolled back, yours proceeds.
• Serializable. Everything above applies, plus the read/write dependency check (Serializable Snapshot Isolation). PostgreSQL tracks what each transaction read using predicate locks. When a pattern of reads and writes could produce a result no serial order would give, it cancels one participant. The "Reason code" in DETAIL says at which point it happened. It is useful for PostgreSQL developers and rarely changes what you do.

A minimal Repeatable Read reproduction:

-- session A                                -- session B
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT stock FROM items WHERE id = 7;   -- 10
                                            UPDATE items SET stock = 9 WHERE id = 7;
                                            -- autocommit, done
UPDATE items SET stock = stock - 1 WHERE id = 7;
-- ERROR:  could not serialize access due to concurrent update

Step 1: retry the whole transaction, correctly

The documentation is direct about this: applications using these levels must be prepared to retry. The retry rules are short:

1. Retry the entire transaction from BEGIN, including the application logic that decided what to write. If you re-run only the failed UPDATE with values computed from the old snapshot, you have rebuilt the lost-update bug the error was protecting you from.
2. Match on SQLSTATE. 40001 can be retried unconditionally. 40P01 (deadlock, see our deadlock detected guide) is also worth retrying. Unique violations (23505) and exclusion violations (23P01) may be retried only with more care, because they can be persistent errors.
3. Add a limit and backoff. The docs warn that under high contention many attempts may be needed. Cap the attempts, add jittered backoff, and log every retry so you can see the rate.
4. Do not run external side effects inside the transaction. An email, payment API call or message published before the commit will repeat on every retry. Move such side effects after the commit or use an outbox table.

Step 2: find out why collisions are frequent

-- which isolation level do sessions actually use?
SHOW default_transaction_isolation;
SELECT rolname, setconfig FROM pg_roles WHERE setconfig::text LIKE '%isolation%';

-- under SERIALIZABLE: how many predicate locks are held right now?
SELECT locktype, count(*) FROM pg_locks
WHERE mode = 'SIReadLock' GROUP BY locktype;

-- long transactions widen the conflict window
SELECT pid, now() - xact_start AS xact_age, state, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 10;

Server logs show the failed statement, because log_min_error_statement defaults to error. Group them by statement text and you usually find one or two hot spots: a counter row, a "last updated" column on a parent record, a queue table, a balance.

Step 3: reduce the failure rate

• Keep transactions short. The longer a snapshot lives, the more concurrent commits it can collide with. The docs specifically warn against connections left "idle in transaction"; set idle_in_transaction_session_timeout (and on PostgreSQL 17+ consider transaction_timeout). Our idle in transaction article shows how to find them.
• Remove hot rows. A single row that every transaction updates guarantees conflicts under strict isolation. Split counters into several rows and sum them, or append events instead of updating one record.
• Use the weakest level that is correct. Many workloads are correct at Read Committed with atomic statements such as UPDATE ... SET stock = stock - 1 WHERE stock > 0. Use Repeatable Read or Serializable where you need them, not as a global default.
• For Serializable, follow the documented tuning list: declare transactions READ ONLY when possible; control the number of active connections with a pool; drop explicit locks and SELECT FOR UPDATE that Serializable no longer needs; prefer index scans, since a sequential scan always takes a relation-level predicate lock.
• Watch predicate lock promotion. When the predicate lock table runs short, PostgreSQL combines page-level locks into relation-level ones, and the docs note the failure rate may rise. The knobs are max_pred_locks_per_transaction (default 64, restart required), max_pred_locks_per_relation (default -2, meaning a fraction of the per-transaction value) and max_pred_locks_per_page (default 2).

On large databases

• Long reports and batch jobs. A multi-hour Serializable report touching hundreds of millions of rows holds predicate locks and a snapshot for all that time, which raises conflict chances for everyone else. Run it as SERIALIZABLE READ ONLY DEFERRABLE: according to the documentation it may wait at the start until it gets a safe snapshot, then runs without taking predicate locks and cannot be cancelled by serialization failures.
• Sequential scans on big tables. At Serializable, one seq scan of a large table puts a predicate lock on the whole relation. Any concurrent write to that table can then look like a conflict. Missing indexes therefore cost you aborted transactions, not only time.
• Batches that update many rows. A Repeatable Read job updating millions of rows fails as soon as it hits one row changed by live traffic after it started, and the retry starts from scratch. Process large updates in small committed chunks instead.
• Replicas. Serializable is not available on a hot standby; PostgreSQL rejects it with "cannot use serializable mode in a hot standby" and suggests Repeatable Read. Applications that send the same code to primary and replicas need to take this into account.
• Measure under real concurrency. Serialization failure rates depend on concurrency and data distribution, so they show up only under production-like load on full-size data. Track retries per endpoint as a metric and alert when the rate grows.