锁升级

缺乏、安全感 2021-12-15 07:41 297阅读 0赞

前言：

本文的原文在[这里][Link 1]，这个兄弟真心觉得挺厉害的，他的博客看过来，真的是从菜鸟到高手，向他学习，当然这个兄弟也是参考老外的文章，在[这里][Link 2]，前面还有一篇文章，讲的是 [更新锁][Link 3] ，讲的也很好。由于我总是有一些自己的拙见需要加进去，所以没有直接转载，我已经备注了出处，希望作者不要介意。

# 锁层级（Lock Hierarchy ） #

![1163931-20190528090712551-423029713.png][]

从图里可以看到，锁层级开始于数据库层级，向下至行层级。在数据库本身层级，你一直有一个共享锁（Shared Lock (S) ）。当你的查询连接到一个数据库（例如**USE MyDatabase**），共享锁会阻止数据库被删除，或者在那个数据库上还原备份。当你进行一个操作时，在数据库层级下，在表上，在页上，在记录上都会有锁。

当你执行一个**SELECT**语句，在表和页上会有一个意向共享锁（Intent Shared Lock (IS) ），在记录本身上有共享锁（Shared Lock (S) ）。当你进行数据修改语句（INSERT，UPDATE，DELETE），在表和页上会有一个意向排它或意向更新锁（ Intent Exclusive or Update Lock (IX or IU) ），在改变的记录上有排它锁或更新锁（Exclusive or Update Lock (X or U) ）。当多个线程尝试在锁层级里并发获取锁时，SQL Server会一直获取从头到脚的锁来阻止所谓的[竞争危害][Link 4]。当你对表进行20000条记录的删除操作时，现在想象下这个锁层级会是什么样的？

我们来假定页里面的每条记录是400 bytes长，这就意味这在8kb的页里刚好有20条记录（理想情况，认为页里全是表数据），而20000条记录就占用了1000个页：

![1163931-20190528091608080-1834364534.png][]

在数据库上你有1个共享锁（S），在表上有1个意向排它锁（IX），在页上有1000个意向排它锁（IX）（因为20000条记录分布在1000个页上，每个页上有一个锁），最后在记录本身你有20000个排它锁（X）。对于**DELETE**操作总计你获取了21002个锁。在SQL Server里每个锁需要96 bytes的内存，因此对这个简单的查询需要1.9MB的锁。当你并行执行多个查询时，这个不会无限扩展。因此，SQL Server现在用所谓的**锁升级（Lock Escalation）**实现。

# 锁升级（Lock Escalations） #

在你的锁层级里一旦有超过5000个锁，SQL Server会将这个多细粒度（fine-granularity）的锁升级为简单的粗粒度（coarse-granularity）的锁。默认情况下，SQL Server“总是”升级到表层级。这意味着你的锁层级从刚才例子的样子，在锁升级成功执行后，变成如下的样子：

![1163931-20190528091929737-683482066.png][]

如你所见，在表本身上你只有一个大锁。在**DELETE**操作的情况下，在表层级你有一个排它锁（X）。这会以负面伤及你数据库的并发。在表层级把持一个排它锁（X）意味着没有其他会话可以访问那个表——每个其它查询会被阻塞。当你在可重读隔离级别（Repeatable Read Isolation Level）运行你的**SELECT**语句，你也在把持你的共享锁（S）直到事务结束，这也就是说只要你读超过5000条记录就会发生锁升级。这里的结果是一个共享锁（S）在表本身！你的表只是暂时只读，因为在表上每个其它数据修改都会阻塞！

这里还有个误解——SQL Server会锁升级从行层级到页层级，最后到表层级。错了！在SQL Server里没有这样的代码路径存在！默认情况下，SQL Server总是会直接升级到表层级，到页层级的升级策略不存在。如果你的表被分区了（只针对企业版本的SQL Server），那样的话，你可以配置升级到分区层级。但这里你必须非常仔细测试你的数据访问模式，因为锁升级到分区层级会引起死锁。因此这个选项默认是没启用的。

自SQL Server 2008开始，你可以控制SQL Server如何进行锁升级——通过ALTER TABLE语句和LOCK\_ESCALTATION属性。有3个可用选项：

*  TABLE
 *  AUTO
 *  DISABLE

-- Controllling Lock Escalation
    ALTER TABLE Person.Person
    SET
    (
        LOCK_ESCALATION = AUTO -- or TABLE or DISABLE
    )
    GO

默认选项是**TABLE**，意味着SQL Server**总是**执行锁升级到表层级——即使这个表已被分区。如果你的表已被分区，你想设置分区层级的锁升级（因为你已经测试了你的数据访问模式，用它你不会引起死锁），那么你可以修改选项为**AUTO**。**AUTO**意味着你的锁升级会执行到分区层级，如果表被分区的话，否则就到表层级。使用**DISABLE**选项你可以完全禁用那个表的锁升级。但是禁用锁升级并不是最好的选项，因为SQL Server的锁管理器会消耗大量的内存，如果你对你的查询和索引设计不深思熟虑的话。

# 小结 #

在SQL Server里锁升级基本是个噩梦。你如何才能从表里删除5000行记录而不产生锁升级？你可以临时禁用锁升级，但这里你要非常仔细。另外一个方法（我推荐的）是让你的**DELETE**/**UPDATE**语句在一个循环里，作为不同，独立的事务：**DELETE**/**UPDATE**少于5000行记录，这样的话你可以阻止锁升级。这样做的好处，你庞大的事务会分解为多个小事务，但也会让你的事务日志更多，带来自动增长问题。

转载于:https://www.cnblogs.com/ziqiumeng/p/10935198.html

[Link 1]: https://www.cnblogs.com/woodytu/p/4641124.html
[Link 2]: https://www.sqlpassion.at/archive/2014/02/25/lock-escalations/
[Link 3]: https://www.cnblogs.com/woodytu/p/4678787.html
[1163931-20190528090712551-423029713.png]: /images/20211214/d39f2ddc32fc466ab5e1476fbddf6496.png
[Link 4]: https://zh.wikipedia.org/wiki/%E7%AB%B6%E7%88%AD%E5%8D%B1%E5%AE%B3
[1163931-20190528091608080-1834364534.png]: /images/20211214/533399d95ccf4f38932f70acb042001b.png
[1163931-20190528091929737-683482066.png]: /images/20211214/f5d42c285e5f4de58f9b8d69128ebb1a.png