Retry Transactions on Serialization Failures

Retry the whole unit of work on SQLSTATE 40001 and 40P01 with bounded backoff, and never retry errors that will fail again.

~30 minpostgresqltransactionsresilience

Task

Turn a transient serialization failure into a bounded, observable retry instead of a spurious 500 response to the user.

When to use this

Two concurrent transfers read the same balance under SERIALIZABLE. One commit fails with a serialization error, and the user sees an error for a request that would succeed on a second attempt.

Prerequisites

PostgreSQL 13 or newer
A driver that exposes SQLSTATE codes on errors
A transaction isolation level you can set per transaction

Steps

1. Keep the entire unit of work in one function that owns BEGIN, COMMIT, and ROLLBACK.

async function transferOnce(pool, from, to, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
    await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, from]);
    await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, to]);
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Expected output: Each call runs in exactly one transaction and releases the client on every path.

2. Wrap the unit of work in a retry that only reacts to serialization failures and deadlocks.

const RETRYABLE = new Set(['40001', '40P01']);
const sleep = (ms) => new Promise((resolve) => setTimeout(resolve, ms));

async function withRetry(work, attempts = 4) {
  for (let attempt = 1; ; attempt += 1) {
    try {
      return await work();
    } catch (err) {
      if (!RETRYABLE.has(err.code) || attempt >= attempts) throw err;
      const backoff = 2 ** attempt * 10 + Math.random() * 25;
      await sleep(backoff);
    }
  }
}

Expected output: Transient conflicts retry up to four times with jittered exponential delays; other errors surface immediately.

3. Move external side effects out of the transaction or into an outbox row committed with it.

await client.query(
  'INSERT INTO outbox (topic, payload) VALUES ($1, $2)',
  ['transfer.completed', JSON.stringify({ from, to, amount })],
);
// the relay sends the email after COMMIT succeeds

Expected output: A retried transaction cannot send the same email or charge twice, because delivery happens only after commit.

4. Run a concurrency test that forces overlapping writes.

node --test test/serializable-retry.test.js

Expected output: All 8 transfers commit and at least one retry counter increments; no request returns 40001 to the caller.

Troubleshooting

Problem

All attempts fail with 40001

Solution

Contention is persistent, not transient. Shorten the transaction, batch updates, or lock rows in a fixed order; retries cannot fix a hot row.

Problem

A retried transfer sends two confirmation emails

Solution

The email was inside the transaction. Move it after COMMIT or behind the outbox so it runs once per committed transaction.

Problem

err.code is undefined in the catch block

Solution

The driver may wrap the database error. Read err.code ?? err.cause?.code and log the full chain before deciding whether to retry.

Limitations

Retries add latency and load during contention; cap attempts and count retries per endpoint.
Only serialization failures and deadlocks are safe to retry; unique violations and validation errors need different handling.
The unit of work must be free of non-idempotent external side effects for retries to be safe.

Serialization failures are normal

Under SERIALIZABLE (and sometimes REPEATABLE READ), PostgreSQL may abort a transaction to preserve a result equivalent to serial execution. SQLSTATE 40001 is not a bug; it is an instruction to run the transaction again. The same applies to deadlocks (40P01). Treating either as a 500 is a design error, because a retry usually succeeds and the user never notices.

Retry the whole unit, not the statement

A transaction that failed at commit has no usable state. Every statement it executed must be replayed, including reads that influenced the decision. That is why the retry wrapper belongs around the function that opens and closes the transaction, not around a single UPDATE. Reusing the same client is fine, but a fresh transaction is mandatory; never continue after a rollback with the belief that earlier reads still hold.

Bound the retries

Use a small attempt budget, exponential backoff, and jitter so a burst of conflicting writers does not retry in lockstep. Four attempts with tens of milliseconds is typical for OLTP. Export a retry counter per endpoint: a slow climb means contention is rising and the transaction should be shortened. An exhausted retry budget is a real failure and should surface as a 503, not a silent success.

Keep side effects outside

If the transaction sends an email, charges a card, or calls a partner API, a retry repeats that effect. The standard fix is an outbox row written inside the same transaction and relayed after commit. For calls that must happen immediately, use an idempotency key so the downstream system collapses duplicates.

const RETRYABLE = new Set(['40001', '40P01']);
async function withRetry(work, attempts = 4) {
  for (let attempt = 1; ; attempt += 1) {
    try { return await work(); }
    catch (err) {
      if (!RETRYABLE.has(err.code) || attempt >= attempts) throw err;
      await sleep(2 ** attempt * 10 + Math.random() * 25);
    }
  }
}

Test the conflict, not just the happy path

A unit test that throws { code: '40001' } twice proves the loop and the budget. An integration test with two overlapping serializable transactions proves the database actually produces the conflict and that both requests eventually succeed. Run the integration test against the same isolation level and schema as production; retry logic that is only exercised on READ COMMITTED will not protect you.