How to troubleshoot Deadlock in SQL Server 2005
Madhu K Nair , May 28, 2008
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 TestDeadLoack (ID int,Name varchar(100))
GO
Create table DeadLoack (ID int,Name varchar(100))
GO
Insert TestDeadLoack Select 1,'Madhu'
Insert TestDeadLoack Select 2,'ABC'
Insert TestDeadLoack Select 3,'XYZ'
Insert DeadLoack Select 1,'Madhu'
Insert DeadLoack Select 2,'ABC'
Insert DeadLoack Select 3,'XYZ'
-- Dead lock scenario starts
-- Step #1
Begin Tran
UPDATE TestDeadLoack 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 DeadLoack SET Name = 'xyz'
SELECT * FROM TestDeadLoack
--(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 4) You can also export the deadlock event. Steps are as follows Profiler – File – Export – Extract SQL Server Events – Extract Deadlock events and save to some files Summary The beauty of this utility is that it is easy to read and understand. More than using, It is very significant to know that there are wonderful features like this in SQL Server 2005 which makes life easier than before.
原文出自：
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Troubleshoot%20Deadlocking%20in%20SQL%20Server%202005%20using%20Profiler&referringTitle=Home
