sql-server - SQL事务错误: The current transaction cannot be committed and cannot support operations that write to the log file

Question

我遇到了与 The current transaction cannot be committed and cannot support operations that write to the log file 类似的问题，但我有一个后续问题。

答案引用Using TRY...CATCH in Transact-SQL ，我稍后会回来...

我的代码(当然是继承的)具有简化的形式:

SET NOCOUNT ON
SET XACT_ABORT ON

CREATE TABLE #tmp

SET @transaction = 'insert_backtest_results'
BEGIN TRANSACTION @transaction

BEGIN TRY

    --do some bulk insert stuff into #tmp

END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION @transaction
    SET @errorMessage = 'bulk insert error importing results for backtest '
        + CAST(@backtest_id as VARCHAR) +
        '; check backtestfiles$ directory for error files ' + 
        ' error_number: ' + CAST(ERROR_NUMBER() AS VARCHAR) + 
        ' error_message: ' + CAST(ERROR_MESSAGE() AS VARCHAR(200)) +
        ' error_severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR) +
        ' error_state ' +  CAST(ERROR_STATE() AS VARCHAR) + 
        ' error_line: ' + CAST(ERROR_LINE() AS VARCHAR)
    RAISERROR(@errorMessage, 16, 1)
    RETURN -666
END CATCH

BEGIN TRY

    EXEC usp_other_stuff_1 @whatever

    EXEC usp_other_stuff_2 @whatever

    -- a LOT of "normal" logic here... inserts, updates, etc...

END TRY

BEGIN CATCH

    ROLLBACK TRANSACTION @transaction
    SET @errorMessage = 'error importing results for backtest '
        + CAST(@backtest_id as VARCHAR) +
        ' error_number: ' + CAST(ERROR_NUMBER() AS VARCHAR) + 
        ' error_message: ' + CAST(ERROR_MESSAGE() AS VARCHAR(200)) +
        ' error_severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR) +
        ' error_state ' +  CAST(ERROR_STATE() AS VARCHAR) + 
        ' error_line: ' + CAST(ERROR_LINE() AS VARCHAR)
    RAISERROR(@errorMessage, 16, 1)
    RETURN -777

END CATCH

RETURN 0

我想我有足够的信息来玩弄它并自己找出答案...不幸的是，事实证明重现该错误几乎是不可能的。所以我希望在这里提问能够帮助澄清我对问题和解决方案的理解。

此存储过程间歇性地抛出如下错误:

error importing results for backtest 9649 error_number: 3930 error_message: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. error_severity: 16 error_state 1 error_line: 217

很明显错误来自第二个 catch block

基于我在 Using TRY...CATCH in Transact-SQL 中读到的内容，我认为发生的情况是，当抛出异常时，使用 XACT_ABORT 导致事务“终止并回滚”...然后 BEGIN CATCH 的第一行正在盲目地再次尝试回滚。

我不知道为什么原始开发者启用XACT_ABORT ，所以我认为更好的解决方案(比删除它)是使用 XACT_STATE()仅在存在事务时回滚 ( <>0 )。听起来合理吗？我错过了什么吗？

此外，错误消息中提到的日志记录让我想知道:是否还有其他问题，可能与配置有关？我们使用的是RAISEERROR()在这种情况下会导致问题吗？正如错误消息所暗示的那样，在某种不可能进行日志记录的情况下，是否会记录下来？

Answer 1

您始终需要检查 XACT_STATE()，与 XACT_ABORT 设置无关。我有一个存储过程模板示例，需要在 Exception handling and nested transactions 处处理 TRY/CATCH 上下文中的事务。 :

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0   
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(),
               @message = ERROR_MESSAGE(), 
               @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch   
end