Database Reference
In-Depth Information
where
resource_type = 'KEY' and request_session_id = @@SPID
update dbo.T1
set CI_Col = 'abc'
where CI_Key = 1
-- some code
update dbo.T1
set NCI_Included_Col = 1
where NCI_Key = 1
commit
Figure 19-15.
Row-level locks after SELECT statement with (XLOCK) hint
Deadlock Troubleshooting
How to troubleshoot deadlocks? In a nutshell, the approach is very similar to the blocking problems troubleshooting.
Similarly to the
blocking process report
there is the
deadlock graph
that can help you to analyze what is involved in the
deadlock situation.
There are plenty of ways to obtain the deadlock graph. One of them is by using SQL Trace. There is a
Deadlock
Graph event
in the Locks event group. When deadlocks occur, a deadlock graph is stored in
TextData
column.
Let's emulate the deadlock with the code shown in Figure
19-16
. You need to have two sessions running in
parallel—run update statements first and then select statements.
Figure 19-16.
Generating deadlock in the system
