# 40P01 — deadlock_detected

> PostgreSQL reports SQLSTATE 40P01 when transactions form a lock cycle. Roll back the victim, keep lock order consistent, and retry the complete operation only when its business semantics permit it.
---

## At a glance {#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](../data/evidence/40p01.json).

<!-- BEGIN SQLSTATE FACTS: generated by scripts/generate.py; do not edit -->

| Field | Value |
| --- | --- |
| SQLSTATE | `40P01` |
| Condition | `deadlock_detected` |
| Status | `active` |
| Known present by | `7.4` |
| Locked snapshots | `9.0.23, 9.1.24, 9.2.24, 9.3.25, 9.4.26, 9.5.25, 9.6.24, 10.23, 11.22, 12.22, 13.23, 14.24, 15.19, 16.15, 17.11, 18.6, 19beta3` |
| Macros | `ERRCODE_T_R_DEADLOCK_DETECTED` |
| Aliases | `—` |

<!-- source facts: data/errcodes/40P01.json -->
<!-- END SQLSTATE FACTS -->

## Meaning and trigger paths {#meaning}

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 {#messages}

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.

<!-- BEGIN SQLSTATE SNIPPET: two_session_lock_cycle -->
```sql
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;
```
<!-- END SQLSTATE SNIPPET -->

PostgreSQL 18.6 returned this shape for the victim:

```text
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 {#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 {#response}

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 {#versions}

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 {#related}

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

## Sources {#sources}

Structured evidence is recorded in the [public evidence JSON](../data/evidence/40p01.json). All source records use PostgreSQL commit `724edf9bde9d356724ad384a2e196edc3c9f80f7`; run records retain exact target IDs and structured observations.

- `src.errcodes.18.6` — [`errcodes.txt`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt#L329-L334)
- `src.deadlock.18.6` — [`deadlock.c`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/storage/lmgr/deadlock.c#L1071-L1138)
- `doc.mvcc.18` — [Deadlocks and serialization failures](https://www.postgresql.org/docs/18/mvcc-serialization-failure-handling.html)
- Runtime: `40P01-manual-boundary-final-20260909` on latest and pg10; structured observations are in the [public evidence JSON](../data/evidence/40p01.json)