How to verify PostgreSQL serialization failures retry safely

The symptoms

• •Application logs contain SQLSTATE 40001 (serialization_failure) entries followed by a successful retry within the same request or transaction scope.
• •pg_stat_database shows xact_rollback counts increasing while the application reports task success, suggesting client-side retry is absorbing aborts.
• •Duplicate or missing side effects (emails, messages, external API calls) appear after periods of database retry activity, indicating the retry envelope is wider than the database transaction.
• •Monitoring surfaces deadlocks (40P01) or other errors misclassified as 40001 and silently retried, stretching latency without resolution.
• •Long-tail p99 latency grows during contention windows even when error rates are low, consistent with retry storms amplifying serialization conflicts.

Likely causes

• •Retry loop encloses non-transactional side effects such as outbound HTTP calls, file writes, or message publishes, so the second attempt duplicates work.
• •Application catches a broad exception type and retries any database error, including 40P01 deadlock_detected, 40001 serialization_failure, and non-retryable errors like 23505 unique_violation.
• •Retry policy is unbounded in count or wall-clock time, causing request threads to stall under sustained contention and exhausting connection pools.
• •Connection state is reused across retries without resetting session-level settings (e.g., SET LOCAL) or statement timeouts, so the second attempt begins with stale state.
• •Read committed isolation is in use but the application assumes repeatable read or serializable semantics, producing optimistic concurrency outcomes that retry incorrectly.
• •Code path begins a transaction, performs a write, then issues a read that could now see a newer committed row, breaking the assumption that the retry is a clean replay.

First ten minutes

1. 01Confirm the SQLSTATE: inspect the relevant log file or driver error object and verify the code is exactly 40001, not 40P01, 23505, or 40002. Document the exact count of each in the observation window.
2. 02Identify the isolation level in use on the connection: query the application configuration or run SELECT current_setting('transaction_isolation') inside a session to confirm it matches the intended level (typically 'serializable' for 40001).
3. 03Map the retry boundary: read the code path that catches the error and list every statement and side effect between BEGIN and the catch site, marking which are side-effecting and which are pure reads.
4. 04Capture the retry policy: record the maximum retry count, backoff strategy, and total elapsed budget from the configuration or framework defaults.
5. 05Take a monitoring snapshot: query pg_stat_database for xact_rollback, pg_stat_activity for active serialization failures, and the application's own retry metric to establish a baseline before any change.

Evidence to collect

• •Application log lines showing the SQLSTATE code, transaction identifier (xid), and the operation label preceding the 40001, with timestamps at millisecond resolution.
• •PostgreSQL log entries from the same window with the corresponding xid, statement text, and DETAIL/HINT fields produced by the backend on serialization_failure.
• •pg_stat_database row for the target database showing xact_commit, xact_rollback, and conflicts counts for the relevant time window.
• •Application-side counter of retry attempts per logical request, plus the count of side effects emitted per request, to detect duplication.
• •Configuration evidence of the isolation level, retry count cap, and backoff schedule from the deployed binary or framework configuration file.

Where to look

• •PostgreSQL boundary: the server-log stream filtered for SQLSTATE 40001, and the pg_stat_database view for the application database.
• •Application boundary: the data access layer's transaction wrapper, the exception handler that maps SQLSTATE values to retry decisions, and the configuration that bounds retries.
• •Side-effect boundary: any code that performs I/O, network calls, or queue publishes inside the same try block as the database transaction, between BEGIN and COMMIT/ROLLBACK.
• •Connection boundary: the connection pool or session factory, where state such as SET LOCAL, statement_timeout, or advisory locks may persist across retries.
• •Driver boundary: the driver's error object, which distinguishes SQLSTATE 40001 from 40P01 and 23505, and may carry a vendor-specific error code that should be cross-referenced.

Diagnostic steps

1. 01Sort log evidence by SQLSTATE and isolate 40001 from 40P01 and 23505; if retry treats all three identically, the policy is too broad and must be narrowed.
2. 02For each 40001 occurrence, reconstruct the transaction body and label each statement as retry-safe (pure read or idempotent write keyed by a deterministic identifier) or retry-unsafe (side-effecting or non-idempotent). A retry-safe transaction should contain only the former.
3. 03Compare retry count distribution against the configured cap; if any observation exceeds the cap, the configuration is not being applied or the policy is being overridden.
4. 04Inspect connection lifecycle: verify that the retry runs on a clean session or that session-scoped state is reset before each attempt; absence of this points to a session-leak vector.
5. 05Replay the transaction under controlled contention: run a deterministic workload that forces two transactions to update the same row in serializable mode, and observe that the loser's retry succeeds without re-executing side effects.
6. 06Check for non-repeatable reads inside the transaction: if the retry re-reads a row that may have changed since the first attempt, the read must be reordered or assumed stale.
7. 07Correlate pg_stat_database.conflicts against the application's retry metric to confirm that retries are happening because of database conflicts, not client-side classification errors.

Common mistakes

• •Retrying on any SQLException rather than filtering by SQLSTATE 40001, which causes silent retries of unique violations and deadlocks that will never succeed.
• •Wrapping the retry around the entire request handler, including outbound HTTP calls and message publishes, so retries duplicate external effects.
• •Using an unbounded retry count with no jittered backoff, which amplifies contention and turns a recoverable conflict into a connection-pool exhaustion incident.
• •Assuming the driver throws a uniform exception for 40001 across all versions; some drivers expose the SQLSTATE in a nested field or as a text code, and the comparison must match the driver's contract.
• •Reusing a session that previously held an advisory lock or ran SET LOCAL, so the retry begins with implicit state the original attempt did not have.
• •Adding a retry layer without a corresponding metric, so the system cannot distinguish between a retry that succeeded and a request that failed terminally.

Safe fixes

• •Narrow the catch site to SQLSTATE 40001 only, using a typed error class or an explicit switch on the SQLSTATE string, and treat 40P01 and 23505 as terminal or separately handled.
• •Move every side effect outside the transaction: perform database work first, and only after commit publish events, send messages, or call external services. This is conditional on the evidence that side effects are inside the retry envelope.
• •Make every write inside the transaction idempotent by keying it on a deterministic request identifier recorded in a unique constraint, so a retry that attempts the same write twice is rejected by the database rather than duplicated.
• •Bound retries with a fixed count (for example, three attempts) and a jittered exponential backoff, and surface a terminal error to the caller when the budget is exhausted.
• •Reset session state before each retry by acquiring a fresh connection from the pool or by explicitly clearing SET LOCAL values and releasing advisory locks; this is conditional on evidence that session state leaks across attempts.
• •Cap the per-request wall-clock time spent retrying so that a single contended transaction cannot starve the rest of the workload.

Prove the fix

1. 01Re-run the deterministic two-transaction contention scenario and observe that the loser's retry succeeds on the second attempt, with side-effect counters increasing by exactly one per logical request.
2. 02Query pg_stat_database for the test window and confirm that xact_rollback counts increase by the expected number of aborted attempts while the application's logical-success counter increases by the intended number of completed requests.
3. 03Inspect application logs for the test run and verify that retries occur only on SQLSTATE 40001, never on 40P01 or 23505, and that no retry exceeds the configured cap.
4. 04Confirm that the application's retry metric is non-zero during the test and that the side-effect-per-request metric remains stable across retried and non-retried requests.
5. 05Run a regression suite that includes a deliberately non-idempotent operation inside the transaction; the test should fail loudly if the retry envelope includes that operation, proving the fix is structural.

Prevention and next steps

• •Establish a code-review rule that any retry on a database error must specify the SQLSTATE list, the maximum attempt count, and the side-effect boundary, and reject reviews that omit any of these.
• •Add a metric for retry attempts per logical request and a separate metric for terminal errors, so the team can detect silent retry storms in dashboards.
• •Document the isolation level and retry contract per service so that engineers understand the assumption before modifying transaction code.
• •Include a load test in the CI pipeline that forces serialization conflicts and asserts that the side-effect-per-request counter remains exactly one per logical operation.
• •Periodically audit the data access layer for any new code that performs I/O or external calls inside a transactional block, since such code is the most common source of unsafe retries.

Safe commands and checks

SELECT datname, xact_commit, xact_rollback, conflicts FROM pg_stat_database WHERE datname = current_database();
SELECT current_setting('transaction_isolation');
SELECT pid, state, query_start, xact_start, wait_event_type, wait_event FROM pg_stat_activity WHERE state <> 'idle' ORDER BY xact_start;
BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT * FROM accounts WHERE id = <account_id> FOR UPDATE; -- observe 40001 on conflict; ROLLBACK;
SET log_min_messages = 'debug2'; SET log_lock_waits = on; -- then re-run the contended workload and inspect the resulting log lines for SQLSTATE 40001.