主页 / dba / 问题 / 175965
Accepted
jesijesi
Asked: 2017-06-11 20:30:24 +0800 CST

了解更新期间的非聚集索引锁定

  • 772

安装脚本

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 的条目的锁，记录 6 已更改为 7。下面 DBCC PAGE 的输出

在此处输入图像描述

我想了解的是另一个钥匙锁 (5ebca7ef4e2c) 的用途是什么以及它的锁定是什么。

sql-server sql-server-2014

1 个回答

  1. Best Answer

    如果您尝试以下...

    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)
    • 8

相关问题