1

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.

Alain Cruz
  • 4,757
  • 3
  • 25
  • 43
  • 1) With any large database deadlocks are not only possible, but sometimes unavoidable. In which case you either need a retry mechanism *OR* a serialisation mechanism. – Dale K Aug 30 '22 at 20:14
  • And yes there are monitoring tools and solutions out there, your favourite search engine should assist you with that. – Dale K Aug 30 '22 at 20:15
  • Query/index tuning often mitigates deadlocks by touch only data needed for the task at hand. Take a look at the execution plan. – Dan Guzman Aug 30 '22 at 20:36

1 Answers1

2

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.

  1. Added a bigint field to the tables that were modified in the commonly deadlocked transactions (database wise)
  2. Then, added this new field with the @Version annotation to the JPA entities that are being modified

This 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.

Alain Cruz
  • 4,757
  • 3
  • 25
  • 43