sql server deadlock trace flag 1222

Hi Friends,

This is my 16th blog on SQL Server Trace Flag 122 in the series of One Trace Flag a Day. You can go to all blogs under that series by clicking here.

Deadlock is a very common problem for all the DBA. First phase of deadlock resolution is Deadlock detection. So for deadlock detection, Microsoft provides a trace flag 1222 that can write deadlock information to sql server error log. You can analyze that information later to prevent it in future.

Trace flag 1222 provides process and resource base information about deadlock in XML format. In another words you can say that all processes and resources which are involved in deadlock.

Now I’ll show you the output of trace flag 1222 by creating a deadlock. Below script will only creates database, tables and also insert data into them. It will also do error log recycle and enable the trace flag 1222.

use master
go
Create database Trace1222
go
use Trace1222
go
create table xttrace1
(
id int identity(1,1),
fname varchar(50),
lname varchar(50),
city varchar(100)
)
go
insert into xttrace1 values('prince','rastogi','moradabad')
go
insert into xttrace1 values('deep','pandey','haldwani')
go
insert into xttrace1 values('ashok','kumar','hydrabad')
go
insert into xttrace1 values('keshav','mahapatra','cuttack')
go
select * into xttrace2 from xttrace1
go
DBCC TRACEON(1222,-1)
go
exec sp_cycle_errorlog
go

Now open a new query window (first session) in management studio and run below query:

use Trace1222
go
begin tran

update xttrace1
set fname='ashwani'
where city='moradabad'

waitfor delay '00:00:30'

update xttrace2
set fname='raveesh'
where city='cuttack'

commit tran

Again open a new query window (second session) in management studio and run below query:

use Trace1222
go
begin tran

update xttrace2
set fname='raveesh'
where city='cuttack'

waitfor delay '00:00:30'

update xttrace1
set fname='ashwani'
where city='moradabad'

commit tran

In my case, first session completed successfully while second session selected as deadlock victim. Second session gave me below as output.

(1 row(s) affected)
Msg 1205, Level 13, State 45, Line 11
Transaction (Process ID 54) was deadlocked on lock resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.

Due to trace flag 1222 output, you can read the detailed information from sql server error log now.

deadlock-list
 deadlock victim=process4bb0a60
  process-list
   process id=process4bb0a60 taskpriority=0 logused=268 waitresource=RID: 16:1:296:0 waittime=716 ownerId=1090942 transactionname=user_transaction lasttranstarted=2014-10-01T23:33:28.063 XDES=0xb1ad0f8 lockMode=U schedulerid=1 kpid=4324 status=suspended sp
    executionStack
     frame procname=adhoc line=9 stmtstart=70 stmtend=170 sqlhandle=0x0200000091761b06bd00263eb084ef41f29e0d96a07f6abb00000000000000000000000000000000
unknown     
     frame procname=adhoc line=9 stmtstart=208 stmtend=326 sqlhandle=0x02000000d5df0e0144b2cc88f83f899aa72e45d2615a4e8100000000000000000000000000000000
unknown     
    inputbuf
begin tran
update xttrace2
set fname='raveesh'
where city='cuttack'
waitfor delay '00:00:30'
update xttrace1
set fname='ashwani'
where city='moradabad'
commit tran    
   process id=process159e0a60 taskpriority=0 logused=268 waitresource=RID: 16:1:300:3 waittime=2871 ownerId=1089573 transactionname=user_transaction lasttranstarted=2014-10-01T23:33:25.910 XDES=0xb934298 lockMode=U schedulerid=1 kpid=5612 status=suspended 
    executionStack
     frame procname=adhoc line=9 stmtstart=70 stmtend=170 sqlhandle=0x0200000068364a06a2ab21217623fb31110e431156174e2700000000000000000000000000000000
unknown     
     frame procname=adhoc line=9 stmtstart=212 stmtend=326 sqlhandle=0x020000000fb0b23ae62c384f091bb50094c66076f57e9c7a00000000000000000000000000000000
unknown     
    inputbuf
begin tran
update xttrace1
set fname='ashwani'
where city='moradabad'
waitfor delay '00:00:30'
update xttrace2
set fname='raveesh'
where city='cuttack'
commit tran    
  resource-list
   ridlock fileid=1 pageid=296 dbid=16 objectname=Trace1222.dbo.xttrace1 id=lock4c31400 mode=X associatedObjectId=72057594040549376
    owner-list
     owner id=process159e0a60 mode=X
    waiter-list
     waiter id=process4bb0a60 mode=U requestType=wait
   ridlock fileid=1 pageid=300 dbid=16 objectname=Trace1222.dbo.xttrace2 id=lock4c30800 mode=X associatedObjectId=72057594040614912
    owner-list
     owner id=process4bb0a60 mode=X
    waiter-list
     waiter id=process159e0a60 mode=U requestType=wait

Now you can see that it provides the processes and resource based information about deadlock.

Finally, don’t forget to turnoff the trace flag 1222 here during this test scenario.

use master
go
DBCC TRACEOFF(1222,-1)
Go

PS: Do not use trace flags in production environment without testing it on non production environments and without consulting because everything comes at a cost.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook