主页 / dba / 问题 / 168116
Accepted
Martin Guth
Asked: 2017-03-25 08:57:57 +0800 CST

用于在 SQL Server 中的聚集索引上生成页锁的示例查询/DDL（无提示）

  • 772

我们目前有一些索引ALLOW_PAGELOCKS设置为关闭。这样做大概是为了减少死锁。但是，我怀疑它在当时是否真的会产生影响。

现在我试图了解什么时候 SQL Server 实际上选择开始锁定页面而不是聚集索引中的键。我最近问过 Jonathan Keyhaisas，他告诉我，如果我在几个后续页面上触摸行，就会发生这种情况。但是，我没有设法通过使用示例查询更新聚集索引中的行来获得任何独占页锁。

您能否通过示例查询和表帮助我更好地理解页锁？我正在运行 SQL Server 2008 SP4。

提前致谢

马丁

sql-server sql-server-2008

2 个回答

  1. Best Answer

    我们可以使用大小差不多的表，但我们需要一些更有趣的数据。具体来说，SQL 没有索引但我们在修改谓词中使用的数据。

    USE tempdb;

CREATE TABLE dbo.Dummy
(
  Id INT IDENTITY(1, 1),
  Crap DATE,
  MoreCrap VARCHAR(1000)
);

INSERT dbo.Dummy ( Crap, MoreCrap )
    SELECT TOP 1000000 DATEADD(DAY, x.Rn % 365 + 1, GETDATE()), CONVERT(VARCHAR(100), NEWID())
        FROM ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Rn
        FROM sys.messages AS m
        CROSS JOIN sys.messages AS m2 
        ) AS x ( Rn );

CREATE CLUSTERED INDEX cx_RemusForPresident ON dbo.Dummy (Id)

    只需使用 Id 列上的 CX，我们就可以更新！

    BEGIN TRAN
UPDATE d
SET d.MoreCrap = 'Happy Friday'
FROM dbo.Dummy AS d
WHERE d.Crap >= '20170601'
AND d.Crap < '20170630'
AND d.MoreCrap LIKE '0%'

    和以前一样，虽然希望不会冒犯 Aaron Bertrand，但我们检查了sp_WhoIsActive

    EXEC sp_WhoIsActive @get_locks = 1

    可恶！Pages 上的独占锁，对象上的 IX 锁，没有键锁。

    坚果

    坚果

    坚果

    • 6

  2. 锁定粒度（即行、页、表）是锁定策略的一部分，它是在语句开始执行时预先确定的。有几个决定粒度的因素，但对于行存储，主要因素是 a) 允许的粒度（即sp_tableoptions）和 b) 预期的锁估计数量（与基数估计几乎相同，但不完全相同）。

    因此，如果您放宽 a) 约束（即允许页锁），那么要获得页锁，您需要运行一个查询，该查询执行引擎将期望大量行锁并决定最好使用页锁. 典型的例子是对足够大的表进行表扫描。您几乎可以假设“页面锁定粒度 => 缺少索引”，并且您几乎总是正确的。

    对于更新（即使锁独占），您可以采用类似的方法。通常是找到符合条件的行的扫描使用 U 页面锁，然后升级到 X 模式必须在相同的粒度。

    • 4

相关问题