查询Sqlserver数据库死锁的一个存储过程分享
在Sqlserver数据库中，死锁是不可避免的。为了避免死锁对系统造成影响，需要及时监控和排查死锁的情况。本文将介绍一个可以查询Sqlserver数据库死锁的存储过程，并通过代码示例演示如何使用。
1. 存储过程介绍
本文要介绍的存储过程名为GetDeadlockInfo，它是一个开源的存储过程，可以查询Sqlserver数据库中的死锁信息。具体包含以下信息：
- 死锁发生的时间
- 死锁的进程ID
- 死锁的状态
- 死锁的数据库名
- 死锁的用户
- 死锁的表名
- 死锁的语句
2. 存储过程实现代码
下面是GetDeadlockInfo存储过程的详细实现代码：
CREATE PROCEDURE [dbo].[GetDeadlockInfo]
AS
BEGIN
SET NOCOUNT ON;
WITH Deadlock (Date,DeadlockGraph)
AS
(
select
CAST(xml_deadlock_report AS XML).value('(//@timestamp)[1]','datetime2') AS Date,
xml_deadlock_report as DeadlockGraph
from
sys.dm_os_ring_buffers
where
ring_buffer_type = 'RING_BUFFER_DEADLOCK'
)
SELECT
Deadlock.Date,
Deadlock.DeadlockGraph,
DB_NAME(B.database_id) AS [database_name],
(SELECT
CONVERT(nvarchar(10),[timestamp],120) + ' '
+ CONVERT(nvarchar(8),[timestamp],108) + ' '
+ CASE
[transaction_type]
WHEN 1 THEN 'Read'
WHEN 2 THEN 'Write'
WHEN 3 THEN 'Exclusive'
WHEN 4 THEN 'Intent'
WHEN 5 THEN 'Shared'
WHEN 6 THEN 'Update'
END + ' '
+ '[page_id (' + CONVERT(nvarchar(30),[page_id]) + '):'
+ CONVERT(nvarchar(6),[lock_mode]) + ']'
+ ' ' + '[ObjId:'
+ CONVERT(nvarchar(10),[object_id]) + '] '
+ ' ' + '[Subresource:'
+ CONVERT(nvarchar(10),[subresource]) + '] '
+ CASE
[resource_type]
WHEN 'OBJECT' THEN ' [Result:Obj] '
WHEN 'PAGE' THEN ' [Result:Page] '
WHEN 'KEY' THEN ' [Result:Key] '
WHEN 'RID' THEN ' [Result:RID] '
ELSE ' [Result:UNK] '
END
FROM
sys.dm_tran_locks
WHERE
request_session_id IN
(SELECT
[blocked_process_report].value('(//@waiter)[1]','int')
FROM
Deadlock
CROSS APPLY [Deadlock].DeadlockGraph.nodes('//deadlock/victim-list/victimProcess') AS V([blocked_process_report])
UNION ALL
SELECT
[blocking_process_report].value('(//@spid)[1]','int')
FROM
Deadlock
CROSS APPLY [Deadlock].DeadlockGraph.nodes('//deadlock/process-list/process') AS V([blocking_process_report])
)
OR [request_owner_id] IN
(SELECT
[blocked_process_report].value('(//@waiter)[1]','int')
FROM
Deadlock
CROSS APPLY [Deadlock].DeadlockGraph.nodes('//deadlock/victim-list/victimProcess') AS V([blocked_process_report])
UNION ALL
SELECT
[blocking_process_report].value('(//@spid)[1]','int')
FROM
Deadlock
CROSS APPLY [Deadlock].DeadlockGraph.nodes('//deadlock/process-list/process') AS V([blocking_process_report])
)
ORDER BY --order them by the deadlock graph session order
Deadlock.DeadlockGraph.value('(//@spid)[1]','int'),
[blocking_process_report].value('(//@rid)[1]','int')
FOR XML PATH(''), TYPE
)
FROM
Deadlock
CROSS APPLY Deadlock.DeadlockGraph.nodes('//deadlock') AS D (DeadlockReport)
CROSS APPLY DeadlockReport.nodes('process-list/process') AS P (Process)
CROSS APPLY Process.nodes('inputbuf') AS InputBuf([inputbuf])
LEFT JOIN sys.dm_exec_requests R ON R.session_id = P.Process.value('(//@spid)[1]','int')
LEFT JOIN sys.dm_tran_database_transactions T ON P.Process.value('(/process/@transactionname)[1]','nvarchar(max)') = T.name
LEFT JOIN sys.databases B ON T.database_id = B.database_id
WHERE
Deadlock.DeadlockGraph.exist('//deadlock') = 1
ORDER BY
Deadlock.Date DESC;
END
2.1 存储过程使用说明：
- 执行GetDeadlockInfo存储过程，会返回数据库中的死锁信息。
- 执行存储过程前，必须要有足够的权限访问sys.dm_os_ring_buffers和sys.dm_tran_locks等系统表。
2.2 存储过程实现说明：
- GetDeadlockInfo存储过程使用了递归查询的方式，通过sys.dm_os_ring_buffers表查询死锁信息，使用sys.dm_tran_locks表和死锁报告的xml数据进行关联查询，最后生成死锁信息的结果集。
- Deadlock with语句用于查询sys.dm_os_ring_buffers表中的死锁信息，其中xml_deadlock_report的值为包含死锁信息的xml文档。
- 死锁信息的生成使用到了多个系统表，包括sys.dm_tran_locks、sys.dm_exec_requests、sys.dm_tran_database_transactions和sys.databases等，通过这些表的关联查询，生成包含死锁信息的结果集。
3. 使用示例
下面是使用GetDeadlockInfo存储过程查询Sqlserver数据库中的死锁信息的示例代码：
EXEC dbo.GetDeadlockInfo;
3.1 示例说明：
- 使用EXEC执行GetDeadlockInfo存储过程，会返回数据库中的死锁信息。
3.2 示例结果分析：
存储过程返回的结果集包含以下字段：
- Date：死锁发生的时间
- DeadlockGraph：死锁信息的XML文档，包含死锁进程等信息
- [database_name]：死锁所在的数据库名称
- [inputbuf]：死锁过程的相关语句
在分析存储过程返回的结果集时，可以通过查询DeadlockGraph字段的xml数据，获取更多的死锁信息，例如死锁进程的会话ID、锁类型和锁对象等。
4. 总结
本文介绍了一个用于查询Sqlserver数据库死锁信息的存储过程GetDeadlockInfo，通过代码示例演示了如何使用和分析死锁信息。在Sqlserver数据库中，死锁是不可避免的，通过及时监控和排查死锁，可以减少死锁对系统造成的影响。使用GetDeadlockInfo存储过程可以监控并及时排查Sqlserver数据库中的死锁情况，有助于保证系统稳定运行。