is it a good idea to handle deadlock retry from stored procedure catch block is it a good idea to handle deadlock retry from stored procedure catch block database database

is it a good idea to handle deadlock retry from stored procedure catch block


The implementation you have is not a good idea, as it blindly retries without finding out the actual error. If the error was a timeout, for example, you might end up tying up a connection for 5 times the timeout amount, without ever resolving a thing.

A much better approach is to detect that it was Error 1205 - a deadlock victim and retry only in that case.

You can use:

IF ERROR_NUMBER() = 1205 

See the documentation for ERROR_NUMBER().


Retry logic for recoverable errors should be in the client code.

For deadlocks, MSDN states to do it there

If you retry in SQL, then you may hit CommandTimeout eventually.

There are other errors too so you can write a generic handler