Deadlocks - Pro SQL Server Internals - page 400

, l.request_mode as [Mode], l.request_status as [Status]
, wt.blocking_session_id as [Blocked By]
from
sys.dm_tran_locks l join sys.partitions p on
p.hobt_id = l.resource_associated_entity_id
join sys.indexes i on
p.object_id = i.object_id and p.index_id = i.index_id
left outer join sys.dm_os_waiting_tasks wt with (nolock) on
l.lock_owner_address = wt.resource_address and l.request_status = 'WAIT'
where
resource_type = 'KEY' and request_session_id = @@SPID
commit
The code in Listing 19-2 shows the situation when the row has been updated twice. If you looked at the row level
locks held after first updates, you would see that it updates the column that belongs only to the clustered index and
places only one row-level lock, as shown in Figure 19-11 .
Figure 19-11. Row-level locks after the first update
The second update, which updates the column that exists in the non-clustered index, places another exclusive
(X) there, as shown in Figure 19-12 . This proves that the lock on the non-clustered index row is not acquired until the
index column is actually updated.
Figure 19-12. Row-level locks after the second update
Now let's look at another session with SPID = 55 running the select shown in Listing 19-3 in between two
updates, where you have just one row-level lock held, as shown in Figure 19-11 .
Listing 19-3. The code that leads to the deadlock
select CI_Key, CI_Col
from dbo.T1 with (index = IDX_T1_NCI)
where NCI_Key = 1
The table has just one data page, and you have to use an index hint to be able to force the plan, which uses a
non-clustered index. When this happens, the query successfully acquires the shared (S) lock on the non-clustered
index row and is blocked by trying to acquire the lock on the clustered index row, as shown in Figure 19-13 .