Retrying serialization failures

Repeatable Read can reject conflicting updates; Serializable also detects serialization conflicts. Neither substitutes for application constraints. PostgreSQL 18's recovery contract requires a complete transaction retry for 40001, including decisions about SQL and values.

/** Re-run `fn` when it fails with a serialization failure (SQLSTATE 40001). */
export async function withRetry<T>(fn: () => Promise<T>, attempts = 5): Promise<T> {
  for (let attempt = 1; ; attempt++) {
    try {
      return await fn();
    } catch (e: any) {
      if (e.code !== "40001" || attempt === attempts) throw e;
    }
  }
}

The example forces one REPEATABLE READ concurrent-update failure. Its callback rolls back the failed transaction and then starts a fresh one:

let attempt = 0;
await withRetry(async () => {
  attempt++;
  await B`BEGIN ISOLATION LEVEL REPEATABLE READ`;
  const [row] = await B`SELECT balance FROM accounts WHERE id = 1`;

  if (attempt === 1) {
    // Force the conflict once: A commits a deposit between B's read and B's write.
    t.note("B read 100. Before it writes, A commits a conflicting deposit — B's write is now doomed.");
    await A`UPDATE accounts SET balance = balance + 10 WHERE id = 1`;
    const err = await B.fails`UPDATE accounts SET balance = ${row!.balance + 5} WHERE id = 1`;
    eq(err.code, "40001");
    await B`ROLLBACK`;
    throw err; // hand the 40001 to withRetry, exactly as a driver would
  }

  t.note("Attempt 2 is a brand-new transaction: it reads 110 — the state that made attempt 1 impossible.");
  await B`UPDATE accounts SET balance = ${row!.balance + 5} WHERE id = 1`;
  await B`COMMIT`;
});
eq(attempt, 2);

const [final] = await A`SELECT balance FROM accounts WHERE id = 1`;
eq(final!.balance, 115); // A's +10 and B's +5 both applied

B> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

B> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     100 
(1 row)

B read 100. Before it writes, A commits a conflicting deposit — B's write is now doomed.

A> UPDATE accounts SET balance = balance + 10 WHERE id = 1;
UPDATE 1

B> UPDATE accounts SET balance = 105 WHERE id = 1;
ERROR:  40001: could not serialize access due to concurrent update

B> ROLLBACK;
ROLLBACK

B> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

B> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     110 
(1 row)

Attempt 2 is a brand-new transaction: it reads 110 — the state that made attempt 1 impossible.

B> UPDATE accounts SET balance = 115 WHERE id = 1;
UPDATE 1

B> COMMIT;
COMMIT

A> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     115 
(1 row)

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

The assertions require two attempts and a final balance of 115, preserving A's deposit of 10 and B's deposit of 5. This is Demonstrated behavior for one forced conflict; later retries can conflict again or fail for another reason. Eventual success is not guaranteed.

Retry the transaction, not the statement

Start with a fresh BEGIN, reread, and recompute. The shown callback cleans up its expected first failure; withRetry itself does not begin, roll back, or commit transactions. Its default cap is five attempts, without backoff. Other errors and exhaustion propagate. The caller must end any failed block and report a failure it cannot recover from.

The manual also permits retry for deadlocks (40P01), but this helper retries only 40001. No deadlock or exhaustion case is executed here. External effects in a retried callback can repeat, and a database rollback cannot undo them. The idempotency example protects database-local work only; use a separate effect protocol for external work.

Further reading

• PostgreSQL 18: Serialization Failure Handling
• The same lesson on MySQL

The reconstructed support assessment records the exact manual support and execution limits for this lesson.