How can we handle DEADLOCK

SQL Server Administration
1

HI Experts,

Please let me know how we can handle the deadlock.

In my organization, Employees are using one application in same time. Due to that dead lock is happening. So please suggest me how to handle.If i am using nolock in SP, it will affect the report because database will refresh every single minute.

Thanks ,
Paramasivan B

2

If you can make the deadlock happen, follow the process found here:

https://msdn.microsoft.com/en-us/library/ms188246.aspx?f=255&MSPPError=-2147217396

Then you will see the cause of the deadlock. Often it is a case of out-of-order DML between two queries. That is usually not so hard to fix. First though, find the offending queries. Profiler is one way to do that!

3

First, and to be sure, I'm not trying to be a contrarian here. Out-of-Order DML between two queries can certainly be a problem. I've just personally not found it to be the primary problem.

95% of all the deadlocks problems that I've repaired have simply been due to either inappropriate use of explicit transactions or just junk code that used too many resources and took too long. "Too long" can be surprisingly short on an active system and needs to be fixed even though you may initially be unsure that it can be fixed (and go for it because it can be fixed).

The DML doesn't need to be "Out-of-Order" to cause a deadlock, either. The worst deadlock problem I ever fixed was a SELECT from a table followed by an UPDATE to the table. Both actions were to the same single row (very properly indexed table, BTW) and was lightning fast but both actions were within an explicit transaction and the code was called upon very frequently within the same second. The code would deadlock against other copies of the code even though the DML was identical.

I can't speak for anyone else but here's what I've been very successful with when it comes to deadlocks.

  1. First, find them. They show up in the SQL Server logs if you have your startup trace flags setup to capture them. (1204 and 1222).
  2. Realize that WITH (NOLOCK) does NOT prevent deadlocks.
  3. Rewrite the code to do any validations, lookups, other heavy lifting, and get the data you want to insert and/or update properly staged before you even think of starting a transaction. All of that will help to make the transaction shorter and it always needs to be as short as possible.
  4. During the rewrite, write good code. "Good enough" frequently just isn't good enough... especially if it's code generated by an ORM. If you can't write the front end code so that the ORM generates good code, then bite whatever bullet you think you need to bite and write an excellent stored procedure.
  5. During the rewrite and all the pre-work and validation you do, you may actually find that you no longer need an explicit, multi-statement transaction. Get rid of it if you can. If you can't, the Item #4 needs to come to bear in a serious fashion.

I have an old saying... "Performance is in the code". So are the deadlocks. If your code doesn't perform well, then there's more of a chance for deadlocks.

1 Like
4

you need to enable TF 1222 and/1204 to capture deadlock details. Read here: https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/

5

"Method 1: Use the RETRY Command in a catch block of stored procedures when you get ERROR_NUMBER = 1205. When RETRY
Command is used, you need to take care of CommandTimeout for the SP in the calling method(C#.NET, VB.NET).
Method 2: Once Deadlock occurs, you'll see the message below:
Your transaction (process ID # XXXX) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim.
The message should not be shown to the user. Handle it within the method that performs the Query execution. In the Catch block of the calling method, check for the 1205 error message and resubmit the query."

6

Thank you so much guys...