开发者社区> 杰克.陈> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介: 原文:在SQL Serve里停用行和页层级锁 今天我想谈下SQL Server里另一个非常有趣的话题:在SQL Server里停用行和页层级锁。在SQL Server里,每次你重建一个索引,你可以使用ALLOW_ROW_LOCKS 和ALLOW_PAGE_LOCKS选项来指定,SQLServer在用读写访问你的数据时,应该获得行和页锁。
+关注继续查看
原文:在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/

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
函数计算 Python 连接 SQL Server
函数计算 Python 连接 SQL Server 自制脑图
0 0
SQL Server 2000 函数使用---CAST 和 CONVERT
日期格式样式,借以将 datetime 或 smalldatetime 数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型);或者字符串格式样式,借以将 float、real、money 或 smallmoney 数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型)。
0 0
【Sql Server】进阶之行数据转为列显示
在开发系统维护阶段,经常需要进行各种数据统计,各种报表之类的。 这个时候,行数据转数据显示就发挥作用了。 场景:行数据的某列值想作为字段列显示
0 0
【Sql Server】时间转换和查询时间范围查询不正确的原因
最近在做时间方法封装的时候发现了一个问题! 如果sql语句输出的时间字段转为了字符串输出,那么在使用此字段作为时间范围筛选时发现无效了,没法过滤对应的时间范围内记录
0 0
【Sql Server】基础之统计库龄语句,仅作为语句使用
知识点的综合使用 分组、数据转换、Case when then、max、min、count、sum、left join多表关联等知识点
0 0
【Sql Server】sql语句文件组分区函数分组方案对应分区表的简单步骤
本篇文章中,主要讲讲sql server数据库中通过sql语句方式对组分区函数的使用 在实际项目中,sql server数据库中有分区的概念,因为在一个表存在大量数据的情况下,需要通过分区方式保存数据来提供查询性能
0 0
【Sql Server】存储过程通过定时执行添加记录作业
通过上篇了解了什么是存储过程,创建存储过程的方法,以及调用存储过程的方法 本次将通过数据库中的作业功能,进行定时执行存储过程,这样就可以完成我们刚开始假设的场景
0 0
SQL Server锁分区特性引发死锁解析
原文:SQL Server锁分区特性引发死锁解析 锁分区技术使得SQL Server可以更好地应对并发情形,但也有可能带来负面影响,这里通过实例为大家介绍,分析由于锁分区造成的死锁情形. 前段时间园友@JentleWang在我的博客锁分区提升并发,以及锁等待实例中问及锁分区的一些特性造成死锁的问题,这类死锁并不常见,我们在这里仔细分析下.
985 0
+关注
杰克.陈
一个安静的程序猿~
文章
问答
文章排行榜
最热
最新
相关电子书
更多
用SQL做数据分析
立即下载
阿里云流计算 Flink SQL 核心功能解密
立即下载
Comparison of Spark SQL with Hive
立即下载