你的位置:首页 > 数据库

[数据库]锁升级


在今天的文章里,我想谈下SQL Server里锁升级(Lock Escalations)。锁升级是SQL Server使用的优化技术,用来控制在SQL Server锁管理里把持锁的数量。我们首先用SQL Server里所谓的锁层级(Lock Hierarchy )开始,因为那是在像SQL Server的关系数据库里,为什么有锁升级概念存在的原因。

锁层级(Lock Hierarchy )

 下图展示了SQL Server使用的锁层级:

 

从图里可以看到,锁层级开始于数据库层级,向下至行层级。在数据库本身层级,你一直有一个共享锁(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会一直获取从头到脚的锁来阻止所谓的竞争危害。当你对表进行20000条记录的删除操作时,现在想象下这个锁层级会是什么样的?我们来假定记录是400 bytes长,这就意味这在8kb的页里刚好有20条记录:

 

在数据库上你有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“总是”升级到表层级。这意味着你的锁层级从刚才例子的样子,在锁升级成功执行后,变成如下的样子:

 

 

如你所见,在表本身上你只有一个大锁。在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
1 -- Controllling Lock Escalation2 ALTER TABLE Person.Person3 SET4 (5   LOCK_ESCALATION = AUTO -- or TABLE or DISABLE6 )7 GO

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

小结

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

感谢关注!