Deadlock issue in jpa token store transaction

Imported from Google Groups
#1

Hi Team,

We have two tracking event processors configured which sends messages to two different messaging queues. But, we encounters the following exception often from TEP’s
Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Causes:

  1. org.hibernate.exception.LockAcquisitionException: could not execute query. Preparing for retry in 1s.

  2. org.hibernate.exception.LockAcquisitionException: could not load an entity.
    System Details:

Axon Framework Version: 4.0.3
DB: SQL Server 2012
Token store: Having clustered index with processor_name and segment (which i think does row level locking)

Can anyone help me in configuring the token store table to avoid this deadlock scenario?

#2

Hi Prasanth,

SQL Server does indeed use/support row-level locking. However, it’s actually likely that it’s doing full table scans and locking the entire table. This is related to the combination of Java (more specifically, the JDBC Driver for SQL Server) and SQL Server, depending on the actual column types that you have defined.

For details, see: https://techcommunity.microsoft.com/t5/DataCAT/Character-data-type-conversion-when-using-SQL-Server-JDBC/ba-p/305048
Or: https://stackoverflow.com/questions/38654672/sql-server-nvarchar-and-java-prepared-statement

Getting the data type incorrectly transferred doesn’t change the outcome of the SQL, but it does make the indices unusable, causing table scans and (as we have seen with other customers) deadlocks.

Hope this helps.
Cheers,

Allard Buijze
CTO

E: allard.buijze@axoniq.io
T: +31 6 34 73 99 89

#3

Thank you so much Allard. I just tested with the changes you specified, now deadlock doesn’t occurs. Anyhow, I will check with bulk event ingestion.