sql-server - 如何解释T-SQL死锁跟踪

我试图通过查看t-sql跟踪来解决死锁问题，但我很难理解这些信息。我在这里有一个信息摘要，然后在文章末尾有完整的死锁跟踪：
第一个锁在名为dbo.retailvoucher的表上
第二把锁在一张叫做ooc.planneduversalvoucher的桌子上
第一个存储过程读取以下表：
dbo.retailVoucher、ooc.plannedOrderitem和ooc.plannedOrder。
第二个存储过程读取以下表：
ooc.plannedbatch和ooc.planneduniversalvoucher
现在，为了让这些程序彼此等待，我想它们在某个时刻都必须读同一张表。很明显我不明白怎么解释这个痕迹。在其他过程中，是否需要查找dbo.retailervoucher和ooc.planneduversalvoucher上的锁的来源？
谢谢。
这是完整的线索：

<deadlock-list>
 <deadlock victim="process894748">
  <process-list>
   <process id="process894748" taskpriority="0" logused="3064" waitresource="KEY: 8:72057594084655104 (845afc30a382)" waittime="944" ownerId="12987790066" transactionname="user_transaction" lasttranstarted="2014-04-10T19:07:02.250" XDES="0x803d53c0" lockMode="S" schedulerid="3" kpid="14356" status="suspended" spid="67" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2014-04-10T19:07:02.287" lastbatchcompleted="2014-04-10T19:07:02.287" clientapp=".Net SqlClient Data Provider" hostname="ID13115" hostpid="4872" loginname="UVUser" isolationlevel="read committed (2)" xactid="12987790066" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="UVSystem.ooc.PlannedPacketPreserveEVouchers" line="144" stmtstart="12286" stmtend="13618" sqlhandle="0x0300080039a18c7fa2ed9800dda200000100000000000000">
update dbo.RetailVoucher
            set QuantityInStock -= rvsum.QuantitySum
            from dbo.RetailVoucher urv
            join (
                select oib.id, SUM(oib.Quantity) as QuantitySum
                from (
                    select coalesce(rv.RelatedVoucherId, rv.Id) id, oi.Quantity
                    from
                        ooc.PlannedOrderItem oi
                        join ooc.PlannedOrder po on oi.PlannedOrderId = po.Id
                        join RetailVoucher rv on oi.RetailVoucherId = rv.Id
                    where po.PlannedPacketId = @PlannedPacketId
                        and oi.PartnerId is null
                        and oi.OnDmenadServiceId is null
                        and rv.VoucherTypeId = 1
                ) oib
                group by oib.Id
            ) rvsum
            on urv.Id = rvsum.id;

         </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 8 Object Id = 2139922745]    </inputbuf>
   </process>
   <process id="processd0db8088" taskpriority="0" logused="10716" waitresource="PAGE: 8:1:778990" waittime="1078" ownerId="12987783115" transactionname="user_transaction" lasttranstarted="2014-04-10T19:07:01.473" XDES="0x144975950" lockMode="S" schedulerid="7" kpid="11172" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-04-10T19:07:01.473" lastbatchcompleted="2014-04-10T19:07:01.473" clientapp=".Net SqlClient Data Provider" hostname="ID13115" hostpid="12168" loginname="UVUser" isolationlevel="read committed (2)" xactid="12987783115" currentdb="8" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="UVSystem.ooc.OfflineOrdersImportPacket" line="294" stmtstart="23548" stmtend="24026" sqlhandle="0x030008006d6796551b829700dda200000100000000000000">
update ooc.PlannedBatch
    set
        IsCompleted = @True
    from ooc.PlannedBatch pb
        join ooc.PlannedUniversalVoucher puv on puv.PlannedBatchId = pb.Id
        left outer join @DuplicatedVouchers dv on dv.Id = puv.Id
    where dv.Id is null;     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 8 Object Id = 1435920237]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594084655104" dbid="8" objectname="UVSystem.dbo.RetailVoucher" indexname="PK_RetailVoucher" id="lock10ccbd180" mode="X" associatedObjectId="72057594084655104">
    <owner-list>
     <owner id="processd0db8088" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process894748" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <pagelock fileid="1" pageid="778990" dbid="8" objectname="UVSystem.ooc.PlannedUniversalVoucher" id="lock15ba81480" mode="IX" associatedObjectId="72057594113425408">
    <owner-list>
     <owner id="process894748" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="processd0db8088" mode="S" requestType="wait"/>
    </waiter-list>
   </pagelock>
  </resource-list>
 </deadlock>
</deadlock-list>

最佳答案

让我试试。
您有THO进程，processd0db8088和process894748。从执行堆栈中可以看到，process894748正在执行plannedpackketpreserveevouchers，processd0db8088正在执行offlineordersimportpacket。您也可以在那里看到这两个进程的问题查询。
从资源列表中可以找到这两个进程死锁的原因。第一个是retailtoucher表上pk_retailtoucher index中的一个特定索引行：processd0db8088独占地锁定了它，而process894748正在等待获得对它的共享锁。另一个资源是属于planneduniversalvoucher表的数据页778990，在processd0db8088等待获得对它的读取访问时，由process894748持有。
这些锁可能从事务开始时就被拿走了。执行堆栈中显示的查询是等待资源的查询，而不是获取锁的查询。检查整个执行跟踪以找出锁的实际位置。
标准建议适用于：缩短事务、按顺序访问表、适当索引。

关于sql-server - 如何解释T-SQL死锁跟踪，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23053560/