Another day and another question from my client of Comprehensive Database Performance Health Check. A large financial organization recently hired me to help them with one particular issue. They had one transaction which was often the victim of deadlock. Let us learn today how to Reduce Deadlock for Important Transactions With Minimum Code Change.
Reduce Deadlock
The requirement of the client was, no matter what happens they do not want that transaction to deadlock at all. Another requirement was that I can’t change much of the code across their system. This was totally fine to me as I did not want to also divide deep in the code and fix the deadlock as it can sometimes take a long time to understand the business logic.
The first thing I did was to understand what is doing creating the deadlock. After investigating a bit I figured out that they have a table called invoices where the deadlocks are happening due to a few large concurrently updates.
The best solution is always to re-write the code so there is no deadlock but as I mentioned it was not possible as an immediate solution. Additionally, the client was just fine with the deadlocking other transactions/threads but not one specific transaction. This lead to only one solution and that was to set up the deadlock priority.
SET DEADLOCK_PRIORITY
I opened the stored procedure which was very important for the transactions and typed the following statement at the beginning of the stored procedure.
SET DEADLOCK_PRIORITY HIGH;
The statement above specified the relative importance of the current session if it is deadlocked with another transaction. While the statement above made sure that the important transaction does not deadlock the other transactions got deadlock more often.
Eventually, the trick described in this blog post did reduce deadlock for the important transaction but did not lower the total number of the deadlock. So use this technique in the special cases as described earlier.
Do you use the deadlock priority in your business logic? If yes, please share your experience in the comment.
Reference: Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
Hi,
about deadlock it’s very difficult to modify code when the code belongs to a solution provider or software package under penalty of no longer being supported.
in some cas i can modify data access strategy.
hello, it is advisable to use this instruction every time you have a deadlock and a high priority instruction
Hi Yes i was try to used on this production . But problem is any enviroment multiple sp,’s are important to safe without dead lock . But after using this just reduce the nubmer of deadlocks .but still getting. I hope proper maintence and monitring are always needs this.
Like
” Without heart human does’nt work ,Same like without DBA prod. enviroment does’nt work.”
Thanks
Ajit kumar