Deadlocks in Sql Server 2008 R2
To track down a deadlock in SQL Server 2008 R2:
Query the system health to get the details of your deadlock. The result is XML which you can paste into an editor and look for xml_deadlock_report nodes.
SELECT CAST(xet.target_data as xml) FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health'
In here, you will find information about the deadlock. In particular interest are the objects (tables) which were involved in the deadlock. Find any "associatedObjectId"'s. They will look something like: associatedObjectId="72057595681701888"
And to find that object name, query:
SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE partition_id = [The Object Id]
"transactionname" will also help you determine if the call was deadlocked with another select, insert, or delete.
1. Use read uncommitted or nolock on your select statement. I don't recommend this; you can get phantom or dirty reads. But it will eliminate the deadlock.
2. Examine the indexes of your tables. Try to include clustered index columns such as primary keys in any nonclustered indexes.
3. Catch the SQL error, use Thread.Sleep for a few seconds, and reissue the command once (or just immediately give a friendly error message on your UI rather than throwing an exception). This will let the delete/insert/update finish, and then give you your results, with deadlocks happening less frequently. I'd recommend this approach; if you move your SQL calls to a common DB layer, this should be fairly straightforward to implement.
Created By: amos 12/4/2013 11:16:10 AM
Updated: 12/4/2013 11:20:06 AM