Ошибка транзакции SQL: текущая транзакция не может быть зафиксирована и не может поддерживать операции, которые записываются в файл журнала

У меня есть аналогичная проблема с Текущая транзакция не может быть зафиксирована и не может поддерживать операции, которые записываются в файл журнала, но у меня есть следующий вопрос.

Ответ там ссылается Используя TRY... CATCH в 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

Я думаю, что у меня достаточно информации, чтобы просто сыграть с ней и понять ее сам... к сожалению, воспроизведение ошибки оказывается почти невозможным. Поэтому я надеюсь, что здесь просьба поможет прояснить мое понимание проблемы и решения.

Эта хранимая процедура, с перерывами, бросает ошибки, подобные этой:

Ошибка импорта результатов для backtest 9649 error_number: 3930 error_message: текущая транзакция не может быть зафиксирована и не может поддерживать операции, которые записываются в файл журнала. Отмените транзакцию. error_severity: 16 error_state 1 error_line: 217

Таким образом, очевидно, что ошибка исходит от второго блока catch

Основываясь на том, что я читал в Используя TRY... CATCH в Transact-SQL, я думаю, что происходит, когда исключение выбрано, использование XACT_ABORT приводит к тому, что транзакция будет "завершена и откат"... а затем первая строка из BEGIN CATCH слепо пытается вернуться назад.

Я не знаю, почему исходный разработчик включил XACT_ABORT, поэтому я думаю, что лучшим решением (чем его удалением) было бы использовать XACT_STATE() только для откат, если есть транзакция (<>0). Это звучит разумно? Я что-то пропустил?

Кроме того, упоминание о регистрации в сообщении об ошибке заставляет меня задаться вопросом: есть ли еще одна проблема, потенциально с конфигурацией? Используется ли использование RAISEERROR() в этом сценарии для решения этой проблемы? Записывается ли это в какой-то случай, когда регистрация невозможна, поскольку сообщение об ошибке ссылается на?

Ответ 1

Вам всегда нужно проверить XACT_STATE(), не относящуюся к настройке XACT_ABORT. У меня есть пример шаблона для хранимых процедур, которые должны обрабатывать транзакции в контексте 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

Ответ 2

В обсуждении выше есть несколько недоразумений.

Во-первых, вы всегда можете выполнить ROLLBACK транзакцию... независимо от состояния транзакции. Поэтому вам нужно только проверить XACT_STATE перед COMMIT, а не перед откатом.

Что касается ошибки в коде, вы хотите поместить транзакцию внутри TRY. Затем в вашем CATCH первое, что вам нужно сделать, это следующее:

 IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION @transaction

Затем, после вышесказанного, вы можете отправить электронное письмо или что-то еще. (FYI: Если вы отправляете электронное письмо перед откатом, то вы обязательно получите сообщение "can not... write to log file".)

Эта проблема была с прошлого года, поэтому я надеюсь, что вы решили это к настоящему времени:-) Ремус указал вам в правильном направлении.

Как правило... TRY сразу же перейдет к CATCH при возникновении ошибки. Затем, когда вы находитесь в CATCH, вы можете использовать XACT_STATE, чтобы решить, можете ли вы совершить. Но если вы всегда хотите ROLLBACK в catch, тогда вам вообще не нужно проверять состояние.