question

MariosPavlidis-4625 avatar image
0 Votes"
MariosPavlidis-4625 asked CarrinWu-MSFT commented

Deadlock on Distributed Transaction

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:

103403-deadlock.png

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_TablC87C037C4BB46DC6" 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


sql-server-general
deadlock.png (43.5 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MariosPavlidis-4625, we have not get a reply from you. Did any answers could help you? If there have any answers helped you, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @MariosPavlidis-4625,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.