Sql-Server
死鎖圖 - 倖存語句 inputbuf 不顯示被鎖定的對象
我在我的系統上擷取了一個死鎖，並且（匿名）XML 輸出如下：
<deadlock> <victim-list> <victimProcess id="processf4d9233468" /> </victim-list> <process-list> <process id="processf4d9233468" taskpriority="0" logused="0" waitresource="KEY: 6:72057594039631872 (d117f90e375f)" waittime="481" ownerId="840005340" transactionname="SELECT" lasttranstarted="2019-10-14T10:16:07.550" XDES="0xeec803db90" lockMode="S" schedulerid="16" kpid="7220" status="suspended" spid="145" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2019-10-14T10:16:07.547" lastbatchcompleted="2019-10-14T10:16:07.550" lastattention="1900-01-01T00:00:00.550" clientapp=".Net SqlClient Data Provider" hostname="MYWEBSERVER" hostpid="4512" loginname="MyOtherLogin" isolationlevel="read committed (2)" xactid="840005340" currentdb="6" currentdbname="MyDB" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="MyDB.MySchema.MyProc" line="13" stmtstart="670" stmtend="9106" sqlhandle="MYSQLHANDLE"> SELECT p.[Col25] , p.Col1 , pId.Col2 , p.Col3 , p.Col4 , p.Col5 , CONVERT(VARCHAR(10),p.Col6,103) , a.[Col7] , a.[Col8] , a.[Col9] , a.[Col10] , a.[Col11] , a.[Col12] , a.[Col13] , a.[Col14] , a.[Col15] , a.[Col16] , a.[Col17] , a.[Col18] , a.[Col19] FROM ( SELECT COALESCE(p.[Col34], p.[Col25]) AS Col25 , lpm.[Col20] , lpm.[Col21] , ISNULL(c.[Col22] + ' ','') + ISNULL(c.[Col23] + ' ','') + ISNULL(c.[Col3],'') , Table1.[Col10] AS [Col10] , CONVERT(VARCHAR(10), ae.[Col23], 103) + ' ' + CONVERT(VARCHAR(5),ae.[Col23],108) AS Col11 , ISNULL(Table2.[Col24], ae.[Col25]) AS Col12 , ISNULL </frame> </executionStack> <inputbuf> Proc [Database Id = 6 Object Id = 279672044] </inputbuf> </process> <process id="process103fec6f468" taskpriority="0" logused="292" waitresource="OBJECT: 6:2105058535:30 " waittime="1449" ownerId="840433263" transactionname="user_transaction" lasttranstarted="2019-10-14T10:16:42.233" XDES="0x103f9fb0e80" lockMode="IX" schedulerid="31" kpid="9972" status="suspended" spid="130" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-10-14T10:16:42.240" lastbatchcompleted="2019-10-14T10:16:42.237" lastattention="1900-01-01T00:00:00.237" clientapp=".Net SqlClient Data Provider" hostname="MYWEBSERVER2" hostpid="3948" loginname="MyLogin" isolationlevel="read committed (2)" xactid="840433263" currentdb="6" currentdbname="MyDB" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="adhoc" line="1" stmtstart="478" stmtend="1170" sqlhandle="0x020000006b742230b8276e1a3e7508dd257586243f5833450000000000000000000000000000000000000000"> unknown </frame> <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> (@Col25 nvarchar(11),@Col26 nvarchar(11),@Col27 nvarchar(23),@Col28 nvarchar(12),@Col29 nvarchar(2),@Col30 nvarchar(4000),@Col23 nvarchar(23),@Col25 nvarchar(2),@Col31 nvarchar(11)) UPDATE [dbo].[TableInInputBuff] SET [Col25] = @Col25, [Col26] = @Col26, [Col27] = @Col27, [Col28] = @Col28, [Col29] = @Col29, [Col30] = @Col30, [Col23] = @Col23, [Col25] = @Col25 OUTPUT Inserted.Col31 WHERE [Col31]=@Col31 </inputbuf> </process> </process-list> <resource-list> <keylock hobtid="72057594039631872" dbid="6" objectname="MyDB.dbo.TableNotInInputBuff" indexname="PK__Encounte__4278DD362D27B809" id="lockf6be6aa900" mode="X" associatedObjectId="72057594039631872"> <owner-list> <owner id="process103fec6f468" mode="X" /> </owner-list> <waiter-list> <waiter id="processf4d9233468" mode="S" requestType="wait" /> </waiter-list> </keylock> <objectlock lockPartition="30" objid="2105058535" subresource="FULL" dbid="6" objectname="MyDB.dbo.TableInInputBuff" id="lock10332f42500" mode="S" associatedObjectId="2105058535"> <owner-list> <owner id="processf4d9233468" mode="S" /> </owner-list> <waiter-list> <waiter id="process103fec6f468" mode="IX" requestType="wait" /> </waiter-list> </objectlock> </resource-list> </deadlock>我可以看到倖存的程序 (spid130) 在一個表上有一個排他鎖，該鎖在程序部分
MyDB.dbo.TableNotInInputBuff顯示的查詢中沒有使用<inputbuf>``process103fec6f468我假設這裡發生的情況是輸入緩衝區僅顯示來自較大事務的最後一條語句，並且該事務中的較早語句已完成更新或類似操作
MyDB.dbo.TableNotInInputBuff，因此整個事務仍持有鎖？兩個問題：
- 我的假設是否正確
- 如果是這樣，我如何追踪完整的交易文本以查看導致鎖定的程式碼？
在某些情況下，可以在不是 UPDATE 的直接目標的表上獲取鎖（例如外鍵或更新觸發器），但我認為這裡不是這種情況？
當然，如時間戳所示，同一事務中有前一批
- lasttransstarted=“2019-10-14T10:16:42.233”
- lastbatchcompleted=“2019-10-14T10:16:42.237”
- lastbatchstarted=“2019-10-14T10:16:42.240”
因此，鎖定可能已被另一批在
16:42.233和之間佔用16:42.237。沒有任何內置功能可以幫助您解決此問題。SQL Server 不會保留以前執行的 SQL 文本的歷史記錄，只是因為會話可能會在以後的批處理中陷入死鎖。
最好的方法是審核應用程式碼以查看它在同一事務中較早送出的其他語句。如果這不可能，您將需要查看擴展事件以在應用程序執行該程式碼路徑時擷取應用程序的所有語句。