开发者社区> 范大脚脚> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

在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提供的默认的锁策略即可,因为不然的话锁会约束太多,从而伤及你的性能……

感谢关注!



本文转自Woodytu博客园博客,原文链接:http://www.cnblogs.com/woodytu/p/6044288.html,如需转载请自行联系原作者

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

相关文章
SQL Server——SQL Server触发器及事务和锁
SQL Server——SQL Server触发器及事务和锁
0 0
MySQL 数据库sql命令查询被锁的表实例演示,mysql的锁表与解锁,mysql强制解锁杀掉进程,mysql查询锁表一直转圈
MySQL 数据库sql命令查询被锁的表实例演示,mysql的锁表与解锁,mysql强制解锁杀掉进程,mysql查询锁表一直转圈
0 0
【DB吐槽大会】第49期 - PG 不支持打印慢SQL锁等待信息
大家好,这里是DB吐槽大会,第49期 - PG 不支持打印慢SQL锁等待信息
0 0
一个线上SQL死锁异常分析:深入了解事务和锁
引发死锁的原因是什么?如何避免?本文详细介绍了和死锁有关的知识点,通过深入分析MySQL事务和锁的机制,结合案例背景,找到了问题的所在,并梳理了解决方案,详解其原理。希望对同学们有所启发。
0 0
MySQL的Slow_log如何记录SQL的MDL锁耗时
MySQL的Slow_log如何记录SQL的MDL锁耗时
0 0
别废话,各种SQL到底加了什么锁?
InnoDB:并发控制,MVCC,索引,锁... 一个SQL到底加了什么锁!?
169 0
MS SQL 锁与事务
MS SQL 锁与事务加锁的主要目的是为了防止并发操作时导致的数据不一致等问题,锁分为共享锁(S)、更新锁(U)、排他锁(X),共享锁与更新只是单向兼容?传说中的单相思? 事务 事务能保证数据操作的原子性,要么内部操作都提交,要么都回退。
501 0
Innodb中常见SQL语句设置的锁类型
1、select … from 除了serializable隔离级别,这种SQL都是一致性非锁定读,不加锁;在serializable级别,这种SQL加next-key锁。 2、select … from … lock in share mode 这种SQL加S类型的next-key锁。
1067 0
SQL SERVER错误:已超过了锁请求超时时段。 (Microsoft SQL Server,错误: 1222)
原文:SQL SERVER错误:已超过了锁请求超时时段。 (Microsoft SQL Server,错误: 1222)     在SSMS(Microsoft SQL Server Management Studio)里面,查看数据库对应的表的时候,会遇到“Lock Request time out period exceeded.(Microsoft SQL Server, 错误1222)”,对应的中文错误提示为“已超过了锁请求超时时段。
1117 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
用SQL做数据分析
立即下载
阿里云流计算 Flink SQL 核心功能解密
立即下载
Comparison of Spark SQL with Hive
立即下载