Tracking Event Processor Optimistic Locking

Imported from Google Groups
#1

Hello Team,

At the moment, We have tracking event processor (Token Entry) with pessimistic locking in our production. We face some issues with this locking. We found JpaTokenStore locking mechanism configurable and planning to opt for optimistic locking for token store, wish to have your insight on pros and cons of doing that in the distributed system?

Our Configs:

  • 3 node cluster

  • Axon version: 4.3.1

  • SQL Server DB

  • Having 4 tracking event processors ( with max of 2 segments, batch size 100 )

#2

Found few comments on JpaTokenStore for LockModeType. Wanted to know which cases it can result in deadlock if we dont use pessimistic locking?

#3

Hi Prashant,

you’re likely hitting a known Java+MSSQL Server issue. MySQL uses a different default representation for Strings than Java does. When using MSSQL’s default character column types, the java values are implicitly converted to the MSSQL types. However, because of this implicit conversion, MSSQL cannot use the indices on the fields with this data. And as a result of that, the queries on the Token_Entry table cause a table scan. Since it’s an update query (or a select for update), this will effectively lock the entire table.

The solution is to configure the right column types in the database or instruct the mssql connector to not convert Strings to Unicode when sending data to MSSQL.
For more details, see: https://techcommunity.microsoft.com/t5/datacat/character-data-type-conversion-when-using-sql-server-jdbc/ba-p/305048

Just out of curiosity, are you using AxonServer as your Event Store or an Embedded Event Store with MSSQL? In case it’s the latter, you’ll need to check with your DBA if all indices are correctly tuned. These Java/MSSQL issues have shown in several places and cause bad performance even with small event streams. With larger event streams, this is inevitable, unfortunately, due to the way BTree indices work. AxonServer obviously doesn’t suffer from this impact, as it uses specialized indices for event storage.

Hope this helps.

Cheers,

#4

Hi Allard,

We are using Embedded Event Store with MSSQL. Also currently, we tuned DB connection property to avoid table lock issue.

jdbc:sqlserver://localhost:1433;databaseName=test;sendStringParametersAsUnicode=false;lockTimeout=10000

Now, the question is,
We want to remove pessimistic_write lock while loading token from token entry table(select for update). Instead , we want to configure optimistic locking. Wish to know the impact in doing so?

#5

Any suggestions?