从存储过程 catch 块处理死锁重试是个好主意吗
据我了解,完全防止事务死锁是不可能的。
我希望交易从应用程序代码的角度来看永远不会失败。 我已经看到这种模式用于 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' -- Wait for 50 ms CONTINUE END ELSE BEGIN RAISEERROR('Non-deadlock condition encountered',16,1); BREAK; END END CATCH; END
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
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
可恢复错误的重试逻辑应位于客户端代码中。
对于死锁,MSDN 规定在那里执行
如果您在 SQL 中重试,那么最终可能会遇到 CommandTimeout。
还有其他错误,因此您可以 编写一个通用处理程序
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
您的实现不是一个好主意,因为它盲目地重试而没有找出实际的错误。例如,如果错误是超时,您可能最终会占用连接 5 倍的超时时间,而无法解决任何问题。
更好的方法是检测它是错误 1205 - 死锁受害者并仅在这种情况下重试。
您可以使用:
请参阅
ERROR_NUMBER()
< 的文档/a>.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:
See the documentation for
ERROR_NUMBER()
.