SQL Server死锁诊断--同一行数据在不同索引操作下引起的死锁

  1. 云栖社区>
  2. 博客>
  3. 正文

SQL Server死锁诊断--同一行数据在不同索引操作下引起的死锁

杰克.陈 2018-08-17 09:45:00 浏览462
展开阅读全文

原文:SQL Server死锁诊断--同一行数据在不同索引操作下引起的死锁

 

 

死锁概述

对于数据库中出现的死锁,通俗地解释就是:不同Session(会话)持有一部分资源,并且同时相互排他性地申请对方持有的资源,然后双方都得不到自己想要的资源,从而造成的一种僵持的现象。
当然,在任何一种数据库中,这种僵持的情况不会一直持续下去,因为一直持续下去双方永远都无法执行,没有任何意义,
在SQL Server中,后台线程会以3秒钟一次的频率检测死锁Session,并且选择其中一个回滚代价相对较低的作为牺牲品,从而使解除不同Session相互僵持的现象。
因此SQL Server中死锁的僵持时间不会超过3秒钟。

通常情况下,最简单也是最常见的死锁是发生在不同表级别的,
Session 1 第一步修改A表,第二步修改B表,
Session 2第一步修改B表,第二步修改A表,
当发生Session 1与Session 2推进顺序发生交叉的时候,死锁就发生了,这种结局办法也比较简单,以相同的推进顺序进行操作即可解除死锁。

以下演示一种不用于以上情况,稍微特殊一点的死锁。

 

同一张表上发生的死锁演示

不过死锁的种类有很多种,上述的仅是一种最简单最常见的一种死锁,
理论上,只要满足死锁发生的条件:不同Session(会话)排他性地持有一部分资源,并且相互申请对方持有的资源
都会产生死锁,并不仅仅是在不同的表上,而是在不同的资源上,这种资源,可以是同一张表,甚至同一行数据上,以下举例说明。

--TestDeadLock的Id是主键(默认生成聚集索引),Col2字段是唯一性的非聚集索引
create table TestDeadLock
(
    Id int constraint pk_TestDeadLock_id primary key,
    Col2 int constraint uk_TestDeadLock_col2 unique,
    Remark varchar(100)
)

然后利用SQLQueryStress,开启两个回话,分别按照聚集索引和非聚集索引,删除同一行数据(造测试数据的时候会设置Id和Col2都为1),
如下图所示
一开始先让这两个Session一直执行(空运行),随后往TestDeadLock表中插入一行数据(insert into [TestDeadLock] values (1,1,newid()))
可能需要执行几次尝试,就会观察到其中一个SQLQueryStress中发生了异常信息

打开其异常信息的详细内容 ,会发现是死锁

 

首先查一下表上索引的id,一下分析加锁的过程中会用到。
pk_TestDeadLock_id 是聚集索引,其Id是 72057594050314240
uk_TestDeadLock_col2 是非聚集索引,其Id是 72057594050379776

利用sqlserver自带的system_health扩展事件,观察其死锁信息(xml_deadlock_report)

SELECT  CAST(xet.target_data AS XML)
FROM    sys.dm_xe_session_targets xet
        JOIN sys.dm_xe_sessions xe ON ( xe.address = xet.event_session_address )
WHERE   xe.name = 'system_health'

select xml_event_data,
xml_event_data.value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime') Execution_Time,
xml_event_data.value('(event/data/value)[1]','varchar(max)') Query
from 
(
    SELECT event_table.xml_event_data
    FROM(
            SELECT CAST(event_data AS XML) xml_event_data 
            FROM sys.fn_xe_file_target_read_file(N'your path \system_health_*', NULL, NULL, NULL)
        ) AS event_table
        CROSS APPLY xml_event_data.nodes('//event') n (event_xml)
    WHERE  event_xml.value('(./@name)', 'varchar(1000)') IN ('xml_deadlock_report')   
) v 
order by Execution_Time

得到如下的死锁信息,扩展事件中的xml_deadlock_report清楚吧地表明:对于当前这一行数据(8194443284a0一样)
delete from [TestDeadLock] where Id= 1     等待非聚集索引上的锁(waitresource="KEY: 11:72057594050379776 (8194443284a0)" )
delete from [TestDeadLock] where Col2 = 1     等待聚集索引上的锁(waitresource="KEY: 11:72057594050314240 (8194443284a0)" )
两者有死锁,肯定是相互等待对方已经持有的资源(索引上的锁)
因此,当前这个死锁可以这么理解
delete from [TestDeadLock] where Id=1     持有聚集索引上的U锁,申请非聚集索引上的X锁
delete from [TestDeadLock] where Col2 = 1    持有非聚集索引上的X锁,申请聚集索引上的U锁
结果:死锁!

 

关于waitresource的解读,参考:https://blog.csdn.net/kk185800961/article/details/41687209

 

两个SQL对同一行数据的加锁顺序分析

上述分析只是根据已有现象推测其过程,如果能够观察到每一个sql语句执行过程中的锁的申请与释放顺序,问题就更容易理解了。
以下利用profile观察两个语句执行过程中对锁的申请和释放顺序

观察一下delete from [TestDeadLock] where Id = 1 这句sql的执行过程的锁的申请顺序
profile里就很清楚,对于delete from [TestDeadLock] where Id = 1
先申请聚集索引(72057594050314240)page层面上的意向排它锁(IX),转为行级别的排它锁(X),再申请非聚集索引(72057594050379776)的page层面意向排它锁(IX),转换为行级别排它锁(X)

对于delete from [TestDeadLock] where Col2 = 1
先申请非聚集索引(72057594050379776)上page层面的意向更新锁(IU),转为行级别更新锁锁(U),再申请page层面聚集索引(72057594050314240)的意向排它锁(IX),转换为行级别排它锁(X)

通过以上加锁顺序的分析,印证了上述加锁方式的推测,不难理解两个SQL语句为什么会发生死锁。
仍然回到死锁的概念上:不同Session(会话)排他性地持有一部分资源,并且同时申请对方持有的资源
这种相互持有的资源,可以是不同表上的资源,可以是同一个表上的资源,甚至可以是同一行数据的不同资源(不同索引的资源)
只要发生不同Session相互排他性地持有对方想要的资源,死锁就会发生。

这种方式是双方根据不同的索引同时delete引起的死锁,类似上述情况,可以延伸到双方同时update,双方同时delete或者update,双方同时update或者select等等
只要是索引推进顺序不一致,都有可能引起死锁的发生,此类问题可以归结为同一行数据上,不同索引操作引起的死锁。

 

如何解决?

对于常见的不同表上的推进顺序不当造成的死锁,只要改进持锁的顺序即可,也就是按照同一种方式来操作不同表中的数据。
对于上述的问题,不是不同表上的推进顺序造成的,而是同一张表的同一行数据的资源推进顺序不当导致的,在sql语句层面看起来并没有什么不妥当的,因此只能从锁的范围或者隔离级别上进行调整。
1,尝试从业务入手,是否能够按照统一的方式对数据进行操作。
2,使用队列消除并发操作的峰值。
3,尝试tablockx,一次性锁定整个表。
4,尝试改变隔离级别,尝试序列化隔离级别。

最后佛系一下:
很多问题都喜欢用奇怪解释,其实很多问题并不奇怪,只是不知道而已,
技术上的问题,不知道也没什么大不了,知道了更没什么大不了,知道也仅仅是知道而已,不知道经历一次就知道了,知不知道都没有任何值得自豪或者自卑的
你的知识死角不能否定你的技术能力,应用层面的东西,只不过是在人家制定好的规则上玩游戏而已,谁也不要装。

参考:
https://www.cnblogs.com/Uest/p/4998527.html
https://blogs.msdn.microsoft.com/apgcdsd/2012/02/27/sql-serverdeadlock/
https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/

 

 

需要注意的是:扩展事件中记录的事件发生的时间,都是标准时间(格林威治时间),而其errorlog中或者自定义异常中的时间,都是当前时间

 

网友评论

登录后评论
0/500
评论
杰克.陈
+ 关注