开发者社区> 风移> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

RDS SQL Server死锁(Deadlock)系列之二使用Profiler捕获死锁

简介: # 问题引入 不管是RDS SQL Server还是自建SQL Server数据库,死锁的确是一个非常头疼的问题,上一篇文章我们已经谈到了[使用DBCC捕获死锁](https://yq.aliyun.com/articles/73856?spm=5176.8091938.0.0.rjljJx)。这篇文章是以阿里云RDS客户遇到的死锁问题为背景,分享死锁文章系列之二使用Profiler捕获死锁。
+关注继续查看

问题引入

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

Profiler捕获死锁

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

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

死锁测试

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

死锁分析

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

分析Deadlock graph图

接下来就是分析死锁发生时的情况,参加如下截图:
03.png

通过死锁关系图的展示,我们可以分析如下:
牺牲进程:图中最左边被×掉的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获取表和索引名,方法如下:
04.png

分析Deadlock Trace文件

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

文件保存完毕以后的.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

执行的结果如下截图:
06.png

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

分析Deadlock Trace表

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

分析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
)

阿里云RDS SQL Server

如果你是阿里云RDS SQL Server 2008R2用户,请工单联系阿里云,申请实例的Profiler权限,然后即可按照本方法来自行排查;如果你是阿里云RDS SQL Server 2012用户,默认已经具备Profiler权限,无需申请权限。

最后总结

使用Profiler捕获死锁信息的方法比使用DBCC的方式更加灵活,直观,一目了然。希望阿里云RDS SQL Server客户借助本系列文章都可以自己动起手来,分析死锁,解决死锁的问题。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
强强联合 阿里云 RDS for SQL Server 与 金蝶 K/3 WISE 产品实现兼容适配
强强联合 阿里云 RDS for SQL Server 与 金蝶 K/3 WISE 产品实现兼容适配,原K/3 WISE用户通过简单配置就可以无缝搭配RDS SQL Server使用,不需再费时费力自建SQL Server数据库。
925 0
强强联合 阿里云 RDS for SQL Server 与 金蝶 K/3 WISE 产品实现兼容适配
强强联合 阿里云 RDS for SQL Server 与 金蝶 K/3 WISE 产品实现兼容适配,原K/3 WISE用户通过简单配置就可以无缝搭配RDS SQL Server使用,不需再费时费力自建SQL Server数据库。
446 0
强强联合 阿里云 RDS for SQL Server 与 金蝶 K/3 WISE 产品实现兼容适配
强强联合 阿里云 RDS for SQL Server 与 金蝶 K/3 WISE 产品实现兼容适配,原K/3 WISE用户通过简单配置就可以无缝搭配RDS SQL Server使用,不需再费时费力自建SQL Server数据库,RDS for SQL Server 为K/3 WISE的数据存储提供超捷的便利性、极其稳定的可靠性以及极高的安全性保障。
4140 0
动手实战-最佳应用实践之使用RDS MySQL和ECS搭建个人博客
场景体验完成,简单描述以下实验过程,可以看到wordpress安装配置非常简单。
86 0
+关注
风移
阿里云数据库专家,负责SQL Server数据库产品线。SQL Server从业10年,经历过SQL 2000、SQL 2005、SQL 2008、SQL 2008R2、SQL 2012、SQL 2014、SQL 2016和SQL on Linux各个版本。
75
文章
46
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载