Retrying a 40001: Everything the Second Attempt Must Forget

A retry loop that re-runs the failed transaction and re-reads everything finishes 342 operations and loses none. The same loop, changed in one place so that the second attempt reuses the value the first attempt already read, finishes exactly the same 342 operations — and 148 of them are wrong. No error is raised. No log line appears. The success rate is identical.

SQLSTATE 40001 is the serialization failure: the code Postgres returns when it will not let a transaction commit because the result would not correspond to any serial order of the transactions involved. You get it at REPEATABLE READ and SERIALIZABLE, described in isolation levels, and the documentation's instruction is one sentence long:

"When an application receives this error message, it should abort the current transaction and retry the whole transaction from the beginning. The second time through, the transaction will see the previously-committed change as part of its initial view of the database, so there is no logical conflict in using the new version of the row as the starting point for the new transaction's update."

Read the second sentence again, because it is the load-bearing one. The retry is safe because it gets a new initial view of the database. That is the entire mechanism. A retry that carries any value forward from the failed attempt has kept a piece of the old view, and the guarantee is void for exactly that value — quietly, because nothing checks.

The panel below is a workload plus a retry loop. Eight clients each run a read-modify-write transaction against a small number of hot rows: read a counter, spend some time in application code, write the counter back at one more than what they read. The retry policy is yours. So is the isolation level, and so is the one detail that decides whether the loop is correct.

Start at the defaults and read the two big numbers. Then untick the retry re-reads.

At the defaults — REPEATABLE READ, eight clients, two hot rows, three retries with exponential backoff — the loop completes 342 operations, absorbs 995 serialization failures, gives up on 192, and burns 3.93 attempts for every success. The counter and the number of successes agree exactly: nothing was lost.

Now untick the retry re-reads. The throughput is unchanged, 342. The error count is unchanged, 995. The silently wrong readout goes from 0 to 148: 148 increments that a client believed it had applied are not in the counter. Every one of those requests returned success. This is the whole lesson in one checkbox, and it is a checkbox because that is how the bug looks in real code — a variable read before the try block instead of inside it.

Then set the isolation level to READ COMMITTED. Throughput quadruples to 1,555, the error count drops to zero, the retry loop never runs, and 1,205 increments are lost. The fastest configuration is the wrongest one, and it is the default isolation level. Serialization failures are not a cost you are paying for nothing — they are the sound of a conflict being detected instead of being applied.

What the session looks like after the error

The first thing to fix is smaller than the retry logic. When a statement raises 40001, the transaction is over — not paused, not partially failed. The connection is in a failed transaction block, and every command you send it now answers with SQLSTATE 25P02, in_failed_sql_transaction, whose message text is one of the most-searched strings in Postgres: current transaction is aborted, commands ignored until end of transaction block.

Click through what a session does after the error. Each line is one statement sent to the server. Watch the prompt as well as the output: psql writes postgres=# outside a transaction, postgres=*# inside one, and postgres=!# inside one that has failed. That exclamation mark is the state your driver is in and your code cannot see.

The savepoint option is the one that catches people, because savepoints rescue a session from every other error. They do not help here, and the reason is not that Postgres refuses — it is that a savepoint does not give you a new snapshot. At REPEATABLE READ and SERIALIZABLE the snapshot belongs to the top-level transaction and is taken once. Roll back to a savepoint and you are still reasoning from the same frozen view of the database, so the statement that conflicted will conflict again. Only ROLLBACK followed by a new BEGIN produces the "new initial view of the database" the documentation's justification rests on.

This is also why the retry has to sit outside your transaction helper rather than inside it. If your framework's with transaction(): block catches the error, retries the body, and only then commits, it has retried inside a transaction that is already dead. The loop must own the BEGIN.

Two errors, one SQLSTATE

40001 arrives with one of two message texts, and they mean different things. Grep your logs for both, separately, because the fix differs.

could not serialize access due to concurrent update is a write conflict. Your transaction tried to modify a row version that was superseded after your snapshot was taken. It comes from the TM_Updated branch of the executor, guarded by IsolationUsesXactSnapshot() — true at REPEATABLE READ and SERIALIZABLE, false at READ COMMITTED, which is why the same workload produces zero errors and silent data loss one level down. Two transactions had to touch the same row. The fix is to touch fewer rows in common: shard the counter, move the write later in the transaction, or hold the row explicitly with SELECT FOR UPDATE and wait instead of failing.

could not serialize access due to read/write dependencies among transactions is a Serializable Snapshot Isolation abort — SSI, the algorithm that makes SERIALIZABLE non-blocking by tracking which transactions read what and aborting cycles after the fact. It only happens at SERIALIZABLE, and the two transactions involved need not have touched a single row in common. Switch the panel's scenario to the on-call roster and its level to SERIALIZABLE to produce them: each transaction reads two rows and writes a different one, so no write conflict exists to detect.

The rule is written out in the comment above OnConflict_CheckForSerializationFailure in src/backend/storage/lmgr/predicate.c:

/*
 * A serialization failure can only occur if there is a dangerous structure
 * in the dependency graph:
 *
 *		Tin ------> Tpivot ------> Tout
 *			  rw			 rw
 *
 * Furthermore, Tout must commit first.
 */

An rw edge from A to B means A read something B wrote, and A could not see the write — A comes before B in any serial order that could explain A's reads. Two of those in a row, pointing through the same transaction, with the far end already committed, is an anomaly that has already happened; the middle one is the pivot, and one of the three must be rolled back.

Two consequences the panel makes visible. First, the check runs "as we are about to add a RW-edge to the dependency graph" — not at commit — so a SERIALIZABLE transaction can be aborted in the middle of a statement it has not finished, before it ever sends COMMIT. Second, because the abort is about the graph rather than about rows, the transaction that dies is often not the one that did anything unusual. Every one of these errors carries an errdetail naming which of predicate.c's nine reason codes fired — for example Reason code: Canceled on identification as a pivot, during conflict out checking. — and an errhint that reads, verbatim, The transaction might succeed if retried. Log the detail line, not just the message: the message is the same for all nine, and the reason code is the only thing that tells you which position in the graph your transaction occupied.

There is one more surprise here worth stating plainly, because it contradicts a line people remember from the documentation. Under REPEATABLE READ, read-only transactions genuinely never fail — the documentation says so outright: only updating transactions might need to be retried; read-only transactions will never have serialization conflicts, and that sentence sits in the Repeatable Read section. Under SERIALIZABLE it stops being true. A read-only transaction can be flagged doomed by another backend that found it in a dangerous structure, and it then discovers this the next time it reads a modified tuple: CheckForSerializableConflictOutNeeded tests SxactIsDoomed(MySerializableXact) before every such read and raises 40001 with Reason code: Canceled on identification as a pivot, during conflict out checking. A second path fires when the conflicting transaction has been aged out to SLRU storage, giving Canceled on conflict out to old pivot. Both happen inside a SELECT, in a transaction that has written nothing. The escape hatch is a declaration, not a retry loop:

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;

Such a transaction "may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a SERIALIZABLE transaction and without any risk of contributing to or being canceled by a serialization failure." It waits for a snapshot that is provably safe. For a nightly report against a SERIALIZABLE workload, that is the right answer and no retry loop is needed at all.

The loop is not free

Set the panel to eight clients and note the numbers: 342 operations, 3.93 attempts each, 39.8 server-seconds of work thrown away. Now drag client sessions to 16. Operations go to 363 — six per cent more — while attempts per success climb to 7.23 and wasted server work reaches 90.2 seconds inside an eight-second window. Set backoff to none as well and it becomes 106.9 seconds, with 600 operations abandoned instead of 481.

That shape is not specific to this model. It is what optimistic concurrency control does past its saturation point: each additional client lowers everyone's chance of finishing, so the same amount of useful work is achieved by more attempts, and the extra attempts are themselves the contention. The documentation's advice for SERIALIZABLE leads with it — "Control the number of active connections, using a connection pool if needed" — and the reason a pool helps is precisely this: it caps the number of transactions that can be in flight against the same rows.

The retry budget is a different trade. With zero retries, 1,172 operations are abandoned and p99 latency is 40 ms. With three, 192 are abandoned and p99 is 198 ms. With eight, only 12 are abandoned and p99 is 1,323 ms. Every retry you add converts a fast failure into a slow success, and there is no setting that gives you both. Choose it from the caller's timeout, not from a sense of thoroughness: a retry that completes after the HTTP request has been abandoned is pure cost, and worse, if the transaction has side effects it is a cost the customer can see.

Which is the last property of a correct loop. Tick it emails the customer before COMMIT and watch duplicate emails reach 798 at the default settings. The transaction body runs once per attempt; anything in it that is not a database write happens once per attempt too. This is a real, shipped bug — one production ERP fixed it in a patch whose description reads: the retry was "creating and sending emails until no concurrency issue is found." The rule is that the transaction body must contain only work the database can undo. Queue the email as a row and send it after the commit.

Contention itself is the variable with the most leverage, and it is not in the retry loop at all. Drag hot rows from 2 to 16 with everything else at defaults: operations go from 342 to 1,036 and attempts per success from 3.93 to 1.43. Nothing about the loop changed. The workload stopped colliding.

Where retrying does not help

Three cases, in increasing order of how much time they waste.

A conflict that will recur every time. If two long-running transactions both read a large range and both write into it, retrying reproduces the same structure with the same participants. Postgres's own hint says the transaction might succeed if retried, and that word is doing work. A loop with unbounded retries on a genuinely repeating conflict is an infinite loop with a database bill. Always cap the attempts, and log the give-ups: a rising give-up rate is the signal that the fix is schema-shaped, not loop-shaped.

A false positive from lock promotion. SSI tracks predicate locks at tuple, page and relation granularity, and when the lock table runs short it combines finer locks into coarser ones. A relation-level predicate lock conflicts with any write to that table, so transactions that never overlapped begin failing. The documentation names the levers — max_pred_locks_per_transaction, max_pred_locks_per_relation, max_pred_locks_per_page — and adds the sharper diagnostic: "A sequential scan will always necessitate a relation-level predicate lock." A SERIALIZABLE workload whose failure rate jumped after a table grew past the point where the planner stopped using an index is not a concurrency problem; it is a plan problem wearing a concurrency costume.

An error that is not retryable at all. Retry 40001 and 40P01 (deadlock detected, which needs the identical loop) and nothing else. A unique-violation 23505 will not be fixed by another attempt, and neither will 25P02, which only means your loop is already broken. Match on SQLSTATE, never on message text: the strings are translated when the server runs under a non-English locale, and code that greps for "could not serialize" fails silently on a German-locale server.

Checking it on a real system

Postgres has no counter for serialization failures, which is the first problem. The cheapest proxy is the rollback ratio:

SELECT datname, xact_commit, xact_rollback,
       round(100.0 * xact_rollback / nullif(xact_commit + xact_rollback, 0), 2) AS pct_rollback
FROM pg_stat_database
WHERE datname = current_database();

That moves for every kind of rollback, so pair it with the log. Turn the errors into countable lines and keep the two messages apart:

ALTER SYSTEM SET log_min_error_statement = 'error';
SELECT pg_reload_conf();

-- then, per hour:
--   grep -c 'could not serialize access due to concurrent update'      postgresql.log
--   grep -c 'could not serialize access due to read/write dependencies' postgresql.log
--   grep -c 'deadlock detected'                                        postgresql.log

The ratio between the first two tells you where to look. Mostly the first means specific hot rows and a schema answer. Mostly the second means your transactions read much more widely than they write, and the answer is usually a narrower SELECT or an index that turns a sequential scan into an index scan.

While a SERIALIZABLE workload is running, look at what SSI is tracking:

SELECT locktype, relation::regclass, page, tuple, count(*)
FROM pg_locks
WHERE mode = 'SIReadLock'
GROUP BY 1, 2, 3, 4
ORDER BY count(*) DESC
LIMIT 20;

Rows with locktype = 'tuple' are fine-grained tracking working as intended. Rows with locktype = 'page', and especially 'relation' with page and tuple null, mean promotion has happened on that table and your failure rate now includes transactions that never overlapped.

Finally, instrument your own loop, because the database cannot see it. Three numbers are enough: attempts per successful operation, the give-up rate, and the p99 of total operation latency including retries. The first tells you whether contention is rising. The second tells you whether the retry budget is still adequate. The third is the one your users feel, and it is the one that a bigger retry budget makes worse. A skeleton that has all the properties this lesson argues for:

def run_operation(request):
    deferred = []                       # side effects, collected not performed
    for attempt in range(MAX_ATTEMPTS):
        try:
            with connection.begin():    # BEGIN is inside the loop
                # every read happens here, on this attempt's snapshot
                n = read_counter(request.key)
                deferred = [("email", request.customer)]
                write_counter(request.key, n + 1)
            break                       # committed
        except SerializationFailure as e:      # SQLSTATE 40001 or 40P01 only
            deferred = []                      # nothing from this attempt survives
            if attempt == MAX_ATTEMPTS - 1:
                metrics.gave_up()
                raise
            time.sleep(random.uniform(0, min(BASE * 2 ** attempt, CAP)))
    metrics.attempts(attempt + 1)
    for effect in deferred:             # side effects only after the commit
        perform(effect)

Four properties, all of which the panel above will break if you remove them: BEGIN is inside the loop, so each attempt gets a fresh snapshot. Every read is inside the transaction, so nothing crosses an attempt boundary. The side-effect list is cleared on failure and performed after the commit. And the loop is bounded, with the give-up counted rather than swallowed.

Two directions from here. If the failures you are seeing are 40P01 rather than 40001, the detection mechanism is completely different — a timer, not a graph — and so is what you can do about it: deadlock detection. And if you would rather wait for a row than be told to try again, the other half of this trade-off is SELECT FOR UPDATE, which converts every one of these errors into a queue.