mssql sqlserver 使用traceon 跟踪死锁的方法分享


摘要:
下文分享通过打开跟踪标志来跟踪死锁的方法分享,如下所示:


    DBCC TRACEON (1222,3605,1204,-1)  
    ---打开跟踪编号 1222 3605 1204 
	 --1222:以不符合任何 XSD 架构的 XML 格式,返回参与死锁的锁的资源和类型,
	         以及受影响的当前命令
	 --3605:将DBCC的结果输出到错误日志中,方便查看日志分析错误
     --1204:返回参与死锁的锁的资源和类型,以及受影响的当前命令
	 ---:(-1):全局方式打开跟踪标记
-----------------------------------------------------------------------------------------------------
    例:
------------------ 查询窗口1. 开启死锁标记 -----------------------
DBCC TRACEON (1222,3605,1204,-1) 
 go
-------------------查询窗口2. 模拟死锁场景     -----------------------
--2.1 创建测试表及测试数据
 use tempdb
 go
IF OBJECT_ID('tmp1') IS NOT NULL DROP TABLE tmp1
IF OBJECT_ID('tmp2') IS NOT NULL DROP TABLE tmp2
GO
CREATE   TABLE  tmp1(keyIdA  int   default 188);
CREATE   TABLE  tmp2(keyIdB  int   default 88);
SET NOCOUNT ON
INSERT   INTO  tmp1  VALUES (89);
INSERT   INTO  tmp2  VALUES (90);
GO
------------------查询窗口3---------------- 
Begin   Tran 
   Update  tmp1  Set  keyIdA = keyIdA + 92 ;
   WaitFor  Delay  ' 00:00:10' ;
   SELECT   *   FROM  tmp2
 
------------------查询窗口4----------------
Begin   Tran 
   Update  tmp2  Set  keyIdB = keyIdB + 88;
   SELECT   *   FROM  tmp1
 
---------由于产生死锁,所以查询分析器会输出--------
/*
 (1 行受影响)
消息 1205,级别 13,状态 45,第 3 行
事务(进程 ID 63)与另一个进程被死锁在 锁 资源上,并且已被选作死锁牺牲品。请重新运行该事务。
*/
----------------使用 xp_readerrorlog查询死锁相关信息
EXEC xp_readerrorlog 0,1,NULL,NULL,'2018-07-10 16:30','2018-07-10 17:20','ASC'
---查询sql server 日志 在 '2018-07-10 16:30','2018-07-10 17:20'之间的日志信息
----检索死锁相关日志信息:
EXEC xp_readerrorlog 0,1,'lock',NULL,'2018-07-10 16:30','2018-07-10 17:20','ASC'
 ----我们可以通过检索 lock 字符串,然后找出死锁所涉及的sql信息
---如下所示:


LogDate ProcessInfo Text
2018-07-10 16:38:13.460 spid56 DBCC TRACEON 1222, server process ID (SPID) 56. This is an informational message only; no user action is required.
2018-07-10 16:38:13.460 spid56 DBCC TRACEON 3605, server process ID (SPID) 56. This is an informational message only; no user action is required.
2018-07-10 16:38:13.460 spid56 DBCC TRACEON 1204, server process ID (SPID) 56. This is an informational message only; no user action is required.
2018-07-10 16:44:15.650 spid6s Deadlock encountered …. Printing deadlock information
2018-07-10 16:44:15.650 spid6s Wait-for graph
2018-07-10 16:44:15.650 spid6s NULL
2018-07-10 16:44:15.650 spid6s Node:1
2018-07-10 16:44:15.660 spid6s RID: 14:1:1618263:0 CleanCnt:2 Mode:X Flags: 0x3
2018-07-10 16:44:15.660 spid6s Grant List 0:
2018-07-10 16:44:15.660 spid6s Owner:0x00000000C4DB4EC0 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:59 ECID:0 XactLockInfo: 0x00000000815A4EC0
2018-07-10 16:44:15.680 spid6s SPID: 59 ECID: 0 Statement Type: SELECT Line #: 5
2018-07-10 16:44:15.690 spid6s Input Buf: Language Event: Begin Tran Update tmp1 Set keyIdA = keyIdA + 92 ; WaitFor Delay ‘ 00:00:30 ‘ ; SELECT * FROM tmp2
2018-07-10 16:44:15.690 spid6s Requested by:
2018-07-10 16:44:15.690 spid6s ResType:LockOwner Stype:’OR’Xdes:0x00000000D7974E80 Mode: S SPID:63 BatchID:0 ECID:0 TaskProxy:(0x00000000D82D0538) Value:0xc4d39e00 Cost:(0/208)
2018-07-10 16:44:15.690 spid6s NULL
2018-07-10 16:44:15.690 spid6s Node:2
2018-07-10 16:44:15.690 spid6s RID: 14:1:1881385:0 CleanCnt:2 Mode:X Flags: 0x3
2018-07-10 16:44:15.690 spid6s Grant List 1:
2018-07-10 16:44:15.690 spid6s Owner:0x00000000C4D39F80 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:63 ECID:0 XactLockInfo: 0x00000000D7974EC0
2018-07-10 16:44:15.690 spid6s SPID: 63 ECID: 0 Statement Type: SELECT Line #: 3
2018-07-10 16:44:15.690 spid6s Input Buf: Language Event: Begin Tran Update tmp2 Set keyIdB = keyIdB + 88; SELECT * FROM tmp1
2018-07-10 16:44:15.690 spid6s Requested by:
2018-07-10 16:44:15.690 spid6s ResType:LockOwner Stype:’OR’Xdes:0x00000000815A4E80 Mode: S SPID:59 BatchID:0 ECID:0 TaskProxy:(0x00000000D82EE538) Value:0xc4db4d80 Cost:(0/308)
2018-07-10 16:44:15.790 spid6s NULL
2018-07-10 16:44:15.790 spid6s Victim Resource Owner:
2018-07-10 16:44:15.790 spid6s ResType:LockOwner Stype:’OR’Xdes:0x00000000D7974E80 Mode: S SPID:63 BatchID:0 ECID:0 TaskProxy:(0x00000000D82D0538) Value:0xc4d39e00 Cost:(0/208)
2018-07-10 16:44:15.810 spid19s deadlock-list
2018-07-10 16:44:15.810 spid19s deadlock victim=process3d49288
2018-07-10 16:44:15.810 spid19s process-list
2018-07-10 16:44:15.810 spid19s process id=process3d49288 taskpriority=0 logused=208 waitresource=RID: 14:1:1618263:0 waittime=30023 ownerId=106661 transactionname=user_transaction lasttranstarted=2018-07-10T16:41:42.377 XDES=0xd7974e80 lockMode=S schedulerid=2 kpid=23388 status=suspended spid=63 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2018-07-10T16:43:45.720 lastbatchcompleted=2018-07-10T16:43:40.110 lastattention=2018-07-10T16:43:40.110 clientapp=Microsoft SQL Server Management Studio – 查询 hostname=USER-PC hostpid=18728 loginname=sa isolationlevel=read committed (2) xactid=106661 currentdb=14 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2018-07-10 16:44:15.810 spid19s executionStack
2018-07-10 16:44:15.810 spid19s frame procname=adhoc line=3 stmtstart=126 sqlhandle=0x02000000c62a49004816ca0d8d12073735b0dff4bb97e6c1
2018-07-10 16:44:15.810 spid19s SELECT * FROM tmp1
2018-07-10 16:44:15.810 spid19s inputbuf
2018-07-10 16:44:15.810 spid19s Begin Tran
2018-07-10 16:44:15.810 spid19s Update tmp2 Set keyIdB = keyIdB + 88;
2018-07-10 16:44:15.810 spid19s SELECT * FROM tmp1
2018-07-10 16:44:15.810 spid19s process id=process13ebc8 taskpriority=0 logused=308 waitresource=RID: 14:1:1881385:0 waittime=1412 ownerId=106659 transactionname=user_transaction lasttranstarted=2018-07-10T16:41:40.910 XDES=0x815a4e80 lockMode=S schedulerid=1 kpid=14908 status=suspended spid=59 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2018-07-10T16:43:44.373 lastbatchcompleted=2018-07-10T16:42:11.010 clientapp=Microsoft SQL Server Management Studio – 查询 hostname=USER-PC hostpid=18728 loginname=sa isolationlevel=read committed (2) xactid=106659 currentdb=14 lockTimeout=4294967295 clientoption1=673187936 clientoption2=390200
2018-07-10 16:44:15.810 spid19s executionStack
2018-07-10 16:44:15.810 spid19s frame procname=adhoc line=5 stmtstart=204 sqlhandle=0x02000000d9a131125332a4ec5838b07475764a1339dfd1b5
2018-07-10 16:44:15.810 spid19s SELECT * FROM tmp2
2018-07-10 16:44:15.810 spid19s inputbuf
2018-07-10 16:44:15.810 spid19s Begin Tran
2018-07-10 16:44:15.810 spid19s Update tmp1 Set keyIdA = keyIdA + 92 ;
2018-07-10 16:44:15.810 spid19s WaitFor Delay ‘ 00:00:30 ‘ ;
2018-07-10 16:44:15.810 spid19s SELECT * FROM tmp2
2018-07-10 16:44:15.810 spid19s resource-list
2018-07-10 16:44:15.810 spid19s ridlock fileid=1 pageid=1618263 dbid=14 objectname=rgvip2018625.dbo.tmp1 id=lockc4cd9380 mode=X associatedObjectId=72057614823981056
2018-07-10 16:44:15.810 spid19s owner-list
2018-07-10 16:44:15.810 spid19s owner id=process13ebc8 mode=X
2018-07-10 16:44:15.810 spid19s waiter-list
2018-07-10 16:44:15.810 spid19s waiter id=process3d49288 mode=S requestType=wait
2018-07-10 16:44:15.810 spid19s ridlock fileid=1 pageid=1881385 dbid=14 objectname=rgvip2018625.dbo.tmp2 id=lockc4de7d80 mode=X associatedObjectId=72057614824046592
2018-07-10 16:44:15.810 spid19s owner-list
2018-07-10 16:44:15.810 spid19s owner id=process3d49288 mode=X
2018-07-10 16:44:15.820 spid19s waiter-list
2018-07-10 16:44:15.820 spid19s waiter id=process13ebc8 mode=S requestType=wait

mssql_sqlserver_traceon死锁相关知识

mssql_sqlserver_traceon死锁相关知识