IT技术互动交流平台

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

作者:Woodytu  来源:IT165收集  发布日期:2016-11-09 20:21:53

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

Tag标签: 层级  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规