Las transacciones anidadas
Recordemos como funcionan las transacciones. Tenemos 4 sentencias que son:
• BEGIN TRAN: Comienza una transacción y aumenta en 1 @@TRANCOUNT
• COMMIT TRAN: Reduce en 1 @@TRANCOUNT, y si @@TRANCOUNT llega a 0 guarda la transacción
• ROLLBACK TRAN: Deshace la transacción actual, o si estamos en transacciones anidadas deshace la más externa y todas las internas. Además pone @@TRANCOUNT a 0
• SAVE TRAN: Guarda un punto (con nombre) al que podemos volver con un ROLLBACK TRAN si es que estamos en transacciones anidadas y no queremos deshacer todo hasta la más externa.
Un par de ejemplos dejarán las cosas más claras:
BEGIN TRAN
-- Primer BEGIN TRAN y ahora @@TRANCOUNT = 1
BEGIN TRAN
-- Ahora @@TRANCOUNT = 2
COMMIT TRAN
-- Volvemos a @@TRANCOUNT = 1
-- Pero no se guarda nada ni se hacen efectivos los posibles cambios
COMMIT TRAN
-- Por fin @@TRANCOUNT = 0
-- Si hubiera cambios pendientes se llevan a la base de datos
-- Y volvemos a un estado normal con la transacción acabada
Uso del commit
BEGIN TRAN
-- Primer BEGIN TRAN y @@TRANCOUNT = 1
BEGIN TRAN
-- Ahora @@TRANCOUNT = 2
COMMIT TRAN
-- Como antes @@TRANCOUNT = 1
--Y como antes nada se guarda
ROLLBACK TRAN
-- Se cancela TODA la transacción. Recordemos que el COMMIT
-- de antes no guardo nada, solo redujo @@TRANCOUNT
-- Ahora @@TRANCOUNT = 0
COMMIT TRAN
-- No vale para nada porque @@TRANCOUNT es 0 por el efecto del ROLLBACK
Uso del rollback
En cuanto al SAVE TRAN podemos recordarlo con el siguiente ejemplo:
CREATE TABLE Tabla1 (Columna1 varchar(50))
GO
BEGIN TRAN
INSERT INTO Tabla1 VALUES (‘Primer valor’)
SAVE TRAN Punto1
INSERT INTO Tabla1 VALUES (‘Segundo valor’)
ROLLBACK TRAN Punto1
INSERT INTO Tabla1 VALUES (‘Tercer valor’)
COMMIT TRAN
SELECT * FROM Tabla1
Columna1
--------------------------------------------------
Primer valor
Tercer valor
(2 filas afectadas)
Un ROLLBACK a un SAVE TRAN no deshace la transacción en curso ni modifica @@TRANCOUNT, simplemente cancela lo ocurrido desde el ‘SAVE TRAN nombre‘ hasta su ‘ROLLBACK TRAN nombre’
Transacciones y procedimientos almacenados…
Cuando trabajamos con procedimientos almacenados debemos recordar que cada procedimiento almacenado es una unidad. Cuando se ejecuta lo hace de manera independiente de quien lo llama. Sin embargo si tenemos un ROLLBACK TRAN dentro de un procedimiento almacenado cancelaremos la transacción en curso, pero si hay una transacción externa al procedimiento en el que estamos trabajando se cancelará esa transacción externa.
Con esto no quiero decir que no se pueda usar, simplemente que hay que tener muy claras las 4 normas sobre las sentencias BEGIN, ROLLBACK y COMMIT que comentamos al principio de este artículo.
Veamos como se comporta una transacción en un procedimiento almacenado llamado desde una transacción.
CREATE PROCEDURE Inserta2
AS
BEGIN TRAN --Uno
INSERT INTO Tabla1 VALUES ('Valor2')
ROLLBACK TRAN --Uno
GO
CREATE PROCEDURE Inserta1
AS
BEGIN TRAN --Dos
INSERT INTO Tabla1 VALUES ('Valor 1')
EXEC Inserta2
INSERT INTO Tabla1 VALUES ('Valor3')
COMMIT TRAN --Dos
GO
En principio parece que si ejecutamos el procedimiento ‘Inserta1’ el resultado sería:
EXECUTE inserta1
SELECT * FROM tabla1
txt
--------------------------------------------------
Valor1
Valor3
(2 filas afectadas)
Pero lo que obtenemos es:
EXECUTE inserta1
SELECT * FROM tabla1
(1 filas afectadas)
(1 filas afectadas)
Servidor: mensaje 266, nivel 16, estado 2, procedimiento Inserta2, línea 8
El recuento de transacciones después de EXECUTE indica que falta una
instrucción COMMIT o ROLLBACK TRANSACTION. Recuento anterior = 1,
recuento actual = 0.
(1 filas afectadas)
Servidor: mensaje 3902, nivel 16, estado 1, procedimiento Inserta1, línea 8
La petición COMMIT TRANSACTION no tiene la correspondiente BEGIN TRANSACTION.
txt
--------------------------------------------------
Valor3
(1 filas afectadas)
Si analizamos estos mensajes vemos que se inserta la primera fila, se salta al segundo procedimiento almacenado y se inserta la segunda fila. Se ejecuta el ‘ROLLBACK TRAN –Dos’ del segundo procedimiento almacenado y se deshacen las dos inserciones porque este ROLLBACK cancela la transacción exterior, la del primer procedimiento almacenado.
A continuación se termina el segundo procedimiento almacenado y como este procedimiento tiene un ‘BEGIN TRAN –Dos’ y no tiene un COMMIT o un ROLLBACK (recordemos que el ‘ROLLBACK TRAN –Dos’ termina el ‘BEGIN TRAN –Uno’) se produce un error y nos avisa que el procedimiento almacenado termina con una transacción pendiente.
Al volver al procedimiento almacenado externo se ejecuta el INSERT que inserta la tercera fila y a continuación el ‘COMMIT TRAN –-Uno’. Aquí aparece otro error puesto que este ‘COMMIT TRAN –Uno’ estaba ahí para finalizar una transacción que ya ha sido cancelada anteriormente.
El modo correcto
Para que nuestras transacciones se comporten como se espera dentro de un procedimiento almacenado podemos recurrir al SAVE TRAN.
Veamos como:
CREATE PROCEDURE Inserta2
AS
SAVE TRAN Guardado
INSERT INTO Tabla1 VALUES ('Valor2')
ROLLBACK TRAN Guardado
GO
CREATE PROCEDURE Inserta1
AS
BEGIN TRAN
INSERT INTO Tabla1 VALUES ('Valor 1')
EXEC Inserta2
INSERT INTO Tabla1 VALUES ('Valor 3')
COMMIT TRAN
GO
Ahora el ‘ROLLBACK TRAN guardado’ del segundo procedimiento almacenado deshace la transacción sólo hasta el punto guardado. Además este ROLLBACK no decrementa el @@TRANCOUNT ni afecta a las transacciones en curso.
El resultado obtenido al ejecutar este procedimiento almacenado es:
EXECUTE inserta1
SELECT * FROM tabla1
(1 filas afectadas)
(1 filas afectadas)
(1 filas afectadas)
txt
--------------------------------------------------
Valor 1
Valor 3
(2 filas afectadas)
Una vez vistos estos ejemplos queda claro que no hay problema en utilizar transacciones dentro de procedimientos almacenados siempre que tengamos en cuenta el comportamiento del ROLLBACK TRAN y que utilicemos el SAVE TRAN de manera adecuada.