A competing update does not automatically make a PostgreSQL transaction fail. The outcome depends on isolation, statement behavior, and the state another transaction commits. Treating every collision as the same retryable event can conceal invalid input or repeat a decision made from stale data. The useful boundary is a complete application operation whose reads and writes can be attempted again.

When it breaks

Under Read Committed, an ordinary query gets a snapshot for that statement. An update can wait for another updater and then recheck its condition against the updated row. Two increments can therefore both succeed. An application that reads a number separately and later writes a previously calculated absolute value has a different contract: Read Committed does not automatically recompute that client calculation.

Repeatable Read retains a transaction snapshot, established by its first nontransaction-control statement. If a row changed after that snapshot, attempting to update it can raise 40001 immediately. Serializable can also reject dependency patterns involving different rows. Neither level promises that failures occur only at commit, nor does stronger isolation imply a universally earlier failure. Check statement execution and commit, using the driver’s structured error information.

Record the first SQLSTATE alongside the isolation level and operation identifier. A later 25P02 is a consequence of an earlier error, so logging only the final failed query can hide the actual reason the transaction stopped.

Check it quickly

Use PostgreSQL 18 and two fresh psql -X -v ON_ERROR_STOP=1 sessions, A and B, connected to the same disposable database. Run blocks in the stated order; B uses autocommit unless a block explicitly begins a transaction. In A, create the isolated fixture:

CREATE SCHEMA conflict_demo;
CREATE TABLE conflict_demo.counter (id integer PRIMARY KEY,n integer);
INSERT INTO conflict_demo.counter VALUES (1,0);

A begins at Read Committed and reads zero:

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT n FROM conflict_demo.counter WHERE id=1;

B commits an increment and returns one:

UPDATE conflict_demo.counter SET n=n+1 WHERE id=1 RETURNING n;

A now increments the current row and returns two, then commits successfully:

UPDATE conflict_demo.counter SET n=n+1 WHERE id=1 RETURNING n;
COMMIT;

This is a successful concurrent history. A’s earlier read did not freeze the value used by its later arithmetic update. In B, reset the counter after A commits:

UPDATE conflict_demo.counter SET n=0 WHERE id=1;

In A, begin Repeatable Read and establish the snapshot by reading zero:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT n FROM conflict_demo.counter WHERE id=1;

B again commits an increment and returns one:

UPDATE conflict_demo.counter SET n=n+1 WHERE id=1 RETURNING n;

A attempts the same update. Temporarily allow the expected errors so the script can show both states and perform cleanup:

\set ON_ERROR_STOP off
UPDATE conflict_demo.counter SET n=n+1 WHERE id=1;
\echo update_state :SQLSTATE
SELECT n FROM conflict_demo.counter WHERE id=1;
\echo transaction_state :SQLSTATE
\set ON_ERROR_STOP on
ROLLBACK;

The update reports 40001; the subsequent read reports 25P02, meaning the transaction is already failed. No commit was attempted, so this directly disproves a commit-only error model. Restoring the client’s stop-on-error setting does not repair a failed server transaction; rollback does. In A, retry the complete operation after B has finished:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT n FROM conflict_demo.counter WHERE id=1;
UPDATE conflict_demo.counter SET n=n+1 WHERE id=1 RETURNING n;
COMMIT;

The fresh read returns one and the update returns two. Finally, B verifies the committed result and removes the fixture:

SELECT n FROM conflict_demo.counter WHERE id=1;
DROP SCHEMA conflict_demo CASCADE;

The final two includes one committed increment from each participant. The failed attempt contributed nothing. If a result differs, check that both terminals selected the same database, used the intended isolation, and followed the reset and commit boundaries before interpreting it as a server anomaly.

Define the retry boundary

A 40001 response calls for a new transaction and new decisions, including reads used to choose keys or amounts. A 40P01 deadlock may also justify retrying the complete operation. Bound attempts and elapsed time; replay can be expensive and may still fail under sustained contention. Roll back before returning a failed connection to a pool, and preserve the final error when the budget expires.

Do not classify every 23505 uniqueness violation as transient. A duplicate permanent identifier remains invalid after waiting. Some application-generated keys depend on earlier reads, so a particular uniqueness failure can justify recomputation, but that decision needs operation-specific evidence. Likewise, losing the network acknowledgement of commit leaves an unknown outcome: it is different from receiving a server-declared serialization failure. Resolve that ambiguity with an operation identifier and durable result before replaying a payment or other externally visible effect.

Sources