Retrying serialization failures

Transactions, isolation and locking Senior Python 3.13+ PostgreSQL 17+ Reviewed 31 Oct 2026 By Best Latest Apps

Spot the bug

This retry loop still produces wrong results. Which line is the bug?

import psycopg
from psycopg import IsolationLevel
from psycopg.rows import TupleRow
def transfer(
conn: psycopg.Connection[TupleRow], src: int, dst: int, amount: int
) -> None:
conn.isolation_level = IsolationLevel.SERIALIZABLE
row = conn.execute(
"SELECT balance FROM accounts WHERE id = %s", (src,)
).fetchone()
assert row is not None
for attempt in range(5):
try:
if row[0] >= amount:
conn.execute(
"UPDATE accounts SET balance = balance - %s"
" WHERE id = %s",
(amount, src),
)
conn.execute(
"UPDATE accounts SET balance = balance + %s"
" WHERE id = %s",
(amount, dst),
)
conn.commit()
return
except psycopg.errors.SerializationFailure:
conn.rollback()

Hint 1 of 1

What does a retry need to redo besides the writes?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: line 10 row = conn.execute(

The balance is read once, before the loop. Every retry reuses the stale value, so the decision isn't re-made on fresh data. The read must be inside the retried block. A second, smaller flaw: after 5 failures the loop falls through and returns None silently; re-raise on the last attempt.

30-second answer

Roll back and run the whole transaction again from the start, including its reads, because the decision it made was based on data that's now invalid. Wrap the transaction in a function and retry it a few times with a short randomised backoff. Never retry just the failed statement, and make sure nothing outside the database, like an email or HTTP call, happened inside the attempt.

Practise saying it · 30 s. No recording, just a timer

How do you handle 40001?

Say your answer out loud, then check which of these points you covered:

Roll back
Retry the whole transaction, reads included
Cap attempts, add jitter
No side effects inside

Deep dive

40001 (serialization_failure) and 40P01 (deadlock_detected) are both safe to retry as whole transactions.

With psycopg 3, catch psycopg.errors.SerializationFailure; the connection's transaction is already aborted.

Cap attempts (for example 5) and add jitter so the same transactions don't collide again.

Side effects belong after commit, or in an outbox row written inside the transaction.

A retry wrapper for whole transactions Ran on Python 3.13.5 · mypy --strict

Python 3.13

import random
import time
from collections.abc import Callable
from typing import TypeVar

import psycopg
from psycopg import IsolationLevel
from psycopg.rows import TupleRow

T = TypeVar("T")
Conn = psycopg.Connection[TupleRow]
RETRYABLE = (
    psycopg.errors.SerializationFailure,
    psycopg.errors.DeadlockDetected,
)


def run_serializable(work: Callable[[Conn], T], attempts: int = 5) -> T:
    with psycopg.connect() as conn:
        conn.isolation_level = IsolationLevel.SERIALIZABLE
        for attempt in range(1, attempts + 1):
            try:
                result = work(
                    conn
                )  # all reads and writes happen in here
                conn.commit()
                return result
            except RETRYABLE:
                conn.rollback()
                if attempt == attempts:
                    raise
                time.sleep(random.uniform(0, 0.01 * 2**attempt))
    raise AssertionError("unreachable")


def count_tables(conn: Conn) -> int:
    row = conn.execute(
        "SELECT count(*) FROM pg_class WHERE relname = 'pg_class'"
    ).fetchone()
    assert row is not None
    n: int = row[0]
    return n


print(run_serializable(count_tables))

Common mistakes

Retrying only the statement that failed.
Sending a confirmation email inside the transaction body that gets retried.
Retrying forever and hiding a hot-spot design problem.

Senior take & follow-ups

Say 'retry the whole unit of work', show the loop, and keep side effects outside it.

Follow-up questions

Think of your answer first, then open the hint.

Which other errors are safe to retry?
Show hint
40P01 deadlocks, and connection errors when you know the commit didn't happen.
What if COMMIT itself fails with a network error?
Show hint
You don't know if it committed; make the work idempotent.

Suggest a fix if something here is wrong or out of date.