死锁问题：2页同时出现死锁

Question

我正在解决一个生产环境中出现的死锁问题，这是我第一次接触，但是有些东西让我感到奇怪。下面是死锁图：

死锁的右侧是以下更新操作:

UPDATE order_sub_line SET sub_line_status = 300 WHERE order_sub_line_id = '75C387EC-A1A7-4587-9FA0-DD33A49009BC'

从图中看，这个更新尝试获取2个页面锁。order_sub_line_id是一个聚集索引。

如果是这样的话，为什么需要尝试获取2个页面锁？

额外信息：

死锁受害者是一个视图（连接了几个其他表，包括order_sub_line），其基本上在该表上运行以下查询：

select top(50) * from order_sub_line osl where osl.sub_line_type = 1 and osl.sub_line_status < 375

除了order_sub_line.order_sub_line_id的聚集主键索引外，order_sub_line上没有其他索引。

执行计划：

死锁xml：

<deadlock-list>
 <deadlock victim="process4224eccf8">
  <process-list>
   <process id="process4224eccf8" taskpriority="0" logused="0" waitresource="PAGE: 7:1:13448 " waittime="1628" ownerId="1683307923" transactionname="SELECT" lasttranstarted="2013-07-31T08:45:53.157" XDES="0x48afafc40" lockMode="S" schedulerid="2" kpid="1208" status="suspended" spid="151" sbid="0" ecid="15" priority="0" trancount="0" lastbatchstarted="2013-07-31T08:45:53.157" lastbatchcompleted="2013-07-31T08:45:53.157" lastattention="1900-01-01T00:00:00.157" clientapp="ExactaAOR" hostname="BASTIAN-PC" hostpid="7336" isolationlevel="read committed (2)" xactid="1683307923" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="72" sqlhandle="0x0200000055b04f0c4d136173c4d51458bdb5002bfe5801370000000000000000000000000000000000000000">
SELECT TOP (@p0)  this_.TRANSPORT_CNTNR_ID as TRANSPORT1_9_0_, this_.CNTNR_NAME as CNTNR2_9_0_, this_.CNTNR_TYPE as CNTNR3_9_0_, this_.CNTNR_HEIGHT as CNTNR4_9_0_, this_.CNTNR_WIDTH as CNTNR5_9_0_, this_.CNTNR_DEPTH as CNTNR6_9_0_, this_.CNTNR_WEIGHT as CNTNR7_9_0_, this_.PARENT_CNTNR_ID as PARENT8_9_0_, this_.RESERVATION_LOC_ID as RESERVAT9_9_0_, this_.WORK_AREA_ID as WORK10_9_0_, this_.WORK_AREA_NAME as WORK11_9_0_, this_.GROUP_ID as GROUP12_9_0_, this_.RELEASE_STATUS as RELEASE13_9_0_, this_.RELEASE_TIME as RELEASE14_9_0_, this_.PRINT_STATUS as PRINT15_9_0_, this_.SUB_LINE_COUNT as SUB16_9_0_, this_.ORDER_ID as ORDER17_9_0_, this_.QTY_REQUESTED as QTY18_9_0_, this_.ORDER_NAME as ORDER19_9_0_, this_.ORDER_PRIORITY as ORDER20_9_0_, this_.ORDER_STATUS as ORDER21_9_0_, this_.ON_HOLD as ON22_9_0_, this_.DUE_DATE as DUE23_9_0_, this_.ORDER_SUB_LINE_STATUS as ORDER24_9_0_ FROM V_CARTON_RELEASE this_ WHERE (this_.RELEASE_STATUS = @p1 and this_.ORDER_SUB_LINE_STATUS &lt; @p2) ORDER BY this_.RELEASE_TIME asc     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@p0 int,@p1 nvarchar(4000),@p2 int)SELECT TOP (@p0)  this_.TRANSPORT_CNTNR_ID as TRANSPORT1_9_0_, this_.CNTNR_NAME as CNTNR2_9_0_, this_.CNTNR_TYPE as CNTNR3_9_0_, this_.CNTNR_HEIGHT as CNTNR4_9_0_, this_.CNTNR_WIDTH as CNTNR5_9_0_, this_.CNTNR_DEPTH as CNTNR6_9_0_, this_.CNTNR_WEIGHT as CNTNR7_9_0_, this_.PARENT_CNTNR_ID as PARENT8_9_0_, this_.RESERVATION_LOC_ID as RESERVAT9_9_0_, this_.WORK_AREA_ID as WORK10_9_0_, this_.WORK_AREA_NAME as WORK11_9_0_, this_.GROUP_ID as GROUP12_9_0_, this_.RELEASE_STATUS as RELEASE13_9_0_, this_.RELEASE_TIME as RELEASE14_9_0_, this_.PRINT_STATUS as PRINT15_9_0_, this_.SUB_LINE_COUNT as SUB16_9_0_, this_.ORDER_ID as ORDER17_9_0_, this_.QTY_REQUESTED as QTY18_9_0_, this_.ORDER_NAME as ORDER19_9_0_, this_.ORDER_PRIORITY as ORDER20_9_0_, this_.ORDER_STATUS as ORDER21_9_0_, this_.ON_HOLD as ON22_9_0_, this_.DUE_DATE as DUE23_9_0_, this_.ORDER_SUB_LINE_STATUS as ORDER24_9_0_ FROM V_CARTON_RELEASE this_ WHERE (this_.RELEASE_STATUS = @p1 and this_.ORDER_SUB_LINE_STATUS &lt; @p2) ORDER    </inputbuf>
   </process>
   <process id="process4bf7bdc38" taskpriority="0" logused="8608" waitresource="PAGE: 7:1:13447 " waittime="1616" ownerId="1683308190" transactionname="user_transaction" lasttranstarted="2013-07-31T08:45:53.450" XDES="0x4ebd456a8" lockMode="IX" schedulerid="1" kpid="6032" status="suspended" spid="85" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-07-31T08:45:53.450" lastbatchcompleted="2013-07-31T08:45:53.450" lastattention="1900-01-01T00:00:00.450" clientapp="ExactaAOR" hostname="BASTIAN-PC" hostpid="7336" loginname="asapdb" isolationlevel="read committed (2)" xactid="1683308190" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="60" sqlhandle="0x02000000109639184c42e35fa55701e017640d83bd4818c30000000000000000000000000000000000000000">
UPDATE ORDER_SUB_LINE SET SUB_LINE_STATUS = @p0 WHERE ORDER_SUB_LINE_ID = @p1     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@p0 int,@p1 uniqueidentifier)UPDATE ORDER_SUB_LINE SET SUB_LINE_STATUS = @p0 WHERE ORDER_SUB_LINE_ID = @p1    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <pagelock fileid="1" pageid="13448" dbid="7" subresource="FULL" objectname="Exactadb.dbo.order_sub_line" id="lock4cf017000" mode="IX" associatedObjectId="72057594460962816">
    <owner-list>
     <owner id="process4bf7bdc38" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process4224eccf8" mode="S" requestType="wait"/>
    </waiter-list>
   </pagelock>
   <pagelock fileid="1" pageid="13447" dbid="7" subresource="FULL" objectname="Exactadb.dbo.order_sub_line" id="lock4a4554500" mode="S" associatedObjectId="72057594460962816">
    <owner-list>
     <owner id="process4224eccf8" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process4bf7bdc38" mode="IX" requestType="wait"/>
    </waiter-list>
   </pagelock>
  </resource-list>
 </deadlock>
</deadlock-list>

- Cole W

---

请记住，聚集索引存储为B树，更新将需要从根节点开始导航，通过任何中间级别到达叶节点。请参阅http://msdn.microsoft.com/en-us/library/ms177443(v=sql.105).aspx。 - Steve Ford

@AdamHaines 我已经发布了执行计划。 - Cole W

你需要发布实际的死锁 XML，而不是图片。 - Remus Rusanu

@RemusRusanu 添加了 XML。 - Cole W

@AdamHaines 那个表上有几个触发器。实际上，在更新时有4个不同的触发器。这是一个很好的观点。看起来其中一些正在对该表进行选择操作。 - Cole W

Answer 1

从提供的数据可以得出以下结论：

1. 两个事务都运行在读提交隔离级别下。
2. 一个事务正在执行多个单独的行更新操作，这很明显是由于一个进程持有 IX 锁并等待另一个进程造成的。根据执行计划，UPDATE 语句使用单个行聚集索引查找。因此，它将在 KEY 级别获得 X 锁和页面级别的 IX 锁。
3. SELECT 语句使用页面级锁定，也会在读取页面后保留锁定。在正常情况下，在读提交隔离级别下，SELECT 语句将在读取后立即获取和释放共享锁。

通过这些发现，我几乎可以确定死锁是由一种称为无序预取的查询优化特殊情况引起的。这是我所知道的唯一一种情况，其中在 READ COMMITTED 隔离级别下运行的 SELECT 语句将保留 SHARED 锁直到语句结束。

关于这种类型死锁的复现和可能的解决方案，可访问https://web.archive.org/web/20120806214319/http://sqlindian.com/2012/07/13/deadlock-on-select-due-to-unordered-prefetch/。

- Roji P Thomas