sql try / catch rollback / commit - 防止回滚后的错误提交 [英] sql try/catch rollback/commit - preventing erroneous commit after rollback

问题描述

我正在尝试编写一个具有事务和一个try / catch块的MS sql脚本。如果它捕获异常，则事务将回滚。如果没有，则交易被提交。我看到几个不同的网站说这样做：

I am trying to write an MS sql script that has a transaction and a try/catch block. If it catches an exception, the transaction is rolled back. If not, the transaction is committed. I have seen a few different websites saying to do it like this:

begin transaction
begin try
    --main content of script here
end try
begin catch
    rollback transaction
end catch

commit transaction

但是即使遇到异常，我们仍然不会打到提交事务行？这不会导致SQL错误，因为事务已经被回滚了？我认为应该这样做：

But won't we still hit the "commit transaction" line even in the case of catching an exception? Won't this lead to a SQL error because the transaction has already been rolled back? I think it should be done like this:

declare @success bit = 1

begin transaction
begin try
    --main content of script here
end try
begin catch
    rollback transaction
    set @success = 0
end catch

if(@success = 1)
begin
    commit transaction
end

常见的解决方案的结果不包括@success变量？是否有提交已经回滚的事务的结果发生sql错误？我不正确地说，在捕获异常的情况下，第一个代码示例的提交事务行仍然会被击中？

Howcome the commonly-posted solution does not include the @success variable? Is there no sql error that happens as a result of committing a transaction that has already been rolled back? Am I incorrect in saying that the "commit transaction" line of the first code example will still be hit in the case of catching an exception?

推荐答案

我一直以为这是关于这个问题的更好的文章之一。它包括以下示例，我认为明确，并包括经常被忽视的@@ trancount，这是可靠的嵌套交易所需要的。

I always thought this was one of the better articles on the subject. It includes the following example that I think makes it clear and includes the frequently overlooked @@trancount which is needed for reliable nested transactions

PRINT 'BEFORE TRY'
BEGIN TRY
    BEGIN TRAN
     PRINT 'First Statement in the TRY block'
     INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Account1',  10000)
     UPDATE dbo.Account SET Balance = Balance + CAST('TEN THOUSAND' AS MONEY) WHERE AccountId = 1
     INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(2, 'Account2',  20000)
     PRINT 'Last Statement in the TRY block'
    COMMIT TRAN
END TRY
BEGIN CATCH
    PRINT 'In CATCH Block'
    IF(@@TRANCOUNT > 0)
        ROLLBACK TRAN
END CATCH
PRINT 'After END CATCH'
SELECT * FROM dbo.Account WITH(NOLOCK)
GO

这篇关于sql try / catch rollback / commit - 防止回滚后的错误提交的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！