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,