SQL Server Deadlock

上海isp

　　最近做性能优化时碰到下面的死锁问题，起初看到这个图感觉很诡异，无法理解，两个查询语句为啥会导致死锁呢？查询语句为啥会导致X(排他)锁的产生呢？最后在查阅了一部分资料之后，终于可以解释通了。 

 
1. 首先，此图中两个Key Lock分别代表数据库表上的两个行各自的行锁，是两个锁，而不是一个锁。图中的两个事务执行的是相同的代码，在事务中，首先做了一个查询，最后对查询的数据进行了更新。因此，就可以理解图中X(排他)锁是因为事务中的更新语句导致的，而不是查询语句导致的(虽然右键两个事务显示的都是相同的查询语句)。
　　所以，此图的意思是事务762385持有下面方框对应的行X(排他)锁，正在申请上面方框对应的行S(共享)锁。而事务762460持有上面方框对应的行X(排他)锁，正在申请下面方框对应的行S(共享)锁。而X锁和S锁是不能同时获得的，因此就发生了等待死锁。
　　
　　2. 再来看看查询语句为啥会导致申请S(共享)锁失败，查询语句的执行计划如下：可以看到查询语句使用主键生成的Clustered索引进行了全表扫描，因此它会导致与更新无关的行在扫描的过程当中也需要申请S(共享锁)。

 

　　3. 如何只申请需要更新行的S(共享)锁呢？因为我们的查询语句对应的的两个查询条件正好对应有一个索引，所以只需要修改查询语句，使得查询计划使用这个存在的索引，这样就可以达到目的了。但是这样会引入部分需要的字段无法在同一个查询语句中查出来。解决办法是，先通过这个修改的查询语句找到该行的主键，然后再通过主键找到整行，因为主键也不是进行全表扫描，只会申请更新行的S(共享)锁。执行计划如下：

 

　　4. 还有一种办法是将事务分为两个小的事务，一部分做查询，一部分做更新。这样就不会出现同一个事务既需要X(排他)锁，又需要S(共享)锁的情况，也可以避免死锁的发生。或者将此部分的逻辑进行顺序化，也可以避免死锁。
　　
　　参考文档：
　　1. SQL Server Deadlock - Two SELECT Exclusive Locks on Exact Same Index and Key [closed]
　　此文遇到了类似的问题，但是无解。问题描述相对比较准确。
　　
　　2. Locking in Microsoft SQL Server (Part 3 – Blocking in the system)
　　此文详细描述了持有X(排他)锁，申请S(共享锁)导致死锁的情况，和我们遇到的问题一致。
　　
　　3. SQL Server上的一个奇怪的Deadlock及其分析方法
　　此文描述了类似的死锁问题，对于原因讲述的比较详尽，操作方法执行比较困难。因为如果打出所有的SQL Trace，会产生数百万条的记录，如何在这些记录当中找出死锁对应的两个事务对应的SQL Trace还是相当有困难的。可以考虑将生成的SQL Trace导入到一张数据库表中，在导入的过程当中每隔5分钟删除所有我们不需要的事务对应的SQL Trace。这样最终得到的SQL Trace会减少到不到一万行。