Deadlock Detection and Prevention
You'll learn how databases identify deadlock conditions and the strategies used to prevent or resolve circular lock dependencies in concurrent transactions.
What Is a Deadlock?
A deadlock occurs when two or more transactions are waiting for each other to release locks, creating a circular dependency where none can proceed. Imagine two drivers arriving at a four-way stop simultaneously, each waiting for the other to go first—nobody moves.
In databases:
- Transaction A holds Lock 1, wants Lock 2
- Transaction B holds Lock 2, wants Lock 1
- Both transactions wait forever (without intervention)
Detection Strategies
Most databases periodically check for cycles in the wait-for graph—a structure tracking which transactions are waiting for which locks. When a cycle is detected, the database selects a "victim" transaction to abort (usually the one with the least work done), freeing its locks so others can proceed.
The aborted transaction typically returns a deadlock error that applications must handle by retrying.
Prevention Strategies
Lock ordering: Acquire locks in a consistent order across all transactions. If every transaction always locks tables/rows in alphabetical order, circular waits become impossible.
Lock timeouts: Set maximum wait times. If a transaction can't acquire a lock within the timeout, it aborts rather than waiting indefinitely.
Reducing lock duration: Keep transactions short and release locks quickly to minimize overlap windows.
Deadlock-free protocols: Use timestamp-ordering or wound-wait schemes where transactions have priorities, preventing circular dependencies by design.
Key Takeaway:
Deadlocks arise from circular lock dependencies; databases detect them via wait-for graphs and break them by aborting transactions, while prevention relies on consistent lock ordering, timeouts, and minimizing lock contention.