了解更新期間的非聚集索引鎖定

設置腳本

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)

我執行一個簡單的更新並保持事務在讀取送出隔離級別中打開。

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

如果我在另一個會話中檢查 sp_lock，我會得到以下結果

我想了解的是非聚集索引（indid 2）上的鍵鎖。為什麼非聚集索引上有兩個鍵鎖？

如果我在 id 248 頁上檢查 dbcc 頁面，我可以找到明顯的一個 ((1bfceb831cd9))，它是記錄 6 的條目的鎖，它已更改為 7。 DBCC PAGE 的輸出如下

我想了解的是另一個鑰匙鎖（5ebca7ef4e2c）的目的是什麼以及它的鎖定是什麼。

如果您嘗試以下…

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    |
+-----------------+---------------+

顯示UPDATE針對非聚集索引的操作是作為刪除/插入對實現的。

(5ebca7ef4e2c)是初始記錄的雜湊6,6 (1bfceb831cd9)值是更新後的值6,7。還要看槽號

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)

引用自：https://dba.stackexchange.com/questions/175965