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

今天我想谈下SQL Server里另一个非常有趣的话题:在SQL Server里停用行和页层级锁。在SQL Server里,每次你重建一个索引,你可以使用ALLOW_ROW_LOCKSALLOW_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/

posted @   Woodytu  阅读(5135)  评论(0编辑  收藏  举报
编辑推荐:
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
阅读排行:
· 阿里最新开源QwQ-32B,效果媲美deepseek-r1满血版,部署成本又又又降低了!
· 单线程的Redis速度为什么快?
· SQL Server 2025 AI相关能力初探
· 展开说说关于C#中ORM框架的用法!
· AI编程工具终极对决:字节Trae VS Cursor,谁才是开发者新宠?
点击右上角即可分享
微信分享提示