SQL Server 数据库死锁处理

-- 检查SQL Server中当前的死锁信息
WITH Deadlocks (DeadlockGraph)
AS (
    SELECT CAST([message] AS XML)
    FROM sys.dm_exec_requests
    CROSS APPLY sys.dm_exec_error_codes(error_number)
    WHERE [message] LIKE '%deadlock detected%'
),
DeadlockInfo (Spid, ProcessInfo, NodeId, IsProcess, [DatabaseID], [DatabaseName])
AS (
    SELECT
        Spid,
        ProcessInfo,
        N.value('(@id)[1]', 'int'),
        1 AS IsProcess,
        DB_ID() AS [DatabaseID],
        DB_NAME() AS [DatabaseName]
    FROM Deadlocks
    CROSS APPLY DeadlockGraph.nodes('/deadlock/process') P(N)
    UNION ALL
    SELECT
        Spid,
        Node.value('(text/text())[1]', 'nvarchar(max)'),
        Node.value('(@id)[1]', 'int'),
        0 AS IsProcess,
        CONVERT(int, Node.value('(@dbid)[1]', 'bigint')) AS [DatabaseID],
        Node.value('(@dbname)[1]', 'nvarchar(128)') AS [DatabaseName]
    FROM Deadlocks
    CROSS APPLY DeadlockGraph.nodes('/deadlock/processList/process') P(Node)
)
SELECT
    DI1.Spid AS BlockingSPID,
    DI1.ProcessInfo AS BlockingCallStack,
    DI1.[DatabaseID] AS BlockingDatabaseID,
    DI1.[DatabaseName] AS BlockingDatabaseName,
    DI2.Spid AS BlockedSPID,
    DI2.ProcessInfo AS BlockedCallStack,
    DI2.[DatabaseID] AS BlockedDatabaseID,
    DI2.[DatabaseName] AS BlockedDatabaseName
FROM DeadlockInfo DI1
JOIN DeadlockInfo DI2 ON DI1.NodeId = DI2.NodeId
WHERE DI1.IsProcess = 1 AND DI2.IsProcess = 0
ORDER BY BlockingSPID, BlockedSPID;

这段代码首先定义了一个公用表表达式（CTE）Deadlocks来提取包含死锁信息的消息内容，然后通过另一个CTE DeadlockInfo 来解析这些XML数据，最后通过一个SELECT语句来提取出导致死锁的进程信息，包括阻塞和被阻塞的进程ID、调用栈信息、数据库ID和名称。这个查询可以帮助DBA或开发人员快速理解死锁的情况，并且可以用来进行进一步的调查和解决问题。