Transacciones en Trasact SQL
¿Que es una transacción?
Una transacción es un conjunto de órdenes que se ejecutan formando una unidad de trabajo, es decir, es forma indivisible o atómica, en otras palabras es un bloque de instrucciones, parecido a un archivo batch, pero programado de tal manera, que si una instrucción en el bloque falla, no se realizará ninguna instrucción. Veamos un poco de teoría y algunos ejemplos para su mayor compreensión.
Un sistema de base de datos se dice que es transaccional si puede mantener la integridad de los datos, haciendo que estas transacciones no puedan finalizar en un estado intermedio. Cuando por alguna razón el sistema deba cancelar la transacción, empieza a deshacer las ordenes ejecutadas hasta dejar la base de datos en su estado inicial (llamado punto de integridad), como si la orden de transacción nunca se hubiera ejecutado.
Para esto el motor de base de datos de SQL Server, provee los mecanismos para especificar que un conjunto de acciones deben constituir una transacción. Se cuenta específicamente con tres:
- BEGIN TRAN: Específica que va a iniciar una transacción
- COMMIT TRAN: Le indica al motor de base de datos que puede considerar la transacción terminada y completada correctamente
- ROLLBACK TRAN: Indica que se ha alcanzado un error y que debe restablecer la base al punto de integridad.
BEGIN TRAN representa un punto en el que los datos a los que hace referencia son lógica y físicamente coherentes. Si se producen errores se pueden revertir todas las operaciones realizadas en la base de datos después de BEGIN TRAN para devolver los datos al estado conocido de coherencia. Cada transacción dura, hasta que se completa sin errores y se emite COMMIT TRAN para hacer que las modificaciones sean una parte permanente de la base de datos o hasta que se produzcan errores y se borren todas las modificaciones con la instrucción ROLLBACK TRAN.
Aunque BEGIN TRAN indica una transacción local, esta no se guardará en el registro de transacciones, hasta que la aplicación realice posteriormente una acción que se deba almacenar en el registro, como la ejecución de la instrucción INSERT, UPDATE o DELETE.
Ejemplo 1
Implentar un procedimiento almacenado que permita registrar
un pasajero controlando dicha inserción por medio de una transacción, emita un
mensaje en cada ocasión, es decir, si todo es correcto emitir PASAJERO
REGISTRADO CON ÉXITO, caso contrario OCURRIO UN ERROR AL INSERTAR
--1
IF OBJECT_ID('NUEVOPASAJERO') IS NOT NULL
BEGIN
DROP PROCEDURE DBO.NUEVOPASAJERO
END
GO
--2
CREATE PROCEDURE NUEVOPASAJERO(
@IDPAS CHAR(5), @NOM VARCHAR(40), @PAI VARCHAR(40),
@TEL VARCHAR(15), @EMA VARCHAR(40))
AS
BEGIN TRAN TPASAJERO
DECLARE
@IDPAI CHAR(4)
SELECT @IDPAI = IDPAIS FROM PAIS WHERE PAIS=@PAI
INSERT INTO PASAJERO
VALUES (@IDPAS, @NOM, @IDPAI, @TEL, @EMA)
IF @@ERROR = 0
BEGIN
PRINT 'PASAJERO REGISTRADO
CON EXITO'
COMMIT
TRAN TPASAJERO
END
ELSE
BEGIN
PRINT
'OCURRIO UN ERROR AL INSERTAR'
ROLLBACK
TRAN TPASAJERO
END
GO
En el punto uno se verifica la existencia del procedimiento almacenado NUEVOPASAJERO. En este procedimiento se declaran parámetros que representan un valor en las columnas de la tabla, luego se inicia el bloque de transacciones con BEGIN TRAN, esto permitirá controlar algún evento dentro del proceso y al final tomar una decisión con respecto a las instrucciones siguientes.
Para poder encontrar si hubo algún error se tuvo que invocar la variable globar @@ERROR que captura el error y lo muestra por medio de sus funciones, pero en este caso comprobamos si no hubo errores con IF @@ERROR=0 entonces se mostrará el mensaje de confirmación exitosa y allí se invoca a la sentencia COMMIT TRAN asegurando el proceso confirmado.
El mismo caso sucede si encuentra un error, mostrará un mensaje y se aplicará la sentencia ROLLBACK TRAN, esto anulará todo el proceso, es decir, desde donde se inicio la transacción.
Para ejecutar el procedimiento se necesita el siguiente script:
EXEC
NUEVOPASAJERO 'P0015','EMMANUEL URIAS','MEXICO','3333333333','leur0985@gmail.com'
GO
La siguiente imagen muestra el resultado de la ejecución (primera vez)
La siguiente imagen muestra solo si ejecuta nuevamente el script anterior, ocurre un error porque el IDPASAJERO registrado se intenta correr registrar nuevamente
Ahora implementaremos el mismo caso, pero usando el control de error BEGIN TRy... BEGIn CATCH, y así evitar la comparación de la variable @@ERROR
Ejemplo 1
Implentar un procedimiento almacenado que permita registrar
un pasajero controlando dicha inserción por medio de una transacción, emita un
mensaje en cada ocasión, es decir, si todo es correcto emitir PASAJERO
REGISTRADO CON ÉXITO, caso contrario OCURRIO UN ERROR AL INSERTAR
--1
IF OBJECT_ID('NUEVOPASAJERO') IS NOT NULL
BEGIN
DROP PROCEDURE DBO.NUEVOPASAJERO
END
GO
--1
IF OBJECT_ID('NUEVOPASAJERO') IS NOT NULL
BEGIN
DROP PROCEDURE DBO.NUEVOPASAJERO
END
GO
--2
CREATE PROCEDURE
NUEVOPASAJERO(
@IDPAS
CHAR(5),@NOM VARCHAR(40),@PAI VARCHAR(40),
@TEL
VARCHAR(15),@EMA VARCHAR(40)
)
AS
BEGIN TRANSACTION
TPASAJERO
BEGIN TRY
DECLARE @IDPAI CHAR(4)
SELECT @IDPAI =
IDPAIS FROM PAIS WHERE
PAIS=@PAI
INSERT INTO PASAJERO
VALUES
(@IDPAS,@NOM,@IDPAI,@TEL,@EMA)
PRINT
'PASAJERO REGISTRADO CON EXITO'
COMMIT TRAN TPASAJERO
END TRY
BEGIN CATCH
PRINT 'OCURRIO UN ERROR AL
INSERTAR'
ROLLBACK TRAN TPASAJERO
END CATCH
GO
En el script solo se muestra una versión similar, la diferencia es que controlamos los mensajes con BEGIN TRYy ya no dependemos de la variable global @@ERROR.
El mensaje de error es mejor controlado desde BEGIN TRY
Ejemplo 2:
Implemente un
procedimiento almacenado que permita registrar el pago y a la vez debe
actualizar los valores de la tabla reserva involucradas en los pagos, emitiendo
mensajes para cada caso
--1
IF OBJECT_ID('REGISTRAPAGO') IS NOT NULL
BEGIN
DROP PROCEDURE
REGISTRAPAGO
END
GO
--2
CREATE PROCEDURE
REGISTRAPAGO(
@NUM INT,
@IDRES INT,
@IDPAS CHAR(5),
@FEC DATE,
@MON MONEY)
AS
--3
BEGIN TRAN TPAGO
BEGIN TRY
--4
INSERT INTO PAGO VALUES (@NUM, @IDRES,@IDPAS,@FEC,@MON)
--5
UPDATE RESERVA
SET COSTO+=@MON
WHERE IDRESERVA=@IDRES
PRINT 'ACTUALIZACION DE
RESERVA CORRECTA'
COMMIT
TRAN TPAGO
END TRY
--6
BEGIN CATCH
PRINT
'OCURRIO UN ERROR AL REGISTRAR EL PAGO'
PRINT @@ERROR
ROLLBACK TRAN TPAGO
END CATCH
GO
En el punto dos implementa el procedimiento almacenado REGISTRAPAGO el cual tiene 5 parámetros de entrada y son proporcionales al número de columnas de la tabla PAGO
En el punto tres se define el inicio de la transacción, en este caso se llamo TPAGO y dentro de él se controlará los errores ocasionados por el script BEGIN TRY y BEGIN CATCH.
CREATE PROCEDURE REGISTRAPAGO(
@NUM INT,
@IDRES INT,
@IDPAS CHAR(5),
@FEC DATE,
@MON MONEY)
En el punto tres se define el inicio de la transacción, en este caso se llamo TPAGO y dentro de él se controlará los errores ocasionados por el script BEGIN TRY y BEGIN CATCH.
BEGIN TRAN TPAGO
BEGIN TRY
En el punto cuatro se aplica la sentencia INSERT INTO para registrar los valores ingresados por los parámetros y enviarlos a la tabla PAGO
En el punto cinco, se actualizan los valores de la tabla RESERVA, siempre y cuando el IDRESERVA de la tabla PAGO se encuentre registrada en la tabla RESERVA, en caso que lo fuera, se utiliza el campo COSTO de la tabla RESERVA. Aquí se esta usando el operador complejo de Transact SQL += que permite acumular valores numéricos, aquí se actualiza la columna COSTO con el acumulado del mismo COSTO mas el valor ingresado por el procedimiento como parámetro llamado @MON. Luego se emite el mensaje de TRANSACCION CORRECTA y se asegura el proceso con COMMIT TRAN PAGO.
En el punto cuatro se aplica la sentencia INSERT INTO para registrar los valores ingresados por los parámetros y enviarlos a la tabla PAGO
INSERT INTO PAGO VALUES (@NUM, @IDRES,@IDPAS,@FEC,@MON)
En el punto cinco, se actualizan los valores de la tabla RESERVA, siempre y cuando el IDRESERVA de la tabla PAGO se encuentre registrada en la tabla RESERVA, en caso que lo fuera, se utiliza el campo COSTO de la tabla RESERVA. Aquí se esta usando el operador complejo de Transact SQL += que permite acumular valores numéricos, aquí se actualiza la columna COSTO con el acumulado del mismo COSTO mas el valor ingresado por el procedimiento como parámetro llamado @MON. Luego se emite el mensaje de TRANSACCION CORRECTA y se asegura el proceso con COMMIT TRAN PAGO.
UPDATE RESERVA
SET COSTO+=@MON
WHERE IDRESERVA=@IDRES
PRINT 'ACTUALIZACION DE RESERVA CORRECTA'
COMMIT TRAN TPAGO
En el punto seis se implementa el script que hará que se deshagan los cambios realizados a la base de datos en caso de un error. Para anular todo el proceso, se tiene que colocar la instrucción ROLLBACK TRAN TPAGO, con esto, la instrucción contenida dentro del bloque BEGIN TRAN TPAGO queda anulada.
En el punto seis se implementa el script que hará que se deshagan los cambios realizados a la base de datos en caso de un error. Para anular todo el proceso, se tiene que colocar la instrucción ROLLBACK TRAN TPAGO, con esto, la instrucción contenida dentro del bloque BEGIN TRAN TPAGO queda anulada.
PRINT 'OCURRIO UN ERROR AL REGISTRAR EL PAGO'
PRINT @@ERROR
ROLLBACK TRAN TPAGO
Para comprobar el procedimiento almacenado, primero veamos cual es contenido inicial de las tablas PAGO y RESERVA, para esto ejecute en conjunto el siguiente script:
SELECT * FROM PAGO WHERE
IDRESERVA=1
SELECT * FROM RESERVA WHERE
IDRESERVA=1
En la imagen siguiente se muestra el resultado de las consultas y notamos que en la tabla RESERVA se encuentra registrada la reserva con IDRESERVA 1 a costo 0 y lo reservó el día 01 de octubre de 2011
Ejecutaremos el procedimiento almacenado pensando en el IDRESERVA 1 y así comprobar sí la transacción es correcta o no. Ejecute el siguiente script
EXEC
REGISTRAPAGO 1,1,'P0003','20160311',400
La imagen siguiente muestra el resultado de la ejecución y como notará aparecen dos confirmaciones y el mensaje preparado en el procedimiento, estas confirmaciones se debe a la ejecución del INSERT INTO y el segundo obedece al UPDATE.
Ahora, veamos nuevamente como están los registros de ambas tablas.
Como verá en la tabla PAGO se registró el primer pago realizado por el pasajero de código P0003 con un monto de 400.00 y a la vez observamos que la tabla RESERVA actualizó su costo a 400 por ser el primer pago. Si ejecutáramos un segundo pago del mismo pasajero, entonces tendríamos que ejecutar el siguiente script:
EXEC
REGISTRAPAGO 2,1,'P0003','20160319',800
En esta simulación de registro se envió $800.00 a la tabla y; por lo tanto, la actualización de la tabla RESERVA muestra 1200
Aqui el final de las Transacciones queridos lectores, como ven, es un tema sumamente interesante, y ademas básico para la administración de base de Datos, ademas de que nos evitaría muchos errores, espero haya sido de utilidad, de su agrado y que, compartan, comenten, den +1 o den clic en algún anuncio que les interese. El próximo post, será acerca de los disparadores, otro tema bastante útil e interesante.
Un abrazo y hasta pronto
No hay comentarios.:
Publicar un comentario