RDS SQL Server死锁（Deadlock）系列之二使用Profiler捕获死锁-CSDN博客

问题引入

不管是RDS SQL Server还是自建SQL Server数据库，死锁的确是一个非常头疼的问题，上一篇文章我们已经谈到了使用DBCC捕获死锁。这篇文章是以阿里云RDS客户遇到的死锁问题为背景，分享死锁文章系列之二使用Profiler捕获死锁。

Profiler捕获死锁

使用Profiler工具的Deadlock graph事件，可以非常方便直观的捕获死锁信息。方法是：
开启MSSQL Profiler：开始 -> 运行 -> 键入profiler
新建Deadlock Graph Trace：在Profiler窗体中，开启一个Trace -> 显示所有事件 -> 依次找到Locks -> DeadLocak Graph -> 运行（详情参见下面的截图，按照字母标号依次点击）。

注意这里我们仅Trace这一个事件就好了，取消其他多余与死锁无关的事件。

死锁测试

死锁测试的方法和上一篇文章一致，参见上一篇文章RDS SQL Server死锁（Deadlock）系列之一使用DBCC捕获死锁中死锁测试部分，在此不在累述。

死锁分析

当死锁状况发生时，Profiler捕获到死锁信息，绘制成Deadlock Graph图，非常直观的展示了死锁的进程、牺牲的进程和争抢的资源。

分析Deadlock graph图

接下来就是分析死锁发生时的情况，参加如下截图：

通过死锁关系图的展示，我们可以分析如下：
牺牲进程：图中最左边被×掉的64号进程是死锁牺牲品，它申请到了test_deadlock2的X锁，再申请test_deadlock1的X锁时，被做为了牺牲品。
死锁进程：图中最右边63号进程首先获取到了test_deadlock1的X锁，然后申请test_deadlock2的X锁，但这个时候64号进程已经拿走了test_deadlock2的X锁。系统选择杀死64号进程（即64做为了牺牲品），让63号进程成功获取到test_deadlock2的X锁，他是本次资源争抢的获胜者。
争抢的资源：图中中部是两个进程争抢的资源，我们可以通过图中资源的HoBt ID获取表和索引名，方法如下：

分析Deadlock Trace文件

虽然通过Deadlock Graph图可以很清楚的分析出死锁的关系，找到资源的争抢点，但是我个人推荐分析Deadlock Trace文件的方式，这种方式更加简单明了。我们需要首先保存Deadlock Graph监控信息到文件，比如保存到C:TempDeadlock_testing.trc，方法如下：

文件保存完毕以后的.trc为后缀的文件其实就是xml类型的文件，我们可以使用接下来的语句进行分析XML文件：

use master
go

-- declare variables.
declare 
        @file nvarchar(256)
;
select 
        @file = N'C:\Temp\Deadlock_testing.trc'
;

WITH DATA
AS
(        
        SELECT 
                --[TraceID] = @trace_id , 
                RowNumber = row_number() OVER (ORDER BY StartTime), -- assign a row number to each deadlock
                [DeadlockGraph]=case when TextData like '<deadlock-list%' then convert(xml, TextData) else null end, 
                * 
        from ::fn_trace_gettable(@file, default)
        where TextData like '<deadlock-list%'
)
,
deadlock
AS
(
        SELECT 
                        RowNumber
                        ,OwnerID = T.C.value('@id', 'varchar(50)')
                        ,SPid = T.C.value('(./@spid)[1]','int')
                        ,status = T.C.value('(./@status)[1]','varchar(10)')
                        --,PagelockObject = T.C.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)')
                        --,DeadlockObject = T.C.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)')
                        --,KeylockObject = T.C.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)')
                        --,DeadlockObject = T.C.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)')
                        ,Victim = case when T.C.value('@id', 'varchar(50)') = T.C.value('./../../@victim','varchar(50)') then 1 else 0 end
                        ,LockMode = T.C.value('@lockMode', 'varchar(20)')
                        ,DeadlockGraph
                        ,Inputbuf = T.C.value('(./inputbuf/text())[1]','varchar(max)')
                        ,Code = T.C.value('(./executionStack/frame/text())[1]','VARCHAR(max)')
                        ,SPName = T.C.value('(./executionStack/frame/@procname)[1]','sysname')
                        ,Hostname = T.C.value('(./@hostname)[1]','sysname')
                        ,Clientapp = T.C.value('(./@clientapp)[1]','varchar(max)')
                        ,LoginName = T.C.value('@loginname', 'varchar(20)')
                        ,Action = T.C.value('(./@transactionname)[1]','varchar(max)')
                        ,StartTime
                        ,TransactionTime = T.C.value('@lasttranstarted', 'datetime')
                        --,* 
        FROM DATA AS A
                CROSS APPLY DeadlockGraph.nodes('deadlock-list/deadlock/process-list/process') AS T(C)
)
,
keylock
AS
(
        SELECT
                OwnerID = T.C.value('./owner[1]/@id', 'varchar(50)')
                ,KeylockObject = T.C.value('./../@objectname', 'varchar(200)')
                ,Indexname = T.C.value('./../@indexname', 'varchar(200)')
                ,IndexLockMode = T.C.value('./../@mode', 'varchar(20)')
                --,owner = T.C.query('.')
        FROM DATA AS A
                CROSS APPLY DeadlockGraph.nodes('deadlock-list/deadlock/resource-list/keylock/owner-list') AS T(C)
)
SELECT 
        --A.OwnerID
        A.SPid
        ,is_Vitim = A.Victim
        --,A.DeadlockGraph
        ,A.SPName
        ,A.Code
        ,A.LockMode
        ,A.StartTime
        ,B.Indexname
        ,B.KeylockObject
        ,B.IndexLockMode
        ,A.Inputbuf
        ,A.Hostname
        ,A.LoginName
        ,A.Clientapp
        ,A.Action
        ,status
        ,A.TransactionTime
FROM deadlock AS A
        LEFT JOIN keylock AS B
        ON A.OwnerID = B.OwnerID
ORDER BY A.RowNumber,A.Victim

执行的结果如下截图：

从这个分析结果来看，我们可以非常清晰明了得到如下信息：
死锁与被死锁进程：63和64号进程
死锁发生时，两个进程执行的语句
死锁的类型：本例是X锁
锁定资源的对象和索引名
死锁的两个进程执行的语句块是什么
进程执行所在的主机
......

分析Deadlock Trace表

我们既可以将Deadlock Graph保存为Trace文件，还可以将其保存到Trace表中，假如我们将这个捕获到的死锁信息保存到本地数据库表test.dbo.Deadlock_testing中，方法如下：

分析Deadlock Trace Table方法与分析Deadlock Trace File类似，只需要将分析语句中的DATA公用表示稍微修改即可：

WITH DATA
AS
(
        
        SELECT 
                RowNumber = row_number() OVER (ORDER BY StartTime)
                ,DeadlockGraph = CAST(TextData AS xml)
                ,StartTime
                ,spid
        FROM test.dbo.Deadlock_testing WITH (NOLOCK)
        WHERE EventClass = 148
)