Debug: PostgreSQL deadlock detected

PostgreSQL throws 'deadlock detected' error when concurrent transactions wait for each other's locks.

Diagnosis and prevention:

1. Read the error message:

ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678
# PostgreSQL logs the queries involved

1. Check current locks:

SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.query AS blocked_query,
blocking_locks.pid AS blocking_pid,
blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.relation = blocked_locks.relation
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

1. Prevention strategies:

a) Always acquire locks in the same order:
-- BAD: TX1 locks A then B, TX2 locks B then A
-- GOOD: Both lock A first, then B

b) Keep transactions short:
-- Don't hold locks while doing external calls

c) Use SELECT ... FOR UPDATE SKIP LOCKED:
-- Skip already-locked rows instead of waiting

d) Use advisory locks for application-level locking:
SELECT pg_advisory_lock(hashtext('order:123'));

e) Set lock_timeout to fail fast:
SET lock_timeout = '5s';