是从存储过程catch块处理死锁重试的好主意 [英] is it a good idea to handle deadlock retry from stored procedure catch block

问题描述

根据我的承诺，完全防止交易被锁死是不可能的。

我想从应用程序代码的角度永远不会失败的事务。
所以我已经看到这种模式在使用Microsoft SQL和我不知道这是一个好主意吗？

 
 
 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' - 等待50 ms 
 CONTINUE 
 END 
 ELSE 
 BEGIN 
 RAISEERROR（'非死锁条件遇到'，16,1）; 
 BREAK; 
 END 
 END CATCH; 
 END 
 

解决方案

可恢复错误的重试逻辑应在客户端代码中。 / p>

对于死锁， MSDN状态在那里做它

如果你在SQL中重试，那么你最终可能命中CommandTimeout。

还有其他错误，所以你可以编写通用处理程序

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

解决方案

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

这篇关于是从存储过程catch块处理死锁重试的好主意的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！