sql-server - 如何在 Azure SQL Server 中查看死锁详细信息？

Question

我在使用 Azure SQL Server 数据库的 Java Spring Boot Web 应用程序中遇到一些数据库死锁问题。

我可以运行哪个查询来获取最近发生的死锁的历史记录？

Azure AppInsights 仅告诉我一些通用信息，例如:

Transaction (Process ID 79) was deadlocked on lock resources withanother process and has been chosen as the deadlock victim. Rerun thetransaction

并且查询受到影响。

但不是交易细节互相争斗。

此查询(我在网上找到的)不会产生任何结果集

 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 

这个其他查询(来 self 的 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

提前致谢。

Answer 1

在 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

从上面的代码中，您可以了解哪些特定查询和对象导致数据库中出现死锁。

最后，您可以使用此查询获取特定对象的死锁图。替换<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>'

来源:Deadlock analysis for SQL Azure Database