原文:在SQL Serve里停用行和页层级锁
今天我想谈下SQL Server里另一个非常有趣的话题：在SQL Server里停用行和页层级锁。在SQL Server里，每次你重建一个索引，你可以使用ALLOW_ROW_LOCKS 和ALLOW_PAGE_LOCKS选项来指定，SQLServer在用读写访问你的数据时，应该获得行和页锁。我们从内部看下，当我们停用这些锁时会发生什么。
停用行层级锁
让我们在一个聚集索引上运行一个简单的REBUILD操作，这里我们停用行层级锁：
-- Disable row level locks ALTER INDEX idx_ci ON Foo REBUILD WITH (ALLOW_ROW_LOCKS = OFF) GO
如你从锁层级里知道的，SQL Server从表层级、页层级和行级别获取锁。现在让我们在一个显式事务里运行一个SELECT语句，并且我们用HOLDLOCK查询提示来把持共享锁直到事务结束。
-- SQL Server acquires in Repeatable Read a Shared Lock on the Page Level, -- because Shared Row Locks are not possible anymore. BEGIN TRANSACTION SELECT * FROM Foo WITH (HOLDLOCK) WHERE ID = 5000 SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID ROLLBACK GO
在这个事务期间，当你查看锁管理器时，你可以看到SQL Server只在表层级获得IS所，在页层级获得共享锁，没有行级别的锁！
这些获得的锁现在没有约束，因为通常SQL Server在页层级获得IS锁，在行本身获得共享锁。当你通过一个事务修改你的数据，这个概念同样适用。
-- SQL Server acquires for an UPDATE statement an Exclusive Lock on the Page Level,
-- because Exclusive Row Locks are not possible anymore.
BEGIN TRANSACTION
UPDATE Foo
SET Col2 = REPLICATE('y', 100)
WHERE ID = 5000
SELECT * FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
ROLLBACK
GO
在这个情况下，最后你还是在页层级有排它锁，而不是IX锁。
停用页层级锁
接下来让我们停用页层级锁：
-- Disable Page level locks ALTER INDEX idx_ci ON Foo REBUILD WITH (ALLOW_PAGE_LOCKS = OFF) GO
首先我想向你展示下索引重组操作取决于页层级锁，因此这个重组操作会失败：
The index “idx_ci” on table “Foo” cannot be reorganized because page level locking is disabled.
现在让用重新运行我们的SELECT语句，但这次使用HOLDLOCK查询提示：
-- There is no IS lock on the Page anymore. BEGIN TRANSACTION SELECT * FROM Foo WITH (HOLDLOCK) WHERE ID = 5000 SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID ROLLBACK GO
当你再次查看锁管理器，你会看到在页层级IS锁消失了。我们只有在表层级IS锁，在行层级有共享锁。
让我们再来修改一条记录：
-- There is no IX lock on the Page anymore.
BEGIN TRANSACTION
UPDATE Foo
SET Col2 = REPLICATE('y', 100)
WHERE ID = 5000
SELECT * FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
ROLLBACK
GO
和刚才一样的事情发生了：SQL Server在表层级获得IX锁，在行上获得排它锁。在页层级没有锁……
停用行和叶层级锁
现在让我们更进一步，对于我们的具体索引停用行和页层级锁：
-- Disable Row and Page level locks ALTER INDEX idx_ci ON Foo REBUILD WITH (ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = OFF) GO
现在当你读取一些数据，SQL Server只在表层级获得共享锁，你的整个表是只读的：
当你修改没有获得页和行锁的一条记录时，SQL Server在整个表上获得了排它锁——偶滴神：
小结
这篇文章的意义？为什么你应该在SQL Server里停用页和行层级锁，真的没有一个很好的理由。就用SQL Server提供的默认的锁策略即可，因为不然的话锁会约束太多，从而伤及你的性能……
感谢关注！
原文链接：
https://www.sqlpassion.at/archive/2016/10/31/disabling-row-and-page-level-locks-in-sql-server/