8

I have a few production SQL Server instances and I get around 100 deadlock alert emails a day (up to 100 for one server, 150 in total).

Is this a problem I should investigate? How many deadlocks are problematic and what can be the possible consequence of ignoring this issue?

4
  • 2
    I'd be worried if there was one deadlock a day - it's an application design issue/flaw
    – Philᵀᴹ
    Feb 4, 2015 at 15:12
  • hopefully you are not feeding philosophers. Feb 4, 2015 at 19:33
  • The app devs should already know about this. Surely they have error reporting in their app.
    – usr
    Feb 4, 2015 at 21:26
  • 1
    The question was put on hold. I understand that it might be based on opinion but in fact what I'm looking for is some advice from experienced and conscious DBA. I would like to know how severe this problem can be. I'm not asking what does the error number X mean or why did it happen. I'm asking about its severity - a matter that can not be answered purely based on facts or theories.
    – BuahahaXD
    Feb 5, 2015 at 7:00

4 Answers 4

18

It's typically not the count of deadlocks that can cause a serious problem, but the reason to investigate would be to find out what workloads are deadlocking on each other. After all, in a deadlock there is a victim. So now you have a process that didn't complete the work that it intended to complete. And, in good theory applications should have some sort of retry logic in them for situations like this... but in practice this almost never happens.

With that being said, investigate what is deadlocking. Typically it is the same workload or type of workload that generates the majority of deadlocks. A few quick fixes (after reading the deadlock XML) could be an easy win to get rid of most of them.

(yes, 100+ deadlocks a day is a lot. But any recurring deadlocking pattern, whether it is 1 or 100, should be investigated)

3

Deadlock is quite a heavy operation from performance point of view because it takes some time to figure out that there is a deadlock, choose which one to kill, roll back all the changes it has made -- and most likely then try to do the operation again. Also the end users might get frustrated especially if they get error messages they don't understand or have to do certain actions again, but this of course depends on what you're working with.

At least I would personally be worried if there's over 100 deadlocks per day and try to solve the issue before it gets worse, but like everything, it of course depends.

2

What does it cost your stakeholders ? What will it cost to "fix" ?

Deadlocks will happen. We design as much as possible to eliminate them. A timeout is not quite the same thing, as we may need to perform some maintenance that will involve an extended lock.

The impact will depend upon the type of transaction. Batch processes and asynchronous processes should be tolerant with little or no impact (continuous deadlocks may require transactions to be serialised or a redesign). Online transactions will generally not be as tolerant.

The effort required to roll back an online deadlock is generally tolerable compared to rolling back a batch process with limited commit points.

The real impact is what has to happen to the failed transactions ? Is your application capable of tolerating these or is a manual process required. Think in dollars.

I would suggest you receive one email, with a daily summary. This will allow a clearer picture. 100 is a worry. Then look at the transactions, tables should be hit in the same order, from "outside" to "in" (where the parent tables are "inside"). There are a number of strategies, without seeing your transactions, that spring to mind. Avoid hotspots, defaulted values in an index can be hashed etc. Take suggestions to your users.

But seriously, one email, and get your graphs on. If this is a hardware issue best to catch it as early as possible.

0

Yes Deadlock is a serious problem and it should be dealt accordingly as Deadlocks are sometimes called a deadly embrace.

Deadlocks can kill an application’s performance. Users will complain about the app being slow or broken. Developers will ask the DBA to fix the problem, DBAs will push the problem back on developers.

Count cannot tell you the impact, deadlock itself is deadly!

Please use sql profiler trace to find the cause and to find more on how to minimize and fix this issue, you can refer to below link:

https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/

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