Transaction deadlock victim

BIZ TMS XData
1

Hi,

need help/suggestions!!!

I have a REST API application where I am using the TMS Aurelius, SQLServer and FireDAC combination for the Data layer. Each thread/session has it's own database connection to the sql server and it is working quite good up to the 10 parallel sessions. When the number of parallel sessions are increased for example 20, the below error is getting logged int eh application log file.

"[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Transaction (Process ID 96) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.]"

here the point is, All clients are trying to update/access the same table, as one session is acquired the lock on the table on a transaction level (TObjectManager.BeginUpdate) and inserting/updating the records, other sessions are waiting for the resource to be released. After some time, session is getting failed with the above message.

Is there any way to handle this situation apart from serializing all the requests by using a SemaPhore?

Regards,
Abdul

2

Topic moved to TMS Aurelius category.

3

You should code your application having multithread in mind.

First, configure your transactions levels so that multiple connections can access and update the same tables, otherwise your server won't scale.

Second, make sure your code keeps transactions open the shortest time as possible.

Third, make sure your code hold a database connection the shortest time as possible.

Fourth, use connection pools to reuse existing connections and avoid too many connections being used at the same time.