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.