How to detect and fix deadlocks in PostgreSQL

Deadlocks

A deadlock occurs when two transactions each hold a lock the other needs. PostgreSQL detects deadlocks automatically and aborts one transaction.

Deadlock error

ERROR:  deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678;
        blocked by process 5678.
        Process 5678 waits for ShareLock on transaction 1234;
        blocked by process 1234.
HINT: See server log for query details.

Prevent deadlocks with consistent lock ordering

-- Always lock in the same order across transactions
-- BAD: TX1 locks account 1 then 2; TX2 locks account 2 then 1

-- GOOD: always lock by ascending ID
SELECT * FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

Find current locks

SELECT pid, relation::regclass, mode, granted
FROM   pg_locks
WHERE  NOT granted;

Kill a blocking query

SELECT pg_cancel_backend(pid);   -- graceful
SELECT pg_terminate_backend(pid); -- forceful

Set deadlock_timeout = '1s' in postgresql.conf to control how long PostgreSQL waits before running deadlock detection. Application code should always handle deadlock errors with a retry loop.