SQL try/catch回滚/提交-防止回滚后错误提交

Question

我试图写一个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

我一直认为这是关于这个主题的最好的文章之一。它包括下面的例子，我认为它很清楚，包括经常被忽视的@@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

Answer 2

在您的第一个示例中，您是正确的。不管try块是否触发，批处理都会命中commit事务。

在你的第二个例子中，我同意其他评论者的观点。没有必要使用成功标志。

我认为下面的方法本质上是一种轻量级的最佳实践方法。

如果你想看看它是如何处理异常的，把第二次插入的值从255改为256。

CREATE TABLE #TEMP ( ID TINYINT NOT NULL );
INSERT  INTO #TEMP( ID ) VALUES  ( 1 )
BEGIN TRY
    BEGIN TRANSACTION
    INSERT  INTO #TEMP( ID ) VALUES  ( 2 )
    INSERT  INTO #TEMP( ID ) VALUES  ( 255 )
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    DECLARE 
        @ErrorMessage NVARCHAR(4000),
        @ErrorSeverity INT,
        @ErrorState INT;
    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
    RAISERROR (
        @ErrorMessage,
        @ErrorSeverity,
        @ErrorState    
        );
    ROLLBACK TRANSACTION
END CATCH
SET NOCOUNT ON
SELECT ID
FROM #TEMP
DROP TABLE #TEMP

Answer 3

我使用以下ms sql脚本模式几次成功使用Try-Catch，提交事务-回滚事务，错误跟踪。

您的TRY块将如下所示

 BEGIN TRY
 BEGIN TRANSACTION T
 ----
 //your script block
 ----
 COMMIT TRANSACTION T 
 END TRY

CATCH代码块将如下所示

BEGIN CATCH
DECLARE @ErrMsg NVarChar(4000), 
        @ErrNum Int, 
        @ErrSeverity Int, 
        @ErrState Int, 
        @ErrLine Int, 
        @ErrProc NVarChar(200)
 SELECT @ErrNum = Error_Number(), 
       @ErrSeverity = Error_Severity(), 
       @ErrState = Error_State(), 
       @ErrLine = Error_Line(), 
       @ErrProc = IsNull(Error_Procedure(), '-')
 SET @ErrMsg = N'ErrLine: ' + rtrim(@ErrLine) + ', proc: ' + RTRIM(@ErrProc) + ', 
       Message: '+ Error_Message()

你的ROLLBACK脚本将是CATCH块的一部分，如下所示

IF (@@TRANCOUNT) > 0 
BEGIN
PRINT 'ROLLBACK: ' + SUBSTRING(@ErrMsg,1,4000)
ROLLBACK TRANSACTION T
END
ELSE
BEGIN
PRINT SUBSTRING(@ErrMsg,1,4000);   
END
END CATCH

以上不同的脚本块，你需要使用作为一个块。如果在TRY块中发生任何错误，它将转到CATCH块中。在那里，它设置了关于错误号、错误严重程度、错误线等的各种细节。最后，所有这些细节将被附加到@ErrMsg参数。然后，它将检查事务的计数(@@TRANCOUNT>0)，即如果在事务中有任何回滚。如果存在，则显示错误消息和ROLLBACK TRANSACTION。否则，只需打印错误消息。

我们将COMMIT TRANSACTION T脚本保存在TRY块的最后一行，以确保只有在TRY块中的所有代码成功运行后才提交事务(数据库中的最终更改)。

Answer 4

交易计数器

--@@TRANCOUNT = 0
begin try
--@@TRANCOUNT = 0
BEGIN TRANSACTION tran1
 --@@TRANCOUNT = 1
        --your code
        -- if failed  @@TRANCOUNT = 1
        -- if success @@TRANCOUNT = 0
COMMIT TRANSACTION tran1
end try
begin catch
    print 'FAILED'
end catch

Answer 5

下面可能有用。

来源:https://msdn.microsoft.com/en-us/library/ms175976.aspx

BEGIN TRANSACTION;
BEGIN TRY
    -- your code --
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO