Deadlock Detection & Prevention

A deadlock occurs when two or more transactions hold locks and each waits for a lock held by the other, creating a circular dependency that cannot resolve itself. For example: Transaction A locks row 1 and waits for row 2, while Transaction B locks row 2 and waits for row 1. Neither can proceed. PostgreSQL automatically detects deadlocks (every deadlock_timeout interval, default 1 second), aborts one of the transactions with a deadlock_detected error (SQLSTATE 40P01), and allows the other to proceed.

Prevention is always better than detection. The primary prevention strategy is consistent lock ordering: if all transactions acquire locks on resources in the same deterministic order (e.g., always lock rows by ascending ID), circular dependencies cannot form. Other prevention strategies include: minimizing transaction duration (hold locks for the shortest possible time), using row-level locks instead of table-level locks, acquiring all needed locks upfront with SELECT ... FOR UPDATE rather than incrementally, and using advisory locks for application-level coordination.

When deadlocks do occur, the application must handle them gracefully. Catch the deadlock error and retry the entire transaction (not just the failed statement). Implement retry logic with jittered backoff to prevent both transactions from immediately deadlocking again. Monitor deadlock frequency in PostgreSQL logs (log_lock_waits = on, deadlock_timeout = 1s) and investigate recurring deadlocks -- they indicate a lock ordering problem in application code that should be fixed, not just retried.