Two (or more) transactions each hold a lock the other one needs, so they wait forever.
Tx-1 Tx-2
----- -----
lock row A
lock row B
lock row B ← waits… (Tx-2 owns B)
lock row A ← waits… (Tx-1 owns A)
Both are stuck → deadlock.
Anytime transactions lock multiple rows / tables in different orders.
Example: Tx-1 locks “Alice”, then “Bob”; Tx-2 locks “Bob”, then “Alice”.
Long-running transactions that keep locks for a while.
High-contention hotspots (popular rows, counters, inventory records, etc.).
Automatic detection & kill
Most OLTP engines (PostgreSQL, MySQL /InnoDB, SQL Server, Oracle) watch for wait-cycles.
When found, they abort (“rollback”) one victim transaction.
➜ You must retry aborted transactions in app code.
Timeout
Some engines simply give up after N seconds of waiting and return an error.
Same outcome: you retry.
| Trick | Why it helps |
|---|---|
| Keep transactions short. | Locks are held for less time → fewer clashes. |
| Lock objects in a consistent order everywhere in the code. | If everyone grabs A then B, you can’t get the A-then-B vs. B-then-A circle. |
| Use the smallest lock you can. | Row-level beats table-level; reduces overlap. |
Rely on built-in atomic operations or UPDATE … SET x = x + 1. |
Let the DB do a single lock instead of your read-modify-write cycle. |
Prefer optimistic methods (snapshot isolation, SELECT … FOR UPDATE SKIP LOCKED, etc.) when possible. |
Fewer long-held locks → fewer deadlock cycles. |
| Back off & retry with jitter when you do get a deadlock error. | Spreads out contention; usually one retry succeeds. |