Deadlock rate spiking — finding and eliminating the cause

Application error logs show ERROR: deadlock detected, and the deadlocks counter in pg_stat_database is climbing.

Diagnose it

-- Deadlock count by database (cumulative since last reset):
SELECT datname,
       deadlocks,
       xact_commit,
       round(1000.0 * deadlocks / NULLIF(xact_commit, 0), 3)
           AS deadlocks_per_1k_txn
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY deadlocks DESC;

For the full deadlock detail (which relations and which PIDs were involved), enable
log_lock_waits = on and check the PostgreSQL log for the
DETAIL: Process N waits for ... blocked by Process M lines that accompany every
deadlock log entry.

Why it happens

A deadlock occurs when two (or more) transactions each hold a lock that the other needs,
creating a cycle. PostgreSQL detects the cycle after deadlock_timeout (default
1 second) and rolls back one transaction. The most common cause is inconsistent lock ordering
across transactions — e.g., transaction A locks row 1 then row 2, while transaction B locks
row 2 then row 1.