How to troubleshoot Deadlock in SQL Server 2005

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Troubleshoot%20Deadlocking%20in%20SQL%20Server%202005%20using%20Profiler&referringTitle=Home

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

The Anatomy of a Deadlock

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=DeadlockAnatomy&referringTitle=Home

A common question of the forums is “How do I stop these deadlocks?” I wrote a small reference article
for how to trap deadlocks using trace flags and Madhu Nair wrote an article for how to trap them with SQL Profiler:

Troubleshoot Deadlocking in SQL Server.
Troubleshoot Deadlocking in SQL Server 2005 using Profiler.

Most people seem to understand a UPDATE/INSERT, UPDATE/DELETE, INSERT/INSERT, DELETE/DELETE deadlocks
pretty well, and the books online cover them pretty good. However, commonly I see where deadlocks occur with
a INSERT/SELECT, UPDATE/SELECT, DELETE/SELECT process that is a bit more complex. The following image
should provide a bit more explanation as to what is happening with this kind of deadlock.

This is a specific kind of deadlock and is caused by a nonclustered index that forces a key lookup to the clustered
index. To demonstrate this kind of structure consider the following:

use tempdb
go
create table KeyLookupDeadlock
(rowid int identity primary key,
 firstname varchar(30),
 lastname varchar(30),
 dateofbirth datetime,
 favoritecolor varchar(10))
go

create nonclustered index ix_KeyLookupDeadlock_firstname
on KeyLookupDeadLock (firstname)
go

insert into keylookupdeadlock select 'jon', 'kehayias', '07/29/2008', 'blue'
insert into keylookupdeadlock select 'mickey', 'mouse', '07/28/2008', 'green'
insert into keylookupdeadlock select 'minnie', 'mouse', '07/24/2008', 'yellow'
insert into keylookupdeadlock select 'donald', 'duck', '07/23/2008', 'fusia'
go 150000

insert into keylookupdeadlock select 'jim', 'fisher', '07/28/2008', 'red'
go

alter index all on keylookupdeadlock rebuild
go

Return to Top

This will create a table that has enough rows on most systems to cause a key lookup for the following query:

select firstname, lastname, dateofbirth, favoritecolor
from keylookupdeadlock
where firstname = 'jim'

Return to Top

Keeping in mind that this is an overly simplistic example, what will happen to cause the deadlock is that two SPIDS
will start within microseconds of each other, one issuing the SELECT, and the other attempting to INSERT a new Row.
The SELECT immediately takes a shared lock on the NonClustered Index and the INSERT immediately takes a
Exclusive Lock on the Clustered Index. For the SELECT to complete, it will need to take a shared lock on the clustered
index to get the additional columns in the SELECT list, but it will be blocked. At the same time, to complete the INSERT
operation will require an Exclusive lock on the nonclustered indexes on the table, which is incompatible with the existing
shared lock held by the SELECT operation, so both processes will be blocked waiting on the other to release its lock.
This is where the deadlock occurs.

To resolve this, the nonclustered index can be dropped and replaced with a covering index that uses the INCLUDE option to cover the query completely:

create nonclustered index ix_KeyLookupDeadlock_firstname_included
on KeyLookupDeadLock (firstname)
include (lastname, dateofbirth, favoritecolor)

Return to Top

The resulting execution plan shows that the Key Lookup no longer happens, which will prevent the deadlock from occuring:

For further information on Lock compatibilities see:

Lock Compatibility (Database Engine)
http://msdn.microsoft.com/en-us/library/ms186396.aspx

Troubleshooting Deadlocks in SQL Server

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Troubleshoot%20Deadlocking%20in%20SQL%20Server&referringTitle=DeadlockAnatomy

One of the more difficult/frustrating things to isolate and eliminate in SQL Server are deadlocks. In all but the rarest exception, deadlocks are a side effect of blocking and not a SQL Server Bug. They can generally be resolved by changing application or stored procedure code to access tables in the same logic order, or by changing the actual database schema or indexing structures. The first step in Deadlock elimination is to identify what two statements are actually causing the deadlocks to occur. In some cases, there may be more than just two statements that contribute to a deadlock. The methods available to troubleshoot deadlocks in SQL Server differ between SQL Server 2000 and SQL Server 2005.

SQL 2000 Trace Flags

Trace Flags can be used to output the details of deadlocks as well, and generally provide you the maximum amount of information to help facilitate identifying the root of the problem. In SQL 2000, flags 1204 (basic) and 1205 (detailed) provide information regarding deadlocks. The output of these flags can be sent to the Errorlog with flag 3605, or to your query analyzer 3604. You will also need to combine any of these flags with the -1 flag which activates them for all of the connections in SQL Server.

An Example statement to enable tracing to the ErrorLog for all connections:

DBCC TRACEON (1204, 3605, -1)
DBCC TRACEON (1205, 3605, -1)

The output of these flags can seem very cryptic however, it doesn’t take much to figure out how to read them. Bart Duncan has provided a wonderful detail of the 1204 output at http://blogs.msdn.com/bartd/attachment/747119.ashx.

Return to Top

SQL 2005 Trace Flags

SQL 2005 also supports the same trace flags that existed in SQL 2000. Added to the list however is Trace Flag 1222 which is far superior to the previous flags. In general, the 1222 flag provides enough details in it to prevent you from having to use any further commands to identify where the problem exists. It includes detailed lock data including the object being locked, the type of lock being held for each node, along with the actual executing SQL Statement that is causing the locking.

An Example statement to enable tracing to the ErrorLog for all connections in 2005 is :

DBCC TRACEON (1222, 3605, -1)

Once again, Bart Duncan has provided excellent information on decifering the output of this trace flag. His series on Deadlock elimination in SQL 2005 is located http://blogs.msdn.com/bartd/archive/2006/09/13/Deadlock-Troubleshooting_2C00_-Part-2.aspx

Return to Top

SQL Profiler

The SQL Profilercan also be used to help in identifying deadlocking. It does not however, output in SQL 2000 the specific page information necessary to isolate the exact problem. In order to identify the deadlocking statements in SQL Profiler, you need to be tracing the following events.

Locks – Lock:Deadlock
Locks – Lock:Deadlock Chain
Stored Procedure – SP:StmtStarting
TSQL – SQL:StmtStarting

When a deadlock is encountered, the Deadlock chain event will output the SPID’s associated with the deadlock. From there, you can look at the StmtStarting event for the SPID’s immediately following the Deadlock Event to isolate the problem statements.

In SQL 2005 the same trace events listed in the SQL 2000 section can be used for deadlock identification. SQL 2005 however, adds to these events the newer Locks – Deadlock Graph event which occurs with the Locks:Deadlock event, and provides an XML output of the deadlocking events. This XML output is displayed graphically in SQL Profiler and helps you to better understand what is happening that resulted in the deadlock.

Additional Resources

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2733075&SiteID=1