lunes, 14 de diciembre de 2009

Transacciones anidadas (Nested Transactions)

En ocasiones necesitamos llamar a un procedimiento dentro de una transacción, y que éste a su vez tenga otra transacción. Con lo cual tendríamos una transacción dentro de otra. Esto no es tan "sencillo" como parece...

ANTES DE NADA es inmportante saber lo que es la variable de sqlserver @@TRANCOUNT, esta variable indica el número de transacciones pendientes de finalizar. Es decir, cada vez que hacemos un BEGIN TRAN este contador aumenta en 1. Entonces, ¿qué hace un ROLLBACK TRAN?... Decrementar en 1 @@TRANCOUNT... claro.... ¡PUES NO!; la instrucción ROLLBACK establece @@TRANCOUNT a 0, es decir, deshace TODOS los cambios. Los de "su transacción" y todos los cambios hasta el primer BEGIN TRANS. En cambio COMMIT TRAN SÍ que decrementa @@TRANCOUNT en 1.

- ¿Qué pasa si usamos tal cual una transacción dentro de otra, si ignoramos lo explicado anteriormente de @@TRANCOUNT?
Pues que estaría MAL, obtendríamos un error. Por ejemplo:

-- Crea T1
   BEGIN TRAN
-- Insertar en tabla (1)
   INSERT INTO Familias(famcod, famdes, famdiasr)
   VALUES('T1', 'T1', 0)

    -- Otra Transaccion. T2
       BEGIN TRAN

    -- Insertar en tabla (2)
       INSERT INTO Familias(famcod, famdes, famdiasr)
       VALUES('T2', 'T2', 0)

    -- ROLLBACK de inserción de tabla (2) (supuestamente de T2)
       ROLLBACK TRAN

-- Hacemos commit de T1
   COMMIT TRAN
En el código anterior, si no sabemos que ROLLBACK pone @@TRANCOUNT a 0, podríamos pensar que en la tabla Familias hemos insertado el registro 'T1', 'T1', 0.
Pero lo cierto es que no, hemos obtenido un bonito error de sqlserver: La explicación es que al principio comenzamos una transacción nueva (T1), insertamos un registro, luego empezamos otra transacción (T2), insertamos otro registro, luego hacemos ROLLBACK (aquí imaginábamos que el rollback se haría sólo de T2), al hacer este rollback hemos deshecho las dos inserciones. Por eso, al hacer luego un COMMIT TRAN SqlServer nos dice: ¿estamos locos?...

Aunque se intente dar nombres a las transacciones, no conseguiremos nada, lo anterior no se puede hacer EN NINGÚN CASO.

- "Yo quiero poder hacer un ROLLBACK sin deshacer TODOS los cambios, sólo los de la segunda transacción, ¿Cómo lo hago?"
Es importante que si no has entendido todo lo anterior no continúes leyendo, vuelve a empezar en ese caso.

Aquí es donde entra en juego la instrucción "SAVE TRAN". Esta instrucción lo que hace es crear un punto de guardado (igual que cuando guardamos una partida en un video juego).
Lo que haremos será NO crear la transacción T2, sino que, llegados al punto donde deberíamos crearla, en vez de ello crearemos un punto de guardado llamado T2. De esta forma, al hacer el ROLLBACK TRAN de T2, se desharán los cambios hasta el punto de guardado, no el resto. @@TRANCOUNT no se ve afectada.

Aquí va el ejemplo de arriba, bien hecho:
-- Crea T1
   BEGIN TRAN T1
-- Insertar en tabla (1)
   INSERT INTO Familias(famcod, famdes, famdiasr)
   VALUES('T1', 'T1', 0)

    -- Guardar Transaccion
       SAVE TRAN SavePoint

    -- Insertar en tabla (2)
       INSERT INTO Familias(famcod, famdes, famdiasr)
       VALUES('T2', 'T2', 0)

    -- ROLLBACK de inserción de tabla (2)
       ROLLBACK TRAN SavePoint

-- Commit de T1
   COMMIT TRAN T1
El resultado es el esperado, si hacemos una select de la tabla familias veremos que sólo se ha insertado el registro 'T1', 'T1', 0 ya que el otro se ha deshecho.

- "Vale, muy bonito, pero quiero aplicar esto a los procedimientos almacenados que usamos en la realidad"
Imaginemos que tenemos dos procedimientos llamados P2 y P1:
CREATE PROCEDURE P2 AS BEGIN
    BEGIN TRAN T2
        INSERT INTO Familias(famcod, famdes, famdiasr)
        VALUES('T2', 'T2', 0)
    ROLLBACK TRAN T2
END
GO

CREATE PROCEDURE P1 AS BEGIN
    BEGIN TRAN T1
        INSERT INTO Familias(famcod, famdes, famdiasr)
        VALUES('T1', 'T1', 0)
        EXEC P2
    COMMIT TRAN T1
END
O sea, P1 inserta en familias, llama a P2 que inserta y hace ROLLBACK, y P1 hace COMMIT. Este es el ejemplo anterior pero separado en procedimientos almacenados.
¿Cómo lo arreglamos?, cambiamos en P2, BEGIN TRANS por SAVE TRANS... ¡PUES NO!, ¿¿por qué??, pues porque los procedimientos han de ser independientes entre si. Si ponemos SAVE TRANS y ejecutamos directamente el procedimiento P2, sqlserver nos dice: "¿estamos locos?, ¡NO HAY TRANSACCIÓN QUE GUARDAR!". Aquí vuelve a entrar en juego @@TRANCOUNT. Esta es la solución, P2 quedaría así:
ALTER PROCEDURE P2 AS BEGIN
    IF @@TRANCOUNT = 0 BEGIN TRAN T ELSE SAVE TRAN T
        INSERT INTO Familias(famcod, famdes, famdiasr)
        VALUES('T2', 'T2', 0)
    ROLLBACK TRAN T
END
De esta forma el procedimiento es independiente (caso de @@TRANCOUNT = 0) y también se puede llamar desde otro procedimiento con transacciones (caso de @@TRANCOUNT > 0).
Ahora podéis probar a ejecutar tanto P1 como P2 para comprobar que lo dicho es verdad.
¿Y si quremos que P2 NO DESHAGA los cambios?: Pues depende. Tenemos dos casos:
1 - El procedimiento se está ejecutando de forma independiente (@@TRANCOUNT = 0): En este caso hacemos COMMIT T de forma normal.
2 - El procedimiento está siendo llamado por otro, dentro de otra transacción (@@TRANCOUNT > 0): En este caso no hacemos NADA. Es, como he dicho antes, como guardar una partida en un videojuego, en este caso lo que queremos es seguir jugando sin deshacer los cambios, por tanto no cargamos la partida que hemos guardado y ya está. Si en este caso hiciésemos un COMMIT T sqlserver nos diría... "¿estamos locos?, no existe transacción T!!".

Ahora vamos a ver otro caso:
ALTER PROCEDURE P2 AS BEGIN
    SAVE TRAN T
        INSERT INTO Familias(famcod, famdes, famdiasr)
        VALUES('T2', 'T2', 0)
END
GO

ALTER PROCEDURE P1 AS BEGIN
    BEGIN TRAN T1
        INSERT INTO Familias(famcod, famdes, famdiasr)
        VALUES('T1', 'T1', 0)
        EXEC P2
    ROLLBACK TRAN T1
END
En este caso P1 inserta en familias, llama a P2 quien crea un punto de guardado e inserta en familias, y por último P1 hace un ROLLBACK TRAN T1. El resultado es que se han deshecho TODOS los cambios, no sólo hasta el punto de guardado. Si hubiésemos querido deshacer los cambios hasta el punto de guardado tendríamos que haber hecho ROLLBACK TRAN T (evidentemente en P2, porque P1 no sabe de la existencia de T).

- "Vale, pero aún no lo hemos aplicado del todo a la realidad, quiero que P1 haga commit ó rollback en función de si P2 ha realizado correctamente las operaciones o no"
Vaaaaaaale, ahí va como quedaría definitivamente como si fuese un procedimiento almacenado "real":
ALTER PROCEDURE P2 AS BEGIN
    DECLARE @ERROR AS INT
    DECLARE @TRANCOUNT AS INT SET @TRANCOUNT = @@TRANCOUNT
    IF @TRANCOUNT = 0 BEGIN TRAN T_P2 ELSE SAVE TRAN T_P2

    INSERT INTO Familias(famcod, famdes, famdiasr) VALUES('T2', 'T2', 0)
    SET @ERROR = @@ERROR IF @ERROR <> 0 GOTO HANDLE_ERROR

    IF @TRANCOUNT = 0 COMMIT TRAN T_P2
    RETURN 0

    HANDLE_ERROR:
        ROLLBACK TRAN T_P2
        RETURN @ERROR
END
GO

ALTER PROCEDURE P1 AS BEGIN
    DECLARE @ERROR AS INT

    BEGIN TRAN T_P1

    INSERT INTO Familias(famcod, famdes, famdiasr) VALUES('T1', 'T1', 0)
    SET @ERROR = @@ERROR IF @ERROR <> 0 GOTO HANDLE_ERROR

    EXEC @ERROR = P2
    IF @ERROR <> 0 GOTO HANDLE_ERROR

    COMMIT TRAN T_P1
    RETURN 0

    HANDLE_ERROR:
        ROLLBACK TRAN T_P1
        RETURN @ERROR
END
Hemos preparado P2 para que pueda funcionar de forma independiente ó siendo llamado desde otro procedimiento, dentro de otra transacción. P1 no está preparado para ello, pero en cualquier momento se puede adaptar para que así sea, de la misma forma que hemos adaptado P2.

Ahora ya podemos hacer transacciones anidadas sin ningún problema.

No hay comentarios:

Publicar un comentario