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.