Database deadlock detection and prevention

Multiple transactions block each other in a cycle, causing deadlocks. The database detects and kills one transaction, but it keeps happening under load.

(1) Always acquire locks in a consistent order — if two transactions lock tables A then B, never lock B then A. (2) Keep transactions short — do computation outside the transaction. (3) Use SELECT FOR UPDATE NOWAIT or SKIP LOCKED to fail fast or skip locked rows. (4) In application code: catch deadlock errors and retry the transaction (with backoff). (5) Reduce lock scope: use row-level locks instead of table locks. (6) Use SERIALIZABLE isolation only when necessary — READ COMMITTED with explicit locking is usually sufficient.

Deadlocks occur when two transactions hold locks the other needs. Databases detect this by analyzing the wait-for graph and aborting one transaction as the victim.