Database Reference
In-Depth Information
Figure 19-13.
Row-level locks when select query is blocked
If you ran the second update now in our original session with
SPID = 56
, it would try to acquire an exclusive (X)
lock on the non-clustered index, and it would be blocked by our select session, as shown in Figure
19-14
. That would
lead to the deadlock.
Figure 19-14.
Row-level locks when second update is running (Deadlock)
The best method to avoid such problems is to eliminate multiple updates of the same rows. You can use variables
or temporary tables to store preliminary data and run the single update statement that references them close to the
end of the transaction. Alternatively, if you cannot do that, you can change the code and assign some temporary value
to
NCI_Included_Col
as part of the first
update
statement. As a result, that statement would acquire exclusive (X)
locks on both of the indexes. Select from the session with
SPID = 55
would be unable to acquire the lock on the
non-clustered index and the second update would run just fine.
As a last resort, you can read the row using the plan that utilizes both indexes using an
XLOCK
table hint, which
will place exclusive (X) locks, as shown in Listing 19-4 and Figure
19-15
. Obviously, you need to consider the overhead
this introduces.
Listing 19-4.
Obtaining exclusive (X) locks on the rows in both indexes
begin tran
declare
@Dummy varchar(32)
select @Dummy = CI_Col
from dbo.T1 with (XLOCK index=IDX_T1_NCI)
where NCI_Key = 1
select
l.request_session_id as [SPID], object_name(p.object_id) as [Object], i.name as [Index]
,l.resource_type as [Lock Type], resource_description as [Resource],
l.request_mode as [Mode]
,l.request_status as [Status]
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
