PostgreSQL 'ERROR: deadlock detected' (Process X waits for ShareLock on transaction Y; blocked by process Z): why does it happen and how do I prevent it?

Cause: Two or more transactions each hold a lock the other needs, so none can proceed. PostgreSQL detects this itself and aborts one of the transactions with 'deadlock detected'; the others continue. A classic case is two transactions updating the same rows in opposite order. Foreign keys, bulk updates without ORDER BY, and explicit LOCK TABLE calls also cause them. Which transaction is aborted is not predictable. Fix (best first): 1. Read the server log: the DETAIL lines list the processes, the locks and the statements involved. This tells you which code paths collide. 2. Make every code path acquire locks in a consistent order, for example update rows sorted by primary key, or use SELECT ... FROM t WHERE id = ANY(...) ORDER BY id FOR UPDATE before updating. 3. Keep transactions short and do not hold them open across network calls or user input, so locks are held briefly. 4. Take the strongest lock you will need first, rather than starting with a weak lock and upgrading later. 5. Make the application retry the whole aborted transaction a few times with a small random delay; the PostgreSQL docs say that when you cannot guarantee ordering, retrying is the way to handle it. 6. For investigation, turn on log_lock_waits; waits longer than deadlock_timeout (default 1s) are logged. Raising deadlock_timeout reduces wasted checks on a busy server but delays deadlock reports. lock_timeout can make a statement give up on a lock wait, but it does not resolve deadlocks. Verify: the 'deadlock detected' entries disappear from the log under the same load, and pg_locks / pg_stat_activity show no long lock waits. Sources: https://www.postgresql.org/docs/current/explicit-locking.html https://www.postgresql.org/docs/current/runtime-config-locks.html