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

RDS SQL Server死锁(Deadlock)系列之五利用Extended Events获取死锁信息

简介: # 问题引入 在过去很长一段时间,不断有客人会问道:“在事先没有任何跟踪或者监控部署的情况下,阿里云RDS SQL Server有没有办法获取到历史死锁信息,供我们分析?”。在写到RDS SQL Server死锁系列文章之五时,我们就可以使用Extended Events来解决这个问题。 # 分析问题 Extended Events是微软从SQL Server 2008版本开始引入的,其中有
+关注继续查看

问题引入

在过去很长一段时间,不断有客人会问道:“在事先没有任何跟踪或者监控部署的情况下,阿里云RDS SQL Server有没有办法获取到历史死锁信息,供我们分析?”。在写到RDS SQL Server死锁系列文章之五时,我们就可以使用Extended Events来解决这个问题。

分析问题

Extended Events是微软从SQL Server 2008版本开始引入的,其中有一个默认事件会话是system_health,它的作用是用来收集SQL Server发生的错误信息,以XML格式存储在一个名为sys.dm_xe_session_targets的DMV中,这其中就包含了死锁信息。所以,我们可以利用这个DMV关联另外一个名为sys.dm_xe_sessions的DMV来获取死锁信息。
注意:存储在这两个DMV中的信息会伴随SQL Server服务重启而消失。也就是说,我们无法获取到SQL Server服务重启之前的历史死锁信息。

获取历史死锁信息

为了描述清楚如何获取历史死锁信息,我们可以选择其中任意一条死锁信息加以分析,代码如下:

USE master
GO

-- analysis Extend Event for deadlock
DECLARE
    @deadlock_graph_nvarchar nvarchar(max)
    ,@deadlock_graph_xml xml
;

;WITH RingBufferTarget
AS
(
    SELECT CAST (target_data AS XML) AS target_xml
    FROM sys.dm_xe_session_targets st
        INNER JOIN sys.dm_xe_sessions s 
            ON s.address = st.event_session_address
    WHERE s.[name] = 'system_health' and st.target_name = 'ring_buffer'
)
SELECT TOP 1 @deadlock_graph_nvarchar = CAST(T.C.query('.') AS NVARCHAR(MAX))
FROM RingBufferTarget AS A
    CROSS APPLY target_xml.nodes('./RingBufferTarget/event') AS T(C)
WHERE T.C.value('./@name','varchar(200)') = 'xml_deadlock_report'

SELECT 
    @deadlock_graph_xml = CAST(REPLACE(REPLACE(@deadlock_graph_nvarchar, '&lt;', '<'),'&gt;', '>') as xml)
;

select @deadlock_graph_xml.query('event/data/value/deadlock')


;WITH deadlock
AS
(
        SELECT 
                       OwnerID = T.C.value('@id', 'varchar(50)')
                        ,SPid = T.C.value('(./@spid)[1]','int')
                        ,status = T.C.value('(./@status)[1]','varchar(10)')
                        ,Victim = case when T.C.value('@id', 'varchar(50)') = T.C.value('(./../../victim-list/victimProcess/@id)[1]','varchar(50)') then 1 else 0 end
                        ,LockMode = T.C.value('@lockMode', 'varchar(20)')
                        ,Inputbuf = T.C.value('(./inputbuf/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)')
                        ,TransactionTime = T.C.value('@lasttranstarted', 'datetime')
                        --,* 
        FROM @deadlock_graph_xml.nodes('./event/data/value/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)')
        FROM @deadlock_graph_xml.nodes('./event/data/value/deadlock/resource-list/keylock/owner-list') AS T(C)
)
SELECT 
        A.SPid
        ,A.TransactionTime
        ,is_Vitim = A.Victim
        ,A.SPName
        ,A.LockMode
        ,B.Indexname
        ,B.KeylockObject
        ,B.IndexLockMode
        ,A.Inputbuf
        ,A.Hostname
        ,A.LoginName
        ,A.Clientapp
        ,A.Action
        ,status
FROM deadlock AS A
        LEFT JOIN keylock AS B
        ON A.OwnerID = B.OwnerID

从执行查询后的结果来看,我们成功拿到了历史死锁信息(从时间字段来看,死锁发生在8天以前),这些有用的信息包含:锁进程,死锁进程,锁的类型,执行的语句,登录用户等信息,如下截图所示:
01.png

我们也可以打开这个死锁信息的xml,如下:

<deadlock>
  <victim-list>
    <victimProcess id="process15ee08" />
  </victim-list>
  <process-list>
    <process id="process15ee08" taskpriority="0" logused="0" waitresource="KEY: 14:72057594038910976 (8194443284a0)" waittime="3906" ownerId="23597" transactionname="user_transaction" lasttranstarted="2017-04-19T21:28:11.050" XDES="0x87141730" lockMode="X" schedulerid="1" kpid="4784" status="suspended" spid="64" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-04-19T21:28:11.050" lastbatchcompleted="2017-04-19T21:28:11.027" clientapp="Microsoft SQL Server Management Studio - Query" hostname="CHERISH-PC" hostpid="4284" loginname="Cherish-PC\Cherish" isolationlevel="read committed (2)" xactid="23597" currentdb="14" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
      <executionStack>
        <frame procname="" line="8" stmtstart="58" sqlhandle="0x020000008902b6141ee31ae1865c893c9823c9cf5d55fafb" />
        <frame procname="" line="8" stmtstart="156" stmtend="276" sqlhandle="0x020000008e0a8d0d015ffe0258d01a670c6864df6370c807" />
      </executionStack>
      <inputbuf>

BEGIN TRAN 
UPDATE dbo.test_deadlock2
SET name = &amp;apos;CC&amp;apos;
WHERE id = 1
;

UPDATE dbo.test_deadlock1
SET name = &amp;apos;CC&amp;apos;
WHERE id = 1
;
COMMIT

   </inputbuf>
    </process>
    <process id="process15f048" taskpriority="0" logused="0" waitresource="KEY: 14:72057594038976512 (8194443284a0)" waittime="503" ownerId="23574" transactionname="user_transaction" lasttranstarted="2017-04-19T21:28:09.450" XDES="0x87140e80" lockMode="X" schedulerid="1" kpid="4864" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-04-19T21:28:09.443" lastbatchcompleted="2017-04-19T21:28:09.440" clientapp="Microsoft SQL Server Management Studio - Query" hostname="CHERISH-PC" hostpid="4284" loginname="Cherish-PC\Cherish" isolationlevel="read committed (2)" xactid="23574" currentdb="14" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
      <executionStack>
        <frame procname="" line="9" stmtstart="58" sqlhandle="0x0200000070cad20bef4ae5ada9481eea2eb28415cd7e0c04" />
        <frame procname="" line="9" stmtstart="208" stmtend="328" sqlhandle="0x020000008b7a380c6bf24758d2b29f0eeb276e4f0aa76d8f" />
      </executionStack>
      <inputbuf>

BEGIN TRAN 
UPDATE dbo.test_deadlock1
SET name = &amp;apos;CC&amp;apos;
WHERE id = 1
;
WAITFOR DELAY &amp;apos;00:00:05&amp;apos;

UPDATE dbo.test_deadlock2
SET name = &amp;apos;CC&amp;apos;
WHERE id = 1
;
ROLLBACK   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594038910976" dbid="14" objectname="" indexname="" id="lock80153480" mode="X" associatedObjectId="72057594038910976">
      <owner-list>
        <owner id="process15f048" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process15ee08" mode="X" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594038976512" dbid="14" objectname="" indexname="" id="lock80154580" mode="X" associatedObjectId="72057594038976512">
      <owner-list>
        <owner id="process15ee08" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process15f048" mode="X" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

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

相关文章
采用Opserver来监控你的ASP.NET项目系列(二、监控SQL Server与Asp.Net项目)
原文:采用Opserver来监控你的ASP.NET项目系列(二、监控SQL Server与Asp.Net项目) 前言 之前有过2篇关于如何监控ASP.NET core项目的文章,有兴趣的也可以看看.
1626 0
RDS SQL Server死锁(Deadlock)系列之一使用DBCC捕获死锁
# 问题引入 在日常运维阿里云RDS SQL Server产品过程中,经常会被客户问道:“应用程序被死锁报错啦?影响很大,到底是哪个进程导致了死锁发生的啊?怎么解决啊?怎么办呀?”。从客户一连串的问题中,我们深刻体会到了死锁问题的紧迫性和影响之大。授人予鱼而不如授人予渔,RDS SQL Server死锁系列文章就是为了帮助客人彻底解决死锁问题为初衷而诞生的。本篇文章是系列文章的开篇,主要是讨论如
5935 0
SQL Server中clustered与nonclustered的区别
        CLUSTERED :聚集索引。非聚集索引:NONCLUSTERED。         clustered是物理上实现数据排序,并且同一个表里只能有一个clustered索引,而nonclustered是逻辑上的排序。         微软的SQL Server 支持两种类型的索引:clustered 索引和nonclustered索引。         Cluste
723 0
SQL Server中clustered与nonclustered的区别
        CLUSTERED :聚集索引。非聚集索引:NONCLUSTERED。         clustered是物理上实现数据排序,并且同一个表里只能有一个clustered索引,而nonclustered是逻辑上的排序。         微软的SQL Server 支持两种类型的索引:clustered 索引和nonclustered索引。         Cluste
1791 0
sql server 安装缺少adventureworks解决办法
在安装SQL Server 2005时,假如你选择的是默认安装,则不会安装AdventureWorks 数据库。因为许多教程和例子都需要用到AdventureWorks 数据库,所以安装AdventureWorks数据库非常必要。
726 0
SqlServer中用@@IDENTITY取最新ID不准的问题
一个网友问我一个关于@@IDENTITY的问题。他的数据库中有一个存储过程,有 insert语句,然后马上就用SELECT @@IDENTITY取刚插入的ID值,通常这是没有问题的,但是问题是ID实际上已经达到了100多万了,而 SELECT @@IDENTITY  返回的只有很小的值(才30多),令人费解。
724 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数据库进阶实战
立即下载