Another Deadlock

Question

Another Deadlock

VastSQL

SSC-Dedicated

Points: 36153
More actions
March 10, 2016 at 12:32 am

#311979

Hi Experts,
Below is the xml of deadlock i observed today. Analysing the deadlock give me some idea that the App Lock is deadlocking on BizTalk Job 'TrackedMessages_Copy_BizTalkMsgBoxDb'. Can anyone help me understand more and resolve this?
These are BizTalk internal Jobs are Procedures involved.
<deadlock>
<victim-list>
<victimProcess id="process2792108" />
</victim-list>
<process-list>
<process id="process2792108" taskpriority="5" logused="0" waitresource="KEY: 5:72057594046644224 (5efa53ea692a)" waittime="1969" ownerId="259407729" transactionname="COND WITH QUERY" lasttranstarted="2016-03-09T23:31:04.190" XDES="0x5be78da20" lockMode="S" schedulerid="7" kpid="32660" status="suspended" spid="126" sbid="0" ecid="0" priority="-5" trancount="0" lastbatchstarted="2016-03-09T23:31:00.630" lastbatchcompleted="2016-03-09T23:31:00.630" lastattention="1900-01-01T00:00:00.630" clientapp="SQLAgent - TSQL JobStep (Job 0xD4354551779F5E4BB0C85045265A736B : Step 1)" hostname="SERVER2" hostpid="27228" loginname="DC\BTLOGIN" isolationlevel="read committed (2)" xactid="259407729" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
<executionStack>
<frame procname="BizTalkMsgBoxDb.dbo.bts_CopyTrackedMessagesToDTA" line="86" stmtstart="8370" stmtend="9228" sqlhandle="0x03000500061f8c07f3580b016da5000001000000000000000000000000000000000000000000000000000000">
IF EXISTS (SELECT TOP 1 p.nNumFragments
FROM (SELECT TOP 50 uidMessageID, nID FROM dbo.TrackingMessageReferences WITH (ROWLOCK INDEX(IX_TrackingMessageReferences)) ORDER BY nID ASC) as t
INNER LOOP JOIN dbo.MessageParts mp WITH (ROWLOCK INDEX(CIX_MessageParts)) ON t.uidMessageID = mp.uidMessageID
INNER LOOP JOIN dbo.Parts p WITH (ROWLOCK INDEX(IX_Parts)) ON mp.uidPartID = p.uidPartID AND p.nNumFragments > 1 </frame>
<frame procname="adhoc" line="1" sqlhandle="0x0100050082d13003b00a74a60500000000000000000000000000000000000000000000000000000000000000">
exec bts_CopyTrackedMessagesToDTA 'SERVER1', 'BizTalkDTADb' </frame>
</executionStack>
<inputbuf>
exec bts_CopyTrackedMessagesToDTA 'SERVER1', 'BizTalkDTADb' </inputbuf>
</process>
<process id="process2413088" taskpriority="0" logused="13600" waitresource="APPLICATION: 5:0:[TrkMsgRefs]:(8086087a)" waittime="5009" ownerId="259402672" transactionname="implicit_transaction" lasttranstarted="2016-03-09T23:31:01.143" XDES="0x5fc663400" lockMode="S" schedulerid="1" kpid="48216" status="suspended" spid="74" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-03-09T23:31:01.147" lastbatchcompleted="2016-03-09T23:31:01.143" lastattention="1900-01-01T00:00:00.143" clientapp="9BF05D7F-F9AD-4141-B2F3-FDFFE7FB88A5" hostname="APPSERVER1-SYS" hostpid="38452" loginname="DC\APPLOGIN" isolationlevel="read committed (2)" xactid="259402672" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
<executionStack>
<frame procname="mssqlsystemresource.sys.xp_userlock" line="1" stmtstart="-1" sqlhandle="0x0400ff7f8ed587d8010000000000000000000000000000000000000000000000000000000000000000000000">
xp_userlock </frame>
<frame procname="mssqlsystemresource.sys.sp_getapplock" line="56" stmtstart="2762" stmtend="2958" sqlhandle="0x0300ff7fbb63b9f6d9d7350180a4000001000000000000000000000000000000000000000000000000000000">
exec @result = sys.xp_userlock 0, @dbid, @DbPrincipal, @Resource, @mode, @owner, @LockTimeout </frame>
<frame procname="BizTalkMsgBoxDb.dbo.int_TrackMessage" line="7" stmtstart="250" stmtend="384" sqlhandle="0x030005008f78b95aa5580b016da5000001000000000000000000000000000000000000000000000000000000">
exec @retVal = sp_getapplock 'TrkMsgRefs', 'Shared', 'Transaction' </frame>
<frame procname="BizTalkMsgBoxDb.dbo.bts_InsertMessage" line="82" stmtstart="7044" stmtend="7126" sqlhandle="0x03000500e823327adc580b016da5000001000000000000000000000000000000000000000000000000000000">
exec dbo.int_TrackMessage @uidMessageID </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 5 Object Id = 2050106344] </inputbuf>
</process>
<process id="process2de3088" taskpriority="0" logused="21612" waitresource="APPLICATION: 5:0:[TrkMsgRefs]:(8086087a)" waittime="4987" ownerId="259402812" transactionname="implicit_transaction" lasttranstarted="2016-03-09T23:31:01.167" XDES="0x5e0d17880" lockMode="S" schedulerid="14" kpid="59008" status="suspended" spid="127" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-03-09T23:31:01.170" lastbatchcompleted="2016-03-09T23:31:01.167" lastattention="1900-01-01T00:00:00.167" clientapp="9BF05D7F-F9AD-4141-B2F3-FDFFE7FB88A5" hostname="APPSERVER1-SYS" hostpid="38452" loginname="DC\APPLOGIN" isolationlevel="read committed (2)" xactid="259402812" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
<executionStack>
<frame procname="mssqlsystemresource.sys.xp_userlock" line="1" stmtstart="-1" sqlhandle="0x0400ff7f8ed587d8010000000000000000000000000000000000000000000000000000000000000000000000">
xp_userlock </frame>
<frame procname="mssqlsystemresource.sys.sp_getapplock" line="56" stmtstart="2762" stmtend="2958" sqlhandle="0x0300ff7fbb63b9f6d9d7350180a4000001000000000000000000000000000000000000000000000000000000">
exec @result = sys.xp_userlock 0, @dbid, @DbPrincipal, @Resource, @mode, @owner, @LockTimeout </frame>
<frame procname="BizTalkMsgBoxDb.dbo.int_TrackMessage" line="7" stmtstart="250" stmtend="384" sqlhandle="0x030005008f78b95aa5580b016da5000001000000000000000000000000000000000000000000000000000000">
exec @retVal = sp_getapplock 'TrkMsgRefs', 'Shared', 'Transaction' </frame>
<frame procname="BizTalkMsgBoxDb.dbo.bts_InsertMessage" line="82" stmtstart="7044" stmtend="7126" sqlhandle="0x03000500e823327adc580b016da5000001000000000000000000000000000000000000000000000000000000">
exec dbo.int_TrackMessage @uidMessageID </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 5 Object Id = 2050106344] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594046644224" dbid="5" objectname="BizTalkMsgBoxDb.dbo.MessageParts" indexname="CIX_MessageParts" id="lock5d2ec5880" mode="X" associatedObjectId="72057594046644224">
<owner-list>
<owner id="process2de3088" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process2792108" mode="S" requestType="wait" />
</waiter-list>
</keylock>
<applicationlock hash="TrkMsgRefs8086087a" databasePrincipalId="0" dbid="5" id="lock5e1773900" mode="X">
<owner-list>
<owner id="process2792108" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process2413088" mode="S" requestType="wait" />
</waiter-list>
</applicationlock>
<applicationlock hash="TrkMsgRefs8086087a" databasePrincipalId="0" dbid="5" id="lock5e1773900" mode="X">
<owner-list>
<owner id="process2413088" mode="S" requestType="wait" />
</owner-list>
<waiter-list>
<waiter id="process2de3088" mode="S" requestType="wait" />
</waiter-list>
</applicationlock>
</resource-list>
</deadlock>

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

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

Jon.Morisi SSChampion Points: 12846 More actions · Answer 1

Is this your issue?

You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server

VastSQL SSC-Dedicated Points: 36153 More actions · Answer 2

Jon.Morisi (3/10/2016)
Is this your issue?
You experience blocking, deadlock conditions, or other SQL Server issues when you try to connect to the BizTalkMsgBoxDb database in BizTalk Server

Thanks Jon,

Dont think this is the issue, will check this as well. The job in deadlock is one track changes back to BizTalkMsgBox and the other lock is from Application.