deadlock

Question

Post reply

deadlock

madhu-686862

SSChampion

Points: 10707
More actions
April 10, 2009 at 9:51 am

#136566

Hi,
We have BizTalk databases in SQL Server 2005. Our Biztalk application runs daily at 9:00PM only. But Iam getting this dead lock when there is no activity on application side. Could you please expalin me why Iam getting this deadlock and how to aviod this.
deadlock-list
2009-04-08 17:58:47.24 spid17s deadlock victim=processf3ac58
2009-04-08 17:58:47.24 spid17s process-list
2009-04-08 17:58:47.24 spid17s process id=processf3ac58 taskpriority=5 logused=0 waitresource=KEY: 5:72057594071154688 (ee04eb48cba6) waittime=3609 ownerId=375242193 transactionname=implicit_transaction lasttranstarted=2009-04-08T17:58:43.627 XDES=0xed198ba0 lockMode=U schedulerid=3 kpid=320 status=suspended spid=77 sbid=0 ecid=0 priority=-5 transcount=1 lastbatchstarted=2009-04-08T17:58:43.627 lastbatchcompleted=2009-04-08T17:58:43.627 clientapp=E975A372-1339-4104-AF1A-45EBAA14B310 hostname=BLOCK1 hostpid=2272 loginname=abc\Admin isolationlevel=read committed (2) xactid=375242193 currentdb=5 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128058
2009-04-08 17:58:47.24 spid17s executionStack
2009-04-08 17:58:47.24 spid17s frame procname=BizTalkMsgBoxDb.dbo.bts_DeQueueMessages_abcHermes line=196 stmtstart=17972 stmtend=17998 sqlhandle=0x03000500dc4d7b7b1cb40d01389b00000100000000000000
2009-04-08 17:58:47.24 spid17s OPEN btscurse
2009-04-08 17:58:47.24 spid17s inputbuf
2009-04-08 17:58:47.24 spid17s Proc [Database Id = 5 Object Id = 2071678428]
2009-04-08 17:58:47.24 spid17s process id=processf925c8 taskpriority=0 logused=7744 waitresource=KEY: 5:72057594071154688 (db04f5119d5b) waittime=3609 ownerId=375242203 transactionname=implicit_transaction lasttranstarted=2009-04-08T17:58:43.630 XDES=0x8bb29480 lockMode=U schedulerid=7 kpid=6204 status=suspended spid=81 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-04-08T17:58:43.627 lastbatchcompleted=2009-04-08T17:58:43.610 clientapp=E975A372-1339-4104-AF1A-45EBAA14B310 hostname=BLOCK1 hostpid=2272 loginname=abc\Admin isolationlevel=read committed (2) xactid=375242203 currentdb=5 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128058
2009-04-08 17:58:47.24 spid17s executionStack
2009-04-08 17:58:47.24 spid17s frame procname=BizTalkMsgBoxDb.dbo.int_AlterInstance_abcHermes line=307 stmtstart=31602 stmtend=31914 sqlhandle=0x03000500c0de4b7f25b40d01389b00000100000000000000
2009-04-08 17:58:47.24 spid17s DELETE FROM dbo.[abcHermesQ] FROM dbo.[abcHermesQ] WITH (ROWLOCK INDEX([CIX_abcHermesQ])) WHERE uidInstanceID = @uidInstanceID
OPTION (KEEPFIXED PLAN)
2009-04-08 17:58:47.24 spid17s frame procname=BizTalkMsgBoxDb.dbo.bts_UpdateMsgbox_abcHermes line=59 stmtstart=3746 stmtend=4376 sqlhandle=0x03000500322734012ab40d01389b00000100000000000000
2009-04-08 17:58:47.24 spid17s exec [dbo].[int_AlterInstance_abcHermes] @naction, @uidObjectID, @uidInstanceID, @uidInstanceStateID, @uidWorkID, @uidPortID, @uidServiceID, @nvcErrorID, @nErrorCategory, @nvcAdditionalInfo, @nIsResumable, @fOptimize, @dtResubmitTimeStamp, @nvcLastAction, @nvcURL, @nvcAdapter, @uidDBOwnerID OUTPUT, @nReturn OUTPUT
2009-04-08 17:58:47.24 spid17s inputbuf
2009-04-08 17:58:47.24 spid17s Proc [Database Id = 5 Object Id = 20195122]
2009-04-08 17:58:47.24 spid17s resource-list
2009-04-08 17:58:47.24 spid17s keylock hobtid=72057594071154688 dbid=5 objectname=BizTalkMsgBoxDb.dbo.abcHermesQ indexname=IX_abcHermesQ id=lock801d4a80 mode=U associatedObjectId=72057594071154688
2009-04-08 17:58:47.24 spid17s owner-list
2009-04-08 17:58:47.24 spid17s owner id=processf3ac58 mode=U
2009-04-08 17:58:47.24 spid17s waiter-list
2009-04-08 17:58:47.24 spid17s waiter id=processf925c8 mode=U requestType=wait
2009-04-08 17:58:47.24 spid17s keylock hobtid=72057594071154688 dbid=5 objectname=BizTalkMsgBoxDb.dbo.abcHermesQ indexname=IX_abcHermesQ id=lock8a710500 mode=X associatedObjectId=72057594071154688
2009-04-08 17:58:47.24 spid17s owner-list
2009-04-08 17:58:47.24 spid17s owner id=processf925c8 mode=X
2009-04-08 17:58:47.24 spid17s waiter-list
2009-04-08 17:58:47.24 spid17s waiter id=processf3ac58 mode=U requestType=wait

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Scott Ohar Hall of Fame Points: 3607 More actions · Answer 1

Biztalk is loads of fun....

Make sure your DTA Purge and Archive sql job is configured correctly and running.

It could be a number of things...

Scott Ohar Hall of Fame Points: 3607 More actions · Answer 2

and also check your PurgeSubscriptionJob on the MsgBoxDB

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

processf3ac58 with spid=77 has been asking for an update 'U' lock on resource with KEY 5:72057594071154688

processf925c8 with spid=81 has is asking for an update lock 'U' on the same resource KEY: 5:72057594071154688

processf925c8 has already got an exclusive lock on the resource but process3ac58 is asking for an update lock.

so basically deadlock between processes spid =81 and spid=71. spid =17 has been chosen as the deadlock victim.

DBCC INPUTBUFFER(SPID)- identify the queries and troubleshoot them. modify the indexes or you can use the option WAIT FOR DELAY. its just a matter of timing.

I am not sure whether its the same for biztalk server.