Deadlock Troubleshooting - Trace 1222
OPEN 1222 TRACE
DBCC TRACEON– Enable trace flags.
DBCC TRACEOFF- Disable trace flags.
DBCC TRACESTATUS- Display the status of trace flags.
–> Turn flag on for only the session level
DBCC TRACEON (1222)
DBCC TRACEON (1205,1222)
GO
–> Turn flag on globally at server for all the sessions
DBCC TRACEON (1222, -1 )
GO
– >Turn flag off
DBCC TRACEOFF (1222);
DBCC TRACEOFF (1222, -1 );
GO
–> Show flag status
DBCC TRACESTATUS (1205,1222)
DBCC TRACESTATUS (-1)
Get the sql server error log when deadlock happen
- Approach one
2. Approach 2
Get the deadlock information
deadlock-list
deadlock victim=process1f121ab0
process-list
process id=process1f121ab0 taskpriority=0 logused=0 waitresource=KEY: 105:72057594038910976 (0c006459e83f) waittime=4344 ownerId=341136033 transactionname=INSERT EXEC lasttranstarted=2010-11-07T15:17:10.493 XDES=0x191d6cc0 lockMode=S schedulerid=1 kpid=6012 status=suspended spid=59 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2010-11-07T15:17:10.493 lastbatchcompleted=2010-11-07T15:17:10.480 clientapp=Microsoft SQL Server Management Studio - Query hostname=DUR-DESKTOP1 hostpid=3304 loginname=sa isolationlevel=read committed (2) xactid=341136033 currentdb=105 lockTimeout=4294967295 clientoption1=673187936 clientoption2=390200
executionStack
frame procname=deadlocktest.dbo.p1 line=4 stmtstart=90 sqlhandle=0x0300690020c96c7e14d1fb00279e00000100000000000000
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
frame procname=adhoc line=4 stmtstart=78 stmtend=152 sqlhandle=0x020000000ff6b2216ef7807b9ee77cf22e74f4e2cf9e35b5
INSERT INTO #t1 EXEC p1 4
inputbuf
WHILE (1=1) BEGIN
INSERT INTO #t1 EXEC p1 4
TRUNCATE TABLE #t1
END
process id=process1014c1c0 taskpriority=0 logused=312 waitresource=KEY: 105:72057594038910976 (0b00274d9328) waittime=4344 ownerId=341136042 transactionname=UPDATE lasttranstarted=2010-11-07T15:17:10.510 XDES=0x191d6280 lockMode=X schedulerid=1 kpid=3640 status=suspended spid=58 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2010-11-07T15:17:09.587 lastbatchcompleted=2010-11-07T15:16:56.283 clientapp=Microsoft SQL Server Management Studio - Query hostname=DUR-DESKTOP1 hostpid=3304 loginname=sa isolationlevel=read committed (2) xactid=341136042 currentdb=105 lockTimeout=4294967295 clientoption1=673187936 clientoption2=390200
executionStack
frame procname=deadlocktest.dbo.p2 line=6 stmtstart=188 sqlhandle=0x0300690059ed607f19d1fb00279e00000100000000000000
UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1
frame procname=adhoc line=7 stmtstart=154 stmtend=178 sqlhandle=0x0200000093bb11328a642e3b8a34dde3fe79d2d055d62825
EXEC p2 4
inputbuf
USE deadlocktest
SET NOCOUNT ON
WHILE (1=1)
EXEC p2 4
resource-list
keylock hobtid=72057594038910976 dbid=105 objectname=deadlocktest.dbo.t1 indexname=idx1 id=lock2409ca00 mode=X associatedObjectId=72057594038910976
owner-list
owner id=process1014c1c0 mode=X
waiter-list
waiter id=process1f121ab0 mode=S requestType=wait
keylock hobtid=72057594038910976 dbid=105 objectname=deadlocktest.dbo.t1 indexname=idx1 id=lock2409ddc0 mode=S associatedObjectId=72057594038910976
owner-list
owner id=process1f121ab0 mode=S
waiter-list
waiter id=process1014c1c0 mode=X requestType=wait
转载于:https://www.cnblogs.com/Randy0528/archive/2010/11/10/1873809.html