KBY Technologies Logo
SQLSTATE 40P01

PostgreSQL deadlock_detected

PostgreSQL found a cycle of sessions waiting on one another and aborted one transaction to break it.

PostgreSQL lock managerConcurrencyhigh
Exact message
ERROR: deadlock detected

Also searched as: deadlock_detected

What it means

At least two transactions acquired incompatible locks in different orders. PostgreSQL resolves the cycle by cancelling one participant; the application must roll back that transaction and may retry the whole unit of work.

Typical trigger conditions

  • Updating shared rows in inconsistent order
  • Long transactions holding locks while performing external work
  • DDL competing with application traffic
  • Hidden locks introduced by foreign keys or triggers
Evidence before intervention

First diagnostic checks

1

Capture the server detail

The error detail names the processes and lock types involved; retain it before logs rotate.

2

Inspect current blockers

Use pg_blocking_pids to map blocked sessions to blockers.

SELECT pid, pg_blocking_pids(pid), query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;
3

Compare transaction order

Trace the statements in each transaction and make lock acquisition order consistent.

Version and platform notes

The SQLSTATE is stable across supported PostgreSQL releases; available lock observability columns differ on older versions.

Engineering signal

Follow PostgreSQL failures and fixes

One useful weekly email with new error references, tools, integration notes and production lessons. No daily noise.