December 5, 2019 at 9:47 pm
Looking for some ideas so to prevent deadlocks. I have captured queries that constantly becomes deadlock victims.
Any ideas you can throw out to fix? Thanks in Advance!
December 5, 2019 at 10:56 pm
Since it isn't always possible to write your code to prevent deadlocks, you should write your code to trap for the deadlock error and retry the failed transaction. This sounds easy but it takes some thought when writing the code. You need to be sure that the code can retry the same transaction that was selected to be rolled back. You may have to be able to retry it multiple times. I will have to do some searching through code I wrote a while back to provide a possible frame work that you may use.
A quick overview that I have used is WHILE 1 = 1 loop around a TRY/CATCH block of code. I start a transaction in the try block and commit it if the code is successfully executed and use BREAK to exit out of the WHILE loop after COMMIT. In the CATCH block I capture the error information, rollback the transaction, check if the error is a deadlock and if so delay the process for a short time (used 50 ms in the code I wrote) else I used RAISERROR or THROW (depends on the version of SQL Server) to force the entire routine (a stored procedure in the case of the code I had written) to abort.
December 5, 2019 at 11:11 pm
Thanks for the response. So you saying catch deadlock errors and retry a certain number of times? How about return the records we want from a memory cache, no longer reading from the db? What about setting row version based isolation levels on the queries that deadlock? Does low resources available in the system could cause deadlocks?
December 5, 2019 at 11:53 pm
To start with the last part of your post, low resources. I am not sure if low resources would cause deadlocks, but at the same time not saying it couldn't. As to using snapshot isolation to mitigate deadlocks, it may or may not work. Switching to those transaction isolation levels takes some thought as they have their corresponding issues that you need to consider such as tempdb usage. No longer reading from the database, not sure what you mean here. Same with memory cache, would need to know more.
Retrying a set number of times, yes you can do that as well with the method I briefly outlined. What I provided is but one possible method. In my code, the retry is actually an infinite loop. I chose this method based on my knowledge of the application and that eventually my transaction would succeed. I also wrote my code to ensure that it would be the deadlock victim by setting the deadlock priority to -10, the lowest level.
How you deal with deadlocks is driven by your application(s) and environment. What is working for me in the environment I am supporting may not work in others. It is something you have to test to be sure it works as you want/need.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply