Fix: PostgreSQL Deadlock Detected

!Symptoms

• Application errors: 'ERROR: deadlock detected'
• Transactions fail intermittently under load
• PostgreSQL log shows 'Process X waits for Y on relation Z; blocked by process W'
• Slow queries and connection pool exhaustion during peak traffic

?Root Causes

• Two or more transactions acquiring locks on the same rows in different order
• Long-running transactions holding locks while waiting for other locks
• Bulk UPDATE/DELETE operations conflicting with concurrent transactions
• Missing indexes causing table-level locks instead of row-level locks
• Application logic that opens a transaction and waits for external I/O

#Diagnosis Steps

1. 1Check PostgreSQL logs for the full deadlock detail including the queries involved
2. 2Find current locks: `SELECT * FROM pg_locks WHERE NOT granted;`
3. 3Find blocking queries: `SELECT * FROM pg_stat_activity WHERE pid IN (SELECT pid FROM pg_locks WHERE NOT granted);`
4. 4Check for long-running transactions: `SELECT pid, now() - xact_start, query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY 2 DESC;`
5. 5Enable log_lock_waits in postgresql.conf for future debugging

>Fix

1. 1Ensure all transactions access tables and rows in a consistent order
2. 2Break large UPDATE/DELETE into smaller batches
3. 3Add appropriate indexes to reduce lock scope (row-level vs. table-level)
4. 4Set lock_timeout to fail fast instead of waiting indefinitely: `SET lock_timeout = '5s';`
5. 5Add retry logic in the application for deadlock errors (SQLSTATE 40P01)

*Prevention

• Design transactions to acquire locks in a consistent global order
• Keep transactions as short as possible: no external I/O inside transactions
• Use SELECT ... FOR UPDATE SKIP LOCKED for queue-like patterns
• Monitor deadlock frequency with pg_stat_database.deadlocks
• Enable log_lock_waits and deadlock_timeout for visibility

Related Error Messages