Concept
Deadlock
Two or more transactions blocked waiting for locks held by each other, broken by PostgreSQL aborting one of them.
A deadlock occurs when transaction A holds a lock that transaction B needs, while B holds a lock that A needs. Neither can progress. PostgreSQL's deadlock detector wakes up after deadlock_timeout (default 1 second), spots the cycle, and aborts one transaction with SQLSTATE 40P01.
The classic cause is two code paths that update the same rows in different orders. Fix it by establishing a consistent locking order — always touch tables, or rows within a table, in the same sequence. Sorting the IDs you intend to update before issuing the UPDATE is a cheap, reliable trick.
Deadlocks are recoverable; the calling code should catch the error and retry the transaction. Frequent deadlocks indicate a real concurrency design problem, not just an unlucky workload.