XA transactions - timeout errors

Question

We have recently setup XA Transactions as per the documentation.

However, app team is facing the issues as below

Performance Test 1: XXXX Internal users [get XXXX token API ] – 3 concurrent users with three different accounts for 10 minutes duration. [04:46 - 04:57 AM EST] – No Errors

Performance Test 2: XXXX Federated users [get XXXX token API ] - 3 concurrent users with three different accounts for 5 minutes duration. [05:27 - 05:33 AM EST] – No Errors

Performance Test 3: XXXX Federated users [get XXXX token API ] - 3 concurrent users with 20 different accounts. [05:47 - 05:49:36 AM EST] – Errors were observed

and errors says as below

{"error":"org.hibernate.exception.LockAcquisitionException: could not execute query"}

{"error":": Unable to acquire lock after 10 seconds for key f:6038d4995878:ptuserfafn375759570511 and requestor GlobalTx:local:754. Lock is held by GlobalTx:local:732"}

I checked DPA Monitoring Tool and found blockings and deadlocks between 05:47 - 05:49:36 AM EST. Victim and Survivor are running the same SQL, from same machine except different Txn/Owner ID.

Checked and validated System/SQL Server error logs, with nothing unusual.

Any thoughts please?

Answer 1

below article has more details

https://learn.microsoft.com/en-us/archive/blogs/sqlserverfaq/sql-server-deadlocks-due-to-character-data-type-conversion-when-using-sql-server-jdbc-drivers

If sendStringParametersAsUnicode property is set to "TRUE", String parameters are sent to the server in Unicode format: http://msdn.microsoft.com/en-us/library/ms378988.aspx. That means, if this property is set to False, the data type of the parameters being passed from application will be Varchar, not nVarchar and hence there will be no implicit conversion!

And it worked. No deadlocks.