Transacciones en Transact SQL

Concepto de transaccion

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.

El ejemplo clásico de transacción es una transferencia bancaria, en la que quitamos saldo a una cuenta y lo añadimos en otra. Si no somo capaces de abonar el dinero en la cuenta de destino, no debemos quitarlo de la cuenta de origen.

SQL Server funciona por defecto con Transacciones de confirmación automática , es decir, cada instrucción individual es una transacción y se confirma automáticamente.

Sobre el ejemplo anterior de la transferencia bancaria, un script debería realizar algo parecido a los siguiente:

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

Esta forma de actuar seria erronea, ya que cada instrucción se ejecutaria y confirmaría de forma independiente, por lo que un error dejaría los datos erroneos en la base de datos ( ¡y ese es el peor error que nos podemos encontrar! )

Transacciones implicitas y explicitas

Para agrupar varias sentencias Transact SQL en una única transacción, disponemos de los siguientes métodos:

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 automátivamente una nueva transacción cuando se ejecuta una instrucción que realiza modificaciones en los datos, pero cada transacción se completa explícitamente con una instrucción COMMIT o ROLLBACK.

Para activar-desactivar el modo de transacciones implicitas debemos ejecutar la siguiente instrucción.

--Activamos el modo de transacciones implicitas
SET IMPLICIT_TRANSACTIONS ON

--Desactivamos el modo de transacciones implicitas
SET IMPLICIT_TRANSACTIONS OFF

Cuando la opción ANSI_DEFAULTS está establecida en ON, IMPLICIT_TRANSACTIONS también se establece en ON.

El siguiente ejemplo muestra el script anterior haciendo uso de 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

El siguiente ejemplo muestra el mismo script con transacciones implicitas.

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

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.

La conexión continúa generando transacciones implícitas hasta que se desactiva el modo de transacciones implícitas.

Podemos verificar el número de transacciones activas a través de @@TRANCOUNT.

SET IMPLICIT_TRANSACTIONS ON
BEGIN TRY
UPDATE CUENTAS SET FXALTA = FXALTA - 1
PRINT @@TRANCOUNT
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
PRINT 'Error'
END CATCH

Otro punto a tener en cuenta cuando trabajamos con transacciones son los bloqueos y el nivel de aislamiento. Podemos aprender más sobre bloqueos y nivel de aislamiento en este articulo.

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.

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 consideración a tener en cuanta cuando trabajamos con transacciones anidadas es la posibilidad de utilizar puntos de guardado o SAVEPOINTs.

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.

El siguiente ejemplo muestra como trabajar con puntos de recuperación.

BEGIN TRAN

UPDATE EMPLEADOS
SET NOMBRE = 'Devjoker'
WHERE ID=101

UPDATE EMPLEADOS
SET APELLIDO1 = 'Devjoker.COM'
WHERE ID=101

SAVE TRANSACTION P1 -- Guardamos la transaccion (Savepoint)

UPDATE EMPLEADOS
SET APELLIDO1 = 'Otra cosa!'
WHERE ID=101

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

-- Confirmamos la transaccion
COMMIT

Inicio | Tutorial de Transact SQL

Procedimientos almacenados en Transact SQL

Versión para imprimir

Foros de consulta

		1... por Anónimo Respuesta recibida el [10/10/2007 04:44:39]
		1

		Transacciones con SP en asp por Elmer Perez Respuesta recibida el [22/04/2008 12:58:13]
		Saludos: tengo una duda es cuando guardas un arreglo es decir una matriz de datos que llega de asp y utilizo un Stored Procedured para guardar los datos como grantizo este tipo de entradas?. Atte.

Saludos: Esto es solo un come... por Anónimo
Respuesta recibida el [14/05/2008 09:17:07]

Saludos:
Esto es solo un comentario pero espero t sea util .. cuando kiero guardar una lista de datos inserto uno por uno , pero si en alguno salta error elimino todos los que habia insertado.
ejemplo
tengo las tablas plan y periodo
relacion 1 ... n , es decir un plan puede tener muchos periodos pero un periodo solo pertenece a un plan (disculpa si t parece aburrido) 
bueno si estoy insertando el PLAN 1 
y tengo 5 PERIODOS
inserto plan 1 perido 1
inserto plan 1 perido 2
inserto plan 1 perido 3
inserto plan 1 perido 4
inserto plan 1 perido 5
si en alguno salta un error elimino todos los q tengan plan 1

Para Anonimo por Devjoker
Respuesta recibida el [14/05/2008 05:23:47]

Eso es justamento lo que NO HAY QUE HACER NUNCA.
Imagina que:
inserta plan1 periodo 1
inserta plan1 periodo 2
inserta plan1 periodo 3
¡En este momento se cae la red y pierdes la conexion con el servidor!
Te dispones a borrar ... pero no hay conexion.
Resultado: Error en los datos.
Imagina si en lugar de ser planes son transferencias bancarias ...

Las transacciones se pueden co... por Anónimo
Respuesta recibida el [07/06/2008 05:23:17]

Las transacciones se pueden controlar en la base de datos o en el codigo.
Aunque tu tengas controlado las transacciones en la base de datos para lo que quieres hacer tienes ademas que controlarla en el codigo.
Abririas una unica conexion para todas las inserciones, antes de iniciar las inserciones creas un objeto dbtransaccion en el codigo, no se que lenguaje utilizas.
Cuando terminas si niguno falla haces un dbtransaction.commit y si falñla alguno haces un dbtransaction.rollback.
En el caso de que no utilices un lenguaje que tenga el objeto dbtransaction, tienes que crearte 3procedimientos que sean BeginTransaccion en donde unicamente haces BEGIN TRAN, un procedimiento CommitTransaccion  en donde haces COMMIT y uno ultimo que sea RollbackTransaccion que hace ROLLBACK.
Asi simulas el objeto dbTransaction que utilizan los lenguajes.

Un saludo

		Ejemplo por Richard Romero Respuesta recibida el [03/07/2008 06:17:16]
		Como seria un ejemplo cuando tenes procedimentos almacenados de por medio EJ: BEGIN TRAN SELECT1 UPDATE2 PROCEMIENTO3 INSERT4 END TRAN Y EL PROCEDIMIENTO3 HACE UN OTRA TRANSACION

		Muy útil. por Yanet Respuesta recibida el [18/07/2008 11:46:01]
		Muy bueno el tutorial, me gusta porque es de bolsillo. Me gustan tus tutoriales. saludos buenote.

Transacciones con SP's anidados. por Javier Astete
Respuesta recibida el [16/09/2008 01:02:43]

Como se trabaja la transaccionalidad en el caso de que yo tengo un sp que llama a otros dos para realizar operaciones de insercion, seria necesario a cada sp independiente o el Begin Tran inicial ya controla las operaciones dentro de los SP's anidados. Gracias.

		El mejor por Anonimo Vs Devjoker Respuesta recibida el [29/12/2008 03:57:32]
		Anonimo sabe mas que devjoker..

Excelente por tgirardi
Respuesta recibida el [06/02/2009 08:54:13]

Buenisimo! Llevaba varios minutos buscando una buena explicacion a transacciones. Principalmente no encontraba ninguna porque todos los ejemplos eran malos e incompletos. Los de este tutorial son simples y dejan bien claro todo lo importante del asunto.

		duda por Ninoska Respuesta recibida el [07/04/2009 03:57:13]
		si inicio o activo la transaccion implicita con SET IMPLICIT_TRANSACTIONS ON en que momento se coloca SET IMPLICIT_TRANSACTIONS off para desactivarla. Gracias

		Matate subersa... por TUVieja Respuesta recibida el [18/05/2009 08:56:28]
		Matate subersa

no funciona los procedimientos por david
Respuesta recibida el [16/07/2009 12:20:54]

buenas noches me gusta tu pagina, soy nuevo con esto de las transacciones hice 3 procedimientos uno con begin otro con commit y el ultimo con rollback pero no borra lo que se realizo dentro de la transaccion estoy utlilizando vb.net y sql 2005 a por cierto lo que esta dentro del begin tran y commit son sentencias utilizando Dim con As New SqlConnection(datos.stringCon())Dim com As New SqlCommand(ssql, con) 
algo asi de codigo no se si tiene algo que ver agradezco opinienos gracias

Número de transacción por Isabel sin idea de economía...
Respuesta recibida el [21/01/2010 08:29:15]

Buenas tardes. Necesito saber cómo puedo averiguar el "número de transacción", así literalmente, de un pago que he realizado por internet...pues me lo piden para que, la entidad beneficiaria, pueda reembolsármelo. 

Perdona mi ignorancia, y gracias por adelantado!

		Programa en c# por Ana Laura Torres Respuesta recibida el [07/02/2010 11:48:51]
		buenas tardes.. necesito saber como quedaria el ejemplo de la transaccion explicita con C sharp (C#), esta ligada a mysql. Gracias