Technology · PRACTICAL GUIDE
PostgreSQL SQLSTATE 40001? Retry the whole transaction, not the failed statement
Capture the SQLSTATE, isolation level and failure boundary, roll back, rebuild every read and decision inside a bounded retry loop, and keep irreversible side effects beyond the commit boundary.
SQLSTATE 40001 is PostgreSQL’s serialization_failure signal. It can appear under Repeatable Read or Serializable isolation, during a statement or when COMMIT checks whether the transaction can be placed in a valid serial order. The database has rejected the whole attempt; retrying only the last statement can reuse application decisions derived from a snapshot that is no longer valid.
A safe handler catches the database error around the entire transaction callback, confirms code 40001, rolls back, waits with bounded backoff and jitter, then starts a new transaction and repeats every read, decision and write. PostgreSQL cannot perform that replay automatically because it cannot reconstruct the application logic that selected the SQL or its values.
Retry is not the same as hiding contention. Set a small attempt or time budget, emit attempt and latency metrics, and surface persistent failures. Keep email, payment, publication and other irreversible effects outside the retry body; a transactional outbox or an idempotency key can connect committed database state to later delivery.
Record the failure boundary before retrying
Log the five-character SQLSTATE, isolation level, attempt number, transaction age and whether the exception arrived during a statement or COMMIT. Keep sensitive SQL values out of logs, but include a stable operation name and database trace identifier. Match control flow on SQLSTATE rather than localized message text.
A COMMIT-time error is expected in this failure class: PostgreSQL may need the completed read/write set before it can decide that no serial order is safe. Do not treat a clean statement phase as evidence that the transaction succeeded.
Replay from BEGIN with fresh reads and decisions
After 40001, roll back and re-enter the transaction function from its first read. Re-run existence checks, counters, price or inventory reads, authorization decisions and derived value construction. A savepoint or retry of the failed UPDATE alone preserves assumptions from the rejected snapshot.
Make the transaction callback explicit enough that one invocation equals one database attempt. Pass stable request inputs into it, but compute database-dependent values inside it. Catch around COMMIT as well as the callback so both failure boundaries use the same clean replay path.
Bound retries and make contention visible
Use a small maximum attempt count or elapsed-time budget, exponential or decorrelated backoff, and jitter so concurrent workers do not immediately collide again. Reset the transaction and connection state on every attempt; never continue using a transaction PostgreSQL has marked aborted.
Measure retries by operation, relation or workload class. A rare first retry can be normal under contention; repeated exhaustion is evidence to inspect transaction length, hot keys, index access, pool pressure, lock order and whether Serializable is required for that operation. Lowering isolation is a design decision, not an error-message fix.
Keep side effects beyond a commit-safe boundary
Application code inside the callback may execute more than once. Do not send an email, charge a card, publish a message or call a non-idempotent API before the database attempt commits. Otherwise a failed first attempt can create a real external effect and the retry can create it again.
For reliable delivery, write an outbox record in the same transaction and let a separate worker publish it after commit. Where an external system supports idempotency keys, derive one from the stable business request—not from the retry attempt—and reconcile ambiguous external outcomes before repeating them.
Route nearby SQLSTATEs to different gates
A deadlock, 40P01, also rolls back the transaction and may be retried, but it deserves lock-order diagnosis. PostgreSQL notes that 23505 and 23P01 can sometimes reflect serialization-like races, yet automatic retries require deeper application-specific proof because the server may not identify them as serialization failures.
SQLSTATE 40003 means statement_completion_unknown. That is a reconciliation problem: determine whether the operation committed before repeating it. A retry helper that accepts every class 40 error can duplicate work or erase the distinction between a rejected transaction and an unknown outcome.
