SQL 事务错误:当前事务无法提交，不支持写入日志文件的操作 [英] SQL Transaction Error: The current transaction cannot be committed and cannot support operations that write to the log file

问题描述

我遇到了与 当前事务无法提交，无法支持写入日志文件的操作，但我有一个后续问题.

I'm having a similar issue to The current transaction cannot be committed and cannot support operations that write to the log file, but I have a follow-up question.

那里的答案参考了在 Transact-SQL 中使用 TRY...CATCH，我稍后会回来...

The answer there references Using TRY...CATCH in Transact-SQL, which I'll come back to in a second...

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

My code (inherited, of course) has the simplified form:

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

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

I think I have enough information to just play with it and figure it out myself... unfortunately reproducing the error is proving damn near impossible. So I'm hoping that asking here will help clarify my understanding of the problem and solution.

这个存储过程间歇性地抛出这样的错误:

This stored procedure is, intermittently, throwing errors like this one:

error importing results for backtest 9649 error_number: 3930 error_message: 当前事务无法提交，不支持写入日志文件的操作.回滚事务.error_severity: 16 error_state 1 error_line: 217

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 块

So obviously the error is coming from the 2nd catch block

基于我在中使用 TRY...CATCHTransact-SQL，我认为发生的事情是，当抛出异常时，使用XACT_ABORT 导致事务终止并回滚"......然后是 BEGIN CATCH 盲目地试图再次回滚.

Based on what I've read in Using TRY...CATCH in Transact-SQL, I think what's happening is that when the exception is thrown, the use of XACT_ABORT is causing the transaction to be "terminated and rolled back"... and then the first line of the BEGIN CATCH is blindly attempting to roll back again.

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

I don't know why the original developer enabled XACT_ABORT, so I'm thinking the better solution (than removing it) would be to use XACT_STATE() to only roll back if there is a transaction (<>0). Does that sound reasonable? Am I missing something?

此外，提到登录错误消息让我想知道:还有其他问题吗，可能是配置问题?我们在这个场景中使用 RAISEERROR() 会导致问题吗?在某些无法进行日志记录的情况下，是否会如错误消息所暗示的那样被记录?

Also, the mention of logging in the error message makes me wonder: Is there another problem, potentially with configuration? Is our use of RAISEERROR() in this scenario contributing to the problem? Does that get logged, in some sort of case where logging isn't possible, as the error message alludes to?

推荐答案

您总是需要检查 XACT_STATE()，与 XACT_ABORT 设置无关.我在 异常处理和嵌套事务:

You always need to check for XACT_STATE(), irrelevant of the XACT_ABORT setting. I have an example of a template for stored procedures that need to handle transactions in the TRY/CATCH context at Exception handling and nested transactions:

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

这篇关于SQL 事务错误:当前事务无法提交，不支持写入日志文件的操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！