用扩展事件捕获死锁

最新推荐文章于 2024-06-04 10:16:20 发布

吉普赛的歌

最新推荐文章于 2024-06-04 10:16:20 发布

阅读量1k

点赞数

分类专栏： # 锁 # 运维调优

本文链接：https://blog.csdn.net/yenange/article/details/78287243

版权

运维调优同时被 2 个专栏收录

90 篇文章 4 订阅

订阅专栏

锁

8 篇文章 0 订阅

订阅专栏

另有利用现成系统自带扩展事件查看死锁：点击打开链接

一、打开 SSMS ，找到 “扩展事件”->"会话"->“新建会话向导”：

二、会话名称为： Deadlock_Monitor

三、不使用模板

四、事件库文本框键入： deadlock , 将 xml_deadlock_report 加到右边

五、选择一些需要的字段，不选择也没关系（因为我们只需要知道死锁是怎么造成的就可以了）

六、直接下一步

七、选择扩展事件文件保存位置

八、点击“脚本”，查看。当然，也可以直接点完成来创建事件。不过，有了脚本下次可以一次性创建，更方便。

CREATE EVENT SESSION [Deadlock_Monitor] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text,sqlserver.username)) 
ADD TARGET package0.event_file(SET filename=N'D:\Tools\DBA\EventLog\deadlock.xel',max_file_size=(20))
WITH (STARTUP_STATE=ON)
GO

八、验证是否有效：

窗口一：

USE AdventureWorks2014
GO
BEGIN TRAN
UPDATE Person.Address SET AddressLine1 = 'New address' WHERE AddressID = 20092
WAITFOR DELAY '0:0:10'
SELECT * FROM Person.Address WHERE AddressID = 20091

窗口二：

USE AdventureWorks2014
BEGIN TRAN
UPDATE Person.Address SET AddressLine1 = 'New address' WHERE AddressID = 20091
WAITFOR DELAY '0:0:10'
SELECT * FROM Person.Address WHERE AddressID = 20092

窗口二最后提示：

消息 1205，级别 13，状态 51，第 5 行
事务(进程 ID 67)与另一个进程被死锁在锁资源上，并且已被选作死锁牺牲品。请重新运行该事务。

<deadlock>
 <victim-list>
  <victimProcess id="process2769fa4e8" />
 </victim-list>
 <process-list>
  <process id="process2769fa4e8" taskpriority="0" logused="1244" waitresource="KEY: 5:72057594045333504 (bedc862d8c12)" waittime="2366" ownerId="176579" transactionname="user_transaction" lasttranstarted="2017-10-19T17:09:52" XDES="0x274902d90" lockMode="S" schedulerid="3" kpid="5040" status="suspended" spid="67" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-10-19T17:14:04.047" lastbatchcompleted="2017-10-19T17:13:34.420" lastattention="2017-10-19T17:13:34.420" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="JS1-YNG" hostpid="10280" loginname="JS1-YNG\Administrator" isolationlevel="read committed (2)" xactid="176579" currentdb="5" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <executionStack>
    <frame procname="adhoc" line="5" stmtstart="26" stmtend="130" sqlhandle="0x020000002d93df1b63a320cabd9f817232f8d60932f4a52f0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="adhoc" line="5" stmtstart="280" stmtend="382" sqlhandle="0x020000001d279814abdbd58048180fd345b4e9858707cc2f0000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
USE AdventureWorks2014
BEGIN TRAN
UPDATE Person.Address SET AddressLine1 = 'New address' WHERE AddressID = 20091
WAITFOR DELAY '0:0:10'
SELECT * FROM Person.Address WHERE AddressID = 20092   </inputbuf>
  </process>
  <process id="process2739348c8" taskpriority="0" logused="6836" waitresource="KEY: 5:72057594045333504 (86f9b1a4e067)" waittime="4138" ownerId="176525" transactionname="user_transaction" lasttranstarted="2017-10-19T17:09:36.473" XDES="0x2742043b0" lockMode="S" schedulerid="4" kpid="5376" status="suspended" spid="64" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-10-19T17:14:02.277" lastbatchcompleted="2017-10-19T17:14:02.277" lastattention="1900-01-01T00:00:00.277" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="JS1-YNG" hostpid="10280" loginname="JS1-YNG\Administrator" isolationlevel="read committed (2)" xactid="176525" currentdb="5" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <executionStack>
    <frame procname="adhoc" line="4" stmtstart="26" stmtend="130" sqlhandle="0x020000002d93df1b63a320cabd9f817232f8d60932f4a52f0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="adhoc" line="4" stmtstart="232" stmtend="334" sqlhandle="0x0200000057ffe131f6561fea8e3d2f452315d80af042e2cf0000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
BEGIN TRAN
UPDATE Person.Address SET AddressLine1 = 'New address' WHERE AddressID = 20092
WAITFOR DELAY '0:0:10'
SELECT * FROM Person.Address WHERE AddressID = 20091   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72057594045333504" dbid="5" objectname="AdventureWorks2014.Person.Address" indexname="PK_Address_AddressID" id="lock27bb46780" mode="X" associatedObjectId="72057594045333504">
   <owner-list>
    <owner id="process2739348c8" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process2769fa4e8" mode="S" requestType="wait" />
   </waiter-list>
  </keylock>
  <keylock hobtid="72057594045333504" dbid="5" objectname="AdventureWorks2014.Person.Address" indexname="PK_Address_AddressID" id="lock27bb2c400" mode="X" associatedObjectId="72057594045333504">
   <owner-list>
    <owner id="process2769fa4e8" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process2739348c8" mode="S" requestType="wait" />
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>

最终显示的死锁就是非常清晰的了！

参考：点击打开链接