在SQL Serve里停用行和页层级锁

wang_rx

　　今天我想谈下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/