could not serialize access due to concurrent update

What it means

PostgreSQL’s stricter isolation levels promise that your transaction works from one consistent view of the data. REPEATABLE READ promises you won’t see other transactions’ changes part-way through; SERIALIZABLE also promises the result is the same as if the transactions had run one after another. When a concurrent transaction makes that promise impossible to keep, PostgreSQL cancels yours rather than let it act on stale data.

It isn’t a bug or a broken database. The documentation is direct about it: applications using these levels must be ready to retry. The SQLSTATE is always 40001 (serialization_failure); the wording varies:

The transaction is aborted. Until you roll back, every statement in it fails with current transaction is aborted.

Common causes

1. Two transactions change the same row: a counter, a balance, a stock level, a “last seen” time. Under REPEATABLE READ the second one to write fails.
2. Read, decide, then write. At SERIALIZABLE, two transactions read the same rows, each writes something based on what it read, and together they break a rule neither broke alone.
3. The isolation level isn’t the one you think. A framework, ORM or default_transaction_isolation setting makes every transaction REPEATABLE READ or SERIALIZABLE.
4. Long transactions, which give others more time to change what they read.

How to fix it

Retry the whole transaction

Roll back, then run the transaction again from BEGIN, including the reads and the code that decides what to write. Retrying only the failed statement doesn’t work, because the decision was based on old data. A sketch with node-postgres:

async function inTransaction(pool, work, attempts = 5) {
  for (let attempt = 1; ; attempt++) {
    const client = await pool.connect();
    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
      const result = await work(client);
      await client.query('COMMIT');
      return result;
    } catch (err) {
      await client.query('ROLLBACK');
      const retryable = err.code === '40001' || err.code === '40P01';
      if (!retryable || attempt === attempts) throw err;
      await new Promise((r) => setTimeout(r, Math.random() * 50 * attempt));
    } finally {
      client.release();
    }
  }
}

The same loop handles deadlocks (40P01). A failure can arrive at COMMIT too, so the commit belongs inside the retried block.

Check which isolation level you’re using

SHOW transaction_isolation;
SHOW default_transaction_isolation;

PostgreSQL’s default is READ COMMITTED. There, an UPDATE that finds a row changed by a committed transaction re-reads the new version and carries on instead of failing. That’s often what you want for counters and balances, written as one statement:

UPDATE accounts SET balance = balance - 10 WHERE id = 1;

But READ COMMITTED doesn’t protect read-then-write rules. In the reproduction below, the same two transactions both commit at READ COMMITTED and leave nobody on call. If your code relies on such a rule, keep SERIALIZABLE and retry.

Make failures rarer

• Keep transactions short, and do slow work outside them.
• For long read-only reports at SERIALIZABLE, use BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE. It may wait briefly for a safe snapshot, then can’t be cancelled by a serialisation failure.
• At SERIALIZABLE, give your queries indexes. A query that scans a whole table watches the whole table for conflicting writes.

Reproduce it

Repeatable read, concurrent update. On PostgreSQL 18.6, session A:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM seo_pgconn.accounts WHERE id = 1;   -- 100
SELECT pg_sleep(2);
UPDATE seo_pgconn.accounts SET balance = balance - 10 WHERE id = 1;

While it sleeps, session B runs and commits UPDATE seo_pgconn.accounts SET balance = balance + 5 WHERE id = 1;. Session A’s update then fails:

ERROR:  could not serialize access due to concurrent update

If session B deletes the row instead, the message ends due to concurrent delete.

Serializable, write skew. A table says who’s on call, and the rule is that at least one doctor must be. Both sessions run, a second apart:

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM seo_pgconn.on_call WHERE on_call;   -- 2, so it’s safe to leave
UPDATE seo_pgconn.on_call SET on_call = false WHERE doctor = 'alice';   -- 'bob' in session B
SELECT pg_sleep(2);
COMMIT;

Session A commits. Session B’s COMMIT fails (here with \set VERBOSITY verbose, trimmed):

ERROR:  40001: 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.

Bob stays on call. Run the same two transactions at READ COMMITTED and both commit:

 doctor | on_call 
--------+---------
 alice  | f
 bob    | f
(2 rows)

In Inlet

The query editor shows the server’s error where it reports it, and supports transactions with manual commit, so you can roll back and run the transaction again. With your own Anthropic API key, Ask Claude can explain the failed statement and suggest a fix.