sql server - try - Error de transacción SQL: la transacción actual no se puede comprometer y no puede admitir operaciones que escriben en el archivo de registro

transacciones sql server ejemplos

Question

Tengo un problema similar al de la transacción actual. No se puede confirmar y no puedo admitir operaciones que escriben en el archivo de registro , pero tengo una pregunta de seguimiento.

La respuesta allí hace referencia al uso de TRY ... CATCH en Transact-SQL , al cual volveré en un segundo ...

Mi código (heredado, por supuesto) tiene la forma simplificada:

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

Creo que tengo suficiente información para jugar con ella y resolverlo yo mismo ... desafortunadamente reproducir el error está resultando casi imposible. Así que espero que preguntar aquí ayude a aclarar mi comprensión del problema y la solución.

Este procedimiento almacenado es, intermitentemente, arrojando errores como este:

error al importar los resultados para backtest 9649 error_number: 3930 error_message: la transacción actual no se puede comprometer y no puede admitir operaciones que escriben en el archivo de registro. Revertir la transacción. error_severity: 16 error_state 1 error_line: 217

Entonces, obviamente, el error viene del segundo bloque catch

En base a lo que he leído en el uso de TRY ... CATCH en Transact-SQL , creo que lo que está sucediendo es que cuando se lanza la excepción, el uso de XACT_ABORT está causando que la transacción "finalice y se retrotraiga" ... y luego la primera línea de BEGIN CATCH intenta ciegamente retroceder nuevamente.

No sé por qué el desarrollador original habilitó XACT_ABORT , así que estoy pensando que la mejor solución (que quitarla) sería usar XACT_STATE() para retroceder solo si hay una transacción ( <>0 ). ¿Eso suena razonable? ¿Me estoy perdiendo de algo?

Además, la mención de iniciar sesión en el mensaje de error me hace preguntarme: ¿hay otro problema, potencialmente con la configuración? ¿Nuestro uso de RAISEERROR() en este escenario contribuye al problema? ¿Eso se registra, en algún tipo de caso donde el registro no es posible, como se alude al mensaje de error?

Answer 1

Siempre debe verificar XACT_STATE() , XACT_STATE() de la configuración de XACT_ABORT . Tengo un ejemplo de una plantilla para procedimientos almacenados que necesitan manejar transacciones en el contexto TRY / CATCH en el manejo de excepciones y transacciones anidadas :

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

Answer 2

Hay algunos malentendidos en la discusión anterior.

En primer lugar, siempre puede ROLLBACK una transacción ... sin importar el estado de la transacción. Por lo tanto, solo tiene que verificar el XACT_STATE antes de un COMMIT, no antes de un rollback.

En cuanto al error en el código, querrá poner la transacción dentro del TRY. Luego, en tu captura, lo primero que debes hacer es lo siguiente:

 IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION @transaction

Luego, después de la declaración anterior, puede enviar un correo electrónico o lo que sea necesario. (Para su información: si envía el correo electrónico ANTES de la reversión, definitivamente obtendrá el error "No se puede ... escribir en el archivo de registro").

Este problema fue del año pasado, así que espero que ya hayas resuelto esto :-) Remus te indicó la dirección correcta.

Como regla general, el TRY saltará inmediatamente al CATCH cuando haya un error. Luego, cuando esté en el CATCH, puede usar XACT_STATE para decidir si puede comprometerse. Pero si siempre quieres ROLLBACK en la captura, entonces no necesitas verificar el estado en absoluto.