Retrying serialization failures
Repeatable Read and Serializable keep your data consistent by rejecting transactions they can't fit into a consistent story — SQLSTATE 40001. The manual is blunt about whose job the recovery is: "Applications using this level must be prepared to retry transactions due to serialization failures."
So every application running above READ COMMITTED needs one small piece of infrastructure — a retry loop:
/** 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;
}
}
}And here it is earning its keep. The scenario forces a conflict on the first attempt (A commits a deposit between B's read and B's write), hands the 40001 to withRetry, and proves the second attempt succeeds:
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 appliedB> 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.4 · Run it yourself · Scenario source
Attempt 1 computed 100 + 5 and died; attempt 2 read the world as it actually was — 110 — and wrote 115. Nothing was lost: A's +10 and B's +5 both applied. The error was never a failure, only PostgreSQL saying "not in this order — try again."
Retry the transaction, not the statement
The retry must restart from BEGIN, re-reading everything — the manual: "It is important to retry the complete transaction, including all logic that decides which SQL to issue and/or which values to use." Re-running only the failed UPDATE would write the stale 105 — the exact bug isolation just prevented. That's also why "PostgreSQL does not offer an automatic retry facility, since it cannot do so with any guarantee of correctness": only the application knows where its transaction's logic begins.
The shape of the fix is small. Treat 40001 as transient by design, not as an error to page anyone about — the same transaction, re-run against the new state, succeeds. Retry from the top with a fresh BEGIN, fresh reads, and recomputed values, and cap the attempts so a genuinely stuck transaction fails loudly rather than spinning forever. Deadlocks (40P01) earn the same reflex: one of the two transactions is rolled back precisely so it can be retried. One caveat lives in the loop body: anything non-transactional inside it, an email or an HTTP call, runs once per attempt, so move it out or make it idempotent.