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

Question

我正在尝试编写一个包含事务和 try/catch block 的 MS sql 脚本。如果捕获异常，事务将回滚。如果没有，则提交事务。我见过一些不同的网站说要这样做:

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

commit transaction

但是即使在捕获异常的情况下，我们是否仍然会点击“提交事务”行？这会不会导致 SQL 错误，因为事务已经回滚了？我认为应该这样做:

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错误吗？我说第一个代码示例的“提交事务”行在捕获异常的情况下仍然会被命中，这是错误的吗？

Answer 1

我一直以为this was one of the better articles就此主题而言。它包括以下示例，我认为该示例很清楚，并且包括经常被忽视的@@trancount，这是可靠的嵌套事务所需的

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