Transacciones en SQL Server

Introducción

Una transacción es un conjunto de operaciones Transact SQL que se ejecutan como un único bloque, es decir, si falla una operación Transact SQL fallan todas. Si una transacción tiene éxito, todas las modificaciones de los datos realizadas durante la transacción se confirman y se convierten en una parte permanente de la base de datos. Si una transacción encuentra errores y debe cancelarse o revertirse, se borran todas las modificaciones de los datos.

La transacción más simple en SQL Server es una única sentencia SQL. una transacción ‘autocommit’, una transacción autocompletada.

UPDATE clientes SET sexo='F' WHERE sexo ='FEMENINO'

Cuando enviamos esta sentencia al SQL Server se escribe en el fichero de transacciones lo que va a ocurrir y a continuación realiza los cambios necesarios en la base de datos. Si hay algún tipo de problema al hacer esta operación el SQL Server puede leer en el fichero de transacciones lo que se estaba haciendo y si es necesario puede devolver la base de datos al estado en el que se encontraba antes de recibir la sentencia.

Por supuesto este tipo de transacciones no requieren de nuestra intervención puesto que el sistema se encarga de todo. Sin embargo si hay que realizar varias operaciones y queremos que sean tratadas como una unidad tenemos que crear esas transacciones de manera explícita.

Sentencias

La sentencia que se utiliza para indicar el comienzo de una transacción es ‘BEGIN TRAN’. Si alguna de las operaciones de una transacción falla hay que deshacer la transacción en su totalidad para volver al estado inicial en el que estaba la base de datos antes de empezar. Esto se consigue con la sentencia ‘ROLLBACK TRAN’.

Si todas las operaciones de una transacción se completan con éxito hay que marcar el fin de una transacción para que la base de datos vuelva a estar en un estado consistente con la sentencia ‘COMMIT TRAN’.

Ejemplo:

DECLARE @Monto DECIMAL(18,2),
@CuentaADecrementar VARCHAR(12),
@CuentaAIncrementar VARCHAR(12) 

/* Asignamos el monto de la transacción y las cuentas a afectar*/

SET @Monto = 1900 
SET @CuentaADecrementar = '20161206'
SET @CuentaAIncrementar = '20161207' 

BEGIN TRANSACTION 
BEGIN TRY

/* Descontamos el monto de la cuenta a decrementar */
UPDATE CUENTAS SET SALDO = SALDO - @Monto WHERE NUMCUENTA = @CuentaADecrementar 

/* Registramos el movimiento */
INSERT INTO MOVIMIENTOS (IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR,IMPORTE, FXMOVIMIENTO)

/* Incrementamos el monto de la cuenta a incrementar */
UPDATE CUENTAS SET SALDO = SALDO + @Monto WHERE NUMCUENTA = @CuentaAIncrementar 

/* Registramos el movimiento */
INSERT INTO MOVIMIENTOS (IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)

/* Confirmamos la transaccion*/
COMMIT TRANSACTION 

END TRY

BEGIN CATCH

/* Ocurrió un error, deshacemos los cambios*/ 
ROLLBACK TRANSACTION
PRINT 'Ha ocurrido un error!'

END CATCH

La transacción sigue activa hasta que emita una instrucción COMMIT o ROLLBACK. Una vez que la primera transacción se ha confirmado o revertido, se inicia automáticamente una nueva transacción la siguiente vez que la conexión ejecuta una instruccion para modificar datos.

Transacciones anidadas

Podemos anidar varias transacciones. Cuando anidamos varias transacciones la instrucción COMMIT afectará a la última transacción abierta, pero ROLLBACK afectará a todas las transacciones abiertas.

Un hecho a tener en cuenta, es que, si hacemos ROLLBACK de la transacción superior se desharan también los cambios de todas las transacciones internas, aunque hayamos realizado COMMIT de ellas.

Puntos de recuperacion (SavePoint)

Los puntos de recuperación (SavePoints) permiten manejar las transacciones por pasos, pudiendo hacer rollbacks hasta un punto marcado por el savepoint y no por toda la transacción.

Ejemplo:

 
BEGIN TRAN 

UPDATE Clientes SET Estado = 'Inactivo' WHERE iddepartamento = 1020 

UPDATE Clientes SET Estado = 'DeBaja' WHERE iddepartamento=7025

SAVE TRANSACTION Punto1 -- Guardamos la transaccion (Savepoint)

UPDATE Clientes SET Descripcion = 'Ninguna' WHERE idcliente=5896

-- Este ROLLBACK afecta solo a las instrucciones posteriores al savepoint Punto1.

ROLLBACK TRANSACTION Punto1 

-- Confirmamos la transaccion

COMMIT