Background
I've multiple application servers running the same application which points to single database server.
Sometimes, n users do the same function on n application servers, which goes to the single database server and deadlock happened (as here Lock wait timeout exceeded; try restarting transaction) causing m transactions rolled-back (where 1 < m < n).
We solve this problem on a single Application Server machine, by synchronising the call to the service method (using synchronized keyword, So only one thread at a time can access the database.
But this solution doesn't work on multiple app servers (our case here).
The question
Can we implement some mechanism (e.g. db semaphore), so we can make one caller execute a transaction at a time, and for the other callers to wait until the first one finish, then another one to acquire the lock and so on.