3. 跟蹤標記 (Trace Flag) 1204, 1222 抓取死鎖信息

跟蹤標記：1204/1222 功能及用途：捕獲SQL Server死鎖信息，並自動存放到錯誤日誌(ERRORLOG)中。舉例：跟蹤標記1204 在錯誤日誌中記錄的死鎖信息跟蹤標記1222 在錯誤日誌中記錄的死鎖信息小結： (1) 不需要加跟蹤標記3605，跟蹤標記1204,1222捕獲的死 ...

跟蹤標記：1204/1222

功能及用途：

捕獲SQL Server死鎖信息，並自動存放到錯誤日誌(ERRORLOG)中。

舉例：

USE tempdb
GO 

CREATE TABLE t1(id int)
INSERT t1 SELECT 1

CREATE TABLE t2(id int)
INSERT t2 SELECT 1
GO 

--開啟1204/1222跟蹤標記(任何查詢視窗)
DBCC TRACEON(1204,-1)
--DBCC TRACEON(1205,3605,-1)
--DBCC TRACEON(1206,3605,-1)

--DBCC TRACEON(1222,-1)
GO 

--查詢視窗1
SET LOCK_TIMEOUT -1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN
    UPDATE t1 SET id = 0 WHERE id=1
    WAITFOR DELAY '00:00:05'
    UPDATE t2 SET id = 0 WHERE id=1
--COMMIT TRAN
GO 

--查詢視窗2
SET LOCK_TIMEOUT -1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

--死鎖優先順序為low，將被選擇作為犧牲品
SET DEADLOCK_PRIORITY LOW 

BEGIN TRAN
    UPDATE t2 SET id = 0 WHERE id=1
    UPDATE t1 SET id = 0 WHERE id=1
--COMMIT TRAN
GO 

--關閉1204/1222跟蹤標記(任何查詢視窗)
DBCC TRACEOFF(1204,-1)
--DBCC TRACEOFF(1205,3605,-1)
--DBCC TRACEOFF(1206,3605,-1)

--DBCC TRACEOFF(1222,-1)
GO 

--查看錯誤日誌里的死鎖信息
exec xp_readerrorlog 0,1

DROP TABLE t1,t2

跟蹤標記1204 在錯誤日誌中記錄的死鎖信息

DBCC TRACEON 1204, server process ID (SPID) 56. This is an informational message only; no user action is required.
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1

RID: 2:1:624:0                 CleanCnt:2 Mode:X Flags: 0x3
 Grant List 1:
   Owner:0x00000034DF20D840 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:56 ECID:0 XactLockInfo: 0x00000034DDE34440
   SPID: 56 ECID: 0 Statement Type: UPDATE Line #: 9
   Input Buf: Language Event: 
--查詢視窗1
SET LOCK_TIMEOUT -1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN
    UPDATE t1 SET id = 0 WHERE id=1
    WAITFOR DELAY '00:00:05'
    UPDATE t2 SET id = 0 WHERE id=1
--COMMIT TRAN

Requested by: 
  ResType:LockOwner Stype:'OR'Xdes:0x00000034E122B2B8 Mode: U SPID:58 BatchID:0 ECID:0 TaskProxy:(0x00000034BB418870) Value:0xdf20d040 Cost:(5/224)
NULL
Node:2

RID: 2:3:944:0                 CleanCnt:2 Mode:X Flags: 0x3
 Grant List 1:
   Owner:0x00000034DF20D980 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:58 ECID:0 XactLockInfo: 0x00000034E122B2F0
   SPID: 58 ECID: 0 Statement Type: UPDATE Line #: 8
   Input Buf: Language Event: 
--查詢視窗2
SET LOCK_TIMEOUT -1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW

BEGIN TRAN
    UPDATE t2 SET id = 0 WHERE id=1
    UPDATE t1 SET id = 0 WHERE id=1
--COMMIT TRAN
Requested by: 
  ResType:LockOwner Stype:'OR'Xdes:0x00000034DDE34408 Mode: U SPID:56 BatchID:0 ECID:0 TaskProxy:(0x00000034BB1DC870) Value:0xdf20ec00 Cost:(0/224)
NULL
Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x00000034E122B2B8 Mode: U SPID:58 BatchID:0 ECID:0 TaskProxy:(0x00000034BB418870) Value:0xdf20d040 Cost:(5/224)
DBCC TRACEOFF 1204, server process ID (SPID) 56. This is an informational message only; no user action is required.

跟蹤標記1222 在錯誤日誌中記錄的死鎖信息

DBCC TRACEON 1222, server process ID (SPID) 56. This is an informational message only; no user action is required.
deadlock-list
 deadlock victim=process34d71fc4e8
  process-list
   process id=process34d71fc4e8 taskpriority=5 logused=224 waitresource=RID: 2:1:624:0 waittime=3012 ownerId=1281992 transactionname=user_transaction lasttranstarted=2016-10-12T10:11:24.513 XDES=0x34dde34408 lockMode=U schedulerid=2 kpid=5736 status=suspended spid=58 sbid=0 ecid=0 priority=-5 trancount=2 lastbatchstarted=2016-10-12T10:11:24.513 lastbatchcompleted=2016-10-12T10:11:12.760 lastattention=2016-10-12T10:10:46.323 clientapp=Microsoft SQL Server Management Studio - Query hostname=CHRZHANG hostpid=6924 loginname=PCLC0\chrzhang isolationlevel=read committed (2) xactid=1281992 currentdb=2 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=adhoc line=9 stmtstart=38 stmtend=116 sqlhandle=0x020000006f1dc606af4ee82be297cae142d4eca1b1b26d840000000000000000000000000000000000000000
unknown     
     frame procname=adhoc line=9 stmtstart=324 stmtend=384 sqlhandle=0x02000000c9c0f33adb0fe790eb6b0e4c7175f9f4b5931a970000000000000000000000000000000000000000
unknown     
    inputbuf
--查詢視窗2
SET LOCK_TIMEOUT -1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW
BEGIN TRAN
    UPDATE t2 SET id = 0 WHERE id=1
    UPDATE t1 SET id = 0 WHERE id=1
--COMMIT TRAN
   process id=process34ddc6a4e8 taskpriority=0 logused=224 waitresource=RID: 2:3:944:0 waittime=1071 ownerId=1281979 transactionname=user_transaction lasttranstarted=2016-10-12T10:11:21.440 XDES=0x34e122b2b8 lockMode=U schedulerid=2 kpid=968 status=suspended spid=56 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2016-10-12T10:11:21.440 lastbatchcompleted=2016-10-12T10:11:04.287 lastattention=1900-01-01T00:00:00.287 clientapp=Microsoft SQL Server Management Studio - Query hostname=CHRZHANG hostpid=6924 loginname=PCLC0\chrzhang isolationlevel=read committed (2) xactid=1281979 currentdb=2 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
    executionStack
     frame procname=adhoc line=8 stmtstart=38 stmtend=116 sqlhandle=0x0200000083cd4832d993ca3b2dcaae9f9cc70a25e718dbe90000000000000000000000000000000000000000
unknown     
     frame procname=adhoc line=8 stmtstart=326 stmtend=386 sqlhandle=0x02000000ff6a84274c6888c86c481ae84300231e8f5fb8000000000000000000000000000000000000000000
unknown     
    inputbuf
--查詢視窗1
SET LOCK_TIMEOUT -1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
    UPDATE t1 SET id = 0 WHERE id=1
    WAITFOR DELAY '00:00:05'
    UPDATE t2 SET id = 0 WHERE id=1
--COMMIT TRAN
  resource-list
   ridlock fileid=1 pageid=624 dbid=2 objectname=tempdb.dbo.t1 id=lock34d8ea3200 mode=X associatedObjectId=2017612634171244544
    owner-list
     owner id=process34ddc6a4e8 mode=X
    waiter-list
     waiter id=process34d71fc4e8 mode=U requestType=wait
   ridlock fileid=3 pageid=944 dbid=2 objectname=tempdb.dbo.t2 id=lock34d8ea3780 mode=X associatedObjectId=2089670228250132480
    owner-list
     owner id=process34d71fc4e8 mode=X
    waiter-list
     waiter id=process34ddc6a4e8 mode=U requestType=wait
DBCC TRACEOFF 1222, server process ID (SPID) 56. This is an informational message only; no user action is required.

小結：

(1) 不需要加跟蹤標記3605，跟蹤標記1204,1222捕獲的死鎖信息便會被寫到錯誤日誌；

(2) 跟蹤標記 1222以XML樣式返回死鎖信息，相比跟蹤標記1204，返回的信息也更為豐富；

(3) 無文檔記載的跟蹤標記1205,1206，據說可以用來豐富1204捕獲死鎖的信息，在SQL Server 2016下簡單測試同時開啟1204,1205,1206，和單獨開啟1204並沒發現有什麼不同，個人猜測是隨著版本更替，跟蹤標記捕獲的死鎖信息已經被整合，直接用1204或1222即可；

(4) 從SQL Server 2008開始，引進了擴展事件(Extended Events)，也可以用來捕獲死鎖信息。

參考：

Tracing a SQL Server Deadlock

https://www.mssqltips.com/sqlservertutorial/252/tracing-a-sql-server-deadlock/

Trace Flags - SQL Server Wiki - SQL Server - Toad World

https://www.toadworld.com/platforms/sql-server/w/wiki/9790.trace-flags

Using SQL Server 2008 Extended Events

https://technet.microsoft.com/en-us/library/dd822788%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396