如何在AzureSQLServer中查看死鎖詳細資訊？

我在使用 Azure SQL Server 資料庫的 Java Spring Boot Web 應用程式中遇到了一些資料庫死鎖問題。

我可以運行哪個查詢來獲取最近發生的死鎖的歷史記錄？

Azure AppInsights 只告訴我一些通用資訊，例如：

事務（行程 ID 79）與另一個行程在鎖資源上死鎖，并被選為死鎖犧牲品。重新運行事務

并且查詢受到影響。

但不是交易細節相互爭斗。

這個查詢（我在網上找到的）不會產生任何結果集

 WITH CTE AS (
SELECT CAST(event_data AS XML)  AS [target_data_XML]
FROM sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null)
)
SELECT target_data_XML.value('(/event/@timestamp)[1]', 'DateTime2') AS Timestamp,
target_data_XML.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(100)') AS db_name
FROM CTE 

而這個其他查詢（我的 DBA 建議）似乎只回傳一些關于當前事務索引的資訊鎖（非死鎖）。

SELECT  L.request_session_id AS SPID,
    DB_NAME(L.resource_database_id) AS DatabaseName,
    O.Name AS LockedObjectName,
    P.object_id AS LockedObjectId,
    L.resource_type AS LockedResource,
    L.request_mode AS LockType,
    ST.text AS SqlStatementText,       
    ES.login_name AS LoginName,
    ES.host_name AS HostName,
    TST.is_user_transaction as IsUserTransaction,
    AT.name as TransactionName,
    CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
    JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
    JOIN sys.objects O ON O.object_id = P.object_id
    JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
    JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
    JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
    JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
    CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

提前致謝。

uj5u.com熱心網友回復：

在 Azure SQL 資料庫中，已經運行了一個擴展事件來捕獲死鎖，而無需為客戶方采取任何額外的操作。

1. 正如@MartinSmith 建議的那樣，您的第一個查詢應該在主資料庫中運行。

   您可以通過將 deadlock_xml 結果復制到 xdl 檔案并使用 SSMS 打開它來分析死鎖圖。如果您有大量死鎖，此查詢可能會運行緩慢，如果您有大量死鎖，也很難全面了解正在發生的事情。

2. 下一個查詢將通過按資料庫、查詢和它正在等待的資源為您提供死鎖計數來幫助您分析大量死鎖。

WITH CTE AS (
SELECT CAST(event_data AS XML) AS [target_data_XML] FROM sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null)
)
SELECT [db_name], [query_text], [wait_resource], COUNT(*) as [number_of_deadlocks] FROM (
SELECT LTRIM(RTRIM(Replace(Replace(c.value('.', 'nvarchar(250)'),CHAR(10),' '),CHAR(13),' '))) as query_text,
D.value('@waitresource', 'nvarchar(250)') AS [wait_resource],
target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(250)') AS [db_name]
from CTE CROSS APPLY target_data_XML.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS T(C)
CROSS APPLY target_data_XML.nodes('(/event/data/value/deadlock/process-list/process)') AS Q(D)
) deadlock
GROUP BY [query_text], [wait_resource], [db_name]
ORDER BY [number_of_deadlocks] DESC

從上面的代碼中，您可以了解哪些特定查詢和物件導致資料庫中的死鎖。

3. 最后，您可以使用此查詢獲取特定物件的死鎖圖。替換<YourDB>為資料庫名稱和<wait_resource>上面查詢中標識的等待資源。

WITH CTE AS (
SELECT CAST(event_data AS XML) AS [target_data_XML] FROM sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null)
)
SELECT [db_name], [wait_resource], [deadlock_xml] FROM (
SELECT target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(250)') AS [db_name],
waitresource_node.value('@waitresource', 'nvarchar(250)') AS [wait_resource],
deadlock_node.query('.') as [deadlock_xml]
FROM CTE CROSS APPLY target_data_XML.nodes('(/event/data/value/deadlock)') AS T(deadlock_node)
CROSS APPLY target_data_XML.nodes('(/event/data/value/deadlock/process-list/process)') AS U(waitresource_node)
) deadlock
WHERE [db_name] = '<YourDB>'
AND [wait_resource] = '<wait_resource>'

資料來源：SQL Azure 資料庫的死鎖分析

標籤：sql服务器 天蓝色 数据库死锁 天蓝色 sql 服务器

上一篇：SQLServer-查詢-如何獲得子查詢的最后結果？[復制]

下一篇：表間變化匯總的SQL查詢