How To Find Sql DeadLock and How to Kill It ?

How to troubleshoot Deadlock in SQL Server 2005

Manoj.B.

Create DeadLock Insident :

Poorly written queries in SQL Server can trigger deadlock in the system. If not common, at times you may need to troubleshoot deadlock issues. In SQL Server 2005, troubleshooting deadlock is much simpler compared to earlier versions. In this article, detection of deadlock in SQL Server 2005 is explained with simulation of deadlock. We all knows cyclic dependency causes dead lock. When SQL Server find a deadlock, it kill one process (deadlock victim) rolls back the transaction of the deadlock victim, and returns a 1205 error to the application. Database engine choose deadlock victim according to the least cost to rollback. Read more about Deadlock in Books online.

Detecting deadlock in SQL Server 2000 was bit complex since you have to switch on the trace flag 1204. Trace flag 1204, returns the type of locks participating in the deadlock and the current command affected. The results are captured in the SQL Server 2005 error log. Apart from this trace flag 1204 feature in SQL Server 2005 , you can trace deadlock using Profiler Deadlock Graph event. This is one of the finest utility since it gives you graphical easy to read display of deadlock.

How to create profiler trace to find deadlock?

(a) Create a new trace, using a Blank template.
(b) Add the Deadlock graph event to the trace from the Locks category. You will get an additional tab appears on the Trace Properties window, called Event Extraction Settings.
(c) Click the Save Deadlock XML Events Separately check box. This causes the deadlock information to be written to a separate file.

If you completed all the above mentioned steps (sl (a) to (c)) , the trace is already running on the server to detect deadlock. No we need to simulate Deadlock situation.

Simulation of Deadlock Scenario

--(a) Open first query analyzer window and run the following script

 
 
Use Test --Database for test in my server
 
--Creating two tables to simulate Deadlock situation
GO
Create table TestDeadLock  (ID int,Name varchar(100))
GO
Create table DeadLock (ID int,Name varchar(100))
GO
 
Insert TestDeadLock Select 1,'Madhu'
Insert TestDeadLock Select 2,'ABC'
Insert TestDeadLock Select 3,'XYZ'
 
Insert DeadLock Select 1,'Madhu'
Insert DeadLock Select 2,'ABC'
Insert DeadLock Select 3,'XYZ'
 
 
-- Dead lock scenario starts
-- Step #1
Begin Tran
UPDATE TestDeadLock SET Name = 'Madhu'
 

--(b) Open another query analyser windows and run the following script

--Step #2 - To be run in second query analyser window
 Begin tran
UPDATE DeadLock SET Name = 'xyz' 
SELECT * FROM TestDeadLock

--(c) Go back to first query analyser window and run the following command

--Step #3 -- To be run in the first query analyser window
Select *From DeadLoack

Basically, here cyclic dependency is been created and system has to choose one of the process as deadlock victim. In my case, system made the Step #2 process (query analyzer window 2) as deadlock victim with the following error

(3 row(s) affected)
Msg 1205, Level 13, State 45, Line 3
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In Profiler would show something like this



In the profiler , the Deadlock graph event contains very useful informations about the process and the sql statements caused the deadlock. The oval nodes shows the processes involved in the deadlock. The oval with an X mark across it is the deadlock victim. The other oval where there is no X mark says that process was allowed to complete after the system killed the deadlock victim process and deadlock resolved. IF you point your mouse on these shapes it will give you more information (see figure



 After Finding the DeadLock Hoe To Kill It:

Kill SQL Deadlock

Deadlocks strangely I’m not talking about management meetings, I’m talking about SQL (Structured Query Language) so here is the deal just like when two managers can not agree on something it can become necessary to stop one so that things can continue normally.

If your unlucky enough not to have any monitoring software you might not even know one has happened until one of two things a lovely blue screen happens to you SQL server or two then you have high CPU load.

Most common is the high CPU load this at least lets you found out what happened without having to check debug logs and I’m sure I’ll get around to them in another posting.

If your SQL server is still alive you’ll be able to get some more explanations as to what is going on by using query analyzer, if you have many instances on your server you will have to run the following commands on each server till you find the problem instance.

So the command you want to run in the instance is

sp_who2

This will give a list of processes and what you are looking for is BlkBy column as if you see a number there then that is the process id that is locked, now you can find out more about what it was doing by running and inputbuffer command for the moment we’ll pretend that my locked process is 57 then I can see what it was doing by typing.

dbcc inputbuffer (57)

This information is only really good to developers to debug to you might not even be interested in it, what most likely is of interest to you is how to kill it this can be done my using the kill command simple really.

kill 57

Now after that kill command has run check the server with the sp_who2 again as there might have been more than one deadlock, once you happy you’ve resolve your deadlocks you should find your server goes back to normal.

Well that’s your tip for the day.

                              Practice and Enjoy deadlock .


Regards,
By
  Lord  and Creator Jesus christ's Grace,
 Manoj.B.