patternsqlMinor
Understanding non clustered index locking during an update
Viewed 0 times
understandingclusteredupdatenonduringlockingindex
Problem
The setup script
I run an simple update and keep the transaction open in read committed isolation level.
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.
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=6If 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.
Solution
If you try the following...
You will see
Showing that the
Look also at slot number
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
ROLLBACKYou 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)Code Snippets
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+-----------------+---------------+
| 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 |
+-----------------+---------------+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)Context
StackExchange Database Administrators Q#175965, answer score: 8
Revisions (0)
No revisions yet.