跟踪标记 (Trace Flag) 1204, 1222 抓取死锁信息

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;

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.

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.