问题：

在 SQL Server 中断 tSQLt 回滚中同时使用XACT_ABORT和尝试捕获

勾俊

2023-03-14

我开始在我的生产代码中使用SQL Server的tSQLt单元测试。目前，我使用Erland Sommarskog的SQL Server错误处理模式。

USE TempDB;

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID('dbo.SommarskogRollback') IS NOT NULL
  DROP PROCEDURE dbo.SommarskogRollback;
GO

CREATE PROCEDURE dbo.SommarskogRollback
AS
BEGIN; /*Stored Procedure*/

  SET XACT_ABORT, NOCOUNT ON;

  BEGIN TRY;

    BEGIN TRANSACTION;

      RAISERROR('This is just a test.  Had this been an actual error, we would have given you some cryptic gobbledygook.', 16, 1);

    COMMIT TRANSACTION;

  END TRY
  BEGIN CATCH;

    IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION;

    THROW;

  END CATCH;

END;   /*Stored Procedure*/
GO

Erland Sommarskog建议我们始终设置XACT_ABORTON，因为只有这样SQL服务器才能以（大部分）一致的方式处理错误。

但是，这在使用tSQLt时产生了一个问题。tSQLt执行显式事务内部的所有测试。当测试完成时，整个事务回滚。这使得测试工件的清理完全没有痛苦。但是，当XACT _中止打开时，TRY块中抛出的任何错误都会立即导致事务失败。事务必须完全回滚。它不能提交，也不能回滚到保存点。事实上，在事务回滚之前，任何东西都不能写入该会话中的事务日志。然而，tSQLt不能正确地跟踪测试结果，除非测试结束时事务是打开的。tSQLt停止执行，并为注定失败的事务抛出回滚错误。失败的测试显示错误状态(而不是成功或失败)，并且后续测试不会运行。

tSQL的创建者Sebastian Meine推荐了一种不同的错误处理模式。

USE TempDB;

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID('dbo.MeineRollback') IS NOT NULL
  DROP PROCEDURE dbo.MeineRollback;
GO

CREATE PROCEDURE dbo.MeineRollback
AS
BEGIN /*Stored Procedure*/

  SET NOCOUNT ON;

  /* We declare the error variables here, populate them inside the CATCH 
   * block and then do our error handling after exiting the CATCH block
   */
  DECLARE @ErrorNumber      INT
         ,@MessageTemplate  NVARCHAR(4000)
         ,@ErrorMessage     NVARCHAR(4000)
         ,@ErrorProcedure   NVARCHAR(126)
         ,@ErrorLine        INT
         ,@ErrorSeverity    INT
         ,@ErrorState       INT
         ,@RaisErrorState   INT
         ,@ErrorLineFeed    NCHAR(1) = CHAR(10)
         ,@ErrorStatus      INT = 0
         ,@SavepointName    VARCHAR(32) = REPLACE( (CAST(NEWID() AS VARCHAR(36))), '-', '');
         /*Savepoint names are 32 characters and must be unique.  UNIQUEIDs are 36, four of which are dashes.*/

  BEGIN TRANSACTION; /*If a transaction is already in progress, this just increments the transaction count*/

  SAVE TRANSACTION @SavepointName;

  BEGIN TRY;

    RAISERROR('This is a test.  Had this been an actual error, Sebastian would have given you a meaningful error message.', 16, 1);

  END TRY
  BEGIN CATCH;

    /* Build a message string with placeholders for the original error information
     * Note:  "%d" & "%s" are placeholders (substitution parameters) which capture
     *        the values from the argument list of the original error message.
     */
    SET @MessageTemplate = N': Error %d, Severity %d, State %d, ' + @ErrorLineFeed
                         + N'Procedure %s, Line %d, '             + @ErrorLineFeed
                         + N', Message: %s';

    SELECT @ErrorStatus    = 1
          ,@ErrorMessage   = ERROR_MESSAGE()
          ,@ErrorNumber    = ERROR_NUMBER()
          ,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
          ,@ErrorLine      = ERROR_LINE()
          ,@ErrorSeverity  = ERROR_SEVERITY()
          ,@ErrorState     = ERROR_STATE()
          ,@RaisErrorState = CASE ERROR_STATE()
                               WHEN 0 /*RAISERROR Can't generate errors with State = 0*/
                                 THEN 1 
                               ELSE ERROR_STATE()
                             END;

  END CATCH;

  /*Rollback to savepoint if error occurred.  This does not affect the transaction count.*/
  IF @ErrorStatus <> 0
    ROLLBACK TRANSACTION @SavepointName;

  /*If this procedure executed inside a transaction, then the commit just subtracts one from the transaction count.*/
  COMMIT TRANSACTION;

  IF @ErrorStatus = 0
    RETURN 0;

  ELSE 
    BEGIN; /*Re-throw error*/

      /*Rethrow the error.  The msg_str parameter will contain the original error information*/
      RAISERROR( @MessageTemplate  /*msg_str parameter as message format template*/
                ,@ErrorSeverity    /*severity parameter*/
                ,@RaisErrorState   /*state parameter*/
                ,@ErrorNumber      /*argument: original error number*/
                ,@ErrorSeverity    /*argument: original error severity*/
                ,@ErrorState       /*argument: original error state*/
                ,@ErrorProcedure   /*argument: original error procedure name*/
                ,@ErrorLine        /*argument: original error line number*/
                ,@ErrorMessage     /*argument: original error message*/
                );

      RETURN -1;

    END;   /*Re-throw error*/

END  /*Stored Procedure*/
GO

他声明错误变量，开始事务，设置保存点，然后在 TRY 块内执行过程代码。如果 TRY 块引发错误，则执行将传递到 CATCH 块，后者填充错误变量。然后执行从 TRY 捕获块中传递出来。出错时，事务将回滚到过程开始时设置的存储点。然后事务提交。由于 SQL Server 处理嵌套事务的方式，当在另一个事务中执行时，此 COMMIT 只是从事务计数器中减去一个。（嵌套事务在 SQL 服务器中实际上并不存在。

塞巴斯蒂安创造了一个非常整洁的图案。执行链中的每个过程都会清理自己的事务。不幸的是，这种模式有一个大问题：注定的交易。注定失败的事务会打破这种模式，因为它们无法回滚到保存点或提交。它们只能完全回滚。当然，这意味着在使用TRY-CATCH块时不能将XACT_ABORT设置为ON（并且应该始终使用TRY-CATCH块）。即使关闭了XACT_ ABORT，许多错误（如编译错误）仍会导致事务失败。此外，保存点不适用于分布式事务。

我如何解决这个问题？我需要一个错误处理模式，它可以在tSQL测试框架内工作，并在生产中提供一致、正确的错误处理。我可以在运行时检查环境并相应地调整行为。（请参阅下面的示例。）然而，我不喜欢这样。对我来说，这感觉就像是一种黑客攻击。它要求开发环境配置一致。更糟糕的是，我不测试我的实际生产代码。有人有出色的解决方案吗？

USE TempDB;

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID('dbo.ModifiedRollback') IS NOT NULL
  DROP PROCEDURE dbo.ModifiedRollback;
GO

CREATE PROCEDURE dbo.ModifiedRollback
AS
BEGIN; /*Stored Procedure*/

  SET NOCOUNT ON;

  IF RIGHT(@@SERVERNAME, 9) = '\LOCALDEV'
    SET XACT_ABORT OFF;

  ELSE
    SET XACT_ABORT ON;

  BEGIN TRY;

    BEGIN TRANSACTION;

      RAISERROR('This is just a test.  Had this been an actual error, we would have given you some cryptic gobbledygook.', 16, 1);

    COMMIT TRANSACTION;

  END TRY
  BEGIN CATCH;

    IF @@TRANCOUNT > 0  AND  RIGHT(@@SERVERNAME,9) <> '\LOCALDEV'
      ROLLBACK TRANSACTION;

    THROW;

  END CATCH;

END;   /*Stored Procedure*/
GO

编辑：经过进一步的测试，我发现我修改后的回滚也不起作用。当过程引发错误时，它将退出而不回滚或提交。tSQLt 会引发错误，因为过程退出时@@TRANCOUNT与过程启动时的计数不匹配。经过一些尝试和错误，我找到了一个在我的测试中有效的解决方法。它结合了两种错误处理方法 - 使错误处理更加复杂，并且某些代码路径无法测试。我很想找到一个更好的解决方案。

USE TempDB;

SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO

IF OBJECT_ID('dbo.TestedRollback') IS NOT NULL
  DROP PROCEDURE dbo.TestedRollback;
GO

CREATE PROCEDURE dbo.TestedRollback
AS
BEGIN /*Stored Procedure*/

  SET NOCOUNT ON;

  /* Due to the way tSQLt uses transactions and the way SQL Server handles errors, we declare our error-handling 
   * variables here, populate them inside the CATCH block and then do our error-handling after exiting 
   */
  DECLARE @ErrorStatus       BIT
         ,@ErrorNumber       INT
         ,@MessageTemplate   NVARCHAR(4000)
         ,@ErrorMessage      NVARCHAR(4000)
         ,@ErrorProcedure    NVARCHAR(126)
         ,@ErrorLine         INT
         ,@ErrorSeverity     INT
         ,@ErrorState        INT
         ,@RaisErrorState    INT
         ,@ErrorLineFeed     NCHAR(1) = CHAR(10)
         ,@FALSE             BIT = CAST(0 AS BIT)
         ,@TRUE              BIT = CAST(1 AS BIT)
         ,@tSQLtEnvironment  BIT
         ,@SavepointName     VARCHAR(32) = REPLACE( (CAST(NEWID() AS VARCHAR(36))), '-', '');
         /*Savepoint names are 32 characters long and must be unique.  UNIQUEIDs are 36, four of which are dashes*/

  /* The tSQLt Unit Testing Framework we use in our local development environments must maintain open transactions during testing.  So,
   * we don't roll back transactions during testing.  Also, doomed transactions can't stay open, so we SET XACT_ABORT OFF while testing.
   */
  IF RIGHT(@@SERVERNAME, 9) = '\LOCALDEV'
    SET @tSQLtEnvironment = @TRUE

  ELSE
    SET @tSQLtEnvironment = @FALSE;


  IF @tSQLtEnvironment = @TRUE
    SET XACT_ABORT OFF;

  ELSE
    SET XACT_ABORT ON;

  BEGIN TRY;

    SET ROWCOUNT 0; /*The ROWCOUNT setting can be updated outside the procedure and changes its behavior.  This sets it to the default.*/

    SET @ErrorStatus = @FALSE;

    BEGIN TRANSACTION;

      /*We need a save point to roll back to in the tSQLt Environment.*/
      IF @tSQLtEnvironment = @TRUE
        SAVE TRANSACTION @SavepointName;

      RAISERROR('Cryptic gobbledygook.', 16, 1);

    COMMIT TRANSACTION;

    RETURN 0;

  END TRY
  BEGIN CATCH;

    SET @ErrorStatus = @TRUE;

    /* Build a message string with placeholders for the original error information
     * Note:  "%d" & "%s" are placeholders (substitution parameters) which capture
     *        the values from the argument list of the original error message.
     */
    SET @MessageTemplate = N': Error %d, Severity %d, State %d, ' + @ErrorLineFeed
                         + N'Procedure %s, Line %d, '             + @ErrorLineFeed
                         + N', Message: %s';

    SELECT @ErrorMessage   = ERROR_MESSAGE()
          ,@ErrorNumber    = ERROR_NUMBER()
          ,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')
          ,@ErrorLine      = ERROR_LINE()
          ,@ErrorSeverity  = ERROR_SEVERITY()
          ,@ErrorState     = ERROR_STATE()
          ,@RaisErrorState = CASE ERROR_STATE()
                               WHEN 0 /*RAISERROR Can't generate errors with State = 0*/
                                 THEN 1 
                               ELSE ERROR_STATE()
                             END;
  END CATCH;

  /* Due to the way the tSQLt test framework uses transactions, we use two different error-handling schemes:
   * one for unit-testing and the other for our main Test/Staging/Production environments.  In those environments
   * we roll back transactions in the CATCH block in the event of an error.  In unit-testing, on the other hand, 
   * we begin a transaction and set a save point.  If an error occurs we roll back to the save point and then 
   * commit the transaction.  Since tSQLt executes all test in a single explicit transaction, starting a 
   * transaction at the beginning of this stored procedure just adds one to @@TRANCOUNT.  Committing the 
   * transaction subtracts one from @@TRANCOUNT.  Rolling back to a save point does not affect @@TRANCOUNT.
   */
  IF @ErrorStatus = @TRUE
    BEGIN; /*Error Handling*/

      IF @tSQLtEnvironment = @TRUE
        BEGIN; /*tSQLt Error Handling*/
          ROLLBACK TRANSACTION @SavepointName; /*Rolls back to save point but does not affect @@TRANCOUNT*/

          COMMIT TRANSACTION; /*Subtracts one from @@TRANCOUNT*/
        END;   /*tSQLt Error Handling*/

      ELSE IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

      /*Rethrow the error.  The msg_str parameter will contain the original error information*/
      RAISERROR( @MessageTemplate  /*msg_str parameter as message format template*/
                ,@ErrorSeverity    /*severity parameter*/
                ,@RaisErrorState   /*state parameter*/
                ,@ErrorNumber      /*argument: original error number*/
                ,@ErrorSeverity    /*argument: original error severity*/
                ,@ErrorState       /*argument: original error state*/
                ,@ErrorProcedure   /*argument: original error procedure name*/
                ,@ErrorLine        /*argument: original error line number*/
                ,@ErrorMessage     /*argument: original error message*/
                );

    END;   /*Error Handling*/

END  /*Stored Procedure*/
GO

贺刚毅

2023-03-14

我正在测试一个补丁来修改框架过程tSQLt。Private_RunTest。基本上，在主CATCH块中，它试图做一个命名的回滚(对我来说是1448行)，我替换了

    ROLLBACK TRAN @TranName;

跟

    IF XACT_STATE() = 1 -- transaction is active
        ROLLBACK TRAN @TranName; -- execute original code
    ELSE IF XACT_STATE() = -1 -- transaction is doomed; cannot be partially rolled back
        ROLLBACK;   -- fully roll back

    IF (@@TRANCOUNT = 0)
        BEGIN TRAN; -- restart transaction to fulfill expectations below

初步测试看起来不错。敬请关注。(在我对这个提议的编辑更有信心之后，我会提交给git。)

在 SQL Server 中断 tSQLt 回滚中同时使用XACT_ABORT和尝试捕获

共有1个答案

相关问答

相关文章

相关阅读

相关工具

相关文档