MSSQL J检查拯救死锁：创编新途径

1. 引言

在MSSQL 开发与维护过程中，经常会遭受死锁的威胁。一旦死锁发生，可能会瘫痪整个应用系统甚至整个数据库。同时，这种问题的调试与定位也相对困难。所以，本文将介绍一种新的方式来检查并解决死锁问题。

2. 什么是死锁？

死锁是指两个或多个事务在互相等待对方的锁释放，但没有一个事务能够继续执行的情况。一旦发生死锁，就会导致事务无法继续执行，直到发生超时或者被强制终止。

2.1 死锁的原因

死锁的主要原因是并发控制的不当。在一个并发系统中，当多个事务需要访问同一资源时，必须使用锁来保护资源的完整性和一致性。但是，如果不合理地使用锁，就容易导致死锁的发生。

2.2 死锁的危害

死锁的危害主要表现在以下几个方面：

系统性能下降：死锁发生后，所有涉及到的事务都会停止执行，这将导致系统的性能下降。

数据一致性问题：死锁也可能导致数据不一致的问题，这种情况下，修改已经提交的数据可能会被回滚。

难以定位问题：死锁的调试和定位相对困难，涉及到对系统的深入了解以及复杂的调试技巧。

3. MSSQL 死锁检查方法

3.1 检查死锁信息

MSSQL 中提供了一个系统存储过程

sp_who2

exec sp_who2

这个命令将输出所有进程的详细信息，可以按照进程号 (SPID) 进行排序，找到交叉等待的进程。

3.2 检查死锁图像

MSSQL提供了一个名为

system_health

SELECT CAST(target_data AS XML), * 

FROM sys.dm_xe_sessions s 
JOIN sys.dm_xe_session_targets st 
ON s.address = st.event_session_address 
WHERE s.name = 'system_health'

输出结果中可以看到XML格式的死锁图像，可以通过分析它来找到死锁的原因。但是，在处理大量的死锁时，手动分析XML文件可能是非常耗时的。

3.3 新的死锁检查方式

由于手动分析死锁信息可能非常耗时，因此我使用了一种新的方法：检查 SQL Server 锁竞争，通过这种方法可以检查死锁并快速定位问题。

3.4 死锁检查脚本

以下是一个死锁检查脚本示例，它将输出导致死锁的 SQL 语句以及死锁发生的时间和位置：

DECLARE @xml AS XML 

SET @xml = (SELECT CAST(target_data AS XML) 
            FROM sys.dm_xe_sessions AS s 
            INNER JOIN sys.dm_xe_session_targets AS st 
            ON s.address = st.event_session_address 
            WHERE s.name = 'system_health'  
            AND st.target_name = 'ring_buffer')
SELECT
 dateadd(mi, datediff(mi, getutcdate(), getdate()), xevt.query('(data/value/deadlock)[1]') WITH (WHERE '(data/value/deadlock)[1]' IS NOT NULL).value('@timestamp', 'datetime2(0)')) AS deadlock_time,
 xevt.query('(data/value/deadlock)[1]') AS deadlock_graph
INTO #temp
FROM 
(
  SELECT  
    CAST(target_data AS XML) target_data 
  FROM sys.dm_xe_session_targets st 
  INNER JOIN sys.dm_xe_sessions s 
    ON s.address = st.event_session_address 
  WHERE s.NAME IN ('system_health') 
    AND st.target_name = 'ring_buffer' 
  ) AS tab CROSS APPLY target_data.nodes ('RingBufferTarget/event') AS xevt (query);
WITH temp_cte AS 
(
SELECT
  DeadlockTime = CONVERT(datetime2(0), deadlock_time, 120),
  x.event_data
FROM #temp t
CROSS APPLY ( SELECT CONVERT(xml, deadlock_graph)) x (event_data)
)
SELECT 
  paa.value('./@name','varchar(100)') AS AttributeName,
  paa.value('./value[1]','varchar(max)') AS AttributeValue
FROM temp_cte
CROSS APPLY event_data.nodes('//deadlock/victim-process/..') AS p(v)
CROSS APPLY p.v.nodes('.//process') AS pa(paa)
WHERE pa.paa.value('./@pid','int') IN (
  SELECT TOP 1  PA.paa.value('./@pid','int')
  FROM temp_cte t
    CROSS APPLY t.event_data.nodes('//deadlock/victim-process/..') AS p(v)
  CROSS APPLY p.v.nodes('.//process') AS pa(paa)
  UNION
  SELECT PA.paa.value('./@pid','int')
  FROM temp_cte t
    CROSS APPLY t.event_data.nodes('//deadlock/process-list/process') AS p(v)
  CROSS APPLY p.v.nodes('.//process') AS pa(paa)
  EXCEPT
  SELECT paa.value('./@pid','int')
  FROM temp_cte t
    CROSS APPLY t.event_data.nodes('//deadlock/process-list/process') AS p(v)
  CROSS APPLY p.v.nodes('.//process') AS pa(paa)
)
ORDER BY deadlock_time
DROP TABLE #temp;

3.5 死锁检查脚本说明

这个脚本的主要作用是检查系统中的死锁记录，并且输出导致死锁的 SQL 语句和相关信息。

脚本的第一部分将 XML 文件中的死锁事件与时间戳提取到一个缓存表 #temp 中。

DECLARE @xml AS XML 

SET @xml = (SELECT CAST(target_data AS XML) 
            FROM sys.dm_xe_sessions AS s 
            INNER JOIN sys.dm_xe_session_targets AS st 
            ON s.address = st.event_session_address 
            WHERE s.name = 'system_health'  
            AND st.target_name = 'ring_buffer')
SELECT
 dateadd(mi, datediff(mi, getutcdate(), getdate()), xevt.query('(data/value/deadlock)[1]') WITH (WHERE '(data/value/deadlock)[1]' IS NOT NULL).value('@timestamp', 'datetime2(0)')) AS deadlock_time,
 xevt.query('(data/value/deadlock)[1]') AS deadlock_graph
INTO #temp
FROM 
(
  SELECT  
    CAST(target_data AS XML) target_data 
  FROM sys.dm_xe_session_targets st 
  INNER JOIN sys.dm_xe_sessions s 
    ON s.address = st.event_session_address 
  WHERE s.NAME IN ('system_health') 
    AND st.target_name = 'ring_buffer' 
  ) AS tab CROSS APPLY target_data.nodes ('RingBufferTarget/event') AS xevt (query);

第二部分将缓存表中的数据解析并输出。

WITH temp_cte AS 

(
SELECT
  DeadlockTime = CONVERT(datetime2(0), deadlock_time, 120),
  x.event_data
FROM #temp t
CROSS APPLY ( SELECT CONVERT(xml, deadlock_graph)) x (event_data)
)
SELECT 
  paa.value('./@name','varchar(100)') AS AttributeName,
  paa.value('./value[1]','varchar(max)') AS AttributeValue
FROM temp_cte
CROSS APPLY event_data.nodes('//deadlock/victim-process/..') AS p(v)
CROSS APPLY p.v.nodes('.//process') AS pa(paa)
WHERE pa.paa.value('./@pid','int') IN (
  SELECT TOP 1  PA.paa.value('./@pid','int')
  FROM temp_cte t
    CROSS APPLY t.event_data.nodes('//deadlock/victim-process/..') AS p(v)
  CROSS APPLY p.v.nodes('.//process') AS pa(paa)
  UNION
  SELECT PA.paa.value('./@pid','int')
  FROM temp_cte t
    CROSS APPLY t.event_data.nodes('//deadlock/process-list/process') AS p(v)
  CROSS APPLY p.v.nodes('.//process') AS pa(paa)
  EXCEPT
  SELECT paa.value('./@pid','int')
  FROM temp_cte t
    CROSS APPLY t.event_data.nodes('//deadlock/process-list/process') AS p(v)
  CROSS APPLY p.v.nodes('.//process') AS pa(paa)
)
ORDER BY deadlock_time
DROP TABLE #temp;

4. 总结

死锁是一个常见但让人头痛的问题，在MSSQL中使用

sp_who2

system_health