I really need help on understanding this deadlock, i've already replaced the values, mind if anyone helps me interpreting where my deadlocks are coming from?
What does e_waitpipegetrow mean? btw, i'm using sql server 2008 r2 with read_committed_snapshot on.
What's the reason why i'm getting lock on primary key which is a clustered index? need help on understanding this thanks
I'll post the trace
update TEST_REQUEST set VERSION= @P0 , DEL= @P1 , FR_USER_CD= @P2 , TO_USER_CD= @P3 , CCY= @P4 , TOTAL_AMT= @P5 , TEST_BREAKDOWN= @P6 , STATUS= @P7 , TXN_DT= @P8 , CREATED_DT= @P9 , AS_TESTIER= @P10 , BRANCH_CD= @P11 , ORIG_BRANCH_CD= @P12 where ID= @P13 and VERSION= @P14
inputbuf
(@P0 int,@P1 int,@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 decimal(38,4),@P6 nvarchar(4000),@P7 int,@P8 datetime,@P9 datetime,@P10 int,@P11 nvarchar(4000),@P12 nvarchar(4000),@P13 nvarchar(4000),@P14 int)update TEST_REQUEST set VERSION= @P0 , DEL= @P1 , FR_USER_CD= @P2 , TO_USER_CD= @P3 , CCY= @P4 , TOTAL_AMT= @P5 , TEST_BREAKDOWN= @P6 , STATUS= @P7 , TXN_DT= @P8 , CREATED_DT= @P9 , AS_TESTIER= @P10 , BRANCH_CD= @P11 , ORIG_BRANCH_CD= @P12 where ID= @P13 and VERSION= @P14
process id=process6e0a508 taskpriority=0 logused=10000 waittime=8573 schedulerid=22 kpid=1544 status=suspended spid=282 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-12-18T17:32:59.613 lastbatchcompleted=2012-12-18T17:32:59.610 clientapp=jTDS hostname=TESTAPP hostpid=123 loginname=TEST-MAIN isolationlevel=read committed (2) xactid=10015978215 currentdb=19 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
executionStack
frame procname=adhoc line=1 stmtstart=110 sqlhandle=0x020000008fb821226c5f2091c097aef7b4b4cc8c9b09cc32
update dbo.TEST_REQUEST set "VERSION" = @P0 , "TEST_BREAKDOWN" = @P1 , "STATUS" = @P2 where "ID" = @P3
inputbuf
(@P0 int,@P1 nvarchar(4000),@P2 int,@P3 nvarchar(4000))update dbo.TEST_REQUEST set "VERSION" = @P0 , "TEST_BREAKDOWN" = @P1 , "STATUS" = @P2 where "ID" = @P3
process id=process5a1ae08 taskpriority=0 logused=10000 waittime=7626 schedulerid=14 kpid=5388 status=suspended spid=57 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-12-18T17:33:11.827 lastbatchcompleted=2012-12-18T17:33:11.827 clientapp=jTDS hostname=TESTAPP hostpid=123 loginname=TEST-MAIN isolationlevel=read committed (2) xactid=10015976014 currentdb=19 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
executionStack
frame procname=adhoc line=1 stmtstart=110 sqlhandle=0x020000008fb821226c5f2091c097aef7b4b4cc8c9b09cc32
update dbo.TEST_REQUEST set "VERSION" = @P0 , "TEST_BREAKDOWN" = @P1 , "STATUS" = @P2 where "ID" = @P3
inputbuf
(@P0 int,@P1 nvarchar(4000),@P2 int,@P3 nvarchar(4000))update dbo.TEST_REQUEST set "VERSION" = @P0 , "TEST_BREAKDOWN" = @P1 , "STATUS" = @P2 where "ID" = @P3
process id=process267b9bdc8 taskpriority=0 logused=10000 waittime=2546 schedulerid=2 kpid=7640 status=suspended spid=250 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-12-18T17:33:04.923 lastbatchcompleted=2012-12-18T17:33:04.923 clientapp=jTDS hostname=TESTAPP hostpid=123 loginname=TEST-MAIN isolationlevel=read committed (2) xactid=10016032860 currentdb=19 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
executionStack
frame procname=adhoc line=1 stmtstart=110 sqlhandle=0x020000008fb821226c5f2091c097aef7b4b4cc8c9b09cc32
update dbo.TEST_REQUEST set "VERSION" = @P0 , "TEST_BREAKDOWN" = @P1 , "STATUS" = @P2 where "ID" = @P3
inputbuf
(@P0 int,@P1 nvarchar(4000),@P2 int,@P3 nvarchar(4000))update dbo.TEST_REQUEST set "VERSION" = @P0 , "TEST_BREAKDOWN" = @P1 , "STATUS" = @P2 where "ID" = @P3
process id=process210f882c8 taskpriority=0 logused=10000 waittime=19793 schedulerid=5 kpid=7252 status=suspended spid=134 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2012-12-18T17:32:59.757 lastbatchcompleted=2012-12-18T17:32:59.757 clientapp=jTDS hostname=TESTAPP hostpid=123 loginname=TEST-MAIN isolationlevel=read committed (2) xactid=10015980292 currentdb=19 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
executionStack
frame procname=adhoc line=1 stmtstart=110 sqlhandle=0x020000008fb821226c5f2091c097aef7b4b4cc8c9b09cc32
update dbo.TEST_REQUEST set "VERSION" = @P0 , "TEST_BREAKDOWN" = @P1 , "STATUS" = @P2 where "ID" = @P3
inputbuf
(@P0 int,@P1 nvarchar(4000),@P2 int,@P3 nvarchar(4000))update dbo.TEST_REQUEST set "VERSION" = @P0 , "TEST_BREAKDOWN" = @P1 , "STATUS" = @P2 where "ID" = @P3
resource-list
keylock hobtid=72057594170441728 dbid=19 objectname=test-main.dbo.TEST_REQUEST indexname=PK_TEST_REQUEST_ID id=lock2b6369900 mode=U associatedObjectId=72057594170441728
owner-list
waiter-list
waiter id=process16b231dc8 mode=U requestType=wait
keylock hobtid=72057594170441728 dbid=19 objectname=test-main.dbo.TEST_REQUEST indexname=PK_TEST_REQUEST_ID id=lock29a763580 mode=U associatedObjectId=72057594170441728
owner-list
owner id=process16b231dc8 mode=U
waiter-list
waiter id=process218c6ddc8 mode=U requestType=wait
keylock hobtid=72057594170441728 dbid=19 objectname=test-main.dbo.TEST_REQUEST indexname=PK_TEST_REQUEST_ID id=locka49ae300 mode=U associatedObjectId=72057594170441728
owner-list
owner id=process6e0abc8 mode=U
waiter-list
waiter id=process50e9b88 mode=U requestType=wait
keylock hobtid=72057594170441728 dbid=19 objectname=test-main.dbo.TEST_REQUEST indexname=PK_TEST_REQUEST_ID id=lock22babb180 mode=U associatedObjectId=72057594170441728
owner-list
owner id=process267b9bdc8 mode=U
waiter-list
waiter id=process14fd16748 mode=U requestType=wait
keylock hobtid=72057594170441728 dbid=19 objectname=test-main.dbo.TEST_REQUEST indexname=PK_TEST_REQUEST_ID id=lock30b095600 mode=U associatedObjectId=72057594170441728
owner-list
waiter-list
waiter id=process6dd6988 mode=U requestType=wait
keylock hobtid=72057594170441728 dbid=19 objectname=test-main.dbo.TEST_REQUEST indexname=PK_TEST_REQUEST_ID id=lock28a521480 mode=U associatedObjectId=72057594170441728
owner-list
owner id=processb86df948 mode=U
waiter-list
waiter id=process11fa52e08 mode=U requestType=wait
keylock hobtid=72057594170441728 dbid=19 objectname=test-main.dbo.TEST_REQUEST indexname=PK_TEST_REQUEST_ID id=lock28242fd80 mode=U associatedObjectId=72057594170441728
owner-list
owner id=process267b9bdc8 mode=U
waiter-list
waiter id=process1d0865708 mode=U requestType=wait
keylock hobtid=72057594170441728 dbid=19 objectname=test-main.dbo.TEST_REQUEST indexname=PK_TEST_REQUEST_ID id=lock115617680 mode=U associatedObjectId=72057594170441728
owner-list
owner id=process267b9bdc8 mode=U
waiter-list
e_waitpipegetrow? I don't even see that in your trace? – Martin Smith Jan 2 at 22:19