Summary for Accidental DBA Book / Chapter 7: Handling Deadlocks

Chapter 7: Handling Deadlocks

• When the Lock Monitor finds two sessions in a deadlock: ❶ it chooses on of them as a deadlock victim, ❷ all of the locks held be the victim session are released, ❸ its current transaction is rolled back, ❹and finally it got terminated, ❺ then error 1205 is return. SQL Server selects the deadlock victim based on the following criteria:
1. Deadlock priority: The session with the lowest priority will always be chosen as the deadlock victim.
2. Rollback cost: if the two session has the same priority, the one with the lowest roll back cost will be chosen … P238.
• The Trace Flag 1222 is used to capture the deadlock graphs and presents the information in an easy way to identify the deadlock victim, as well as the resources and processes involved in the deadlock. Trace Flag 1222 is enabled in using DBCC TRACEON(), or the –T1222 startup parameter … P241.
• The Deadlock Graph event is part of the Locks event category and can be added to a SQL Server Profiler trace by selecting the event in Profiler's Trace Properties dialog. But better and to remove the overhead of the Profiler client use SP_TRACE_* system store procedures, which write the captured graphs to a SQL Trace file, and which can be read using the system function fn_trace_gettable or by opening it inside of SQL Profiler then export it to XDL files that can be opened graphically using SQL Server Management Studio … P243. 
• Event notifications allow the capture of deadlock graph information using SQL Server Service Broker, by creating a service and queue for the DEADLOCK_GRAPH trace event … P243-245.
• WMI Provider for Server Events allows WMI to be used to monitor SQL Server events as they occur. Any event that can be captured through event notifications has a corresponding WMI Event Object. SQL Server Agent can manage WMI events, through the use of WMI Query Language (WQL). How to create a SQL Agent alert to capture and store deadlock graphs: http://msdn.microsoft.com/en-us/library/ms186385.aspx ... P246.
• SQL Server 2008 (and later edtions) includes all of the previously discussed techniques for capturing deadlock graphs, and adds one new one, namely collecting the deadlock information through the system_health default event session in Extended Events … P247-248.
• Interpreting Trace Flag 1204 deadlock graphs … P249-253.
• Interpreting Trace Flag 1222 deadlock graphs … P254-257.
• Interpreting XML deadlock graphs … P257-259.
  

Common types of deadlock and how to eliminate them

• Bookmark lookup deadlocks generally occur when a SELECT statement has a shared lock on a non-clustered index and waits for to take another shared lock to lookup on a data table to complete the requested columns that are not covered be that non-clustered index, but an INSERT (or UPDATE or DELETE) statement is having an exclusive lock on the data table, resulting in a deadlock. To fix for this type of deadlock is to change the definition of the non-clustered index so that it contains, either as additional key columns or as INCLUDE columns … P260-261.
• The SERIALIZABLE isolation level is the most restrictive isolation level in SQL Server, as it uses rang locks (in place of the row or page level locking used under READ COMMITTED isolation) to ensure that no data changes can occur that affect the result set. Range locks have two components associated with their names, the lock type used to lock the range and then the lock type used for locking the individual rows within the range. For example, shared-shared (RangeS-S), and exclusive (RangeX-X). SERIALIZABLE isolation deadlocks are generally caused by lock conversion, where a lock of higher compatibility, shared-update (RangeS-U), needs to be converted to a lock of lower compatibility, such as insert-null (RangeI-N). Possible solutions:
1. If it is not necessary for the SELECT statement to be within the transaction that performs range locks, move it outside.
2. If the operation doesn't require the use of SERIALIZABLE isolation, then changing the isolation level to a less restrictive isolation level, for example READ COMMITTED.
3. Force the SELECT statement to use a lower-compatibility lock, through the use of an UPDLOCK or XLOCK table hint … P262-263.
• Cascading constraint deadlocks: In cascading constraints, SQL Server has to traverse the FOREIGN KEY hierarchy to ensure that orphaned child records are not left behind, as the result of an UPDATE or DELETE operation to a parent table. When a deadlock occurs during a cascading operation, look for non-clustered indexes that are missing for the FOREIGN KEY columns, as this will shorten the time the locks being taken to enforce the constraints; so decreasing the likelihood of a deadlock between two operations …P264.

Reference: http://www.amazon.com/Troubleshooting-SQL-Server-Guide-Accidental/dp/1906434786