viernes, 11 de marzo de 2016

Transacciones en SQL Server

Buenas noches queridos lectores, hoy continuaremos con un tema sumamente interesante y útil, se trata de las transacciones, ya había publicado algo al respecto, pero solo fue una pequeña introducción. Vamos entrar en el tema

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

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

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.


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






Banderas de Visitantes

Flag Counter