原文:SQL Server锁分区特性引发死锁解析
锁分区技术使得SQL Server可以更好地应对并发情形,但也有可能带来负面影响,这里通过实例为大家介绍,分析由于锁分区造成的死锁情形.
前段时间园友@JentleWang在我的博客锁分区提升并发,以及锁等待实例中问及锁分区的一些特性造成死锁的问题,这类死锁并不常见,我们在这里仔细分析下.不了解锁分区技术的朋友请先看下我的锁分区那篇实例.
Code(执行测试脚本时请注意执行顺序,说明)
步骤1 创建测试数据
use tempdb go create table testdlk ( id int identity(1,1) primary key, str1 char(3000) ) go insert into testdlk(str1) select 'aaa' insert into testdlk(str1) select 'bbb' insert into testdlk(str1) select 'ccc' insert into testdlk(str1) select 'ddd'
步骤2 开启 session 1 执行语句
--session 1 begin tran update testdlk set str1='ttt' where id=1 ---session id 55 this example ---rollback tran ---manual after session 3 rollback session 1
步骤3 开启session 2 执行语句
--session 2 BEGIN TRAN update testdlk set str1='abc' where id=2 ---update the content of id=2 SELECT * FROM testdlk WITH(TABLOCKX)------ try to get X lock on the object testdlk rollback tran ---session id 58 this example
步骤4 开启session 3执行数据
--session 3 BEGIN TRAN update testdlk set str1='abc' where id=3-------update the content of id=3 SELECT * FROM testdlk WITH(TABLOCKX)--- try to get X lock on the object testdlk rollback tran ---session id 59 this example
步骤5 创建脚本的session中执行语句
select request_session_id,resource_lock_partition,resource_type, object_name(resource_associated_entity_id) as object_name,request_mode,request_status from sys.dm_tran_locks where resource_database_id=2 and resource_type='OBJECT' select session_id,blocking_session_id,wait_type,resource_description from sys.dm_os_waiting_tasks where blocking_session_id is not null
步骤6 session 1中rollback
Rollback session 1 --when session 1 rollback then session 3 deadlock
当session 1回滚时,session2 session 3造成死锁,session 3牺牲.
原因分析.
通过步骤四我们可以得到相应的会话的锁,及相关等待情况如图1-1
图1-1
可以看到session 1(图中55)由于只是更新id=1的列,所以它会在key上加排它锁(图中未列出,感兴趣朋友可以自行查看),而在Object testdlk中某个锁分区中图中为锁分区1加上意向排它锁.
Session 2(图中58)由于更新了id=2的列,所以会在相应Key上加排他锁,并在某个锁分区中加意向排他锁(IX),于此同时由于此事务下面查询有表级TABLOCKX Hint,此时58会尝试在表级上排他锁(X锁).由于X锁需要在所有锁分区中获得,此时58在锁分区0中获得X锁,但由于锁分区1中有55获得了意向排他锁(IX),所以58在锁分区1中尝试获取X锁时状态未Convert,被55阻塞.
Session 3(图中59)由于更新了id=3的列,所以会在相应key上加排他锁,同时在某个锁分区中加意向排他锁(IX),于此同时由于此事务下面查询同样有表级TABLOCKX Hint,这时59也会尝试在表的所有分区中获取X锁.由于58已经获得锁分区0的X锁,所以当59尝试获取锁分区0的X锁时,就会被58阻塞,状态为Wait.
问题来了,当55回滚时,其上面的锁也将被释放.此时58,59都试图获得表级的所有分区X锁,而又同时在锁分区中持有IX锁,这时死锁就不可避免了.
死锁视图如图1-2所示.
图1-2
问题解决
经过分析,可以看出是由于锁分区的特性导致IX与不同spid中的X互斥导致,那如果能禁用锁分区特性不就没有在个别分区上的IX这回事儿了吗.这里介绍一个启动标记 trace flag 1229,可以禁用锁分区特性.我们可以通过配置管理器中添加启动标记,也可以在command启动时加上响应参数.应当注意当使用配置管理器时,我们应在启动参数末尾配置”-T1229”,如果使用command,这时是t1229(大小写区分)
这里我用win中command启用
Code
Net start mssqlserver /t1229
重新启动后重复上述实例,死锁就不在出现了.
注:此实例只为说明由于锁分区造成的死锁情形,实际生产中此类情形却是罕见的,除非遇到这类情形并且没有更好的规避方式,一般我们还是建议默认此特性.这对提升并发是很有帮助的.
关于锁分区特性.
通过微软的在线文档可以得知,只有当CPU的逻辑数大于等于16时,才会默认开启此特性,而授权又是按照CPU收费的.问题来了,当CPU小于16我如果想利用此特性是否可以呢?这个再给大家介绍一个启动跟踪标记 trace flag 1228.当有两个及以上逻辑CPU时就会启动锁分区特性.不过我们使用时清楚自己的使用场景,是否会因此TF得到好处.由于这是无官方文档记录的特性,使用应只针对特定需求,并应慎重.
结语:SQL Server或是其他数据库系统中任何一个特性的引入总会适应大多数场景,但也会伴随着特定场景的弊端出现,清楚其所带来的利弊并合理使用,使得SQL Server适应场景,我们也能适应SQL Server.