El otro día mientras daba un curso en una empresa para desarrolladores, vi que algunas cosas de las transacciones no se comprendían bien y francamente me llamo la atención.

Luego de ese curso pensé en explicar en mi blog como se manejan las transacciones dentro de SQL Server y que cuidados tener, y bueno aquí estamos

¿Porque usamos transacciones?

Bueno aquí la cosa parece simple verdad, básicamente usamos transacciones para poder asegurar integridad de una operación, si por ejemplo necesitamos guardar una factura y esta se compone de Cabecera y Detalle no se debería guardar si hubo algún error en el detalle.

También podríamos mencionar otros ejemplos, donde se necesitan hacer mas de una operación y de fallar alguna no aplicar nada.

Tenemos dos tipos de transacciones en SQL Server, las implícitas y las explicitas.

Las primeras son aquellas que no debemos indicarle al SQL que hacemos una transacción pero el lo genera, por ejemplo como el siguiente caso.

DELETE FROM CLIENTES
WHERE PAIS = 'ARGENTINA'

Aquí se ejecutara el delete para todos los clientes que cumplan la condición de Argentina, si hay un error al intentar borrar un cliente se hará un rollback y no se borrara ninguno, esto se debe a que el delete al igual que el insert y update internamente arman una transacción.

Las transacciones explicitas son aquellas que nosotros indicamos con la sentencia Begin Transaction / Commit o Rollback Transaction.

BEGIN TRANSACTION 
  INSERT INTO EMPLEADOS (ID, NOMBRE)
  VALUES (1,'CONDUIT')
  
  DELETE FROM AUDITORIA
  WHERE EMPLEADO = 1
COMMIT TRAN

¿Como funciona el Commit y Rollback?

El commit es el que confirmara la transacción y el rollback es el que la deshará, pero aquí hay algunos detalles muy importantes en su comportamiento cuando usamos transacciones anidadas.

Primero veamos un ejemplo simple

CREATE TABLE #T1 (ID INT, NOMBRE VARCHAR(50))
GO

CREATE TABLE #T2 (ID INT, FECHA DATETIME NOT NULL)
GO

BEGIN TRY

    BEGIN TRAN   
    
      INSERT INTO #T1 VALUES (1,'CONDUIT')
      INSERT INTO #T1 VALUES (1,GETDATE())
    
    COMMIT TRAN
END TRY

BEGIN CATCH
  ROLLBACK TRAN
  SELECT @@ERROR 
END CATCH

En el ejemplo básicamente hacemos dos insert y si hay algún error entramos en la sección del Catch y lo primero que hacemos es el rollback para luego mostrar el error.

Este es un simple ejemplo donde si no hay problemas se harán los dos insert y el commit los confirmara y de haber un error en algún insert se hará Rollback.

Anidar transacciones

Hasta aquí vimos dos ejemplos bastantes simples del manejo de transacciones. Ahora bien que sucede si necesitamos manejar transacciones anidadas, por ejemplo ejecutamos un Store que abre una transacción que llama a otro Store que abre otra transacción y así, o bien en el mismo código.

Aquí primero debemos comprender como impacta un Rollback y un Commit y es donde mayor confusión quizás hay.

El Rollback hará que se deshagan todas las transacciones o sea si armamos una transacción dentro de otra y la segunda hace un rollback hará que también la primera lo haga, en cambio el commit lo hará por cada bloque a menos que apliquemos un commit en el bloque externo lo cual hará que todo se comitee.

Para poder evaluar cuantas transacciones tenemos abiertas podemos utilizar la variable

@@Trancount

Para ser mas ejemplificado con este tema vamos a ver un poco de código, el primer ejemplo muestra como funciona el Rollback

USE TEMPDB
go

CREATE TABLE Cabecera (id int, fecha Datetime)
go

CREATE TABLE Detalle (CabeceraId int, linea int NOT NULL,
                      Cantidad decimal (8,2) NOT NULL)
GO

CREATE TABLE Auditoria (Id int identity,
                        Fecha datetime not null
                       ) 

-- Ejecute el Script por pasos para ir comprendiendo


-- ======================================= 
--      Funcionamiento del Rollback
-- =======================================

-- Vemos que no hay Transacciones
SELECT @@TRANCOUNT 

-- Abrimos una transacción
BEGIN TRAN
  -- hacemos los insert
  INSERT INTO Cabecera 
  VALUES (1,GETDATE())
  
  INSERT INTO Detalle 
  Values (1,1,100)
  
  -- vemos las transacciones abiertas (1)
  SELECT @@TRANCOUNT 

  -- Abrimos otra transacción
  BEGIN TRAN 
    -- Verificamos que Trancount se incremento en 1
    SELECT @@TRANCOUNT   
    
    -- Hacemos un Rollback
   ROLLBACK TRAN
  
    -- Verificamos que Trancount quedo en 0
    -- Lo cual ha hecho un rollback de todo
    SELECT @@TRANCOUNT   
go    

El siguiente ejemplo muestro como funciona el Commit con el manejo de transacciones

-- ======================================= 
--      Funcionamiento del Commit
-- =======================================

-- Vemos que no hay Transacciones
SELECT @@TRANCOUNT 

-- Abrimos una transacción
BEGIN TRAN
  -- hacemos los insert
  INSERT INTO Cabecera 
  VALUES (1,GETDATE())
  
  INSERT INTO Detalle 
  Values (1,1,100)
  
  -- vemos las transacciones abiertas (1)
  SELECT @@TRANCOUNT 

  -- Abrimos otra transacción
     BEGIN TRAN 
    -- Verificamos que Trancount se incremento en 1
     SELECT @@TRANCOUNT   
    
    -- Hacemos un Rollback
     INSERT INTO Auditoria (Fecha)
     VALUES (GETDATE())
  
     COMMIT TRAN
    -- Verificamos que Trancount decrecio en 1
     SELECT @@TRANCOUNT   

COMMIT TRAN  -- Hacemos el utimo Commit
  
-- Verificamos que Trancount queda en 0
SELECT @@TRANCOUNT   
go    
  

Hasta que pudimos observar con ejemplos la diferencia de comportamiento que tenemos entre el Rollback y el Commit

como hemos explicado en el inicio del articulo.

Ahora bien, en el siguiente ejemplo muestro como manejar transacciones anidadas sin que el rollback se vaya a 0 y que lo haga a un punto que yo determine.

-- ======================================= 
--      Manejo de transacciones Anidadas
-- =======================================

-- Vemos que no hay Transacciones
SELECT @@TRANCOUNT 

-- Abrimos una transacción
BEGIN TRAN A
  -- hacemos los insert
  INSERT INTO Cabecera 
  VALUES (1,GETDATE())
  
  INSERT INTO Detalle 
  Values (1,1,100)
  
  -- Hacemos un punto de salvacion
  
  SAVE TRAN B
  
  -- vemos las transacciones abiertas (1)
  SELECT @@TRANCOUNT 

  -- Abrimos otra transacción
     BEGIN TRAN C
    -- Verificamos que Trancount se incremento en 1
     SELECT @@TRANCOUNT   
    
    -- Hacemos un Rollback
     INSERT INTO Auditoria (Fecha)
     VALUES (GETDATE())
  
     ROLLBACK TRAN B
    -- Verificamos que Trancount es 2 y no 0
    -- Esto se da porque el rollback afecta 
    -- el punto de almacenamiento y no la transacción
    -- en si
     SELECT @@TRANCOUNT   

-- Hacemos los Commit
 COMMIT TRAN  
 COMMIT TRAN  
-- Verificamos que Trancount queda en 0
SELECT @@TRANCOUNT   
go    

-- Verificamos los datos y vemos que en Auditoria
-- no se guardaron registros porque hubo un rollback
-- pero solo de ese punto

SELECT * FROM Cabecera 
SELECT * FROM Detalle 
SELECT * FROM Auditoria 
  

Control de errores y transacciones

Hasta aquí hemos visto que el manejo del Rollback es muy distinto al del Commit Tran, ahora bien, como hacemos para controlar los errores ? la buena practica es que ante un error lo primero que se haga sea un Rollback pero que sucede si hubo un rollback anterior y el TranCount quedo en 0? Lo que deberíamos hacer es en control del error verificar primero la variable Trancount y si es mayor que 0 entonces si hacer el Rollback, aquí vemos un ejemplo.

-- ======================================= 
--      Manejo de Errores & transacciones 
-- =======================================
  
SELECT @@TRANCOUNT 

-- Abrimos una transacción
BEGIN TRY

BEGIN TRAN
  -- hacemos los insert
  INSERT INTO Cabecera 
  VALUES (1,GETDATE())
  
  INSERT INTO Detalle 
  Values (1,1,100)
  
  -- Abrimos otra transacción
  BEGIN TRAN 
    -- Verificamos que Trancount se incremento en 1
    SELECT @@TRANCOUNT   
    
    -- Generamos un error para ir al bloque CATCH
    INSERT INTO Auditoria (Fecha)
    VALUES (NULL)
  
    -- Verificamos que Trancount quedo en 0
    -- Lo cual ha hecho un rollback de todo
  
END TRY

-- Control de errores

BEGIN CATCH
  IF @@TRANCOUNT > 0
   BEGIN
    ROLLBACK TRAN
   END 

SELECT ERROR_MESSAGE() 

END CATCH

Conclusiones

En este post les he mostrado como se manejan y como funcionan las transacciones en SQL Server, siempre recuerden que las transacciones deberían durar lo mínimo posible para evitar loqueos que terminen en problemas de performance, escriban solo el código adecuado dentro del Begin y Commit.

Esto que vimos también se aplica si hay Stores procedures y en uno abren una transacción que llama a otro que a su vez abre otra transacción, recuerden del save tran para esos manejos si lo que desean es volver a un punto determinado.