Fix Postgres 'Could Not Serialize Access' (40001)

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."

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.

const sleep = (ms) => new Promise((resolve) => setTimeout(resolve, ms))
 
async function withSerializableRetry(pool, run, maxRetries = 5) {
  for (let attempt = 0; ; attempt++) {
    const client = await pool.connect()   // one client for the whole transaction
    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE')
      const result = await run(client)    // re-reads + re-computes each attempt
      await client.query('COMMIT')
      return result
    } catch (err) {
      await client.query('ROLLBACK')
      if (err.code === '40001' && attempt < maxRetries) {
        await sleep(2 ** attempt * 20 + Math.random() * 20)  // backoff + jitter
        continue
      }
      throw err
    } finally {
      client.release()
    }
  }
}

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.

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:

• PostgREST, which serves rpc(), runs each request at READ COMMITTED by default; a function only reaches the higher levels if it (or the calling role) sets default_transaction_isolation, for example SET default_transaction_isolation TO 'serializable' in its definition (PostgREST transactions reference). Such a function, or any explicit BEGIN ISOLATION LEVEL ... from a direct Postgres connection, can raise 40001.
• 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.

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

An alternative to raising isolation level: advisory locks#

Retrying under REPEATABLE READ or SERIALIZABLE is the correct fix once you've chosen one of those isolation levels — but sometimes the actual goal is narrower: only one transaction at a time should touch a specific row (a counter increment, a seat reservation, a queue slot), and everything else in the transaction is fine at the default READ COMMITTED. Raising the whole transaction's isolation level just to protect one row means every other statement in it now also has to be written to tolerate 40001, even where no conflict was ever possible.

Postgres's advisory locks solve exactly this narrower case, without changing isolation level at all:

BEGIN;
SELECT pg_advisory_xact_lock(hashtext('seat:' || seat_id::text));
-- read + decide + update the seat row, still under READ COMMITTED
COMMIT;  -- lock releases automatically at transaction end

pg_advisory_xact_lock takes a bigint key (hence hashtext() to fold an arbitrary string into one), blocks any other transaction requesting the same key until this transaction ends, and requires no schema changes — it's not tied to a row, a table, or a constraint, purely an application-defined lock ID. Unlike SELECT ... FOR UPDATE, it doesn't need the row to exist yet, which makes it useful for serializing access to a slot before the row backing it has been inserted.

The trade-off is real: advisory locks are cooperative. Every code path that touches the guarded resource has to remember to take the same lock key, and nothing in the schema enforces that — miss one call site and it silently bypasses the protection. SERIALIZABLE's guarantee comes from Postgres examining actual read/write dependencies regardless of what the application code remembers to do, which is why it remains the right tool when correctness has to hold even against code you don't control. Advisory locks are the pragmatic middle ground for a single well-known hot spot; SERIALIZABLE (with the retry loop above) is the answer when the conflicting writes come from a query pattern you can't fully enumerate in advance, or when enforcing "no duplicate row" is really a uniqueness question — in which case a constraint and ON CONFLICT, covered in Postgres INSERT If Not Exists: Fix Duplicate Key Violations, solves it without any locking at all.

Further reading: Debugging Supabase RLS Issues, Fix Slow Supabase Queries, Supabase Postgres Functions & Triggers Guide, Zero-Downtime Postgres Migrations on Supabase, Select the First Row per Group in Supabase Postgres, Fix Drizzle Rollback: let the exception escape, Fix Foreign Key Constraint Violation in Supabase (23503).