阅读中文版本

PostgreSQL Serialization Failure 40001 — Retry the Transaction

Handle PostgreSQL 40001 serialization failures by retrying the whole transaction at SERIALIZABLE/REPEATABLE READ isolation.

psql -c "SHOW transaction_isolation"

psql -c "BEGIN ISOLATION LEVEL SERIALIZABLE; ..."

Symptoms

The error “PostgreSQL Serialization Failure 40001 — Retry the Transaction” points at a specific failure mode. This guide documents the symptoms you can observe, the commands that isolate the cause, the root cause, the fix, and the checks that prove the fix works.

Observed symptoms include:

• ERROR: could not serialize access due to read/write dependencies among transactions (SQLSTATE 40001)
• Failures appear only under concurrent writes to the same rows
• A retry of the same transaction then succeeds

Reproduce and Diagnose

Reproduce the failure in isolation before changing any code. Work through the checks below in order; each one narrows the search space.

Step 1: Confirm the isolation level

psql -c "SHOW transaction_isolation"

SERIALIZABLE or REPEATABLE READ explains why 40001 can occur under concurrency.

Step 2: Reproduce with two concurrent sessions

psql -c "BEGIN ISOLATION LEVEL SERIALIZABLE; ..."

Running the same transaction twice concurrently reliably triggers the serialization error.

Likely contributors, in order of frequency:

• SERIALIZABLE isolation aborts a transaction whose result cannot be ordered
• REPEATABLE READ detects a concurrent update to a row it already read
• The application treats 40001 as a permanent error instead of retrying

Root Cause

At SERIALIZABLE and REPEATABLE READ, PostgreSQL aborts a transaction that would violate serializability, returning SQLSTATE 40001. This is a normal concurrency signal, and the correct response is to retry the transaction from the beginning.

The Fix

Wrap the transaction in a retry loop with backoff, cap attempts, retry the entire transaction (not a single statement), and reduce contention by ordering accesses consistently.

Watch out: Retrying side effects performed outside the transaction duplicates them; keep all effects inside the transaction.

async function runSerializable(fn, tries = 5) {
  for (let attempt = 1; attempt <= tries; attempt++) {
    const client = await pool.connect();
    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
      const result = await fn(client);
      await client.query('COMMIT');
      return result;
    } catch (e) {
      await client.query('ROLLBACK');
      if (e.code === '40001' && attempt < tries) {
        await new Promise((r) => setTimeout(r, 50 * attempt));
        continue; // retry the WHOLE transaction
      }
      throw e;
    } finally {
      client.release();
    }
  }
}

Verify and Prevent

Run concurrent writers and confirm the retry loop succeeds without surfacing 40001 to callers; count retries in metrics.

Once verified, add a regression test that reproduces the original symptom so the failure cannot silently return.

This class of failure is usually environmental rather than pure logic, so capture the exact command, the environment variables, and the working directory in your bug report. Reproduce it in the smallest possible script, then widen the scope only once the minimal case is understood. A short written checklist for this failure mode saves hours the next time it appears.