transaction - Servidor SQL: ¿las transacciones retrotraen el error?

try catch transaction sql server

Question

Tenemos aplicaciones cliente que ejecutan SQL en SQL Server 2005, como las siguientes:

BEGIN TRAN;
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
COMMIT TRAN;

Se envía con un comando de cadena larga.

Si uno de los insertos falla o falla alguna parte del comando, ¿SQL Server retrotrae la transacción? Si no se revierte, ¿tengo que enviar un segundo comando para devolverlo?

Puedo dar detalles sobre la API y el lenguaje que estoy usando, pero creo que SQL Server debería responder lo mismo para cualquier idioma.

Answer 1

Si uno de los insertos falla o si alguna parte del comando falla, ¿el servidor SQL revierte la transacción?

No, no lo hace.

Si no se revierte, ¿tengo que enviar un segundo comando para devolverlo?

Claro, deberías emitir ROLLBACK lugar de COMMIT .

Si desea decidir si comprometer o revertir la transacción, debe eliminar la oración COMMIT de la declaración, verificar los resultados de las inserciones y luego emitir COMMIT o ROLLBACK dependiendo de los resultados de la verificación.

Answer 2

Aquí el código para obtener el mensaje de error trabajando con MSSQL Server 2016:

BEGIN TRY
    BEGIN TRANSACTION 
        -- Do your stuff that might fail here
    COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN

        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
        DECLARE @ErrorState INT = ERROR_STATE()

    -- Use RAISERROR inside the CATCH block to return error  
    -- information about the original error that caused  
    -- execution to jump to the CATCH block.  
    RAISERROR (@ErrorMessage, -- Message text.  
               @ErrorSeverity, -- Severity.  
               @ErrorState -- State.  
               );
END CATCH

Answer 3

Desde el artículo de MDSN, control de transacciones (motor de base de datos) .

Si se produce un error de sentencia en tiempo de ejecución (como una violación de restricción) en un lote, el comportamiento predeterminado en el Motor de base de datos es retrotraer solo la sentencia que generó el error. Puede cambiar este comportamiento utilizando la instrucción SET XACT_ABORT. Después de ejecutar SET XACT_ABORT ON, cualquier error de instrucción en tiempo de ejecución provoca una reversión automática de la transacción actual. Los errores de compilación, como los errores de sintaxis, no se ven afectados por SET XACT_ABORT. Para obtener más información, vea SET XACT_ABORT (Transact-SQL).

En su caso, revertirá la transacción completa cuando falle alguno de los insertos.

Answer 4

Puede poner el set xact_abort on antes de la transacción para asegurarse de que sql se retrotrae automáticamente en caso de error.

Answer 5

Tiene razón en que toda la transacción se revertirá. Debería ejecutar el comando para retrotraerlo.

Puede envolver esto en un bloque TRY CATCH siguiente manera

BEGIN TRY
    BEGIN TRANSACTION

        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);

    COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    -- you can Raise ERROR with RAISEERROR() Statement including the details of the exception
    RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1)
END CATCH