如何在Azure SQL Server中查看死锁细节?

Question

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

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

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

提前感谢。

Answer 1

在Azure SQL数据库中，已经运行了一个扩展事件来捕获死锁，而不需要对客户端进行任何额外的操作。

1. 正如@MartinSmith所建议的，你的第一个查询应该在主数据库中运行。

   您可以通过将deadlock_xml结果复制到xml文件中并用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数据库的死锁分析