40P01 — deadlock_detected

At a glance

40P01 is PostgreSQL’s deadlock_detected condition in Class 40, transaction_rollback. It means that the lock manager found a cycle in which transactions are waiting for one another, so PostgreSQL chose a victim and aborted its transaction.

The primary message is deadlock detected. The server may add a dynamically assembled wait graph in DETAIL, a hint to consult the server log, and a context naming the statement that was interrupted. Process IDs, transaction IDs, and the exact wait graph are run-specific; branch on SQLSTATE and retain the structured fields rather than matching those values.

The representative case first lets two sessions hold opposite row locks, then uses a threading.Barrier to release both cross-row requests while an independent observer samples pg_stat_activity for wait_event_type=Lock. The observer does not release the requests. After one session receives 40P01, its transaction is INERROR; the surviving session completes, and both sessions are then IDLE. A fresh transaction takes both row locks in order and commits the complete retry.

Run 40P01-manual-boundary-final-20260909 passed on PostgreSQL 18.6 and isolated PostgreSQL 10.21. Per-target assertions and structured observations are retained in the public evidence JSON.

Meaning and trigger paths

A deadlock requires a cycle in the wait-for graph. A common form is session A locking row 1 and requesting row 2 while session B locks row 2 and requests row 1. PostgreSQL’s deadlock detector examines the lock graph after the configured deadlock timeout and reports the condition from DeadLockReport.

The error identifies a transaction coordination failure, not a damaged row. The victim’s whole transaction is aborted. The other transaction may continue after the detector breaks the cycle, but its successful outcome does not automatically mean the victim’s intended work was applied.

40P01 is distinct from a lock wait that eventually succeeds and from 57014 caused by a statement timeout. The observed pg_stat_activity wait is evidence of the cycle’s setup; the server’s deadlock detected response is the decisive condition.

Messages and diagnostics

The following schedule is the representative operation. Run the session A and B sections on separate connections; each session first holds one row, the barrier then releases both cross-row requests, and an independent observer samples both backends while the requests wait. The observer is not the release condition.

CREATE TABLE locks(id integer PRIMARY KEY, marker text NOT NULL);
INSERT INTO locks VALUES (1, 'seed-1'), (2, 'seed-2');

-- Session A: begin, set deadlock_timeout, and lock id = 1.
BEGIN;
SET deadlock_timeout = '100ms';
UPDATE locks SET marker = 'first-1' WHERE id = 1;

-- Session B: begin, set deadlock_timeout, and lock id = 2.
BEGIN;
SET deadlock_timeout = '100ms';
UPDATE locks SET marker = 'second-2' WHERE id = 2;

-- An explicit barrier releases both requests into the lock cycle.
UPDATE locks SET marker = 'first-2' WHERE id = 2;
UPDATE locks SET marker = 'second-1' WHERE id = 1;

-- The victim must ROLLBACK; the survivor can COMMIT.
ROLLBACK;
COMMIT;

-- Fresh retry connection: take locks in the same order and verify both rows.
BEGIN;
UPDATE locks SET marker = 'retry-1' WHERE id = 1;
UPDATE locks SET marker = 'retry-2' WHERE id = 2;
COMMIT;
SELECT id, marker FROM locks ORDER BY id;

PostgreSQL 18.6 returned this shape for the victim:

SQLSTATE: 40P01
severity: ERROR
message_primary: deadlock detected
message_detail: Process <pid-a> waits for ShareLock on transaction <xid-b>; blocked by process <pid-b>.
Process <pid-b> waits for ShareLock on transaction <xid-a>; blocked by process <pid-a>.
message_hint: See server log for query details.
context: while updating tuple (0,2) in relation "locks"
source: deadlock.c / DeadLockReport / line 1138

The PG10 run produced the same primary message and fields, with a version-specific source line. The detail is assembled from the live wait graph, so its process and transaction identifiers are intentionally represented as run-specific values here. The hint is not a claim that the client always receives a server log entry; it directs investigation to that log when configured.

Diagnosis

Record SQLSTATE, severity, primary message, detail, hint, context, the failed statement, backend PIDs, and transaction status. Inspect pg_stat_activity and relevant lock views while the wait is active. A useful observation includes wait_event_type=Lock, the wait event, the current query, and the participating PIDs; a sleep by itself cannot establish a deadlock.

Build the lock order from the actual application code and all paths that can touch the same rows or advisory locks. Check whether a trigger, foreign key, index, or background worker acquired an additional lock. deadlock_timeout controls when PostgreSQL runs detection; increasing it changes detection latency and does not remove the cycle.

After the error, the victim connection is not ready for unrelated commands: it is INERROR until ROLLBACK. The survivor may be INTRANS until its commit. The runner observed both return to IDLE after explicit cleanup and verified the rows before a retry.

Response and repair

Rollback the victim transaction and release its locks. Then choose a complete repair:

• Make every code path acquire the same set of locks in a consistent order, preferably by ordering keys explicitly.
• Keep the transaction short and avoid waiting for external work while holding database locks.
• Retry the whole transaction, including reads and lock acquisition, only when the operation is safe to repeat and its result is idempotent.
• Verify the committed business result after the retry; a survivor’s partial update is not proof that the victim’s work succeeded.

The representative repair took row 1 and row 2 in order, committed retry-1 and retry-2, and read both rows with the connection IDLE. A bounded retry budget and an application-level idempotency key are still needed in production; the SQLSTATE alone cannot decide whether repeating an operation is safe.

Versions and boundaries

The catalogue has a definition-presence observation for 40P01 at PostgreSQL 7.4 and through the locked 8.4.22 pre-9.0 definitions, then in every listed formal snapshot through PostgreSQL 18.6 and the PostgreSQL 19 Beta 3 preview. This is a definition-only presence boundary, not an exact implementation introduction or runtime-use claim. The class title changed between the 9.0 header view and the 9.1 text definition; that catalogue observation does not establish a code introduction date. The condition row itself has no recorded definition change in the scanned range.

The two-session case passed on PostgreSQL 18.6 and 10.21. Detector timing, lock types, and detail text depend on the workload and settings; the compatibility contract used here is SQLSTATE plus the transaction rollback requirement, not a fixed process-ID detail string.

Related

40001 — serialization_failure also requires a complete transaction retry from a fresh snapshot. 57014 — query_canceled reports cancellation rather than a lock cycle. 23503 — foreign_key_violation and 23505 — unique_violation are integrity conditions that can occur inside a transaction whose lock ordering needs review. 25P02 — in_failed_sql_transaction is the follow-on state after the victim’s transaction is left aborted.

Sources

Structured evidence is recorded in the public evidence JSON. All source records use PostgreSQL commit 724edf9bde9d356724ad384a2e196edc3c9f80f7; run records retain exact target IDs and structured observations.

• src.errcodes.18.6 — errcodes.txt
• src.deadlock.18.6 — deadlock.c
• doc.mvcc.18 — Deadlocks and serialization failures
• Runtime: 40P01-manual-boundary-final-20260909 on latest and pg10; structured observations are in the public evidence JSON