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?
Best Answer
below article has more details
https://docs.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.