Sql-server – Understanding non clustered index locking during an update

lockingsql serversql server 2014

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

enter image description here

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

enter image description here

What I am trying to understanding is what the purpose of the other key lock(5ebca7ef4e2c) is and what its locking.

Best Answer

If you try the following...

CHECKPOINT;

GO

BEGIN TRAN

UPDATE t2
SET    [col2] = [col2] + 1
WHERE  col1 = 6

SELECT Operation,
       AllocUnitName
FROM   sys.fn_dblog(NULL, NULL)
WHERE  AllocUnitName IS NOT NULL

ROLLBACK

You will see

+-----------------+---------------+
|    Operation    | AllocUnitName |
+-----------------+---------------+
| LOP_MODIFY_ROW  | dbo.t2.cl     |
| LOP_DELETE_ROWS | dbo.t2.ncl    |
| LOP_SET_BITS    | dbo.t2.ncl    |
| LOP_INSERT_ROWS | dbo.t2.ncl    |
+-----------------+---------------+

Showing that the UPDATE against the non clustered index is implemented as a delete/insert pair.

(5ebca7ef4e2c) is the hash for the initial record 6,6 (1bfceb831cd9) is the value after update 6,7. Look also at slot number

select
    *
    ,%%lockres%%
    ,%%physloc%%
    ,sys.fn_PhysLocFormatter(%%physloc%%)   
from t2 with (nolock,index = ncl)
where 
    %%lockres%% = '(5ebca7ef4e2c)'
    --OR 
    --%%lockres%% ='(1bfceb831cd9)'

begin tran
update t2 set [col2]=[col2]+1  where col1=6
commit tran


select
    *
    ,%%lockres%%
    ,%%physloc%%
    ,sys.fn_PhysLocFormatter(%%physloc%%)   
from t2 with (nolock,index = ncl)
where 
    --%%lockres%% = '(5ebca7ef4e2c)'
    --OR 
    %%lockres%% ='(1bfceb831cd9)'


update t2 set [col2]=[col2]-1  where col1=6


col1    col2    HashValue   bPhysicLocation crackedLocation
6       6       (5ebca7ef4e2c)  0xB401000001000200  (1:436:2)

col1    col2    HashValue   bPhysicLocation crackedLocation
6       7      (1bfceb831cd9)   0xB401000001000300  (1:436:3)
Related Question