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.

Copyable operational controls

Related engineering templates

PostgreSQL Incident Triage Checklist
Engineering signal

Follow PostgreSQL failures and fixes

A useful digest about twice a week with new error references, tools, integration notes and production lessons. No daily noise.