sql try/catch rollback/commit - 防止回滚后错误提交

2022-01-06 00:00:00 sql exception transactions sql-server try-catch

我正在尝试编写一个包含事务和 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;

    THROW; -- raise error to the client
END CATCH
PRINT 'After END CATCH'
SELECT * FROM dbo.Account WITH(NOLOCK)
GO

相关文章