追踪SQL Server执行delete操作时候不同锁申请与释放的过程

 

一直以为很了解sqlserver的加锁过程，在分析一些特殊情况下的死锁之后，尤其是并发单表操作发生的死锁，对于加解锁的过程，有了一些重新的认识，之前的知识还是有一些盲区在里面的。
delete加锁与解锁步骤是怎么样的？什么时候对那些对象，加什么类型的琐，加锁与索引的关系是怎么样的，什么时候释放锁？整个过程锁是如何参与整个delete操作过程表的？
这里通过一个非常简单的delete语句，来分析一条delete执行过程中加解锁的过程。

测试表创建

用一个最最简单的例子做了跟踪，对锁的申请和释放，有了更清晰的认识，这个过程非常有意思，看完之后会非常清晰地认识到delete语句执行过程中加解锁的步骤是怎么样的。
如果对一个sql加解锁的步骤不清楚，解决死锁，只能说经验，或者说是靠蒙、亦或靠优化sql地去减少死锁的可能性（天下武功，唯快不破）。
如下脚本，创建测试表，待用。

跟踪的测试删除sql语句为：delete from  test_require_release_lock where col2 = 'a999' and col3 = 'b999'，where条件的目标数据是1行，条件是用到where的第一个筛选条件的索引。

 

测试数据的基本信息

先拿到一些基本的信息对象id，索引id，key值等物理id（rowhashid）等等，这些id稍后会清晰地展示在profile跟踪日志中，能帮助我们去了解到底在什么对象（数据行，索引行）加什么类型的锁。

当前值得物理地址（rowhashid）

  

profile跟踪日志

如下是profile跟踪出来的delete from test_require_release_lock where col2 = 'a999' and col3 = 'b999'语句执行过程中表级别锁的申请和释放
我只能说：这个case包括数据，是反复测试各种情况之后，最最简单的一种情况了，稍微复杂一点的情况，对于一行数据的删除，加解锁的过程至少要两屏才能显示出来。

 

加解锁过程分析

这个过程可以大致分为3个阶段，仅仅执行一条数据删除的delete语句，就有37步之多，为了简化这个过程，这里只看删除过程中，数据/索引行上的加解锁信息，如图所示的前23步。
阶段1:根据where条件查找数据的物理id，也即rid，这个过程是iu/u锁。虽然执行的是delete，此过程不删任何数据，只是根据条件，找到数据的rid
阶段2：依次删除数据行和索引行，也即依次删除rid，主键索引，多个索引键索引，这个过程涉及到第一步的iu/u锁转换ix/x以及新申请ix/x锁
阶段3：（删除完成）依次释放之前步骤申请尚未释放的锁

如下是profile中这个过程中每一步的说明

阶段1：
1，申请基表82099333上意向ix锁
2，申请目标行索引字段col2(fe51867f3259)所在page的iu锁
3，申请目标行索引字段col2(fe51867f3259)key级别的u锁
4，申请目标所索引字段col2(fe51867f3259)对应的rid所在page的iu锁
5，申请目标所索引字段col2(fe51867f3259)对应的rid行的rid的u锁
阶段2
6，升级4申请的iu锁成ix锁
7，升级5申请的u锁成x锁　　***删除rid行，也即数据行
8，申请6（已经申请到的）rid对应的主键索引(9606db9499cf)所在page的ix锁
9，申请主键索引的key级别的x锁(9606db9499cf)，***删除主键行
10，释放9申请的锁
11，释放8申请的锁
12，申请目标行索引字段col2 (fe51867f3259)所在page的ix锁
13，申请目标行索引字段col2(fe51867f3259)key级别的x锁，***删除col2上的索引行
14，释放13申请的锁
15，释放12申请的锁
16，申请目标行索引字段(fe51867f3259)对应的col3字段(aa0cc0efc6d9)索引所在page的ix锁
17，申请目标行索引字段(fe51867f3259)对应的col3字段(aa0cc0efc6d9)索引key级别的x锁，***删除col3上的索引行
阶段3
18，释放16申请的锁
19，释放17申请的锁
20，释放6申请的锁
21，释放7申请的锁
22，释放4申请的锁
23，释放5申请的锁
24~37 释放其它锁

从中可以看到，锁的申请的简化过程是，或者其规律是：
1，根据查询条件，依次申请查询字段所在page以及字段key本身的iu/u锁，因为这个阶段是找数据（找到加iu/u），而不是直接上来就删数据，所以是iu/u锁
2，根据nocluster index 找到rid，从iu/u升级rid锁称ix/x，进行数据行的删除
3，依次删除主键索引，col2上的索引，col3上的索引，进行索引行的删除
4，依次释放加锁信息，此过程于加锁相反（先page再key），依次释放key/rid和page上的锁（先key再page）

如果是聚集索引表，会用聚集索引key提到rid，省略rid这一步的锁操作。

从这里可以看到，没有聚集索引的表，主键索引跟普通的非聚集索引并无二致，最终还是要使用rid来定位数据。
这里还能够得到另外一个结论：因为这里的主键索引是nonclustered的，因此该表还是数据堆表，既然是堆表，定位数据的还是rid，因此多了依次rid的锁维护动作

 

以上跟踪了一个最简单的delete执行过程中的加锁的步骤，其实情况可以更复杂：
1如果where条件用不到索引，
2如果where条件之一筛选出来的多行，继续用另外的条件筛选，
3如果使用聚集索引筛选，
4如果where条件筛选后仍旧有多行数据
5如果where条件无法命中任何一行
6如果采用多个非聚集索引筛选后merge结果
稍微复杂一点的情况，涉及到的锁都呈指数级增加，原本我以为很清楚sqlserver在执行delete操作的加锁过程，其实还有很多细节，没有注意到。
加解锁是一个复杂的过程，即便是单表，也会涉及不同的索引以及数据行，为此并不难理解，为什么对于单表，除了where条件不一致，为什么会出现死锁的原因。