Database deadlocks and how engines resolve them

WHAT THIS TESTS The interviewer checks whether you understand circular resource dependencies and the standard recovery mechanism, distinguishing deadlock from ordinary contention.

A GOOD ANSWER COVERS A deadlock is a circular wait on locks: transaction A holds a lock on row 1 and wants row 2, while transaction B holds row 2 and wants row 1. Neither can proceed because each waits for a resource the other holds, and they would wait forever. This differs from a simple lock wait, where one transaction merely waits for another to commit and then continues. Databases handle deadlocks by running a deadlock detector, typically by finding a cycle in the wait-for graph; when a cycle is found, the engine chooses a victim, often the transaction that is cheapest to roll back or has done the least work, aborts it to release its locks, and returns a deadlock error so the application can retry. Prevention strategies include acquiring locks in a consistent global order across all code paths, keeping transactions short, and lowering lock granularity or isolation where safe.

COMMON WRONG ANSWERS Calling any slow or blocked query a deadlock; a deadlock specifically requires a mutual cycle. Thinking the database hangs forever; modern engines detect and break deadlocks automatically. Believing the application never needs to retry, when the victim's transaction must be re-run.

LIKELY FOLLOW-UPS How does a wait-for graph detect cycles? How does lock ordering prevent deadlocks? What is a lock-wait timeout versus deadlock detection? How do you pick the victim?

ONE CONCRETE EXAMPLE Two bank transfers run at once: one locks account A then tries B, the other locks B then tries A. They deadlock; the engine detects the cycle, rolls back the cheaper transaction, and the application retries it, which then completes once the locks are free.