如何解释SQL死锁跟踪？

Question

我们的SQL服务器上出现了死锁。我在堆栈溢出和其他地方读了很多页，但是我找不到如何读取跟踪日志的一步一步的指令列表。有人能告诉我如何解释这个吗？显然现在我需要知道如何解释这个特定的日志，但是我真正需要的是长期学习如何读取未来的日志。

完整的跟踪日志如下。让我解释一下我们是如何解释的。然后你可以告诉我我们做错了什么，以及如何正确阅读。

我们在想这些话：

06/14/2018 14:56:25， spid8s，未知， ResType： LockOwner样式：'OR'Xdes： 0x000000086D8ECD90模式： U SPID： 171 BatchID： 2 ECID： 0任务代理：（0x0000000125F1C768）值： 0x2dade880成本：（0/2000) 06/14/2018 14:56:25， spid8s，未知，受害者资源所有者：

也就是说，“受害者”和“Xdes:0x000000086D8ECD90”意味着进程0x000000086D8ECD90是关闭的，因为某些资源上存在死锁。

然后在这些行中再次提到0x000000086D8ECD90：

意味着从site_updates删除命令是由于表上的死锁而停止的命令site_updates

然后这句话：

06/14/2018 14:56:25，spid24s，未知，从site_updates删除table_name='shipment_head'和update_type='插入'和row_id（从插入中选择id

显示这个命令是导致site_update死锁的第一个命令。

我们接近了吗？远离基地？有没有办法确定到底发生了什么和/或如何修复？（例如“表站点上的第一次删除\u更新对表进行了独占锁定，因此在运行第二次删除时导致死锁。要解决此问题，请在第一次删除之前添加XXXX，以便表不会被锁定。）

完整死锁跟踪：

06/14/2018 14:56:25,spid24s,Unknown,keylock hobtid=345803707580416 dbid=7 objectname=MyDatabase.dbo.site_updates indexname=PK_site_updates id=lock140bfd400 mode=X associatedObjectId=345803707580416
06/14/2018 14:56:25,spid24s,Unknown,resource-list
06/14/2018 14:56:25,spid24s,Unknown,IF @@TRANCOUNT > 0 BEGIN exec sp_add_shipment 6379785<c/> 'GND'<c/> '4.00'<c/> 'CLCH105 (6pcs)'<c/> '6x83066'<c/> 'fedexsmartpost'<c/> 'MyDomain\user1'<c/> 'MyDomain\user1' END

06/14/2018 14:56:25,spid24s,Unknown,delete from site_updates where table_name = 'shipment_head' and update_type = 'inserted' and row_id in (select id from inserted
06/14/2018 14:56:25,spid24s,Unknown,frame procname=MyDatabase.dbo.insert_shipment_head line=5 stmtstart=152 stmtend=406 sqlhandle=0x030007000e51c2768d206101239e000000000000000000000000000000000000000000000000000000000000
06/14/2018 14:56:25,spid24s,Unknown,executionStack
06/14/2018 14:56:25,spid24s,Unknown,process id=process7a5313c28 taskpriority=0 logused=88216 waitresource=KEY: 7:345803707580416 (c8ec095a9952) waittime=8911 ownerId=3985893541 transactionname=user_transaction lasttranstarted=2018-06-14T14:56:09.940 XDES=0x152b64d90 lockMode=U schedulerid=3 kpid=8252 status=suspended spid=277 sbid=2 ecid=0 priority=0 trancount=2 lastbatchstarted=2018-06-14T14:56:16.507 lastbatchcompleted=2018-06-14T14:56:16.453 lastattention=1900-01-01T00:00:00.453 clientapp=.Net SqlClient Data Provider hostname=NTSWKS01 hostpid=1340 loginname=MyDomain\ups isolationlevel=read committed (2) xactid=3985893541 currentdb=7 lockTimeout=4294967295 clientoption1=671219744 clientoption2=128056

06/14/2018 14:56:25,spid24s,Unknown,IF @@TRANCOUNT > 0 BEGIN update order_head set address_flag = 'UR'<c/>customer_id = 0<c/> ship_name = '17106'<c/> 06/14/2018 14:56:25,spid24s,Unknown,delete from site_updates where table_name = 'order_head' and update_type = 'update' and row_id in (select id from inserted
06/14/2018 14:56:25,spid24s,Unknown,frame procname=MyDatabase.dbo.update_order_head line=5 stmtstart=168 stmtend=412 sqlhandle=0x03000700a2b0aa21d638280131a7000000000000000000000000000000000000000000000000000000000000
06/14/2018 14:56:25,spid24s,Unknown,executionStack
06/14/2018 14:56:25,spid24s,Unknown,process id=process624d9c108 taskpriority=0 logused=2000 waitresource=KEY: 7:345803707580416 (a1de22506108) waittime=9019 ownerId=3985897338 transactionname=user_transaction lasttranstarted=2018-06-14T14:56:16.413 XDES=0x86d8ecd90 lockMode=U schedulerid=3 kpid=10664 status=suspended spid=171 sbid=2 ecid=0 priority=0 trancount=2 lastbatchstarted=2018-06-14T14:56:16.423 lastbatchcompleted=2018-06-14T14:56:16.423 lastattention=1900-01-01T00:00:00.423 clientapp=.Net SqlClient Data Provider hostname=NTSWKS56 hostpid=12396 loginname=MyDomain\user2 isolationlevel=read committed (2) xactid=3985897338 currentdb=7 lockTimeout=4294967295 clientoption1=671219744 clientoption2=128056
06/14/2018 14:56:25,spid24s,Unknown,process-list
06/14/2018 14:56:25,spid24s,Unknown,deadlock victim=process624d9c108
06/14/2018 14:56:25,spid24s,Unknown,deadlock-list
06/14/2018 14:56:25,spid8s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x000000086D8ECD90 Mode: U SPID:171 BatchID:2 ECID:0 TaskProxy:(0x0000000125F1C768) Value:0x2dade880 Cost:(0/2000)
06/14/2018 14:56:25,spid8s,Unknown,Victim Resource Owner:
06/14/2018 14:56:25,spid8s,Unknown,
06/14/2018 14:56:25,spid8s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000000152B64D90 Mode: U SPID:277 BatchID:2 ECID:0 TaskProxy:(0x00000006E682E768) Value:0xbbe1a740 Cost:(0/88216)
06/14/2018 14:56:25,spid8s,Unknown,Requested by:
06/14/2018 14:56:25,spid8s,Unknown,Owner:0x0000000338998680 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:171 ECID:0 XactLockInfo: 0x000000086D8ECDC8
06/14/2018 14:56:25,spid8s,Unknown,Grant List 1:
06/14/2018 14:56:25,spid8s,Unknown,KEY: 7:345803707580416 (c8ec095a9952) CleanCnt:2 Mode:X Flags: 0x1
06/14/2018 14:56:25,spid8s,Unknown,Node:2
06/14/2018 14:56:25,spid8s,Unknown,
06/14/2018 14:56:25,spid8s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x000000086D8ECD90 Mode: U SPID:171 BatchID:2 ECID:0 TaskProxy:(0x0000000125F1C768) Value:0x2dade880 Cost:(0/2000)
06/14/2018 14:56:25,spid8s,Unknown,Requested by:
06/14/2018 14:56:25,spid8s,Unknown,Owner:0x00000004E373B900 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:277 ECID:0 XactLockInfo: 0x0000000152B64DC8
06/14/2018 14:56:25,spid8s,Unknown,Grant List 0:
06/14/2018 14:56:25,spid8s,Unknown,KEY: 7:345803707580416 (a1de22506108) CleanCnt:2 Mode:X Flags: 0x1
06/14/2018 14:56:25,spid8s,Unknown,Node:1
06/14/2018 14:56:25,spid8s,Unknown,
06/14/2018 14:56:25,spid8s,Unknown,Wait-for graph
06/14/2018 14:56:25,spid8s,Unknown,Deadlock encountered .... Printing deadlock information

*更新*这里是建议的闪电锁输出的一部分。仍然不确定如何解释这一点。

Answer 1

我强烈建议您使用https://www.brentozar.com/archive/2017/12/introducing-sp_blitzlock-troubleshooting-sql-server-deadlocks/这将允许您以新的方式跟踪和理解您的死锁问题。

此外，您可能希望转到system_helath extended event并查找：