sql-server - transaction - transacciones en sql server pdf

GUARDAR TRANSACCIÓN vs COMIENZO DE LA TRANSACCIÓN(Servidor SQL) cómo anidar transacciones muy bien

Question

Tengo un procedimiento almacenado que necesita establecer un punto de guardado para poder, en ciertas circunstancias, deshacer todo lo que hizo y devolver un código de error a la persona que llama, o aceptarlo / confirmarlo y devolverle el éxito a la persona que llama. Pero necesito que funcione si la persona que llama ya inició una transacción o no. El documento es extremadamente confuso sobre este tema. Esto es lo que creo que funcionará, pero no estoy seguro de todas las ramificaciones.

El problema es que este Stored Procedure (SP) es llamado por otros. Así que no sé si iniciaron una transacción o no ... Incluso si solicito a los usuarios que inicien una transacción para usar mi SP, aún tengo dudas sobre el uso adecuado de los Save Points ...

Mi SP probará si una transacción está en progreso, y si no, comience una con BEGIN TRANSACTION . Si una transacción ya está en curso, en su lugar creará un punto de guardado con SAVE TRANSACTION MySavePointName y guardará el hecho de que esto es lo que hice.

Entonces, si tengo que deshacer mis cambios, si hice una BEGIN TRANSACTION antes, entonces ROLLBACK TRANSACTION . Si hice el punto de guardado, entonces ROLLBACK TRANSACTION MySavePointName . Este escenario parece funcionar bien.

Aquí es donde me confundo un poco: si quiero mantener el trabajo que he hecho, si comencé una transacción, ejecutaré COMMIT TRANSACTION . Pero si creé un punto de guardado? Intenté COMMIT TRANSACTION MySavePointName , pero luego la persona que llama intenta confirmar su transacción y obtiene un error:

La solicitud COMPRAR TRANSACCIÓN no tiene una TRANSACCIÓN BEGIN correspondiente.

Entonces me pregunto: un punto de guardado puede revertirse (eso funciona: ROLLBACK TRANSACTION MySavePointName NO revertirá la transacción de la persona que llama). Pero tal vez uno nunca necesita "comprometerlo"? Simplemente permanece allí, en caso de que necesite retroceder, pero desaparece una vez que la transacción original se ha comprometido (o revertido).

Si hay una "mejor" manera de "anidar" una transacción, por favor, arroje algo de luz también. No he descubierto cómo anidar con BEGIN TRANSACTION pero solo retrotrajo o confirmo mi transacción interna. Parece que ROLLBACK siempre retrocederá a la transacción superior, mientras que COMMIT simplemente disminuye @@trancount .

Answer 1

Creo que ya lo he descifrado todo, así que responderé a mi propia pregunta ...

Incluso he publicado mis hallazgos en el blog si desea obtener más información en http://geekswithblogs.net/bbiales/archive/2012/03/15/how-to-nest-transactions-nicely---quotbegin-transactionquot-vs-quotsave .aspx

Así que mi SP comienza con algo como esto, para comenzar una nueva transacción si no hay ninguna, pero use un Punto de guardado si ya hay uno en progreso:

DECLARE @startingTranCount int
SET @startingTranCount = @@TRANCOUNT
IF @startingTranCount > 0
SAVE TRANSACTION mySavePointName
ELSE
BEGIN TRANSACTION
-- …

Luego, cuando esté listo para comprometer los cambios, solo debe confirmar si iniciamos la transacción nosotros mismos:

IF @startingTranCount = 0
COMMIT TRANSACTION

Y, por último, revertir solo sus cambios hasta el momento:

-- Roll back changes...
IF @startingTranCount > 0
ROLLBACK TRANSACTION MySavePointName
ELSE
ROLLBACK TRANSACTION

Answer 2

He utilizado este tipo de administrador de transacciones en mis procedimientos almacenados:

 CREATE PROCEDURE Ardi_Sample_Test
@InputCandidateID INT
AS
DECLARE @TranCounter INT;
SET @TranCounter = @@TRANCOUNT;
IF @TranCounter > 0
SAVE TRANSACTION ProcedureSave;
ELSE
BEGIN TRANSACTION;
BEGIN TRY
/*
<Your Code>
*/
IF @TranCounter = 0
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @TranCounter = 0
ROLLBACK TRANSACTION;
ELSE
IF XACT_STATE() <> -1
ROLLBACK TRANSACTION ProcedureSave;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE();
SELECT @ErrorSeverity = ERROR_SEVERITY();
SELECT @ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
GO 

Answer 3

Extendiendo la respuesta de Brian B.

Esto garantiza que el nombre del punto de guardado sea único y utiliza las nuevas características TRY / CATCH / THROW de SQL Server 2012.

DECLARE @mark CHAR(32) = replace(newid(), ''-'', '''');
DECLARE @trans INT = @@TRANCOUNT;
IF @trans = 0
BEGIN TRANSACTION @mark;
ELSE
SAVE TRANSACTION @mark;
BEGIN TRY
-- do work here
IF @trans = 0
COMMIT TRANSACTION @mark;
END TRY
BEGIN CATCH
IF xact_state() = 1 OR (@trans = 0 AND xact_state() <> 0) ROLLBACK TRANSACTION @mark;
THROW;
END CATCH