What A Deadlock Is
A deadlock happens when transactions wait on each other in a cycle. Transaction one holds lock A and wants lock B, while transaction two holds lock B and wants lock A. Neither can proceed, so they would wait forever without intervention.
Detecting The Cycle
- The database tracks a wait for graph where an edge means one transaction waits on another.
- A deadlock is exactly a cycle in that graph.
- The engine periodically scans for cycles and acts when it finds one.
Breaking It
- The database picks a victim transaction and aborts it, releasing its locks.
- The victim is usually the one that is cheapest to roll back.
- The aborted transaction gets an error and is expected to retry.
Reducing Them
- Always acquire locks in a consistent order across transactions.
- Keep transactions short to shrink the lock window.
Key idea
A deadlock is a cycle in the wait for graph, and databases detect it then abort the cheapest victim to break the cycle, while consistent lock ordering helps prevent it.