sql-server - 插入 catch block 导致错误 : The current transaction cannot be committed and cannot support operations that write to the log file

Question

我有两个过程，一个外部过程和一个内部过程，我想了解错误处理的行为。内部过程引发错误并试图将 catch block 中的内容插入到表中。之后错误被引发，传递给外部过程，然后应该回滚事务。

我想了解为什么我的代码会抛出错误消息:

Msg 50000, Level 11, State 1, Procedure dbo.OuterProcedure, Line 21 [Batch Start Line 9]
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

我希望收到以下消息:

Msg 50000, Level 11, State 1, Procedure dbo.OuterProcedure, Line 21 [Batch Start Line 9]
Error converting data type varchar to numeric.

我知道问题来自内部过程中的 catch block ，它的发生是因为我试图在引发错误之前向我的日志表中插入一些内容。当我切换这些语句或删除插入时，我得到了实际的错误消息。我也知道在内部过程和无论如何回滚的事务中进行日志记录并不明智。

即使 XACT_ABORT 已设置为关闭，我也想了解是什么使此交易成为“注定的”交易。

完整代码:

我的主要程序:

CREATE PROCEDURE [dbo].[OuterProcedure]
AS
BEGIN
    SET XACT_ABORT OFF;

    BEGIN TRY
        BEGIN TRANSACTION ;

        -- do other stuff 

        EXEC [dbo].[innerprocedure];

        -- do other stuff 

        COMMIT TRANSACTION ;
    END TRY

    BEGIN CATCH
        ROLLBACK TRANSACTION;

        DECLARE @ErrText NVARCHAR(2000);
        SET @ErrText = ISNULL(ERROR_MESSAGE(), 'nothing')
        RAISERROR(@ErrText, 11, 1) WITH NOWAIT
    END CATCH;

END;

我的内部程序:

CREATE PROCEDURE [dbo].[InnerProcedure]
AS
BEGIN
    SET XACT_ABORT OFF;
    SET NOCOUNT ON;
    
    BEGIN TRY
    -- do other stuff 

    -- provoke error
        SELECT 
              CASE
                  WHEN 1 = 0
                  THEN 0.0
                  ELSE ''
              END;

    -- do other stuff 
    END TRY
    BEGIN CATCH

        DECLARE @ErrText NVARCHAR(2000);

        SELECT 
              @ErrText = ISNULL(ERROR_MESSAGE(), 'nothing');

        INSERT INTO [dbo].[logtable]
        ( 
              [Message]
            , [ErrNr]
        ) 
        VALUES
        ( @ErrText
        , -1
        );

        RAISERROR(@LogText, 11, 0) WITH NOWAIT;
    END CATCH;
END;

Answer 1

I would like to understand what is making this transaction a "doomed"transaction even though the XACT_ABORT is set to off.

XACT_STATE() 在 catch block 中为 -1，因此交易失败。

  SELECT 
      CASE
          WHEN 1 = 0
          THEN 0.0
          ELSE ''
      END;

抛出错误

Error converting data type varchar to numeric.

“大多数转换错误”是 Erland Sommarskog puts 的错误类型之一在错误类别中。

Batch Abortion with Rollback This is the strongest reaction SQL Servercan take to a user error. These are errors that abort execution on thespot if there is no CATCH handler on the stack and they also roll backany open transaction. If there is a CATCH handler, the error iscaught, but any open transaction is doomed and must be rolled back.The behaviour is the same, no matter whether XACT_ABORT is ON or OFF.

错误行为的分类有些含糊、未记录且不直观。阅读他的文章了解更多详情。