Dealing with Locking, Blocking, and Deadlocking - Microsoft SQL Server 2012 Performance Tuning - page 388

How it works...
In this recipe, we first created a trace by using the Blank trace template. To capture the
T-SQL statements that are executed and the deadlock events that occurred, we selected
the following events for our trace:
F Deadlock graph
F Lock:Deadlock
F Lock:Deadlock Chain
F SQL:StmtCompleted
F SQL:StmtStarting
We configured the trace by specifying a filter on DatabaseName, so that it only captured
events raised against the database AdventureWorks2012 . We also organized columns,
so that we can view the necessary columns relevant to deadlocks, on screen.
After configuring and starting the trace, we ran two separate transactions from two different
query sessions from SSMS. In both transactions, we selected a row from the table Sales.
SalesOrderDetail , on the basis of the value of SalesOrderDetailID , and tried to
update the same row with the other transaction. Note that in both the transactions, we set
the transaction isolation level to REPEATABLE READ . This isolation level holds the shared
locks issued on SELECT statements until the transaction is completed. This is the reason why
Connection-1 had to wait while updating the same row ( SalesOrderDetailID = 121317 )
on which Connection-2 had held the lock, because Connection-2 had not completed its
transaction. We ran the same type of query from Connection-2 and tried to update the same
row ( SalesOrderDetailID = 121316 ) that the Connection-1 had held the lock on. This
creates permanent blocking from both sides and creates a deadlock situation.
SQL Server automatically handles and detects such types of deadlocks. It then selects one of
the processes involved in the deadlock as the deadlock victim and kills that process. That's
why we received the error ( 1205 ) in Connection-2 after executing the UPDATE query.
In SQL Server Profiler, we examined how the trace captured the deadlock events. In the trace
result, you can see that two transactions tried to issue an EXCLUSIVE lock on the KEY for the
UPDATE operation that the other had a SHARED lock on. The lock was requested on a single
row in an index, and this can be confirmed by looking at the value in the Type data column. In
our case, this happens to be KEY , which indicates a single key value in an index.
By clicking the row associated with the Deadlock graph event, we could see the deadlock
graph in the bottom pane of the trace window. This graph gives the details of the deadlock
that occurred. As you can see, it also shows which process was chosen as the deadlock victim
and killed. We then saved the deadlock graph by right-clicking the Deadlock graph row in the
trace window. This is an XML file that consists of deadlock details. A part of this XML file is
shown in the last step of this recipe, in the form of a screenshot.