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

Question

From what i undertand it is impossible to completely prevent a transaction from deadlocking.

I would like to have transaction that neverfail from the perpective of application code. So i have seen this pattern in use for Microsoft SQL and I wonder if this is a good idea?


    DECLARE @retry  tinyint
    SET @retry  = 5
    WHILE @retry >0
    BEGIN
      BEGIN TRANSACTION
      BEGIN TRY
        // do transaction her
        COMMIT
        BREAK
      END TRY
      BEGIN CATCH
        ROLLBACK

        if (ERROR_NUMBER() = 1205 OR ERROR_NUMBER() = 1222)
        BEGIN
          SET @retry = @retry - 1
          IF @retry = 0
             RAISEERROR('Could not complete transaction',16,1);
          WAITFOR DELAY '00:00:00.05' -- Wait for 50 ms
          CONTINUE
        END
        ELSE
        BEGIN
          RAISEERROR('Non-deadlock condition encountered',16,1);
          BREAK;
        END
      END CATCH;
    END

you can take that code-snippet as pseudocode i am mainly interested in why you should or should not be doing that — skyde, Sep 26 '11 at 19:00
For certain workloads we can design tasks that cannot cause deadlocks. The simplest deadlock occurs when one process holds a lock on resource A and requests a lock on resource B, and another process holds a lock on resource B and requests a lock on resource A. Since both tasks hold the resource lock they own while waiting for the other resource lock to become available, neither can make progress. The SQLServer engine checks for such cases (or more generally, a daisy-chain of mutually deadlocked tasks) and picks one (generally the one with least processtime consumed) to kill. — hardmath, Sep 26 '11 at 19:33
I can't actually think of a valid reason why you can't do this. I can't even think of a reason why you shouldn't. The thought still makes me shudder for some reason though. — Ben, Sep 26 '11 at 19:34
@skyde: Some of your comments seem to indicate confusion about the difference between blocked and deadlocked transactions. You really should make sure you understand the difference before you make these kinds of decisions. — Allan, Sep 26 '11 at 20:22
if instead of retrying only 5 times i retry it will not be deadlocked anymore but it might still never return. — skyde, Sep 26 '11 at 20:26

score 7 · Answer 1 · edited Nov 16 '14 at 07:59

7

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().

edited Nov 16 '14 at 07:59

Kingpin2k

47,277
10
78
96

answered Sep 26 '11 at 18:58

Oded

489,969
99
883
1,009

if you do IF ERROR_NUMBER() = 1205 and loop forever instead of 5 times, what is the worse that could happen ? – skyde Sep 26 '11 at 19:03
@skyde - It is very _unlikely_ that the same process will be chosen as a deadlock victim twice in a row. – Oded Sep 26 '11 at 19:04

score 7 · Accepted Answer · edited May 23 '17 at 12:09

7

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

edited May 23 '17 at 12:09

Community

1
1

answered Sep 26 '11 at 19:40

gbn

422,506
82
585
676

it seem possible to set commandtimeout to 0 which mean no limit http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx In that case could it be possible for the transaction to block indefinitely? – skyde Sep 26 '11 at 19:55
@skyde: any pattern should roll back though (as yours does above). – gbn Sep 26 '11 at 19:58
Actually microsoft have a Retry T-SQL Code Example for memory optimized table. So I assume its probably not too bad as long as you retry a fixed number of time. https://msdn.microsoft.com/en-us/library/ba6f1a15-8b69-4ca6-9f44-f5e3f2962bc5#retrytsqlcodeexam35ni – skyde Apr 12 '16 at 23:43

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

2 Answers2

Linked