A deadlock isn't a bug in your locking code — it's an emergent property of multiple transactions acquiring locks in different orders. Transaction A holds lock on row 1 and wants row 2. Transaction B holds lock on row 2 and wants row 1. Both wait forever. Your timeouts will eventually fire, but by then you've burned connection pool slots and angered users.

The wait-for graph. Every lock manager that detects deadlocks builds the same data structure: a directed graph where nodes are transactions and edges point from "waiter" to "holder." If T1 waits for a lock held by T2, draw an edge T1 → T2. A cycle in this graph means a deadlock. Detection is just cycle detection — DFS with a visited set, O(V+E).

Detection vs. prevention. Two camps:

• Detect and resolve: Let deadlocks happen, scan the wait-for graph periodically, kill the cheapest victim. PostgreSQL does this every deadlock_timeout (default 1 second). MySQL InnoDB does it on every lock wait.
• Prevent via ordering: Force all transactions to acquire locks in a canonical order (e.g., by primary key ascending). No cycles possible. Requires discipline in application code — easy to break.

Real-world example. A payments service processes transfers: debit account A, credit account B. Two concurrent transfers — one from Alice→Bob, one from Bob→Alice — will deadlock half the time if you lock rows in the order they appear in the request. Fix: always lock accounts in ascending account-ID order, regardless of debit/credit direction. The cycle becomes impossible because every transaction walks the same path.

Victim selection matters. When the detector finds a cycle, it picks one transaction to abort. Naive choice: youngest transaction (least work lost). Better: the one holding the fewest locks (least disruption when rolled back). PostgreSQL aborts the transaction that completes the cycle — whichever one's lock request triggered the check.

Rule of thumb. If you're seeing >1 deadlock per 1,000 transactions, it's not bad luck — it's an access-order problem. Profile which two locks are involved (PostgreSQL logs both transactions and their queries), then enforce ordering at the application layer. If deadlocks are rare (<1 per 100K), retry with backoff and move on.

The trap: distributed deadlocks across services. No single lock manager sees the whole graph. You need either timeouts (cheap, lossy) or a global coordinator (expensive, complex). Most teams pick timeouts and pretend the rare lost transaction was a network blip.