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

平时遇到的死锁,绝大多数情况下,都可以根据当时的场景进行重现,然后具体分析解决,下文这个死锁几次尝试测试模拟,均没有成功重现
在尝试用profile跟踪加锁顺序之后,大概可以推断到当时死锁发生的原因,但是仍有无法重现,为了避免不必要的麻烦,这里用测试表的方式,尽可能还原尝试的场景,来做进一步的分析。
死锁发生的场景如下(暂不论表设计合不合理,索引合不合理,sql语句写法合不合理,分析死锁是主要目的,解决死锁是另外一回事)

目标表为TestDeadLock,大概结构如下
1,TestDeadLock表为堆表(有自增列的主键,但是主键nonclusterd),col2.col3上的索引分别是idx_col2,idx_col3,Col2无重复,Col3上重复值较多,表数据量不会很少,几千行或者几万行的样子,
2,存在并发按照多个Col2字段删除的情况

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="process6d3048" mode="U" />
    </owner-list>
    <waiter-list>
        <waiter id="process6d3b88" mode="U" requestType="wait" />
    </waiter-list>
    </keylock>
    <keylock hobtid="YYYYYYYYYYYYY" dbid="6" objectname="" indexname="" id="lock126403100" mode="U" associatedObjectId="YYYYYYYYYYYYY">
    <owner-list>
        <owner id="process6d3b88" mode="U" />
    </owner-list>
    <waiter-list>
        <waiter id="process6d3048" 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尝试作分析,均无果。

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

以上。

 

上一篇:SQL IN 子查询返回多对值


下一篇:django - (二) 配置数据库连接