PostgreSQL serialization failure: read the conflicting transaction evidence

The symptoms

• •Application receives ERROR: could not serialize access due to read/write dependencies among transactions with SQLSTATE 40001, typically surfaced from a transaction running at SERIALIZABLE or SERIALIZABLE READ ONLY DEFERRABLE.
• •Background worker, batch loader, or scheduler log shows the 40001 error immediately followed by a successful retry, so the failure is invisible to end users but inflates tail latency and doubles write throughput on the table.
• •Application error handler treats 40001 as a fatal error and either rolls back the entire unit of work or terminates the worker, producing irregular failed-job counts that correlate with concurrent activity on the same rows.
• •Metrics show transaction abort rate spiking only on tables whose hot rows are touched by two or more writers, while read-mostly tables report zero aborts under the same load.
• •Commit fails but reads inside the same transaction were successful, which rules out syntax errors and permission issues and points specifically to the predicate or rw-antidependency check.

Likely causes

• •Application explicitly or implicitly set the transaction to SERIALIZABLE (via SET TRANSACTION, JDBC connection string parameter, or ORM default) while still allowing two transactions to read the same rows and then write different columns of those rows in conflicting orders.
• •Hot row or hot key set receives concurrent reads plus updates because a queue, counter, or balance field is incremented inside SERIALIZABLE blocks without an explicit row lock or order-by-PK discipline.
• •Retry logic is missing, catches a generic SQLException, or only retries connection-level failures, so the transient 40001 abort is treated as a logical failure and surfaced to callers instead of being replayed.
• •Range or predicate reads (SELECT ... WHERE balance < threshold) intersect with concurrent inserts or updates that change whether each row matches the predicate, causing a rw-antidependency that the SSI engine flags.
• •Long-running SERIALIZABLE transactions hold their snapshot while other transactions commit, so the snapshot becomes "stale" relative to recent writes and the engine detects the conflict only at COMMIT time.
• •ORM framework or connection pool is reusing a session across logical operations without resetting isolation level, so a single SERIALIZABLE snapshot spans multiple unit-of-work boundaries and amplifies conflict probability.

First ten minutes

1. 01Confirm SQLSTATE is exactly 40001 and message starts with "could not serialize access". A different SQLSTATE means you are looking at a different class of error (deadlock 40P01, lock timeout 55P03, syntax 42xxx).
2. 02Capture the full error context including application transaction name, isolation level, and the SQL that triggered commit; under JDBC this is the SQLException chain, under psycopg2 it is the exception's pgcode attribute.
3. 03Ask the application to log the current isolation level of the failing transaction so you can rule out misconfigured connections running SERIALIZABLE implicitly.
4. 04Reproduce by running two concurrent transactions with psql on the same database: open two sessions, BEGIN ISOLATION LEVEL SERIALIZABLE, perform overlapping reads and writes, and observe which one aborts.
5. 05While the workload is running, query pg_stat_database for xact_commit versus rollback and pg_stat_activity for the application_name, state, and xact_start of active serializable transactions.
6. 06Record table names, predicates, and ordering of operations from the application code path so evidence collection below can map them to the conflicting read set.
7. 07Decide whether the failure is a logic bug (wrong isolation level chosen) or an expected concurrency cost (correct level, missing retry) before changing anything; the safe fix differs in each branch.

Evidence to collect

• •PostgreSQL server log entries showing "could not serialize access due to read/write dependencies" together with the statement or transaction id that was aborted, because the message names the loser of the conflict.
• •Snapshot of pg_stat_activity during the failure window, including state, wait_event_type, wait_event, isolation level visible via the backend's xact properties, and xact_start so you can identify long-running serializable transactions.
• •Counts from pg_stat_database for xact_commit and xact_rollback of the application database over the failure window, and a ratio of rollback-to-commit so you can quantify how much of the workload is being aborted.
• •Application-side structured logs containing the operation sequence: tables touched, predicates used, write order, and retry counter, so you can map a 40001 to the exact business step.
• •Connection pool configuration showing the default isolation level, transaction scope (auto-commit off), and whether the pool resets isolation between checkouts.
• •The lock dependency graph view pg_locks, filtered to the application database, showing which transactions held AccessShareLocks on the table at the time of the abort.

Where to look

• •Application error frames and exception wrapping layer: the boundary at which 40001 is caught, downgraded to a generic error, or rethrown; this is where retry policy lives.
• •Database server log (log_destination, typically stderr or syslog) at the WARNING level because serialization_failure aborts are classified as WARNING by default.
• •pg_stat_activity and pg_stat_database statistics views documented under the official monitoring-stats reference, which expose transaction counters and active backend state.
• •Connection pool and ORM configuration files: HikariCP, PgBouncer, Spring transaction managers, Django DATABASES, SQLAlchemy engines, where default_transaction_isolation or equivalent may be set to SERIALIZABLE.
• •Migration and schema files: the boundary where SERIALIZABLE was first introduced for this workload, so you can check whether the choice predates a scaling change.
• •Application code path that opens a transaction, performs business reads, then writes: the exact sequence that the SSI engine treats as one decision point.

Diagnostic steps

1. 01Read the aborting transaction's message text. If it says "read/write dependencies among transactions", the SSI engine identified an rw-antidependency, meaning your transaction read data that another transaction later modified.
2. 02Run SELECT datname, xact_commit, xact_rollback FROM pg_stat_database WHERE datname = current_database(); and confirm rollback rate is non-zero only on the affected database; this localizes the problem.
3. 03Run SELECT pid, application_name, state, xact_start, query FROM pg_stat_activity WHERE state <> 'idle'; to find long-running transactions whose snapshot is likely to participate in conflicts.
4. 04Identify whether the application retry loop exists by code inspection: search for SQLSTATE 40001, serialization_failure, or isTransient() in the persistence layer; absence of this code path is itself a finding.
5. 05Compare isolation level declared by code vs observed level by reading the application's pg_stat_activity backend flags or by issuing SET TRANSACTION ISOLATION LEVEL and SHOW transaction_isolation in a test session.
6. 06Reproduce under controlled load with two psql sessions, each BEGIN ISOLATION LEVEL SERIALIZABLE; perform the same SELECT ... FOR UPDATE on the hot row in both sessions, then UPDATE different columns; observe which session aborts and which commits.
7. 07Map the aborting transaction's read set to the concurrent transactions' write sets using application logs of timestamps and row identifiers; the overlap pinpoints the table and column group.
8. 08Decide between three working hypotheses: wrong isolation level (drop to READ COMMITTED), correct isolation but missing retry (add bounded retry on 40001), or unavoidable conflict requiring application redesign (serialize on a single writer or partition by hot key).

Common mistakes

• •Treating 40001 like a generic SQLException and surfacing it to the user as a failure, when the SSI contract is "abort and retry" and the correct response is an automatic, bounded retry loop.
• •Escalating to SERIALIZABLE for "maximum safety" without removing rw-antidependencies, which strictly increases abort rate compared to READ COMMITTED for read-modify-write workloads.
• •Wrapping the entire request in one SERIALIZABLE transaction that includes network or user-think time, because the longer the snapshot is open the larger the read set becomes and the higher the abort probability.
• •Catching SQLException broadly and retrying on every error, including non-transient ones like unique violation (23505) and check violation (23514), which silently produces duplicate writes and semantic corruption.
• •Rerunning the failed transaction with a new connection but the same default isolation level without resetting the pool's defaults, so the retry inherits a stale state or a different level than intended.
• •Adding explicit advisory locks or SELECT FOR UPDATE on every read "just in case", which downgrades SERIALIZABLE to pessimistic locking and removes the point of using SSI in the first place.

Safe fixes

• •If isolation was set to SERIALIZABLE by default and the workload is a simple read-modify-write, change the connection or transaction to READ COMMITTED and verify whether the original concurrency bug it was meant to prevent is real; READ COMMITTED is the PostgreSQL default and avoids SSI aborts entirely.
• •If SERIALIZABLE is required for correctness, wrap the unit of work in a bounded retry loop that catches only 40001, sleeps with jitter, and aborts after N attempts (commonly 3 to 5); record each retry as a metric so you can observe abort rate trend.
• •Shorten SERIALIZABLE transactions so the snapshot excludes network calls and user input: do reads, branch on the result, then open the SERIALIZABLE block only around the critical read-write group.
• •Order operations identically across code paths that touch the same rows (for example, always update by ordered column list, always insert by sorted key set) so rw-antidependencies cannot form on permutation rather than semantic conflicts.
• •For genuinely hot rows such as counters or balances, use a single-writer pattern (one worker process, advisory lock, or row lock) and reserve SERIALIZABLE only for cross-row invariants the single-writer pattern cannot enforce.
• •After any fix, re-run a controlled two-session reproduction with the same workload and confirm either zero 40001 aborts under expected load, or a stable abort rate that the application retry handles within its budget.

Prove the fix

1. 01Run a synthetic concurrent workload against the affected tables using two database sessions that issue the previously conflicting sequence; observe zero or a strictly bounded number of 40001 errors, never increases beyond the retry budget.
2. 02Query pg_stat_database and confirm xact_rollback delta over a fixed window matches expected abort rate plus any user-initiated rollbacks, not the inflated rate from before the fix.
3. 03Replay production traffic against a staging clone with the fix and the retry logger enabled; assert via application metrics that 40001 retries stay below the configured maximum for at least one full burst window.
4. 04Confirm application logs show the retry path executing only on SQLSTATE 40001 and never on 23505 or 40P01, which proves the catch is scoped correctly and not masking other errors.
5. 05Inspect a sampled aborted transaction's pg_stat_activity entry plus its application log lines and verify the read-write overlap that caused the abort is removed or its window is closed by the new code path.

Prevention and next steps

• •Adopt a written policy that SERIALIZABLE is used only when a documented correctness invariant requires it, and that the corresponding code path includes a bounded 40001 retry and a metric for rollback rate.
• •Add integration tests that run two concurrent transactions against hot rows and assert successful commit under both isolation levels, so a future change cannot accidentally re-introduce the conflict.
• •Track xact_rollback rate per database and per application_name in monitoring, with an alert when rollback rate exceeds commit rate or when it grows week-over-week, which signals conflict pressure building.
• •Review PRs that change isolation level or transaction scope through a checklist: snapshot width, retry handler, retry budget, and metric, so the operational contract travels with the code.
• •Keep SERIALIZABLE transactions short by separating input validation and remote calls from the critical read-write segment; document in code comments why each SERIALIZABLE block exists.

Safe commands and checks

SELECT datname, xact_commit, xact_rollback, deadlocks FROM pg_stat_database WHERE datname = current_database();
SELECT pid, application_name, state, wait_event_type, wait_event, xact_start, backend_xmin FROM pg_stat_activity WHERE state <> 'idle';
SELECT mode, granted, count(*) FROM pg_locks WHERE locktype = 'relation' GROUP BY mode, granted ORDER BY mode;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SHOW transaction_isolation;
SELECT pg_current_xact_id_if_assigned(), pg_snapshot_xmin(pg_current_snapshot());
SELECT datname, conflicts FROM pg_stat_database_conflicts WHERE datname = current_database();
SELECT pg_xact_status(pg_current_xact_id());