Dead lock in SQL server

Question

Hi Team,

Here i am attaching dead lock log please provide your suggestion, that how to fix this.
Everyday we are getting about 50-55 deadlocks in PROD DB
62132-deadlock-3.txt

Answer 1

Unfortunately, I don't have any quick fixes for this deadlock, because it will requires changes in the application. And while I will some suggestions for the application, they are on the speculative side, since I'm entirely unfamiliar with the app.

Both processes are running a user-defined transaction. Furthermore, these transactions were started before the current batches started executing. There is nothing in wrong in this as such, but this tells us that the conflict may be due to previous statements not seen in the deadlock trace (and it is in this case.) Furthermore, both processes run with the isolation level Serializable, which means that if a process reads a row, that row will remain locked until the transaction completes. This may sometimes be necessary to get transactional consistency, but this mode is certainly known to cause deadlocks.

However, I am not sure that this is the culprit here, and this would actually have been worse with the default level Read Committed, and I will try to explain.

My analysis of the deadlock is that the two processes first have read the same row, and then goes on to update it. This leads to deadlocks because both start with a shared lock on the row what will block the other guy. The deadlock could be resolved if this initial read would use the UPDLOCK hint, that would block the other process from reading the row until the first process has completed.

However, I think there is all reason to take a deeper look at this. Why are two processes running in parallel to update the same row? That itself is suspect. Furthermore, one of the transactions had been running for 35 seconds at the time for the deadlock, and the other for 19. And yet the operation they clash on is a single-point update. This has the smell of something that is executed in a loop and which maybe should be modified to run set-based, that is perform all updates in a single go. If that is difficult to do, maybe the application team should investigate if they can shorten the transaction scope.

Going back to the isolation level here, it is actually beneficiary with Serializable in this case. If they would change the isolation level to the default Read Committed, there would be no deadlocks. But instead there would be lost updates and incorrect results, which is likely to be worse.

As you understand, there is little you can do as a DBA with this case, but you could refer the application team or the vendor to this thread and consider my analysis.