Transacciones en SQL
Una transacción es un conjunto de operaciones que van a ser tratadas como una única unidad. Estas transacciones deben cumplir 4 propiedades fundamentales comúnmente conocidas como ACID (atomicidad, coherencia, asilamiento y durabilidad).
Tipos de Transacciones
Transacciones de confirmación automática
Transacciones explícitas
Transacciones implícitas
Transacciones anidadas
Transacciones de confirmación automática
Cada instrucción individual es una transacción.
Transacciones explícitas
Cada transacción se inicia explícitamente con la instrucción BEGIN TRANSACTION y se termina explícitamente con una instrucción COMMIT o ROLLBACK.
Transacciones implícitas
Se inicia implícitamente una nueva transacción cuando se ha completado la anterior, pero cada transacción se completa explícitamente con una instrucción COMMIT o ROLLBACK.
Transacciones anidadas
Otra de las posibilidades que nos ofrece el SQL Server es utilizar transacciones anidadas.
Esto quiere decir que podemos tener transacciones dentro de transacciones, es decir, podemos empezar una nueva transacción sin haber terminado la anterior.
Asociada a esta idea de anidamiento existe una variable global @@TRANCOUNT que tiene 0 si no existe ningún nivel de anidamiento, 1 si hay una transacción anidada, 2 si estamos en el segundo nivel de anidamiento… y así sucesivamente.
La dificultad de trabajar con transacciones anidadas está en el comportamiento que tienen ahora las sentencias ‘COMMIT TRAN’ y ‘ROLLBACK TRAN’
ROLLBACK TRAN: Dentro de una transacción anidada esta sentencia deshace todas las transacciones internas hasta la instrucción BEGIN TRANSACTION más externa.
COMMIT TRAN: Dentro de una transacción anidada esta sentencia únicamente reduce en 1 el valor de @@TRANCOUNT, pero no "finaliza" ninguna transacción ni "guarda" los cambios. En el caso en el que @@TRANCOUNT=1 (cuando estamos en la última transacción) COMMIT TRAN hace que todas las modificaciones efectuadas sobre los datos desde el inicio de la transacción sean parte permanente de la base de datos, libera los recursos mantenidos por la conexión y reduce @@TRANCOUNT a 0
EJEMPLOS.
Confirmación automática
DECLARE @importe DECIMAL(18,2),
@CuentaOrigen VARCHAR(12),
@CuentaDestino VARCHAR(12)
/* Asignamos el importe de la transferencia
* y las cuentas de origen y destino
SET @importe = 50
SET @CuentaOrigen = '200700000001'
SET @CuentaDestino = '200700000002'
/* Descontamos el importe de la cuenta origen
UPDATE CUENTAS
SET SALDO = SALDO - @importe
WHERE NUMCUENTA = @CuentaOrigen
/* Registramos el movimiento */
INSERT INTO MOVIMIENTOS
(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)
SELECT
IDCUENTA, SALDO + @importe, SALDO, @importe, getdate()
FROM CUENTAS
WHERE NUMCUENTA = @CuentaOrigen
/* Incrementamos el importe de la cuenta destino */
UPDATE CUENTAS
SET SALDO = SALDO + @importe
WHERE NUMCUENTA = @CuentaDestino
/* Registramos el movimiento */
INSERT INTO MOVIMIENTOS
(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)
SELECT
IDCUENTA, SALDO - @importe, SALDO, @importe, getdate()
FROM CUENTAS
WHERE NUMCUENTA = @CuentaDestino
Transacciones explicitas.
DECLARE @importe DECIMAL(18,2),
@CuentaOrigen VARCHAR(12),
@CuentaDestino VARCHAR(12)
/* Asignamos el importe de la transferencia
* y las cuentas de origen y destino
*/
SET @importe = 50
SET @CuentaOrigen = '200700000002'
SET @CuentaDestino = '200700000001'
BEGIN TRANSACTION -- O solo BEGIN TRAN
BEGIN TRY
/* Descontamos el importe de la cuenta origen */
UPDATE CUENTAS
SET SALDO = SALDO - @importe
WHERE NUMCUENTA = @CuentaOrigen
/* Registramos el movimiento */
INSERT INTO MOVIMIENTOS
(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)
SELECT
IDCUENTA, SALDO + @importe, SALDO, @importe, getdate()
FROM CUENTAS
WHERE NUMCUENTA = @CuentaOrigen
/* Incrementamos el importe de la cuenta destino */
UPDATE CUENTAS
SET SALDO = SALDO + @importe
WHERE NUMCUENTA = @CuentaDestino
/* Registramos el movimiento */
INSERT INTO MOVIMIENTOS
(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)
SELECT
IDCUENTA, SALDO - @importe, SALDO, @importe, getdate()
FROM CUENTAS
WHERE NUMCUENTA = @CuentaDestino
/* Confirmamos la transaccion*/
COMMIT TRANSACTION -- O solo COMMIT
END TRY
BEGIN CATCH
/* Hay un error, deshacemos los cambios*/
ROLLBACK TRANSACTION -- O solo ROLLBACK
PRINT 'Se ha producido un error!'
END CATCH
Transacciones implícitas.
SET IMPLICIT_TRANSACTIONS ON
DECLARE @importe DECIMAL(18,2),
@CuentaOrigen VARCHAR(12),
@CuentaDestino VARCHAR(12)
/* Asignamos el importe de la transferencia
* y las cuentas de origen y destino
*/
SET @importe = 50
SET @CuentaOrigen = '200700000002'
SET @CuentaDestino = '200700000001'
BEGIN TRY
/* Descontamos el importe de la cuenta origen */
UPDATE CUENTAS
SET SALDO = SALDO - @importe
WHERE NUMCUENTA = @CuentaOrigen
/* Registramos el movimiento */
INSERT INTO MOVIMIENTOS
(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)
SELECT
IDCUENTA, SALDO + @importe, SALDO, @importe, getdate()
FROM CUENTAS
WHERE NUMCUENTA = @CuentaOrigen
/* Incrementamos el importe de la cuenta destino */
UPDATE CUENTAS
SET SALDO = SALDO + @importe
WHERE NUMCUENTA = @CuentaDestino
/* Registramos el movimiento */
INSERT INTO MOVIMIENTOS
(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)
SELECT
IDCUENTA, SALDO - @importe, SALDO, @importe, getdate()
FROM CUENTAS
WHERE NUMCUENTA = @CuentaDestino
/* Confirmamos la transaccion*/
COMMIT TRANSACTION -- O solo COMMIT
END TRY
BEGIN CATCH
/* Hay un error, deshacemos los cambios*/
ROLLBACK TRANSACTION -- O solo ROLLBACK
PRINT 'Se ha producido un error!'
END CATCH
Transacciones anidadas.
BEGIN TRAN
UPDATE EMPLEADOS
SET NOMBRE = 'Devjoker'
WHERE ID=101
BEGIN TRAN
UPDATE EMPLEADOS
SET APELLIDO1 = 'Devjoker.COM'
WHERE ID=101
- Este COMMIT solo afecta a la segunda transaccion.
COMMIT
-- Este ROLLBACK afecta a las dos transacciones.
ROLLBACK
Una transacción es un conjunto de operaciones que van a ser tratadas como una única unidad. Estas transacciones deben cumplir 4 propiedades fundamentales comúnmente conocidas como ACID (atomicidad, coherencia, asilamiento y durabilidad).
Tipos de Transacciones
Transacciones de confirmación automática
Transacciones explícitas
Transacciones implícitas
Transacciones anidadas
Transacciones de confirmación automática
Cada instrucción individual es una transacción.
Transacciones explícitas
Cada transacción se inicia explícitamente con la instrucción BEGIN TRANSACTION y se termina explícitamente con una instrucción COMMIT o ROLLBACK.
Transacciones implícitas
Se inicia implícitamente una nueva transacción cuando se ha completado la anterior, pero cada transacción se completa explícitamente con una instrucción COMMIT o ROLLBACK.
Transacciones anidadas
Otra de las posibilidades que nos ofrece el SQL Server es utilizar transacciones anidadas.
Esto quiere decir que podemos tener transacciones dentro de transacciones, es decir, podemos empezar una nueva transacción sin haber terminado la anterior.
Asociada a esta idea de anidamiento existe una variable global @@TRANCOUNT que tiene 0 si no existe ningún nivel de anidamiento, 1 si hay una transacción anidada, 2 si estamos en el segundo nivel de anidamiento… y así sucesivamente.
La dificultad de trabajar con transacciones anidadas está en el comportamiento que tienen ahora las sentencias ‘COMMIT TRAN’ y ‘ROLLBACK TRAN’
ROLLBACK TRAN: Dentro de una transacción anidada esta sentencia deshace todas las transacciones internas hasta la instrucción BEGIN TRANSACTION más externa.
COMMIT TRAN: Dentro de una transacción anidada esta sentencia únicamente reduce en 1 el valor de @@TRANCOUNT, pero no "finaliza" ninguna transacción ni "guarda" los cambios. En el caso en el que @@TRANCOUNT=1 (cuando estamos en la última transacción) COMMIT TRAN hace que todas las modificaciones efectuadas sobre los datos desde el inicio de la transacción sean parte permanente de la base de datos, libera los recursos mantenidos por la conexión y reduce @@TRANCOUNT a 0
EJEMPLOS.
Confirmación automática
DECLARE @importe DECIMAL(18,2),
@CuentaOrigen VARCHAR(12),
@CuentaDestino VARCHAR(12)
/* Asignamos el importe de la transferencia
* y las cuentas de origen y destino
SET @importe = 50
SET @CuentaOrigen = '200700000001'
SET @CuentaDestino = '200700000002'
/* Descontamos el importe de la cuenta origen
UPDATE CUENTAS
SET SALDO = SALDO - @importe
WHERE NUMCUENTA = @CuentaOrigen
/* Registramos el movimiento */
INSERT INTO MOVIMIENTOS
(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)
SELECT
IDCUENTA, SALDO + @importe, SALDO, @importe, getdate()
FROM CUENTAS
WHERE NUMCUENTA = @CuentaOrigen
/* Incrementamos el importe de la cuenta destino */
UPDATE CUENTAS
SET SALDO = SALDO + @importe
WHERE NUMCUENTA = @CuentaDestino
/* Registramos el movimiento */
INSERT INTO MOVIMIENTOS
(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)
SELECT
IDCUENTA, SALDO - @importe, SALDO, @importe, getdate()
FROM CUENTAS
WHERE NUMCUENTA = @CuentaDestino
Transacciones explicitas.
DECLARE @importe DECIMAL(18,2),
@CuentaOrigen VARCHAR(12),
@CuentaDestino VARCHAR(12)
/* Asignamos el importe de la transferencia
* y las cuentas de origen y destino
*/
SET @importe = 50
SET @CuentaOrigen = '200700000002'
SET @CuentaDestino = '200700000001'
BEGIN TRANSACTION -- O solo BEGIN TRAN
BEGIN TRY
/* Descontamos el importe de la cuenta origen */
UPDATE CUENTAS
SET SALDO = SALDO - @importe
WHERE NUMCUENTA = @CuentaOrigen
/* Registramos el movimiento */
INSERT INTO MOVIMIENTOS
(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)
SELECT
IDCUENTA, SALDO + @importe, SALDO, @importe, getdate()
FROM CUENTAS
WHERE NUMCUENTA = @CuentaOrigen
/* Incrementamos el importe de la cuenta destino */
UPDATE CUENTAS
SET SALDO = SALDO + @importe
WHERE NUMCUENTA = @CuentaDestino
/* Registramos el movimiento */
INSERT INTO MOVIMIENTOS
(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)
SELECT
IDCUENTA, SALDO - @importe, SALDO, @importe, getdate()
FROM CUENTAS
WHERE NUMCUENTA = @CuentaDestino
/* Confirmamos la transaccion*/
COMMIT TRANSACTION -- O solo COMMIT
END TRY
BEGIN CATCH
/* Hay un error, deshacemos los cambios*/
ROLLBACK TRANSACTION -- O solo ROLLBACK
PRINT 'Se ha producido un error!'
END CATCH
Transacciones implícitas.
SET IMPLICIT_TRANSACTIONS ON
DECLARE @importe DECIMAL(18,2),
@CuentaOrigen VARCHAR(12),
@CuentaDestino VARCHAR(12)
/* Asignamos el importe de la transferencia
* y las cuentas de origen y destino
*/
SET @importe = 50
SET @CuentaOrigen = '200700000002'
SET @CuentaDestino = '200700000001'
BEGIN TRY
/* Descontamos el importe de la cuenta origen */
UPDATE CUENTAS
SET SALDO = SALDO - @importe
WHERE NUMCUENTA = @CuentaOrigen
/* Registramos el movimiento */
INSERT INTO MOVIMIENTOS
(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)
SELECT
IDCUENTA, SALDO + @importe, SALDO, @importe, getdate()
FROM CUENTAS
WHERE NUMCUENTA = @CuentaOrigen
/* Incrementamos el importe de la cuenta destino */
UPDATE CUENTAS
SET SALDO = SALDO + @importe
WHERE NUMCUENTA = @CuentaDestino
/* Registramos el movimiento */
INSERT INTO MOVIMIENTOS
(IDCUENTA, SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO)
SELECT
IDCUENTA, SALDO - @importe, SALDO, @importe, getdate()
FROM CUENTAS
WHERE NUMCUENTA = @CuentaDestino
/* Confirmamos la transaccion*/
COMMIT TRANSACTION -- O solo COMMIT
END TRY
BEGIN CATCH
/* Hay un error, deshacemos los cambios*/
ROLLBACK TRANSACTION -- O solo ROLLBACK
PRINT 'Se ha producido un error!'
END CATCH
Transacciones anidadas.
BEGIN TRAN
UPDATE EMPLEADOS
SET NOMBRE = 'Devjoker'
WHERE ID=101
BEGIN TRAN
UPDATE EMPLEADOS
SET APELLIDO1 = 'Devjoker.COM'
WHERE ID=101
- Este COMMIT solo afecta a la segunda transaccion.
COMMIT
-- Este ROLLBACK afecta a las dos transacciones.
ROLLBACK
