BD UD7 Transacciones en procedimientos y funciones

De MediaWiki
Ir a la navegación Ir a la búsqueda

Introducción

Vamos a realizar diferentes ejercicios (algunos los modificaremos, de los que llevamos realizados hasta ahora) en los que es necesario el uso de transacciones.
Dejaremos el nivel de aislamiento por defecto que suele ser el más adecuado para las operaciones que se realizan habitualmente contra una base de datos.


Recordar que en las secciones anteriores vimos como el control de errores lo podemos realizar en el 'cliente' o en el 'procedimiento/función'.
Si el procedimiento/función realiza varias operaciones lo normal es que lleve asociado una transacción y un control de errores capturando las posibles excepciones.
A nivel de cliente (aplicación realizada en Java, Php, ASP.NET,...que hace uso de los procedimientos/funciones creados) si la aplicación llama a varios procedimientos o realiza varias operaciones sobre la base de datos, debe llevar también una transacción y un control de errores (sección try...catch) para que pueda realizar el rollback (cancelación) de la transacción.


  • Cuando creamos el procedimiento/función, puede que nos interese capturar excepciones concretas para informar al usuario de ese error en concreto e informarlo con un mensaje más personalizado del tipo de error. Pero normalmente querremos capturar todos los posibles errores, informar al usuario de que se ha producido un error y que sea el administrador el que revise el código de error concreto.
Para capturar cualquier tipo de error SQL, podemos hacer uso de la orden DECLARE HANDLER como ya vimos en esta WIKI, utilizando esta sintaxis:
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET rollback = 1;
Fijarse en FOR SQLEXCEPION. En el ejemplo se asigna un valor a una variable, pero el tratamiento que le deis a la excepción dependerá de vuestro procedimiento.


  • IDEA:
A nivel de administrador de la base de datos, podríamos registrar en una tabla, el usuario, nombre del procedimiento, fecha-hora, tipo de error y mensaje de error.
A dicha tabla sólo podría acceder el administrador y la persona que se encarga de implementar los procedimientos/funciones y así comprobar que errores se ha producido.
Para ello habría que capturar cualquier posible excepción y obtener el texto de la misma. Debemos de hacer uso de la orden GET DIAGNOSTIC de la forma:
DECLARE EXIT HANDLER FOR SQLEXCEPTION    
BEGIN

// Nota: Si estamos en una transacción habría que cancelarla. Lo veremos a continuación

GET DIAGNOSTICS CONDITION 1
    @p2 = MESSAGE_TEXT;
    INSERT INTO LOG_ERRORS (error_message) 
END;
En el ejemplo, se sale del procedimiento/función y se añade una fila a la tabla LOGS_ERRORS. A mayores del texto, yo pondría el nombre del procedimiento, usuario y fecha-hora.



  • Aviso: Cuidado con utilizar la orden START TRANSACTION dentro de un bucle ya que estaríais creando una transacción por cada orden SQL y eso no se debe hacer.



Columnas autonuméricas en Transacciones

  • Muchas veces, cuando trabajamos con bases de datos relacionales, nos encontramos con tablas cuyas claves primarias son autonuméricas.
En algunos casos vamos a tener que programar procedimientos que necesiten utilizar el id generado al dar de alta una fila, para añadir una nueva fila en otra tabla relacionada.
Nota: En SQLServer, la función equivalente seria SCOPE_IDENTITY().


No debemos de realizar consultas del tipo: SELECT MAX(id) FROM TABLA para obtener el id después de realizar un INSERT, ya que al estar en un entorno multiusuario, dos usuarios pueden ejecutar al mismo tiempo la consulta y tendrían el mismo id para dos filas diferentes.
Si hacemos un insert multifila de la forma: INSERT INTO TABLA VALUE (dato11,dato12,...),(dato21,dato22,...); devolverá el primer ID generado.


Veamos un ejemplo práctico basado en este ejemplo. La solución a este ejemplo no es correcta en el enlace anterior, ya que no captura las excepciones que se puedan dar durante la ejecución como haremos nosotros.
Supongamos que tenemos dos tablas con una relación 1-N, teniendo la tabla TELEFONOS una columna (account_id) que es clave foránea de la tabla USUARIOS.
CREATE TABLE USUARIOS (
    account_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL
);

CREATE TABLE TELEFONOS (
    account_id INT NOT NULL,
    phone_id INT AUTO_INCREMENT,
    phone VARCHAR(25) NOT NULL,
    description VARCHAR(255) NOT NULL,
    PRIMARY KEY (account_id,phone_id),
    FOREIGN KEY (account_id)
                 REFERENCES CUENTAS (account_id)
);
Queremos crear un procedimiento que dé de alta a un usuario y un teléfono asociado.
Por lo tanto tenemos que añadir una fila a la tabla USUARIOS, obtener el ID generado y añadir una nueva fila a la tabla TELEFONOS, haciendo uso del id generado para la columna account_id.
Dicho procedimiento tendrá una transacción.
Al cliente (o al que llame al procedimiento) se le va a devolver el id generado tanto de la tabla USUARIOS como de la tabla TELEFONOS con un SELECT.
Se podría hacer uso parámetros de salida.
Esto sólo es necesario si estamos dentro de una operación mayor en la que necesitemos alguno de los id generados para después llamar a otro procedimiento/función y realizar otra operación...
Fijarse que la tabla TELEFONOS tiene otra columna autonumérica...
DROP PROCEDURE cuentas_altaConTelefono;
delimiter $$
CREATE PROCEDURE cuentas_altaConTelefono(p_firstName varchar(255), p_lastName varchar(255), p_phone varchar(25), p_desc varchar(255))
BEGIN
    DECLARE v_idGenCuenta,v_idGenTelefono int default 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SELECT -1;   -- Informamos a quien llamó de que algo fue mal
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;

    START TRANSACTION;	-- INICIO DE LA TRANSACCIÓN

    INSERT INTO CUENTAS (first_name,last_name)
    VALUES (p_firstName, p_lastName);
    
    SET v_idGenCuenta = LAST_INSERT_ID();
    INSERT INTO TELEFONOS (account_id,phone,description)
    VALUES (v_idGenCuenta,p_phone,p_desc);
    
    SET v_idGenTelefono = LAST_INSERT_ID();
    
    COMMIT;   -- CONFIRMA LA  TRANSACCIÓN

    SELECT v_idGenCuenta,v_idGenTelefono;
    
END$$
delimiter ;


Ejemplo de llamada:
call cuentas_altaConTelefono('Angel','Fernández', '123456789','Teléfono de casa');




Ejercicios propuestos Transacciones

Añadir una transacción y controlar los posibles errores.


Crea una nueva versión de este procedimiento y llámale animales_deleteV2.
Este ejercicio estaba pensado para que el control de errores se hiciera desde donde se llamara al procedimiento (desde el programa cliente) y por eso se lanzaba la excepción.
Ahora vamos a capturar todas las excepciones, por lo que en función de v_anos actúa en consecuencia enviando al cliente un código con el SELECT en vez de lanzar una excepción.
Añadir una transacción y controlar los posibles errores.


En este caso vamos a mantener la captura de excepciones y devolver un valor diferente en p_resultado (como está hecho ahora).
Devuelve en p_resultado el valor -3 en caso de error no controlado.
Añadir una transacción (por lo tanto tendréis que hacer un ROLLBACK en todos los tipos de errores capturados).
Indicar que cuando capturamos 'tipos de error', por ejemplo, cuando intentamos añadir una clave primaria duplicada, no podremos determinar directamente cual es la tabla que provoca el error. Podemos analizar el mensaje del error y ver a que tabla hace referencia.


  • Ejercicio 4 (sin solución): Crea un procedimiento de nombre artistas_delete que borre el artista y todas sus tablas relacionadas.
Debe comprobar si el artista existe. En caso de que no existiera, debe devolver el valor -1 con un SELECT.
En caso de que exista debe devolver el valor 0.
Añadir una transacción y controlar los posibles errores.


  • Ejercicio 5 (sin solución): Crea un procedimiento de nombre atracciones_delete que borre la atracción y todas sus tablas relacionadas.
Debe comprobar si la atracción existe. En caso de que no existiera, debe devolver el valor -1 con un SELECT.
En caso de que exista debe devolver el valor 0.
Añadir una transacción y controlar los posibles errores.


  • Ejercicio 6 (sin solución): Crea un procedimiento de nombre artistas_updateJefe, al que se le pase un nif y asigne como jefe dicho nif a todos los artistas que no tengan jefe y que hayan trabajado en al menos dos atracciones.
Emplea un cursor para buscar los artistas que cumplan las condiciones.
Si todo va bien debe devolver 0.
En caso de que el nif enviado no exista debe devolver el valor -1.
En cualquier otro caso de error debe devolver -2.
Añadir una transacción y controlar los posibles errores.




Soluciones Ejercicios propuestos Transacciones

Añadir una transacción y controlar los posibles errores.
USE `CIRCO`;
DROP procedure IF EXISTS `animales_AddAforoPorTipo`;

DELIMITER $$
USE `CIRCO`$$

CREATE PROCEDURE `animales_AddAforoPorTipo`(p_tipo varchar(9), p_incAforo smallint)
    COMMENT 'Hace uso del método animales_AddAforo para incrementar el aforo de las pistas donde trabajan los animales del tipo indicado'
BEGIN
    -- Declaración de variables
    DECLARE v_final INTEGER DEFAULT 0;
    DECLARE v_nombrePista VARCHAR(50);
    DECLARE v_nuevoAforo SMALLINT;

    -- Declaración del cursor
    DECLARE c_pistas CURSOR FOR 
        SELECT DISTINCT nombre_pista
        FROM ANIMALES
        WHERE tipo = p_tipo;
        
  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_Final = TRUE;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SELECT -1;   -- Informamos a quien llamó de que algo fue mal
        DROP TEMPORARY TABLE T_TEMPORAL;
        CLOSE c_pistas;
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;

    START TRANSACTION;	-- INICIO DE LA TRANSACCIÓN

    CREATE TEMPORARY TABLE T_TEMPORAL (nombrePista varchar(50), nuevoAforo SMALLINT);
    OPEN c_pistas;


    read_loop: LOOP
        FETCH c_pistas INTO v_nombrePista;
        IF v_final THEN
            LEAVE read_loop;
        END IF;
        
        SET v_nuevoAforo = p_incAforo;
        CALL pistas_addAforo(v_nombrePista,v_nuevoAforo);	-- En v_nuevoAforo está el aforo incrementado
        
        INSERT INTO T_TEMPORAL (nombrePista,nuevoAforo)
        VALUES (v_nombrePista,v_nuevoAforo);
    
    END LOOP;

    SELECT nombrePista,nuevoAforo
    FROM T_TEMPORAL
    ORDER BY nombrePista;

    CLOSE c_pistas;   
    
    DROP TEMPORARY TABLE T_TEMPORAL;
    
    COMMIT;	    -- CONFIRMA LA TRANSACCIÓN

END$$

DELIMITER ;
Ahora todas las operaciones están dentro de una transacción y en caso de error se hará el ROLLBACK.
Vamos a provocar que después del bucle haya una excepción:
USE `CIRCO`;
DROP procedure IF EXISTS `animales_AddAforoPorTipo`;

DELIMITER $$
USE `CIRCO`$$

CREATE PROCEDURE `animales_AddAforoPorTipo`(p_tipo varchar(9), p_incAforo smallint)
    COMMENT 'Hace uso del método animales_AddAforo para incrementar el aforo de las pistas donde trabajan los animales del tipo indicado'
BEGIN
    -- Declaración de variables
    DECLARE v_final INTEGER DEFAULT 0;
    DECLARE v_nombrePista VARCHAR(50);
    DECLARE v_nuevoAforo SMALLINT;

    -- Declaración del cursor
    DECLARE c_pistas CURSOR FOR 
        SELECT DISTINCT nombre_pista
        FROM ANIMALES
        WHERE tipo = p_tipo;
        
  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_Final = TRUE;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SELECT -1;   -- Informamos a quien llamó de que algo fue mal
        DROP TEMPORARY TABLE T_TEMPORAL;
        CLOSE c_pistas;
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;

    START TRANSACTION;	-- INICIO DE LA TRANSACCIÓN

    CREATE TEMPORARY TABLE T_TEMPORAL (nombrePista varchar(50), nuevoAforo SMALLINT);
    OPEN c_pistas;


    read_loop: LOOP
        FETCH c_pistas INTO v_nombrePista;
        IF v_final THEN
            LEAVE read_loop;
        END IF;
        
        SET v_nuevoAforo = p_incAforo;
        CALL pistas_addAforo(v_nombrePista,v_nuevoAforo);	-- En v_nuevoAforo está el aforo incrementado
        
        INSERT INTO T_TEMPORAL (nombrePista,nuevoAforo)
        VALUES (v_nombrePista,v_nuevoAforo);
    
    END LOOP;

    INSERT INTO PISTAS  -- PROVOCA UNA EXCEPCIÓN DE CLAVE PRIMARIA DUPLICADA
    VALUES ('LATERAL1',100);

    SELECT nombrePista,nuevoAforo
    FROM T_TEMPORAL
    ORDER BY nombrePista;

    CLOSE c_pistas;   
    
    DROP TEMPORARY TABLE T_TEMPORAL;
    
    COMMIT;	    -- CONFIRMA LA TRANSACCIÓN

END$$

DELIMITER ;
Si ahora llamamos al procedimiento podemos comprobar como los aforos de las pistas no se actualizan (ninguno de ellos) y el procedimiento devuelve el valor -1.
Quitar del procedimiento el INSERT añadido después de probar.



Crea una nueva versión de este procedimiento y llámale animales_deleteV2.
Este ejercicio estaba pensado para que el control de errores se hiciera desde donde se llamara al procedimiento (desde el programa cliente) y por eso se lanzaba la excepción.
Ahora vamos a capturar todas las excepciones, por lo que en función de v_anos actúa en consecuencia enviando al cliente un código con el SELECT en vez de lanzar una excepción.
Añadir una transacción y controlar los posibles errores.
USE `CIRCO`;
DROP procedure IF EXISTS `animales_deleteV2`;

DELIMITER $$
CREATE PROCEDURE `animales_deleteV2`(p_nombreAnimal VARCHAR(50))
    COMMENT 'Da de baja un animal siempre que su edad sea superior a 2 años'
bloque_proc:BEGIN
    DECLARE v_anos TINYINT default -1;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SELECT -3;   -- Informamos a quien llamó de que algo fue mal
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;
   
    START TRANSACTION;

    SELECT anhos
    INTO v_anos
    FROM ANIMALES
    WHERE nombre = p_nombreAnimal;
    
    CASE 
        WHEN v_anos = -1 THEN
            SELECT -1;
            LEAVE bloque_proc;    
        WHEN v_anos < 2 THEN
            SELECT -2;
            LEAVE bloque_proc;    
         ELSE BEGIN END;
    END CASE;

    -- BORRAMOS LOS ANIMALES. Necesitaríamos una transacción. 
    DELETE FROM ANIMALES_ARTISTAS
    WHERE nombre_animal = p_nombreAnimal;
    
    DELETE FROM ANIMALES
    WHERE nombre = p_nombreAnimal;

    COMMIT;
END$$

DELIMITER ;
Vamos ahora a provocar que haya una excepción añadiendo una orden INSERT (que no tiene sentido, sólo es para provocar la excepción):
USE `CIRCO`;
DROP procedure IF EXISTS `animales_deleteV2`;

DELIMITER $$
CREATE PROCEDURE `animales_deleteV2`(p_nombreAnimal VARCHAR(50))
    COMMENT 'Da de baja un animal siempre que su edad sea superior a 2 años'
bloque_proc:BEGIN
    DECLARE v_anos TINYINT default -1;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SELECT -3;   -- Informamos a quien llamó de que algo fue mal
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;
   
    START TRANSACTION;

    SELECT anhos
    INTO v_anos
    FROM ANIMALES
    WHERE nombre = p_nombreAnimal;
    
    CASE 
        WHEN v_anos = -1 THEN
            SELECT -1;
            LEAVE bloque_proc;    
        WHEN v_anos < 2 THEN
            SELECT -2;
            LEAVE bloque_proc;    
         ELSE BEGIN END;
    END CASE;

    -- BORRAMOS LOS ANIMALES. Necesitaríamos una transacción. 
    DELETE FROM ANIMALES_ARTISTAS
    WHERE nombre_animal = p_nombreAnimal;
    
    DELETE FROM ANIMALES
    WHERE nombre = p_nombreAnimal;

    INSERT INTO PISTAS
    VALUES ('LATERAL1',100);

    COMMIT;
END$$

DELIMITER ;
Si ahora llamáis podéis comprobar como no se borra ningún animal...
Quitar del procedimiento el INSERT añadido después de probar.



En este caso vamos a mantener la captura de excepciones y devolver un valor diferente en p_resultado (como está hecho ahora).
Devuelve en p_resultado el valor -3 en caso de error no controlado.
Añadir una transacción (por lo tanto tendréis que hacer un ROLLBACK en todos los tipos de errores capturados).
Indicar que cuando capturamos 'tipos de error', por ejemplo, cuando intentamos añadir una clave primaria duplicada, no podremos determinar directamente cual es la tabla que provoca el error. Podemos analizar el mensaje del error y ver a que tabla hace referencia.
USE `CIRCO`;
DROP procedure IF EXISTS `artistas_add`;

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `artistas_add`(p_nif CHAR(9),p_apellidos varchar(100), p_nombre varchar(50),p_nifJefe char(9),p_nombreAnimal varchar(50), OUT p_resultado tinyint)
    COMMENT 'Añade un nuevo artista'
BEGIN
    DECLARE ex_artista_duplicado CONDITION FOR 1062;
    DECLARE ex_animal_no_existe CONDITION FOR SQLSTATE '45000';
    
    DECLARE EXIT HANDLER FOR ex_artista_duplicado
    BEGIN
        SET p_resultado = -1;
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;
    DECLARE EXIT HANDLER FOR ex_animal_no_existe
    BEGIN
        SET p_resultado = -2;
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SET p_resultado = -3;   -- Informamos a quien llamó de que algo fue mal
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;

    START TRANSACTION;
    
    INSERT INTO ARTISTAS (nif, apellidos, nombre, nif_jefe)
    VALUES (p_nif, p_apellidos, p_nombre, p_nifJefe);

    CALL animales_addArtista(p_nombreAnimal,p_nif);

    COMMIT;
    
    SET p_resultado = 0;
END$$
DELIMITER ;


Vamos ahora a provocar que haya una excepción añadiendo una orden INSERT (que no tiene sentido, sólo es para provocar la excepción):
USE `CIRCO`;
DROP procedure IF EXISTS `artistas_add`;

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `artistas_add`(p_nif CHAR(9),p_apellidos varchar(100), p_nombre varchar(50),p_nifJefe char(9),p_nombreAnimal varchar(50), OUT p_resultado tinyint)
    COMMENT 'Añade un nuevo artista'
BEGIN
    DECLARE ex_artista_duplicado CONDITION FOR 1062;
    DECLARE ex_animal_no_existe CONDITION FOR SQLSTATE '45000';
    
    DECLARE EXIT HANDLER FOR ex_artista_duplicado
    BEGIN
        SET p_resultado = -1;
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;
    DECLARE EXIT HANDLER FOR ex_animal_no_existe
    BEGIN
        SET p_resultado = -2;
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        SET p_resultado = -3;   -- Informamos a quien llamó de que algo fue mal
        ROLLBACK;	-- CANCELA LA TRANSACCIÓN EN CASO DE ERROR
    END;

    START TRANSACTION;
    
    INSERT INTO ARTISTAS (nif, apellidos, nombre, nif_jefe)
    VALUES (p_nif, p_apellidos, p_nombre, p_nifJefe);

    CALL animales_addArtista(p_nombreAnimal,p_nif);

    INSERT INTO ANIMALES_ARTISTAS
    VALUES ('NO EXISTE','NO EXISTE');
    
    COMMIT;
    
    SET p_resultado = 0;
END$$
DELIMITER ;
Si ahora llamáis podéis comprobar como no añade ningún artista...y devuelve el valor -3.
Quitar del procedimiento el INSERT añadido después de probar.




Enlace a la página principal de la UD7

Enlace a la página principal del curso




-- Ángel D. Fernández González -- (2020).