deadlock_detected Deadlock Detected — SQLSTATE 40P01

Two or more transactions blocked each other and PostgreSQL aborted one to break the deadlock.

• The error is written to the server log and returned to the client carrying SQLSTATE 40P01.
• Any driver (libpq, JDBC, psycopg, npgsql, pgx) surfaces this code in its error object so you can branch on it programmatically.
• PL/pgSQL can trap it by name: EXCEPTION WHEN deadlock_detected THEN.

Severity: ERROR  |  PostgreSQL versions: 12, 13, 14, 15, 16, 17

Reproduce with the exact statement and read the full message in the server log (raise log_min_messages / set log_min_error_statement for more context).

The deadlock detector found a cycle in lock waits and canceled the cheapest victim transaction.

Common causes:

• Transactions acquiring the same rows or tables in different orders.
• Missing indexes on foreign keys causing wider locks.
• Long transactions holding locks while waiting.

Steps to resolve 40P01:

1. Acquire locks in a consistent order across every code path.
2. Add indexes on FK columns so referential checks take row locks rather than broad waits.
3. Keep transactions short and touch rows in a deterministic order.
4. Catch 40P01 and retry the transaction.
5. Read the deadlock detail in the log — it lists the conflicting statements.

Reference: PostgreSQL error codes — Class 40 (Transaction Rollback).