Database Reference
In-Depth Information
Let's prove that with the code shown in Listing 19-2.
Listing 19-2.
Multiple updates of the same row
create table dbo.T1
(
CI_Key int not null,
NCI_Key int not null,
CI_Col varchar(32),
NCI_Included_Col int
);
create unique clustered index IDX_T1_CI on dbo.T1(CI_Key);
create nonclustered index IDX_T1_NCI
on dbo.T1(NCI_Key)
include (NCI_Included_Col);
insert into dbo.T1(CI_Key,NCI_Key,CI_Col,NCI_Included_Col)
values(1,1,'a',0), (2,2,'b',0), (3,3,'c',0), (4,4,'d',0);
begin tran
update dbo.T1
set CI_Col = 'abc'
where CI_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], l.resource_description as [Resource]
, 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
-- some code
update dbo.T1
set NCI_Included_Col = 1
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], l.resource_description as [Resource]
