Your Postgres SERIALIZABLE transaction fails with SQLSTATE 40001. What should the application do?

Retry the entire transaction from the beginning (re-reading data), with a capped number of attempts and jittered backoff. The error means another transaction conflicted and the database aborted this one to keep the outcome serializable.

Why must side effects like emails be kept out of a transaction that may be retried?

Each retry re-executes the block. An email or payment inside it would be sent once per attempt, even though only the last attempt commits.

How does SERIALIZABLE differ between Postgres and MySQL InnoDB?

Postgres uses Serializable Snapshot Isolation: reads don't block, and conflicting transactions are aborted (40001). MySQL implements SERIALIZABLE by turning plain SELECTs into shared-lock reads, so conflicts show up as lock waits and deadlocks instead.

SERIALIZABLE in Practice: Retrying Serialization Failures — Transactions, Locking & Concurrency in Code

Scenario

Example app: a coupon service. Each coupon has a global usage limit and a per-customer limit, enforced by queries over several tables. After the write-skew lesson the team switches these transactions to SERIALIZABLE on Postgres.

What you see in production

Correctness is fixed, but now about 0.5% of redemptions fail with:

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

Customers see a 500 error and abandon checkout.

The naive code

The level is raised, but nothing handles the new, expected failure:

@app.post("/coupons/{code}/redeem")
def redeem(code: str, customer_id: int):
    with Session(engine.execution_options(isolation_level="SERIALIZABLE")) as s, s.begin():
        coupon = s.get(Coupon, code)
        used = s.scalar(select(func.count()).where(Redemption.coupon_code == code))
        mine = s.scalar(select(func.count()).where(Redemption.coupon_code == code,
                                                   Redemption.customer_id == customer_id))
        if used >= coupon.max_uses or mine >= coupon.per_customer:
            raise CouponExhausted()
        s.add(Redemption(coupon_code=code, customer_id=customer_id))
    # a SerializationFailure here becomes a 500

@Transactional(isolation = Isolation.SERIALIZABLE)
public void redeem(String code, long customerId) {
    // checks + insert; CannotSerializeTransactionException propagates as a 500
}

Reproduce it

A retry wrapper around a transaction that fails with a serialization error on its first two attempts:

Retrying the whole transaction with backoff and jitterTry it yourself

import random
random.seed(7)                              # deterministic jitter for the demo

class SerializationFailure(Exception):
    sqlstate = "40001"

attempts_that_fail = {1, 2}                 # the first two attempts conflict with other redeemers

def redeem_once(attempt):
    print(f"attempt {attempt}: BEGIN ISOLATION LEVEL SERIALIZABLE; checks; INSERT")
    if attempt in attempts_that_fail:
        raise SerializationFailure("could not serialize access")
    print(f"attempt {attempt}: COMMIT")
    return "redeemed"

def with_retries(fn, max_attempts=5, base=0.02, cap=0.5):
    for attempt in range(1, max_attempts + 1):
        try:
            return fn(attempt)
        except SerializationFailure as e:
            if attempt == max_attempts:
                raise
            delay = random.uniform(0, min(cap, base * 2 ** attempt))   # full jitter
            print(f"attempt {attempt}: {e.sqlstate} -> retry in {delay * 1000:.0f} ms")

print(with_retries(redeem_once))

Output

attempt 1: BEGIN ISOLATION LEVEL SERIALIZABLE; checks; INSERT
attempt 1: 40001 -> retry in 13 ms
attempt 2: BEGIN ISOLATION LEVEL SERIALIZABLE; checks; INSERT
attempt 2: 40001 -> retry in 12 ms
attempt 3: BEGIN ISOLATION LEVEL SERIALIZABLE; checks; INSERT
attempt 3: COMMIT
redeemed

Root cause

Postgres implements SERIALIZABLE with Serializable Snapshot Isolation (SSI): transactions run optimistically and the database aborts one of any group whose reads and writes couldn't have happened in some serial order. Aborts are part of the contract — the application must retry the entire transaction, re-reading data, not just the failed statement. MySQL takes the opposite approach: SERIALIZABLE turns plain reads into shared locks, so you see more lock waits and deadlocks (error 1213) instead.

The fix

Wrap the whole unit of work in a retry helper that recognises only retryable errors:

from psycopg import errors as pgerr
from sqlalchemy.exc import DBAPIError

RETRYABLE = (pgerr.SerializationFailure, pgerr.DeadlockDetected)

def run_serializable(work, attempts=5):
    for attempt in range(1, attempts + 1):
        try:
            with Session(engine.execution_options(isolation_level="SERIALIZABLE")) as s, s.begin():
                return work(s)                      # all reads happen again on every attempt
        except DBAPIError as e:
            if not isinstance(e.orig, RETRYABLE) or attempt == attempts:
                raise
            time.sleep(random.uniform(0, min(0.5, 0.02 * 2 ** attempt)))

public void redeem(String code, long customerId) {
    for (int attempt = 1; ; attempt++) {
        try {
            coupons.redeemSerializable(code, customerId);   // @Transactional(isolation = SERIALIZABLE) in another bean
            return;
        } catch (CannotSerializeTransactionException | PessimisticLockingFailureException e) {
            if (attempt == 5) throw e;
            sleep(ThreadLocalRandom.current().nextLong(0, Math.min(500, 20L << attempt)));
        }
    }
}

// Laravel's DB::transaction retries the closure when it detects a concurrency error (deadlock / serialization):
DB::transaction(function () use ($code, $customerId) {
    DB::statement('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');   // first statement in the transaction
    // checks + insert
}, attempts: 5);

async function withRetry<T>(fn: () => Promise<T>, attempts = 5): Promise<T> {
  for (let i = 1; ; i++) {
    try { return await fn(); }
    catch (e: any) {
      if (e?.code !== "P2034" || i === attempts) throw e;   // P2034: write conflict or deadlock, retry
      await new Promise((r) => setTimeout(r, Math.random() * Math.min(500, 20 * 2 ** i)));
    }
  }
}
await withRetry(() => prisma.$transaction(redeemCoupon, { isolationLevel: "Serializable" }));

Trade-offs

SERIALIZABLE removes a whole class of reasoning bugs but adds retry traffic; under heavy contention on the same data, retries can snowball — cap attempts and add jitter.
Everything inside the retried block must be safe to repeat: no emails, no external charges (do those after commit).
Keep serializable transactions short and declare READ ONLY where possible; Postgres can optimise read-only serializable transactions.
If one hot record causes most conflicts, an explicit lock on that record can be cheaper than repeated aborts.

Detect it in production

Count SQLSTATE 40001 and 40P01 per endpoint, and track retries per request; alert when the retry rate climbs.
Postgres pg_stat_database has a deadlocks counter; serialization failures appear in logs (set log_min_error_statement).
A spike in retries after a deploy usually means a new query reads more rows than before.

Key points

In Postgres SERIALIZABLE, aborts (SQLSTATE 40001) are expected; retry the whole transaction.
Retry only known-retryable errors, with capped attempts and jittered backoff.
Keep side effects outside the retried block.
Monitor retry rates as a contention signal.