I have a complex business web application that receives multiple concurrent requests. One of those requests is to process a new invoice. If multiple of these requests are received at the same time, at least one of the requests will suffer a rollback because of the following error Transaction (Process ID X) was deadlocked on lock resources.
I have been browsing through lots of StackOverflow entries about this topic, but none of them have given me a solution. I tried turning on SNAPSHOT ISOLATION, but nothing changed. I have try reordering my code, but to no avail. User https://stackoverflow.com/users/1560836/user1560836 commented on Correct way to handle deadlocks in Hibernate that there simply was no solution to this issue, and it seems likely. Still, I find it hard to believe, given how used these frameworks are.
Does anybody have a recommendation or maybe a solution? Also, do you have a solution to at least monitor these occurrences? Any help would be greatly appreciated.
To anyone who finds themselves in my same predicament, the solution that had the best results so far is Optimistic Locking. Following this very helpful Baeldung guide, I managed to solve my issue in a elegant way.
You can find below a summary of what I did to achieve this.
bigint field to the tables that were modified in the commonly deadlocked transactions (database wise)@Version annotation to the JPA entities that are being modifiedThis simple solution solved most of my deadlocked issues.
Additionally, you can use Spring Retry to retry deadlock transactions automatically, without needing to resend the request. Also, you can tell Spring to only retry deadlock related exceptions with the following annotation.
@Retryable(value = {CannotAcquireLockException.class, OptimisticLockException.class, TransactionException.class, ObjectOptimisticLockingFailureException.class})
Hope it helps somebody else.