Deadlock on Distributed Transaction

Question

Hi experts,

I have faced some strange deadlock issue and would love to shed some light to it.
Deadlock occurs on an update that uses clustered key to retrieve a row (clustered index seek).

Deadlock image follows:

The deadlock XML follows with sensitive data changed:

<deadlock>
<victim-list>
<victimProcess id="process21acd69cca8" />
</victim-list>
<process-list>
<process id="process21acd69cca8" taskpriority="0" logused="4332" waitresource="KEY: 24:72057594043629568 (3b8389413bf8)" waittime="3904" ownerId="4835185687" transactionguid="0x321a8a986cf10a4da222b8af84dd342f" transactionname="DTCXact" lasttranstarted="2021-06-07T19:09:17.920" XDES="0x273fc3fc040" lockMode="U" schedulerid="5" kpid="7852" status="suspended" spid="169" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-06-07T19:09:17.927" lastbatchcompleted="2021-06-07T19:09:17.927" lastattention="1900-01-01T00:00:00.927" clientapp="Microsoft JDBC Driver for SQL Server" hostname="server-1" hostpid="0" loginname="someLogin" isolationlevel="read committed (2)" xactid="4835185687" currentdb="24" currentdbname="someDB" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="78" stmtend="228" sqlhandle="0x02000000197f2b178dde7197040d5a74471aeaad931f7d320000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P0 nvarchar(4000),@P1 nvarchar(4000))UPDATE Table WITH(ROWLOCK) SET Status = @P0 WHERE MessageID = @P1 </inputbuf>
</process>
<process id="process227402a8108" taskpriority="0" logused="4332" waitresource="KEY: 24:72057594043629568 (42c7e3f6470c)" waittime="3901" ownerId="4835185677" transactionguid="0xe435330a0ca0c644afcba569011217b2" transactionname="DTCXact" lasttranstarted="2021-06-07T19:09:17.913" XDES="0x22c27d5c040" lockMode="U" schedulerid="1" kpid="12084" status="suspended" spid="192" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-06-07T19:09:17.930" lastbatchcompleted="2021-06-07T19:09:17.923" lastattention="1900-01-01T00:00:00.923" clientapp="Microsoft JDBC Driver for SQL Server" hostname="Server-2" hostpid="0" loginname="someLogin" isolationlevel="read committed (2)" xactid="4835185677" currentdb="24" currentdbname="someDB" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="78" stmtend="228" sqlhandle="0x02000000197f2b178dde7197040d5a74471aeaad931f7d320000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P0 nvarchar(4000),@P1 nvarchar(4000))UPDATE Table WITH(ROWLOCK) SET Status = @P0 WHERE MessageID = @P1 </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594043629568" dbid="24" objectname="Table" indexname="PK__Tabl__C87C037C4BB46DC6" id="lock223c3c66b00" mode="X" associatedObjectId="72057594043629568">
<owner-list>
<owner id="process227402a8108" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process21acd69cca8" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594043629568" dbid="24" objectname="Table" indexname="PK__Tabl__C87C037C4BB46DC6" id="lock22c14d83e80" mode="X" associatedObjectId="72057594043629568">
<owner-list>
<owner id="process21acd69cca8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process227402a8108" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>

What I find strange is that plan is optimal and the same IDs should not be received from different servers thus deadlocking each other and I am affraid this could be an issue from distributed transaction.
We use two local DTCs (not clustered) located on an failover cluster hosting the SQL instance.
Transactions involve activeMQ queues.

Can somebody help further investigating into this?

thank you in advance,
Marios

Answer 1

Both processes are running transactions that spans batches. It seems that both processes are trying to update the same rows, but update them in different order. You say "same IDs should not be received from different servers". Well, I don't anything about from where you get the IDs, but the deadlock tells the story loud and clear.

I would suspect that the root problem is that the client is running a loop to update one row at the time. That's a bad pattern. You should send all rows in a table-valued parameter (which I think you can do in JDBC), or as XML/JSON.

Answer 2

Hi @Marios Pavlidis ,

Welcome to Microsoft Q&A!

After donging some research, I think this deadlock may be cause by two update statements try to update one row at the same time. Please refer to How to resolve deadlocks in SQL Server to get more information. And you could refer to this link to get more information about Minimizing Deadlocks.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.