Handling Unique-Violation Races Under Serializable Isolation

SERIALIZABLE is often adopted to make concurrency bugs go away, and for deduplication it does — by converting them into aborts that your application must handle. A transaction that would have silently created a duplicate at READ COMMITTED instead fails with 40001, and if nothing retries it, the user sees an error. This runbook covers what changes and what to write, and sits under database unique constraints & upserts.

Prerequisites. PostgreSQL with default_transaction_isolation under your control, a unique constraint on the deduplication key, and familiarity with ON CONFLICT versus MERGE.

Step 1 — What SERIALIZABLE adds

PostgreSQL implements Serializable Snapshot Isolation. On top of a snapshot, it tracks read-write dependencies between concurrent transactions using predicate locks — including locks on index ranges and on gaps where a row does not yet exist. When it detects a dependency cycle that could not have arisen in any serial execution, it aborts one transaction with 40001 serialization_failure.

A read of nothing still takes a lock An index is drawn with existing entries and a gap where the searched key would sit. A select at serializable isolation places a predicate lock over that gap. When a concurrent transaction inserts into the gap, the read-write dependency is recorded and one of the two transactions is aborted with error 40001. gap SIReadLock tx A: SELECT → 0 rows, but the absence is now claimed tx B inserts here → rw-conflict → 40001 The narrower the read, the smaller the gap it claims — and the lower the abort rate.

For deduplication the relevant consequence is that a SELECT for a key that does not exist now takes a predicate lock on that absence. A concurrent transaction inserting that key creates the conflict, and one of the two aborts.

SELECT-then-INSERT at READ COMMITTED versus SERIALIZABLE Two panels. On the left, at read committed, two transactions each select and find no row, then both insert. Without a unique constraint both commit and a duplicate charge is created. On the right, at serializable, the select takes a predicate lock on the absent key. When the second transaction inserts, the read-write dependency is detected and it aborts with error 40001, so only one charge is created. READ COMMITTED · no constraint tx A SELECT → 0 rows INSERT charge COMMIT tx B SELECT → 0 rows INSERT charge COMMIT both snapshots predate the other's insert 2 charges created no error anywhere — silent duplication SERIALIZABLE · predicate locks tx A SELECT → predicate lock INSERT charge COMMIT ✓ tx B SELECT → predicate lock INSERT charge rw-conflict detected no serial order produces this outcome 1 charge · tx B aborts 40001 correct — IF something retries unretried 40001 = user-visible error

The critical caveat: SERIALIZABLE only protects transactions that are all running at SERIALIZABLE. One background job at READ COMMITTED writing the same table sees none of the predicate locks and can create the duplicate you thought you had prevented. That is why the unique constraint stays, regardless of isolation level.

Step 2 — Two error codes, two meanings

Code Name Cause on a dedup path Safe to retry blindly?
40001 serialization_failure Predicate-lock conflict; a peer committed a conflicting write Yes — replay the whole transaction
40P01 deadlock_detected Lock-ordering cycle, usually multi-row Yes, with backoff
23505 unique_violation The insert reached the index and lost No — it means the key already exists; read and replay

The distinction matters. 40001 says “your transaction was rolled back for concurrency reasons; try again and it may succeed”. 23505 says “this key exists”; retrying the identical transaction produces the same error forever. Treating them alike is the most common bug in this area.

from psycopg import errors

def register(conn, scoped, fingerprint) -> str:
    """Returns 'won' or 'replay'. Raises only on genuinely unexpected errors."""
    try:
        with conn.transaction():
            conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
            row = conn.execute(
                "SELECT state FROM idempotency_keys WHERE scoped_key = %s", (scoped,)
            ).fetchone()
            if row is not None:
                return "replay"
            conn.execute(
                """INSERT INTO idempotency_keys (scoped_key, fingerprint, expires_at)
                   VALUES (%s, %s, now() + interval '24 hours')""",
                (scoped, fingerprint),
            )
            apply_effect(conn)
            return "won"
    except errors.UniqueViolation:
        return "replay"                     # a peer already has it — do NOT retry
    except errors.SerializationFailure:
        raise Retryable("40001")            # caller re-runs the whole transaction

Step 3 — The retry loop

At SERIALIZABLE a retry loop is not optional; it is part of the contract with the database.

import random, time

def with_serializable_retry(fn, *, max_attempts=5, base_ms=20, cap_ms=500):
    for attempt in range(max_attempts):
        try:
            return fn()
        except Retryable:
            if attempt == max_attempts - 1:
                metrics.inc("serialization_retry_exhausted_total")
                raise
            # Full jitter: synchronised retries just recreate the conflict.
            time.sleep(random.uniform(0, min(cap_ms, base_ms * 2 ** attempt)) / 1000)
            metrics.inc("serialization_retry_total")
func WithSerializableRetry(ctx context.Context, db *sql.DB, fn func(*sql.Tx) error) error {
    for attempt := 0; attempt < 5; attempt++ {
        tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
        if err != nil { return err }
        if err = fn(tx); err != nil {
            _ = tx.Rollback()
            var pgErr *pgconn.PgError
            if errors.As(err, &pgErr) && pgErr.Code == "40001" {
                time.Sleep(fullJitter(attempt))
                continue
            }
            return err
        }
        if err = tx.Commit(); err != nil {           // 40001 can surface AT COMMIT
            var pgErr *pgconn.PgError
            if errors.As(err, &pgErr) && pgErr.Code == "40001" {
                time.Sleep(fullJitter(attempt))
                continue
            }
            return err
        }
        return nil
    }
    return errors.New("serialization conflict unresolved after 5 attempts")
}

The Go version highlights something the Python one hides: at SERIALIZABLE, 40001 can be raised by COMMIT itself, not only by a statement. A retry loop that only wraps statement execution misses half the aborts.

Retries must use jitter. Two transactions that abort together and retry together simply recreate the conflict, and the pair can livelock.

Step 4 — Keep transactions short

The abort rate scales with how long transactions hold predicate locks and how much they read.

-- BAD: an external call inside the transaction holds locks for the whole round trip.
BEGIN ISOLATION LEVEL SERIALIZABLE;
  SELECT * FROM idempotency_keys WHERE scoped_key = $1;
  -- HTTP call to the payment processor: 400 ms of held predicate locks
  INSERT INTO idempotency_keys ...;
COMMIT;

-- GOOD: the external call happens outside, bracketed by two short transactions.
BEGIN ISOLATION LEVEL SERIALIZABLE;
  INSERT INTO idempotency_keys (scoped_key, fingerprint, state, expires_at)
  VALUES ($1, $2, 'pending', now() + interval '24 hours');
COMMIT;                                   -- ~1 ms of locks
-- external call here, with its own idempotency key derived from $1
BEGIN;
  UPDATE idempotency_keys SET state='completed', status_code=$3, response=$4
   WHERE scoped_key=$1;
COMMIT;

Narrow the read, too. SELECT state FROM ... WHERE scoped_key = $1 takes a predicate lock on one index entry; SELECT * FROM idempotency_keys WHERE expires_at > now() takes one on a huge range and will conflict with nearly every concurrent insert.

Step 5 — Measure the abort rate

-- Serialization failures since the last stats reset, per database.
SELECT datname, xact_commit, xact_rollback,
       round(100.0 * xact_rollback / nullif(xact_commit + xact_rollback, 0), 3) AS rollback_pct
  FROM pg_stat_database WHERE datname = current_database();

-- Predicate lock pressure: if this approaches max_pred_locks_per_transaction,
-- PostgreSQL escalates to coarser locks and the abort rate jumps.
SELECT mode, count(*) FROM pg_locks WHERE mode LIKE 'SIReadLock' GROUP BY mode;
# Drive 64 concurrent duplicates and count aborts. A healthy dedup path
# resolves every one of them without surfacing an error to the caller.
seq 64 | xargs -P 64 -I{} psql -qtA -v key="'idem:probe'" -f serializable_register.sql \
  2>>errors.log | sort | uniq -c
grep -c '40001' errors.log      # aborts — expected, must be retried internally
grep -c '23505' errors.log      # violations — expected, mapped to "replay"

An abort rate above roughly 1% of transactions is a signal to shorten transactions or narrow reads, not a reason to lower the isolation level silently.

Failure scenarios and debugging

Failure Scenario Remediation Steps Observability Hooks
40001 propagated to the client as a 500, so ordinary concurrency looks like an outage Wrap every SERIALIZABLE transaction in a retry loop, including the COMMIT call. Assert in a test that a forced conflict produces a 201, not a 500. serialization_retry_total versus http_responses_total{status="500"}; the latter should not move with the former
23505 treated as retryable, producing an infinite loop against an existing key Map 23505 to “replay the stored response” and only 40001/40P01 to retry. Distinguish them explicitly rather than catching a base exception class. serialization_retry_exhausted_total — a non-zero value with a flat 40001 rate means 23505 is being retried
Retries without jitter, so two conflicting transactions livelock retrying in lockstep Apply full jitter to the backoff. Cap attempts at 5 and surface exhaustion as a distinct metric. serialization_retry_total per transaction rising above 2 on average indicates lockstep
A background job runs at READ COMMITTED against the same table, bypassing every predicate lock Keep the unique constraint as the hard backstop and set default_transaction_isolation at the role level so no connection opts out accidentally. unique_violation_total from the error log; any value proves a weaker-isolation writer exists
Predicate locks escalate because max_pred_locks_per_transaction is too low, and the abort rate jumps tenfold Raise max_pred_locks_per_transaction (default 64) and narrow the reads. Escalation converts a row-level lock into a page or relation lock and conflicts with everything. pg_locks SIReadLock count per transaction; abort rate correlated with batch size
Abort rate is a function of how long you hold the locks A curve of serialization abort rate against transaction duration. Below twenty milliseconds the rate is negligible. It rises steeply past fifty milliseconds, where an external call inside the transaction would place most workloads. A one percent threshold is marked as the point to shorten transactions rather than raise the retry limit. abort % transaction duration → 1% — act here 50 ms — an HTTP call lands here shorten the transaction, not the retry limit

SRE / observability checklist

  1. serialization_retry_total — Counter of 40001 retries. A rate above 1% of transactions means transactions are too long or reads too wide.
  2. serialization_retry_exhausted_total — Counter of transactions that used all attempts. Any sustained value is user-visible failure.
  3. unique_violation_total — Counter from the error log for the dedup table. Should be flat unless a weaker-isolation writer exists.
  4. transaction_duration_seconds — Histogram per transaction name. Alert if p99 exceeds 50 ms on the deduplication path; long transactions drive the abort rate.
  5. pg_locks{mode="SIReadLock"} — Gauge of predicate locks held. Approaching max_pred_locks_per_transaction predicts escalation.
  6. isolation_level connection label — On every transaction metric, so a service silently running at READ COMMITTED is visible rather than assumed.