Sql-Server

這個死鎖與我們的數據庫有關嗎？

  • April 2, 2019

我們的應用程序在客戶端託管的 SQL 企業集群中執行。我們沒有管理權限。他們說我們的數據庫正在產生死鎖，並向我們發送了一個巨大的跟踪文件。下面的剪輯顯示了“遇到死鎖”段落，其中有一些與我們的“數據庫 ID = 49”相關的段落。但是我沒有看到任何標識符將“遇到死鎖”段落與我們的段落聯繫起來。我們在 C# 中使用讀送出模式，並在 SQL 中打開“讀送出快照”隔離模式。

我的簡單問題是，從這個日誌中，如何判斷這個“遇到的死鎖”與我們的數據庫 #49 有關？因為在同一個日誌文件的其他地方，它們與同一個集群中的其他數據庫更清楚地辨識了其他死鎖。

我們已經要求擴展事件跟踪，但不知道它們需要多長時間。謝謝。

03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,Input Buf: RPC Event: Proc [Database Id = 49 Object Id = 1285631673]
03/20/2019 11:13:58,spid3s,Unknown,SPID: 72 ECID: 20 Statement Type: SELECT Line #: 44
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:20 TaskProxy:(0x00000003E0F2BE20) Value:0x5380c188 Cost:(0/10000)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x00000002221A7400  Xid Slot: 3<c/> Wait Slot: 6<c/> Task: 0x000000015380C188<c/> (Consumer)<c/> Exchange Wait Type: e_waitPipeGetRow<c/> Merging: 0
03/20/2019 11:13:58,spid3s,Unknown,Node:6
03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:25 TaskProxy:(0x0000000141F060A0) Value:0x72f3c188 Cost:(20/0)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x000000066ED0F160  Xid Slot: 4<c/> Wait Slot: 1<c/> Task: 0x0000000772F3C188<c/> (Producer)<c/> Exchange Wait Type: e_waitPipeNewRow<c/> Merging: 1
03/20/2019 11:13:58,spid3s,Unknown,Node:5
03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:26 TaskProxy:(0x00000003E0F2BAC0) Value:0x59403498 Cost:(20/0)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x00000001FC9157D0  Xid Slot: 6<c/> Wait Slot: 3<c/> Task: 0x0000000759403498<c/> (Producer)<c/> Exchange Wait Type: e_waitPipeNewRow<c/> Merging: 1
03/20/2019 11:13:58,spid3s,Unknown,Node:4
03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:26 TaskProxy:(0x0000000141F06100) Value:0x11dc1868 Cost:(20/0)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x000000018A587F20  Xid Slot: 5<c/> Wait Slot: 0<c/> Task: 0x0000000511DC1868<c/> (Producer)<c/> Exchange Wait Type: e_waitPipeNewRow<c/> Merging: 1
03/20/2019 11:13:58,spid3s,Unknown,Node:3
03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:21 TaskProxy:(0x00000003E0F2BE80) Value:0x72f3d868 Cost:(20/0)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x00000004A9A250E0  Xid Slot: 4<c/> Wait Slot: 0<c/> Task: 0x0000000772F3D868<c/> (Producer)<c/> Exchange Wait Type: e_waitPipeNewRow<c/> Merging: 1
03/20/2019 11:13:58,spid3s,Unknown,Node:2
03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:22 TaskProxy:(0x00000003E0F2BEE0) Value:0x11dc0cf8 Cost:(20/0)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x00000012F70769F0  Xid Slot: 7<c/> Wait Slot: 3<c/> Task: 0x0000000511DC0CF8<c/> (Producer)<c/> Exchange Wait Type: e_waitPipeNewRow<c/> Merging: 1
03/20/2019 11:13:58,spid3s,Unknown,Node:1
03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,Wait-for graph
03/20/2019 11:13:58,spid3s,Unknown,Deadlock encountered .... Printing deadlock information
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:27 TaskProxy:(0x0000000141F06160) Value:0xb8ebdc38 Cost:(0/10000)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x00000001FC914160  Xid Slot: 7<c/> Wait Slot: 0<c/> Task: 0x00000001B8EBDC38<c/> (Producer)<c/> Exchange Wait Type: e_waitPipeNewRow<c/> Merging: 0
03/20/2019 11:13:58,spid3s,Unknown,Node:10
03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:23 TaskProxy:(0x00000003E0F2BF40) Value:0x59402188 Cost:(0/10000)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x000000066ED0FCC0  Xid Slot: 1<c/> Wait Slot: 5<c/> Task: 0x0000000759402188<c/> (Consumer)<c/> Exchange Wait Type: e_waitPipeGetRow<c/> Merging: 0
03/20/2019 11:13:58,spid3s,Unknown,Node:9
03/20/2019 11:13:58,spid3s,Unknown,
03/20/2019 11:13:58,spid3s,Unknown,Input Buf: RPC Event: Proc [Database Id = 49 Object Id = 1285631673]
03/20/2019 11:13:58,spid3s,Unknown,SPID: 72 ECID: 19 Statement Type: SELECT Line #: 44
03/20/2019 11:13:58,spid3s,Unknown,ResType:ExchangeId Stype:'AND' SPID:72 BatchID:0 ECID:19 TaskProxy:(0x00000003E0F2BDC0) Value:0x59402558 Cost:(0/10000)
03/20/2019 11:13:58,spid3s,Unknown,Port: 0x0000000225F57790  Xid Slot: 2<c/> Wait Slot: 6<c/> Task: 0x0000000759402558<c/> (Consumer)<c/> Exchange Wait Type: e_waitPipeGetRow<c/> Merging: 0

它與您的數據庫有關，但它比正常的死鎖要復雜一些。

您遇到的死鎖在並行查詢中，而不是在兩個或多個其他查詢之間。

您可以通過生成的 XML 來判斷：

(Consumer)<c/> Exchange Wait Type: e_waitPipeGetRow

這裡有一個類似的問答：為什麼死鎖圖上有無受害者條目？

你可以做所有 Aaron 提到的事情，或者前往First Responder Kit並使用sp_BlitzLock. 預設情況下，它將檢查系統健康會話，但您也可以將其指向擷取死鎖 XML 的擴展事件會話。

引用自：https://dba.stackexchange.com/questions/233783

相關問答

Sql-Server

如何在 SQL Server 中鎖定表以防止其他事務 INSERT 並避免死鎖？

  • August 4, 2021
檢視問答
Sql-Server

啟用擴展事件的成本是多少？

  • June 2, 2021
檢視問答
Sql-Server

我可以創建一個使用者定義的表類型並在同一個事務中使用它嗎？

  • November 25, 2020
檢視問答
Sql-Server

如何將 SQL Server 死鎖報告中的鍵轉換為值？

  • August 27, 2020
檢視問答
Sql-Server

只插入死鎖

  • March 5, 2019
檢視問答
Sql-Server

將 DEADLOCK_PRIORITY 設置為 LOW 是否會導致儲存過程執行速度變慢？

  • January 15, 2019
檢視問答