阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

记录SQL Server中一次无法重现的死锁

252次阅读
没有评论

共计 4126 个字符,预计需要花费 11 分钟才能阅读完成。

平时遇到的死锁,绝大多数情况下,都可以根据当时的场景进行重现,然后具体分析解决,下文这个死锁几次尝试测试模拟,均没有成功重现
在尝试用 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

记录 SQL Server 中一次无法重现的死锁

以 delete from TestDeadLock where col2 in (‘X00000000003′,’X000000000020’)为例,这里先拿到其伪列 Id

记录 SQL Server 中一次无法重现的死锁

理论上,这句 sql 的执行,会走 col2 上的索引进行查找,然后再进行删除(delete 本来就是先查找再删除的过程),测试 case 也是预期地,走了 col2 上的索引

记录 SQL Server 中一次无法重现的死锁

查看锁的申请与释放过程

可以发现

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
最后释放所有上面申请的锁

记录 SQL Server 中一次无法重现的死锁

上述是删除多条数据其中一条数据的加锁以及释放锁的过程,很清楚的看到,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 锁,否则这个死锁就解释不通。

记录 SQL Server 中一次无法重现的死锁

实际上,上述死锁,有可能是一个执行计划走了 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 尝试作分析,均无果。

这个死锁,是笔者遇到的不多的无法重现或者模拟出来的死锁,但愿有高手感兴趣的话,进一步做分析尝试,即便是推翻笔者猜测的结论,得出更有说服力的结果。

以上。

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计4126字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中