The setup script
CREATE TABLE t2 ( [col1] INT, [col2] INT ); DECLARE @int INT; SET @int = 1; WHILE (@int <= 1000) BEGIN INSERT INTO t2 ([col1], [col2]) VALUES (@int*2, @int*2); SET @int = @int + 1; END GO create clustered index cl on t2(col1) create index ncl on t2(col2)
I run an simple update and keep the transaction open in read committed isolation level.
begin tran update t2 set [col2]=[col2]+1 where col1=6
If I check sp_lock in another session I get the below results
What I am trying to understand are the key locks on the non-clustered index(indid 2). Why are there two key lock on non-clustered index?
If I check dbcc page on page id 248, I could locate the obvious one((1bfceb831cd9)) which is the lock for the entry for the record 6 which got changed to 7. Output of DBCC PAGE below
What I am trying to understanding is what the purpose of the other key lock(5ebca7ef4e2c) is and what its locking.