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
可以检查死锁信息,但是手动分析可能非常耗时。通过新的死锁检查方式,我们可以更快速地检查死锁,并快速定位问题,从而提高整个系统的稳定性。

免责声明:本文来自互联网,本站所有信息(包括但不限于文字、视频、音频、数据及图表),不保证该信息的准确性、真实性、完整性、有效性、及时性、原创性等,版权归属于原作者,如无意侵犯媒体或个人知识产权,请来电或致函告之,本站将在第一时间处理。猿码集站发布此文目的在于促进信息交流,此文观点与本站立场无关,不承担任何责任。

数据库标签