具有嵌套事务的 SQL Try/Catch 逻辑
以下存储过程是按照本文中的模板实现的： 异常处理和嵌套事务。该存储过程应该处理死锁，并且它被另一个已经创建事务的存储过程调用。内部事务的 BEGIN/COMMIT 的一些魔法不匹配，因为我得到这个异常： EXECUTE 后的事务计数指示 BEGIN 和 COMMIT 语句的数量不匹配。先前计数 = 1，当前计数 = 0。据我了解，catch被执行，@xstate = -1为true，并且整个外部事务被回滚。
有什么想法会发生不匹配吗？
CREATE PROCEDURE [dbo].[mysproc]
AS
BEGIN
SET NOCOUNT ON;
SET DEADLOCK_PRIORITY LOW;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRY
DECLARE @trancount int;
SET @trancount = @@TRANCOUNT;
IF (@trancount = 0)
BEGIN TRANSACTION;
ELSE
SAVE TRANSACTION InnerTran;
--
-- do some work that can potentially cause a deadlock
--
END TRY
BEGIN CATCH
DECLARE @xstate int
SELECT @xstate = XACT_STATE()
IF (@xstate = - 1)
ROLLBACK;
IF (@xstate = 1 and @trancount = 0)
ROLLBACK;
IF (@xstate = 1 and @trancount > 0)
ROLLBACK TRANSACTION InnerTran;
END CATCH
END
GO
The following sproc is implemented in accord with the template in this article: Exception handling and nested transactions. This sproc is supposed to handle deadlocks and it is called by another sproc that already creates a transaction. Some magic with BEGIN/COMMIT of the inner transaction is not matching, because I get this exception: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0. As far as I understand, catch is executed, @xstate = -1 is true and the whole outer transaction is rolled back.
Any ideas where the mismatch happens?
CREATE PROCEDURE [dbo].[mysproc]
AS
BEGIN
SET NOCOUNT ON;
SET DEADLOCK_PRIORITY LOW;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRY
DECLARE @trancount int;
SET @trancount = @@TRANCOUNT;
IF (@trancount = 0)
BEGIN TRANSACTION;
ELSE
SAVE TRANSACTION InnerTran;
--
-- do some work that can potentially cause a deadlock
--
END TRY
BEGIN CATCH
DECLARE @xstate int
SELECT @xstate = XACT_STATE()
IF (@xstate = - 1)
ROLLBACK;
IF (@xstate = 1 and @trancount = 0)
ROLLBACK;
IF (@xstate = 1 and @trancount > 0)
ROLLBACK TRANSACTION InnerTran;
END CATCH
END
GO
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
不同之处在于您不会引发异常。如果 catch 块中的 XACT_STATE() 为 -1（即不可提交的事务，就像死锁会导致的那样） 在这种情况下，您的过程将回滚（必须的，它在 -1 中没有选择） case）但返回时不引发异常。因此，不匹配。您必须引发异常并在调用者中捕获它。
请参阅不可提交事务和 XACT_STATE：
死锁总是会导致事务不可提交。事实上，在死锁的情况下，当你捕获死锁异常时，事务已经作为死锁受害者回滚了。
The difference is that you do not raise an exception. In case
XACT_STATE()is -1 in the catch block (ie. uncommittable transaction, like a deadlock would cause) In such a case your procedure would rollback (it must, it has no choice in -1 case) but return w/o raising an exception. Hence, the mismatch. You must raise an exception and catch it in the caller.See Uncommittable Transactions and XACT_STATE:
Deadlocks will always result in uncomittable transactions. In fact, in the case of deadlock, by the time you catch the deadlock exception, the transaction has already rolled back as the deadlock victim.