在 ErrorLog 日志中开启死锁标记

最新推荐文章于 2020-06-10 15:56:52 发布

吉普赛的歌

最新推荐文章于 2020-06-10 15:56:52 发布

阅读量1.2k

点赞数 2

分类专栏： SQL Server - 常用代码 SQL Server - 锁

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

版权

SQL Server - 常用代码同时被 2 个专栏收录

93 篇文章 3 订阅

订阅专栏

SQL Server - 锁

8 篇文章 0 订阅

订阅专栏

--使用下面的命令，将有关的跟踪标志启用。

DBCC TRACEON (3605,1204,1222,-1)  

/*
说明：
3605 将DBCC的结果输出到错误日志。
1204 返回参与死锁的锁的资源和类型，以及受影响的当前命令。
1222 返回参与死锁的锁的资源和类型，以及使用了不符合任何 XSD 架构的 XML 格式的受影响的当前命令(比1204更进一步,SQL 2005及以上可用)。
-1 以全局方式打开指定的跟踪标记。

在运行上面的语句后，当SQL Server中发生死锁时，就可以在错误日志中看到了。（SSMS -> SQL Server实例 -> 管理 -> SQL Server日志）*/

注意：

不要一直开着，这个对性能还是有一定影响的，查清到底有没有死锁，有哪些死锁，优化完了之后就关闭：

DBCC TRACEOFF (3605,1204,1222,-1);

死锁检测示例：

------------------- 1. 开启死锁标记 -----------------------
DBCC TRACEON (3605, 1204, 1222, -1)

------------------- 2. 模拟死锁     -----------------------
--2.1 创建测试表及测试数据
USE tempdb
GO
IF OBJECT_ID('Lock1') IS NOT NULL DROP TABLE Lock1
IF OBJECT_ID('Lock2') IS NOT NULL DROP TABLE Lock2
GO
CREATE   TABLE  Lock1(C1  int   default ( 0 ));
CREATE   TABLE  Lock2(C1  int   default ( 0 ));
SET NOCOUNT ON
INSERT   INTO  Lock1  VALUES ( 1 );
INSERT   INTO  Lock2  VALUES ( 1 );
GO
--2.2 开两个查询窗口，分别执行下面两段 sql
--最后查询2的窗口出现如下错误：
/*
消息 1205，级别 13，状态 45，第 3 行
事务(进程 ID 55)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。
*/
-- Query 1 
Begin   Tran 
   Update  Lock1  Set  C1 = C1 + 1 ;
   WaitFor  Delay  ' 00:00:30 ' ;
   SELECT   *   FROM  Lock2
 
-- Query 2 
Begin   Tran 
   Update  Lock2  Set  C1 = C1 + 1 ;
   SELECT   *   FROM  Lock1

------------------- 3. 查看死锁     -----------------------
/*
xp_readerrorlog 的7个参数
1. 存档编号（0~99）
2. 日志类型（1为SQL Server日志，2为SQL Server Agent日志）
3. 查询包含的字符串
4. 查询包含的字符串
5. LogDate开始时间
6. LogDate结束时间
7. 结果排序，按LogDate排序（Desc、Asc）
*/
--1. 查看 20180301 到现在为止的死锁情况
EXEC xp_readerrorlog 0,1,N'deadlock',NULL,'2018-03-01',NULL,'ASC'

--2. 发现 2018-03-26 10:01:07.360 附近有死锁，查看这个时刻附近的日志
EXEC xp_readerrorlog 0,1,NULL,NULL,'2018-03-26 10:00','2018-03-26 10:05','ASC'
/*
LogDate	ProcessInfo	Text
2018-03-26 10:01:07.360	spid7s	Deadlock encountered .... Printing deadlock information
2018-03-26 10:01:07.360	spid7s	Wait-for graph
2018-03-26 10:01:07.360	spid7s	NULL
2018-03-26 10:01:07.360	spid7s	Node:1
2018-03-26 10:01:07.360	spid7s	RID: 2:8:11:0                  CleanCnt:2 Mode:X Flags: 0x3
2018-03-26 10:01:07.360	spid7s	 Grant List 1:
2018-03-26 10:01:07.360	spid7s	   Owner:0x000000026FBBDC00 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:61 ECID:0 XactLockInfo: 0x0000000271DDC858
2018-03-26 10:01:07.360	spid7s	   SPID: 61 ECID: 0 Statement Type: SELECT Line #: 4
2018-03-26 10:01:07.360	spid7s	   Input Buf: Language Event: Begin   Tran 
   Update  Lock1  Set  C1 = C1 + 1 ;
   WaitFor  Delay  ' 00:00:30 ' ;
   SELECT   *   FROM  Lock2
2018-03-26 10:01:07.360	spid7s	Requested by: 
2018-03-26 10:01:07.360	spid7s	  ResType:LockOwner Stype:'OR'Xdes:0x0000000271DE4690 Mode: S SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0000000262B38758) Value:0x6fbbb800 Cost:(0/224)
2018-03-26 10:01:07.360	spid7s	NULL
2018-03-26 10:01:07.360	spid7s	Node:2
2018-03-26 10:01:07.360	spid7s	RID: 2:6:11:0                  CleanCnt:2 Mode:X Flags: 0x3
2018-03-26 10:01:07.360	spid7s	 Grant List 3:
2018-03-26 10:01:07.360	spid7s	   Owner:0x000000026FBBC0C0 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x0000000271DE46C8
2018-03-26 10:01:07.360	spid7s	   SPID: 55 ECID: 0 Statement Type: SELECT Line #: 3
2018-03-26 10:01:07.360	spid7s	   Input Buf: Language Event: Begin   Tran 
   Update  Lock2  Set  C1 = C1 + 1 ;
   SELECT   *   FROM  Lock1
2018-03-26 10:01:07.360	spid7s	Requested by: 
2018-03-26 10:01:07.360	spid7s	  ResType:LockOwner Stype:'OR'Xdes:0x0000000271DDC820 Mode: S SPID:61 BatchID:0 ECID:0 TaskProxy:(0x0000000262806758) Value:0x6fbbd380 Cost:(0/224)
2018-03-26 10:01:07.360	spid7s	NULL
2018-03-26 10:01:07.360	spid7s	Victim Resource Owner:
2018-03-26 10:01:07.360	spid7s	 ResType:LockOwner Stype:'OR'Xdes:0x0000000271DE4690 Mode: S SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0000000262B38758) Value:0x6fbbb800 Cost:(0/224)
2018-03-26 10:01:07.360	spid24s	deadlock-list
2018-03-26 10:01:07.360	spid24s	 deadlock victim=process4796108
2018-03-26 10:01:07.360	spid24s	  process-list
2018-03-26 10:01:07.360	spid24s	   process id=process4796108 taskpriority=0 logused=224 waitresource=RID: 2:8:11:0 waittime=25861 ownerId=38296 transactionname=user_transaction lasttranstarted=2018-03-26T10:00:41.503 XDES=0x271de4690 lockMode=S schedulerid=4 kpid=2132 status=suspended spid=55 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2018-03-26T10:00:41.500 lastbatchcompleted=2018-03-26T10:00:40.723 lastattention=1900-01-01T00:00:00.723 clientapp=Microsoft SQL Server Management Studio - 查询 hostname=JS1-YNG hostpid=3604 loginname=JS1-YNG\Administrator isolationlevel=read committed (2) xactid=38296 currentdb=2 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2018-03-26 10:01:07.360	spid24s	    executionStack
2018-03-26 10:01:07.360	spid24s	     frame procname=adhoc line=3 stmtstart=112 stmtend=158 sqlhandle=0x02000000f1f1c21b906294b06fc29697d0dfb56a0c5aa00f0000000000000000000000000000000000000000
2018-03-26 10:01:07.360	spid24s	unknown     
2018-03-26 10:01:07.360	spid24s	    inputbuf
2018-03-26 10:01:07.360	spid24s	Begin   Tran 
2018-03-26 10:01:07.360	spid24s	   Update  Lock2  Set  C1 = C1 + 1 ;
2018-03-26 10:01:07.360	spid24s	   SELECT   *   FROM  Lock1
2018-03-26 10:01:07.360	spid24s	   process id=process47404e8 taskpriority=0 logused=224 waitresource=RID: 2:6:11:0 waittime=1847 ownerId=38263 transactionname=user_transaction lasttranstarted=2018-03-26T10:00:35.517 XDES=0x271ddc820 lockMode=S schedulerid=2 kpid=2888 status=suspended spid=61 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2018-03-26T10:00:35.513 lastbatchcompleted=2018-03-26T10:00:27.310 lastattention=1900-01-01T00:00:00.310 clientapp=Microsoft SQL Server Management Studio - 查询 hostname=JS1-YNG hostpid=3604 loginname=JS1-YNG\Administrator isolationlevel=read committed (2) xactid=38263 currentdb=2 lockTimeout=4294967295 clientoption1=673187936 clientoption2=390200
2018-03-26 10:01:07.360	spid24s	    executionStack
2018-03-26 10:01:07.360	spid24s	     frame procname=adhoc line=4 stmtstart=182 stmtend=228 sqlhandle=0x02000000ff839023514dd4781624bdeecb357a7e8e6846ff0000000000000000000000000000000000000000
2018-03-26 10:01:07.360	spid24s	unknown     
2018-03-26 10:01:07.360	spid24s	    inputbuf
2018-03-26 10:01:07.360	spid24s	Begin   Tran 
2018-03-26 10:01:07.360	spid24s	   Update  Lock1  Set  C1 = C1 + 1 ;
2018-03-26 10:01:07.360	spid24s	   WaitFor  Delay  ' 00:00:30 ' ;
2018-03-26 10:01:07.360	spid24s	   SELECT   *   FROM  Lock2    
2018-03-26 10:01:07.360	spid24s	  resource-list
2018-03-26 10:01:07.360	spid24s	   ridlock fileid=8 pageid=11 dbid=2 objectname=tempdb.dbo.Lock1 id=lock27306c700 mode=X associatedObjectId=2089670228249411584
2018-03-26 10:01:07.360	spid24s	    owner-list
2018-03-26 10:01:07.360	spid24s	     owner id=process47404e8 mode=X
2018-03-26 10:01:07.360	spid24s	    waiter-list
2018-03-26 10:01:07.360	spid24s	     waiter id=process4796108 mode=S requestType=wait
2018-03-26 10:01:07.360	spid24s	   ridlock fileid=6 pageid=11 dbid=2 objectname=tempdb.dbo.Lock2 id=lock273067a00 mode=X associatedObjectId=2161727822328299520
2018-03-26 10:01:07.360	spid24s	    owner-list
2018-03-26 10:01:07.360	spid24s	     owner id=process4796108 mode=X
2018-03-26 10:01:07.360	spid24s	    waiter-list
2018-03-26 10:01:07.360	spid24s	     waiter id=process47404e8 mode=S requestType=wait
*/

吉普赛的歌

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
1
评论
在 ErrorLog 日志中开启死锁标记

--使用下面的命令，将有关的跟踪标志启用。DBCC TRACEON (3605,1204,1222,-1) /*说明：3605 将DBCC的结果输出到错误日志。1204 返回参与死锁的锁的资源和类型，以及受影响的当前命令。1222 返回参与死锁的锁的资源和类型，以及使用了不符合任何 XSD 架构的 XML 格式的受影响的当前命令(比1204更进一步,SQL 2005及以上可用)。
复制链接

扫一扫