Skip to content

Retrying Database Serialization Failures

PostgreSQL's SERIALIZABLE isolation prevents anomalies by aborting transactions that would conflict, with SQLSTATE 40001, and expects the application to retry them. Under contention the abort rate is high, so the retry loop is not an edge case — it is the normal path. Measured on Python 3.14 with asyncpg 0.31 against PostgreSQL 17, running 2,000 transfers between random pairs of accounts with 20 concurrent transactions: with 10 accounts and no retries, only 366 committed — 1,587 serialization failures and 47 deadlocks, in 24.5 s. Retrying the whole transaction with jittered backoff committed 1,985 in 15.0 s; half of those needed more than one attempt and 15 gave up after 10. With 1,000 accounts, 842–951 of 2,000 still failed without retries — serializable mode tracks reads coarsely enough that different rows conflict — and retries committed all 2,000 in 0.52–0.90 s. The same transfers under READ COMMITTED with row locks taken in a fixed order committed all 2,000 with no errors: in 1.37 s on 10 accounts and 0.35 s on 1,000. This guide builds the retry loop and shows when to avoid needing it.

Prerequisites

1. Measure the abort rate

A transfer reads two balances and writes both back, inside a serializable transaction:

async def transfer(conn, a: int, b: int, amount: int):
    async with conn.transaction(isolation="serializable"):
        bal_a = await conn.fetchval("SELECT bal FROM acc WHERE id = $1", a)
        bal_b = await conn.fetchval("SELECT bal FROM acc WHERE id = $1", b)
        await conn.execute("UPDATE acc SET bal = $2 WHERE id = $1", a, bal_a - amount)
        await conn.execute("UPDATE acc SET bal = $2 WHERE id = $1", b, bal_b + amount)

Measured with 2,000 transfers, 20 at a time: across 10 accounts, 366 committed, 1,587 raised asyncpg.exceptions.SerializationError and 47 raised DeadlockDetectedError; deadlocks wait for PostgreSQL's deadlock_timeout before being broken, which is why the run took 24.5 s. Across 1,000 accounts, 1,049–1,158 committed and the rest failed, even though most pairs did not share an account: serializable mode records reads with predicate locks that can cover index pages rather than single rows, so transactions touching nearby rows can conflict too. In every run the total balance was preserved — the failures are the mechanism that keeps the data correct.

Verify: the abort rate is measured at production-like concurrency and data size before choosing serializable isolation.

2,000 transfers, 20 concurrent, PostgreSQL 17 A grid of 3 rows by 3 columns. 2,000 transfers, 20 concurrent, PostgreSQL 17 approach 10 accounts 1,000 accounts SERIALIZABLE, no retry 366 committed; 1,587 + 47 errors; 24.5 s 1,049-1,158 committed; 0.30-0.51 s SERIALIZABLE, retry whole transaction 1,985 committed, 15 gave up; 15.0 s 2,000 committed; 0.52-0.90 s READ COMMITTED, ordered FOR UPDATE 2,000 committed, 0 errors; 1.37 s 2,000 committed, 0 errors; 0.35 s asyncpg 0.31.0; total balance preserved in every run.

2. Retry the whole transaction

A serialization failure aborts the entire transaction; nothing it read can be trusted, so the retry must start from the beginning — re-reading, recomputing, rewriting. Wrap the function that opens the transaction, not a statement inside it:

RETRYABLE = (asyncpg.exceptions.SerializationError,       # 40001
             asyncpg.exceptions.DeadlockDetectedError)    # 40P01

async def run_serializable(pool, fn, *args, attempts=10, base=0.002):
    for attempt in range(1, attempts + 1):
        try:
            async with pool.acquire() as conn:
                return await fn(conn, *args)               # fn opens its own transaction
        except RETRYABLE:
            if attempt == attempts:
                raise
            await asyncio.sleep(random.uniform(0, base * 2 ** attempt))   # full jitter

Measured on 10 accounts: 1,985 of 2,000 committed. Attempts needed: 926 on the first try, 493 on the second, 236 on the third, and a tail reaching 10 attempts; 15 transfers exhausted all ten. On 1,000 accounts, every transfer committed, 1,376–1,445 on the first try and none beyond nine. The jitter matters: transactions that conflicted once tend to conflict again if they retry in lockstep. The jitter technique itself is covered in exponential backoff with jitter in asyncio.

Verify: the retried unit is a function that opens its own transaction, and a test with forced conflicts shows retries succeeding.

Attempts needed per committed transfer, 10 accounts 6 horizontal bars comparing 1 attempt with the others. Attempts needed per committed transfer, 10 accounts 1 attempt 926 2 attempts 493 3 attempts 236 4 attempts 152 5 attempts 75 6-10 attempts 103 15 transfers gave up after 10 attempts. Under heavy contention, retries are the common case.

3. Keep side effects out of the retried function

Everything inside the retried function runs once per attempt. A transaction that sends an email, publishes a message or calls another service would do so up to ten times:

async def place_order(conn, order):
    async with conn.transaction(isolation="serializable"):
        await reserve_stock(conn, order)
        await conn.execute("INSERT INTO outbox (topic, payload) VALUES ('orders', $1)",
                           json.dumps(order))            # published after commit, once

Write side effects to an outbox table in the same transaction and publish them after commit, as in implementing the transactional outbox pattern in asyncio. Python-side state needs the same care: appending to a list inside the function appends once per attempt. Return values, and apply them only after run_serializable returns.

Verify: the retried function's only effects are database writes inside its transaction.

4. Bound retries by time as well as count

Under a contention spike, ten attempts with backoff can take a long time, and each attempt holds a pooled connection. Measured on 10 accounts, the retrying run took 15.0 s for 2,000 transfers. Put the retry loop under the request's deadline, so a transaction that cannot commit in time fails cleanly:

async def transfer_endpoint(pool, a, b, amount):
    async with asyncio.timeout(2.0):                      # request deadline covers all attempts
        return await run_serializable(pool, transfer, a, b, amount)

When the deadline cancels the loop, a transaction in progress is rolled back as the connection's transaction context exits, and the connection returns to the pool. Count serialization failures and give-ups as metrics: a rising retry rate is the earliest sign that a table has become a hotspot. Retry loops and cancellation interact in other ways too, covered in making retry loops cancellation-safe.

Verify: under forced contention, requests fail at their deadline with a clear error rather than holding connections indefinitely.

5. Avoid the conflict when you can

Serializable isolation is a general tool; for a known access pattern, explicit locking can be cheaper. Lock the rows you will modify, in a consistent order, under READ COMMITTED:

async def transfer_locked(conn, a: int, b: int, amount: int):
    async with conn.transaction():                        # READ COMMITTED
        await conn.fetch(
            "SELECT id FROM acc WHERE id = ANY($1::int[]) ORDER BY id FOR UPDATE",
            sorted((a, b)),
        )
        await conn.execute("UPDATE acc SET bal = bal - $2 WHERE id = $1", a, amount)
        await conn.execute("UPDATE acc SET bal = bal + $2 WHERE id = $1", b, amount)

Measured: all 2,000 transfers committed with zero errors — 1.37 s on 10 accounts, against 15.0 s with serializable retries, and 0.35 s on 1,000. Taking locks in ORDER BY id prevents deadlocks; writing bal = bal - $2 avoids a read-modify-write gap. The trade-off is that correctness now depends on every code path that touches these rows locking them the same way, which serializable isolation does not require. Keep a retry for 40P01 regardless — a code path elsewhere may lock in another order.

Verify: the chosen approach is benchmarked at production concurrency, and invariants such as total balance hold after a concurrent test.

Handling write conflicts A flow of 5 stages. Handling write conflicts Measure abort rate at real concurrency Retry whole transaction, jittered backoff Side effects outbox, applied after commit Bound attempts and request deadline Hot rows ordered FOR UPDATE, READ COMMITTED Measured: 0 errors and 11x faster with ordered locks on 10 hot rows.

Verification

Serialization failures are handled when:

  • Whole transactions are retried on 40001 and 40P01, with jittered backoff.
  • Retried functions have no external side effects beyond their transaction.
  • Retries are bounded by attempts and by the request's deadline, and counted as metrics.
  • Hot access patterns are measured against ordered locking as an alternative.

Diagnostic Hook: when a serializable workload slows sharply under load while the database CPU stays moderate, count SerializationError and DeadlockDetectedError per second. With 10 hot accounts, 1,587 of 2,000 transfers failed serialization and 47 deadlocked, stretching the run to 24.5 s.

Pitfalls & edge cases

  • No retry on 40001. Measured: 1,634 of 2,000 transfers failed on 10 accounts.
  • Retrying a statement instead of the transaction. The transaction is already aborted.
  • Side effects inside the retried function. They run once per attempt.
  • Assuming different rows never conflict. Measured: about half failed on 1,000 accounts.

Frequently Asked Questions

How do I retry a serialization failure with asyncpg?

Catch asyncpg.exceptions.SerializationError (and DeadlockDetectedError), then rerun the whole function that opens the transaction after a jittered backoff. That committed 1,985 of 2,000 hot transfers.

Why do serializable transactions fail even on different rows?

PostgreSQL's predicate locks can cover index pages, not just rows. With 1,000 accounts, 842-951 of 2,000 transfers still failed without retries.

How many times should I retry a serialization failure?

Enough for the contention you have, bounded by a deadline. On 10 hot rows, most committed within three attempts, but 15 of 2,000 still failed after ten.

Is SELECT FOR UPDATE better than SERIALIZABLE for transfers?

For this pattern it was faster and error-free: 2,000 commits in 1.37 s on 10 accounts, against 15.0 s with serializable retries, if every path locks rows in the same order.