Fix Postgres 'Could Not Serialize Access' (40001)

#postgres #supabase #transactions #troubleshooting

A transaction that worked in testing throws under load:

ERROR:  could not serialize access due to concurrent update

Or, under SERIALIZABLE:

ERROR:  could not serialize access due to read/write dependencies among transactions

Both carry SQLSTATE 40001 (serialization_failure). This is not a bug — it's PostgreSQL deliberately aborting one transaction to prevent a data-correctness anomaly. The Postgres docs are blunt about the fix, and it's not "lower the isolation level and hope."

{ name: "PostgreSQL", version: "REPEATABLE READ / SERIALIZABLE" },
{ name: "Supabase", version: "Postgres functions / txns" },
]} />

Why Postgres throws it

Under REPEATABLE READ, a transaction only sees data committed before it began. If it tries to update a row that a concurrent transaction already committed a change to, Postgres can't reconcile that safely — so (from the docs) "the repeatable read transaction will be rolled back with the message could not serialize access due to concurrent update." This is first-updater-wins.

Under SERIALIZABLE, Postgres goes further with predicate locks (SSI) to detect read/write dependency cycles — anomalies like write skew that simple row conflicts miss — and aborts a transaction in the dangerous set with the "read/write dependencies" variant.

In both cases the engine sacrifices one transaction to preserve the illusion of serial execution. The error is correctness working as designed.

The fix the docs mandate: retry

The PostgreSQL "Serialization Failure Handling" page states it directly:

"applications using these levels must be prepared to retry transactions that fail due to serialization errors."

And critically — you must retry the whole transaction:

"It is important to retry the complete transaction, including all logic that decides which SQL to issue and/or which values to use. Therefore, PostgreSQL does not offer an automatic retry facility, since it cannot do so with any guarantee of correctness."

So a retry loop must re-read, re-decide, and re-write every attempt — not replay the failed statement.

This pattern is illustrative (the docs intentionally ship no code) — the shape is what matters: re-run everything inside the loop, with exponential backoff and jitter.

async function withSerializableRetry(run, maxRetries = 5) {
  for (let attempt = 0; ; attempt++) {
    try {
      await db.query('BEGIN ISOLATION LEVEL SERIALIZABLE')
      const result = await run(db)        // re-reads + re-computes each attempt
      await db.query('COMMIT')
      return result
    } catch (err) {
      await db.query('ROLLBACK')
      if (err.code === '40001' && attempt < maxRetries) {
        await sleep(2 ** attempt * 20 + Math.random() * 20)  // backoff + jitter
        continue
      }
      throw err
    }
  }
}

The docs note 40P01 (deadlock_detected) is "also advisable to retry," and in some cases 23505 (unique_violation) and 23P01 (exclusion_violation).

When READ COMMITTED is the right answer instead

READ COMMITTED is the Postgres default and does not throw 40001 for ordinary write conflicts — it re-evaluates against the latest committed row rather than aborting, so no retry loop is needed. People raise the level to eliminate anomalies (non-repeatable reads, lost updates, write skew) that READ COMMITTED permits — and the cost of that choice is handling 40001.

wrong="Catch 40001 and swallow it (or just log) — the user's operation silently never happened."
right="Catch 40001 and retry the full transaction with backoff; only surface an error after maxRetries."
/>

Reduce conflicts so you retry less

Keep transactions short. The docs' SERIALIZABLE performance guidance: "Don't put more into a single transaction than needed for integrity purposes," declare transactions READ ONLY when possible, and "don't leave connections dangling 'idle in transaction' longer than necessary" (see idle_in_transaction_session_timeout).
SELECT ... FOR UPDATE pessimistically locks rows to serialize access — though SERIALIZABLE can replace explicit locks where its automatic protection suffices. Balance against blocking.
Use a connection pool and keep work outside the transaction boundary.

How this surfaces in Supabase

Supabase runs standard PostgreSQL, so the behavior is identical wherever a transaction runs at REPEATABLE READ or SERIALIZABLE:

A Postgres function (plpgsql) called via rpc(), or any explicit BEGIN ... COMMIT, will raise 40001 under those levels.
The Supabase client surfaces it as a Postgres error — check error.code === '40001'.
The retry loop belongs in application code (or an edge function / queue), because the full transaction — including the decision logic — must be replayed, which a single RPC call can't do for you.

You're on READ COMMITTED and seeing 40001 anyway — then something explicitly set a higher isolation level (a BEGIN ISOLATION LEVEL ..., a function, or a session default); find and reconsider it.
The "right" fix is sometimes to reduce contention (shorter txns, better locking), not to crank up retries — a retry storm on a hot row just moves the bottleneck.

Official references: Transaction Isolation, Serialization Failure Handling, Error Codes.

Frequently Asked Questions

What does "could not serialize access due to concurrent update" mean?

It's SQLSTATE 40001. Under REPEATABLE READ or SERIALIZABLE, Postgres detected a conflict that would break correctness and rolled one transaction back. It's the database protecting your data — your app must retry.

How do I fix a 40001 serialization failure?

Retry the whole transaction (re-read, re-decide, re-write) with exponential backoff. The docs say apps using these levels must be prepared to retry, and that Postgres can't retry automatically with any correctness guarantee.

Can I just avoid 40001 with READ COMMITTED?

READ COMMITTED (the default) doesn't raise 40001 for ordinary write conflicts — but you lose the anomaly protection of the higher levels. Drop down only if you don't need those guarantees.

Originally published at https://www.iloveblogs.blog