平时遇到的死锁, 绝大多数情况下, 都可以根据当时的场景进行重现, 然后具体分析解决, 下文这个死锁几次尝试测试模拟, 均没有成功重现
在尝试用 profile 跟踪加锁顺序之后, 大概可以推断到当时死锁发生的原因, 但是仍有无法重现, 为了避免不必要的麻烦, 这里用测试表的方式, 尽可能还原尝试的场景, 来做进一步的分析.
死锁发生的场景如下(暂不论表设计合不合理, 索引合不合理, sql 语句写法合不合理, 分析死锁是主要目的, 解决死锁是另外一回事)
目标表为 TestDeadLock, 大概结构如下
1,TestDeadLock 表为堆表(有自增列的主键, 但是主键 nonclustered),col2.col3 上的索引分别是 idx_col2,idx_col3,Col2 无重复, Col3 上重复值较多, 表数据量不会很多, 几千行或者几万行的样子,
2, 存在并发按照多个不同的 Col2 字段删除的情况(delete from TestDeadLock where col2 in (x,y,z);)
- create table TestDeadLock
- (
- id int identity(1,1) primary key nonclustered,
- col2 varchar(30),
- col3 varchar(30),
- col4 varchar(30)
- )
1,session1 执行 delete from TableA where col2 in (x,y,z);
2,session2 执行 delete from TableA where col2 in (l,m,n);
其中, 删除的目标列条件 in (x,y,z); 与 in (l,m,n); 中的数据在 Col2 这个字段的值上无重复, 无交叉, 但是多个 Col2 条件上对应的 Col3 这个字段值是一样的
session1 与 session2 发生死锁, xml_deadlock_report 显示 session1 是持有 col2 上的 key 级别的 U 锁, 等待 col3 上 key 级别的 U 锁, session1 是持有 col3 上的 key 级别的 U 锁, 等待 col2 上 key 级别的 U 锁
如下是 xml_deadlock_report 锁等待的信息, 敏感信息用 XXXXXXXXXX 和 YYYYYYYYYYYYY 替代了, 其中 XXXXXXXXXX 类似如上的 idx_col3 索引 Id,YYYYYYYYYYYY 类似于如上的 idx_col2 索引 Id
- <resource-list>
- <keylock hobtid="XXXXXXXXXXXXXX" dbid="6" objectname=""indexname="" id="lock12fe62f80" mode="U" associatedObjectId="XXXXXXXXXXXXXX">
- <owner-list>
- <owner id="process--2" mode="U" />
- </owner-list>
- <waiter-list>
- <waiter id="process--1" mode="U" requestType="wait" />
- </waiter-list>
- </keylock>
- <keylock hobtid="YYYYYYYYYYYYY" dbid="6" objectname=""indexname="" id="lock126403100" mode="U" associatedObjectId="YYYYYYYYYYYYY">
- <owner-list>
- <owner id="process--1" mode="U" />
- </owner-list>
- <waiter-list>
- <waiter id="proces--2" mode="U" requestType="wait" />
- </waiter-list>
- </keylock>
- </resource-list>
先说我自己的理解:
理论上说, 两个 delete 的 session 都会走 Col2 上的索引, 两个语句对于其目标数据的加锁顺序是一致的, 不会出现死锁的情况,
当然只是臆测, 因为 sql 语句没有加任何锁提示, 数据量小的时候, 任何一种执行计划都是有可能的.
但是仅仅从死锁的语句, 是无法拿到当时的执行计划的, 也就无法证实当死锁发生的时候, 双方用的哪一种执行计划.
构造测试表以及测试数据, 其中: 对于 col3, 尽管重复值非常多, 仍然有一个索引(再次说明, 这里暂抛开索引合不合理, 语句合不合理这一说)
- create table TestDeadLock
- (
- id int identity(1,1) primary key nonclustered,
- col2 varchar(30),
- col3 varchar(30),
- col4 varchar(30)
- )
- create index idx_col2 on TestDeadLock(col2)
- create index idx_col3 on TestDeadLock(col3)
- declare @i int = 0
- while @i<200000
- begin
- insert into TestDeadLock values (concat('X0000000000',@i),cast(rand()*10 as int),'test')
- set @i = @i+1
- end
测试表的索引对象 Id
以 delete from TestDeadLock where col2 in ( 'X00000000003','X000000000020')为例, 这里先拿到其伪列 Id
理论上, 这句 sql 的执行, 会走 col2 上的索引进行查找, 然后再进行删除(delete 本来就是先查找再删除的过程), 测试 case 也是预期地, 走了 col2 上的索引
查看锁的申请与释放过程
可以发现
1, 删除多条数据的时候, 是一条一条加锁然后删除的
2, 对于第一条记录(32a1976b7833), 也即 col2 = 'X000000000089'的记录, 删除的加锁过程如下
2.1 对 (32a1976b7833), 即 col2 = 'X000000000089'的记录记录所在的 page 加共享排它锁, 对(32a1976b7833) 记录所在的行加 U 锁
2.2 对 (32a1976b7833) 记录对应的主键所在的 page 加 IX 锁, 主键行加 RID 级别的 U 锁
2.3 对 2.2 对 (32a1976b7833) 记录对应的 RID 所在的 page 加 IX 锁, 主键行加 RID 级别的 X 锁
2.4 对 2.2 对 (32a1976b7833) 记录对应的主键所在的 page 加 IX 锁, 主键行加 RID 级别的 U 锁
2.5 对 2.2 对 (32a1976b7833) 记录对应的主键所在的 page 加 IX 锁, 主键行加 KEY 级别的 X 锁
2.6 释放 KEY 与 Page 级别的 X 锁和 IX 锁
2.7 重复 2.1 对 (32a1976b7833) 记录所在的 page 加共享排它锁, 对 (32a1976b7833) 记录所在的行加 U 锁
2.8 释放 (32a1976b7833) 以及其所在 page 的 X 锁和 IX 锁
2.9 对 (d12bea8cbd9f) 这个记录, 也即 Col3 字段上的索引依次加 page 上的 IX 锁, key 上的 X 锁
2.10(反向)依次释放 Col3 key 上的 X 锁, page 上的 IX 锁
2.11 依次释放上述其他的锁
简而言之, 遵循两段锁协议(2PL), 以行为基础, 加锁与释放所过程独立, 互不干扰.
因为走了 Col2 上的索引, 这个过程大概是: 先申请 Col2 上的 U 锁, 找到其 RID 和主键索引, 然后依次删除这 RID 和主键索引, 然后再删除 Col2 上索引的 key, 最后删除对应的 Col3 上的索引 key
最后释放所有上面申请的锁
上述是删除多条数据其中一条数据的加锁以及释放锁的过程, 很清楚的看到, Col2 上的 U 锁只是在第一步申请的, Col3 上根本没有申请 U 锁, 而是直接申请的 X 锁, 然后删除, 然后再释放
因为死锁双方的数据是互不交叉的, U 锁又是单独只在 Col2 索引上申请的, 那么为什么会出现死锁双方相互等待 Col2 与 Col3 上的 U 锁, 从而造成死锁?
之前没有想明白, 是因为就存在一种想当然的推断过程, 两个 session 的删除语句都走 col2 上的索引, 当然不会出现两个 session 相互申请 Col2 与 Col3 上的 U 锁
一旦存在 Session1 走 Col2 上的索引, Session2 走 Col3 上的索引, 才有可能出现 ession 相互申请 Col2 与 Col3 上的 U 锁的可能性
对于 Session1 和 Session2
1,session1 执行 delete from TableA where col2 in (x,y,z);
2,session2 执行 delete from TableA where col2 in (l,m,n);
理论上说, 或者相当然地说, 都会走 col2 上的索引, 但是不能完全肯定一定都会走 Col2 上的索引, 或许有可能走全表扫描, 或者有可能走 Col3 上的索引扫描
比如如下的强制索引提示, 走任何一种执行计划, 都是可能的, 尽管可能会在主观上认为某些执行计划是不好的, 但是这个语句在没有任何索引提示的时候, 不能臆测一定会走 col2 上的索引
否则不会出现 session 双方持有了 Col2 索引上的 U 索引, 申请 Col3 索引上的 U 锁, 否则这个死锁就解释不通.
实际上, 上述死锁, 有可能是一个执行计划走了 Col2 上的索引查找方式删除, 需要先在 Col2 索引上加 U 锁
一个是走了走了全表扫描造成的, 类似于 delete t from TestDeadLock t with(index(0)) where Col2 in ( 'X000000000089','X000000000095')的执行计划
后者先在 Col3 上加 U 锁, 然后找到其对应的 RID, 主键索引, Col2 上的索引, 依次加 U 锁, 加 X 索引, 这样才潜在死锁的可能性
写不下去了, 钻研 SQL Server 的人实在太少了, 如果是 MySQL, 一定会有大神回去做深入的分析, 这个 case 笔者多次尝试重现它, 包括使用 Python 多线程的方式模拟当时的场景, 都无疾而终, 无法重现
发生死锁的这个真实情况下的场景, 也不会经常出现, 笔者也只是偶尔捞到死锁的 xml_deadlock_report 尝试作分析, 均无果.
这个死锁, 是笔者遇到的不多的无法重现或者模拟出来的死锁, 但愿有高手感兴趣的话, 进一步做分析尝试, 即便是推翻笔者猜测的结论, 得出更有说服力的结果.
以上.
来源: http://www.linuxidc.com/Linux/2019-09/160788.htm