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.