Course contentsShow
← Software Engineering
Lesson 917 of 1,93328. Transactions and ConsistencyFree lesson

Deadlock Detection and Prevention

Recognizing deadlock conditions and strategies to avoid or break circular lock dependencies.

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.