transaction - try catch sql server ejemplos

sql try/catch rollback/commit-evitando la confirmación errónea después de la reversión

Question

Estoy tratando de escribir un script MS sql que tenga una transacción y un bloque try / catch. Si detecta una excepción, la transacción se retrotrae. Si no, la transacción está comprometida. He visto algunos sitios web diferentes que dicen hacerlo así:

begin transaction
begin try
    --main content of script here
end try
begin catch
    rollback transaction
end catch

commit transaction

¿Pero aún no vamos a golpear la línea de "comprometer transacción" incluso en el caso de atrapar una excepción? ¿Esto no conducirá a un error SQL porque la transacción ya se ha retrotraído? Creo que debería hacerse así:

declare @success bit = 1

begin transaction
begin try
    --main content of script here
end try
begin catch
    rollback transaction
    set @success = 0
end catch

if(@success = 1)
begin
    commit transaction
end

¿Cómo es que la solución comúnmente publicada no incluye la variable @success? ¿No hay ningún error SQL que ocurra como resultado de la confirmación de una transacción que ya se ha revertido? ¿Soy incorrecto al decir que la línea "comprometer transacción" del primer ejemplo de código seguirá siendo afectada en el caso de detectar una excepción?

Answer 1

A continuación puede ser útil.

Fuente: https://msdn.microsoft.com/en-us/library/ms175976.aspx

BEGIN TRANSACTION;

BEGIN TRY
    -- your code --
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

Answer 2

Contador de transacciones

--@@TRANCOUNT = 0
begin try
--@@TRANCOUNT = 0
BEGIN TRANSACTION tran1
 --@@TRANCOUNT = 1

        --your code
        -- if failed  @@TRANCOUNT = 1
        -- if success @@TRANCOUNT = 0

COMMIT TRANSACTION tran1

end try

begin catch
    print ''FAILED''
end catch

Answer 3

En tu primer ejemplo, estás en lo correcto. El lote llegará a la transacción de confirmación, independientemente de si el bloque de prueba se activa.

En su segundo ejemplo, estoy de acuerdo con otros comentaristas. Usar la bandera de éxito es innecesario.

Considero que el siguiente enfoque es, esencialmente, un enfoque de buenas prácticas livianas.

Si desea ver cómo maneja una excepción, cambie el valor en la segunda inserción de 255 a 256.

CREATE TABLE #TEMP ( ID TINYINT NOT NULL );
INSERT  INTO #TEMP( ID ) VALUES  ( 1 )

BEGIN TRY
    BEGIN TRANSACTION

    INSERT  INTO #TEMP( ID ) VALUES  ( 2 )
    INSERT  INTO #TEMP( ID ) VALUES  ( 255 )

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    DECLARE 
        @ErrorMessage NVARCHAR(4000),
        @ErrorSeverity INT,
        @ErrorState INT;
    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
    RAISERROR (
        @ErrorMessage,
        @ErrorSeverity,
        @ErrorState    
        );
    ROLLBACK TRANSACTION
END CATCH

SET NOCOUNT ON

SELECT ID
FROM #TEMP

DROP TABLE #TEMP

Answer 4

Siempre pensé que este era uno de los mejores artículos sobre el tema. Incluye el siguiente ejemplo que creo que lo deja en claro e incluye la transexual @@ trancount frecuentemente olvidada que es necesaria para transacciones anidadas confiables

PRINT ''BEFORE TRY''
BEGIN TRY
    BEGIN TRAN
     PRINT ''First Statement in the TRY block''
     INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, ''Account1'',  10000)
     UPDATE dbo.Account SET Balance = Balance + CAST(''TEN THOUSAND'' AS MONEY) WHERE AccountId = 1
     INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(2, ''Account2'',  20000)
     PRINT ''Last Statement in the TRY block''
    COMMIT TRAN
END TRY
BEGIN CATCH
    PRINT ''In CATCH Block''
    IF(@@TRANCOUNT > 0)
        ROLLBACK TRAN;

    THROW; -- raise error to the client
END CATCH
PRINT ''After END CATCH''
SELECT * FROM dbo.Account WITH(NOLOCK)
GO

Answer 5

Utilicé varias veces el patrón de scripts ms sql que usa Try-Catch , Commit Transaction, Rollback Transaction , Error Tracking .

Su bloque TRY será el siguiente

 BEGIN TRY
 BEGIN TRANSACTION T
 ----
 //your script block
 ----
 COMMIT TRANSACTION T 
 END TRY

Su bloque CATCH será el siguiente

BEGIN CATCH
DECLARE @ErrMsg NVarChar(4000), 
        @ErrNum Int, 
        @ErrSeverity Int, 
        @ErrState Int, 
        @ErrLine Int, 
        @ErrProc NVarChar(200)
 SELECT @ErrNum = Error_Number(), 
       @ErrSeverity = Error_Severity(), 
       @ErrState = Error_State(), 
       @ErrLine = Error_Line(), 
       @ErrProc = IsNull(Error_Procedure(), ''-'')
 SET @ErrMsg = N''ErrLine: '' + rtrim(@ErrLine) + '', proc: '' + RTRIM(@ErrProc) + '', 
       Message: ''+ Error_Message()

Su secuencia de comandos ROLLBACK será parte del bloque CATCH de la siguiente manera

IF (@@TRANCOUNT) > 0 
BEGIN
PRINT ''ROLLBACK: '' + SUBSTRING(@ErrMsg,1,4000)
ROLLBACK TRANSACTION T
END
ELSE
BEGIN
PRINT SUBSTRING(@ErrMsg,1,4000);   
END

END CATCH

Por encima de los bloques de script diferentes que necesita utilizar como un bloque. Si ocurre algún error en el bloque TRY , irá al bloque CATCH . Allí está estableciendo varios detalles sobre el número de error, la gravedad del error, la línea de error ... etc. Por último, todos estos detalles se agregarán al parámetro @ErrMsg. Luego verificará el recuento de la transacción (@@ TRANCOUNT> 0), es decir, si hay algo en la transacción para deshacer. Si está allí, entonces muestre el mensaje de error y ROLLBACK TRANSACTION . De lo contrario, simplemente imprima el mensaje de error.

Hemos mantenido nuestro script COMMIT TRANSACTION T hacia la última línea del bloque TRY para asegurarnos de que debe confirmar la transacción (cambio final en la base de datos) solo después de que todo el código en el bloque TRY se haya ejecutado correctamente.