Database Deadlock

A database deadlock is a cycle of transactions waiting for resources held by one another. If A holds row 1 and waits for row 2 while B holds row 2 and waits for row 1, neither can proceed on its own. A single transaction waiting behind another is blocking, but not necessarily a deadlock.

PostgreSQL detects the cycle and aborts a victim transaction; do not depend on which one it selects. Finish the failed transaction state with ROLLBACK as appropriate, then retry the whole unit of work. The victim’s earlier writes were rolled back too, so retrying only the statement that failed can omit required work.

Acquiring locks in the same order can prevent this particular cycle. Short transactions and bounded retries reduce impact, but foreign keys and other resources can introduce different cycles. Diagnose the actual blockers, for example with pg_blocking_pids, instead of assuming every slow query needs an index. A lock timeout bounds waiting and may raise an error even when no cycle exists.

Reference: Official documentation. See the worked examples in Relational Database Internals.