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?


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.
  • 4. Acquire resources and perform update/delete/insert patterns in the same order consistently throughout the application.
    – ErikE
    Feb 26 '12 at 18:59
  • 3
    @ErikE it is frequently not possible/feasible to "perform update/delete/insert patterns in the same order consistently throughout the application", although this questionable advice is very popular on the Web. Details here: sqlblog.com/blogs/alexander_kuznetsov/archive/2010/01/15/…
    – A-K
    Feb 26 '12 at 23:26
  • 1
    Good points. But I still think the effort is worthwhile, as long as one is under no illusion that it will always be possible or always fix the problem. The parent/child thing is interesting, how about cascade deletes or acquiring an update lock on the parent rows first? And if you do merges without MERGE, why not be consistent? I do delete->update->insert personally.
    – ErikE
    Feb 26 '12 at 23:58
  • 1
    @AlexKuznetsov: It isn't a solver bullet but shouldn't be dismissed. I have reduced (not eliminated) deadlocks this way though: via static analysis of frequently run code that did deadlock every day or 7. I'd suggest "premature optimisation" applies etc
    – gbn
    Feb 27 '12 at 8:28
  • I disagree with recommendation number 3. When we retry after deadlocks, we are very likely to overwrite other processes' changes. We need to be aware that very likely someone else modified the data we intended to modify. Especially if all the readers run under snapshot isolation, then readers cannot be involved in deadlocks, which means that all the parties involved in a deadlock are writers, modified or attempted to modify the same data. If we just catch the exception and automatically retry, we can overwrite someone else's changes. This is called lost updates, and this is usually wrong.
    – A-K
    Feb 27 '12 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.