How are deadlocks detected and reported in an RDBMS

deadlock

I was given this essay type question during an interview but did not get the job. The full question was as follows:

How are deadlocks detected and reported in an RDBMS? What are the transaction owner and application developer responsible for ensuring in both detection and prevention scenarios?

Best Answer

In SQL Server there is a separate thread that periodically (default 5 seconds, lower interval if a deadlock has just been detected) checks a list of waits for any cycles. I.e. it identifies the resource a thread is waiting for, then it finds the owner of that resource and recursively finds which resource that thread is in turn waiting for, thereby identifying threads that are waiting for each others resources.

If a deadlock is found then a victim is chosen to be killed using this algorithm:

  1. Identify threads that are not unkillable (e.g. a thread that is rolling back a transaction is unkillable).
  2. Find the thread with the lowest deadlock priority.
  3. Chose the one that is cheapest to roll back, i.e. the one that has done the least work so far.

You can find more information about SQL Servers deadlock detection here: http://msdn.microsoft.com/en-us/library/ms178104.aspx



The transaction owner/application developer is responsible for minimizing the risks of deadlocks occurring, and to do that they should:

  1. Make sure to keep transactions as short as possible. E.g. don't show a login form after starting a transaction and wait for user input, instead gather all the info you need and then run the transaction.
  2. Use the lowest possible isolation level, e.g. don't set serializable when you just want to temporarily show some values to the user. Please note that setting correct isolation level is a science in itself and out of scope in this answer.
  3. If you are the victim of a deadlock, i.e. you get error# 1205, then re-run your transaction transparently to your user. Since the other, competing, transaction has now hopefully acquired the resources it was waiting for and finished, it is unlikey that you will encounter the same deadlock again.
Related Question