4

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.

  • I belive that , (5ebca7ef4e2c) is the initial record, 6,6 . Try to look at it before begin transaction – Sabin Bio Jun 11 '17 at 8:25
6

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)
  • 1
    @Martin, thank you for completing the answer – Sabin Bio Jun 11 '17 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.