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