transaction - MySQL: transacción dentro de un procedimiento almacenado

transacciones en mysql workbench

Question

La estructura básica de mi procedimiento almacenado es,

BEGIN

    .. Declare statements ..

    START TRANSACTION;

        .. Query 1 ..
        .. Query 2 ..
        .. Query 3 ..

    COMMIT;

END

Versión de MySQL: 5.1.61-0ubuntu0.11.10.1-log

Actualmente, si falla la "consulta 2", se confirma el resultado de la "consulta 1".

• ¿Cómo puedo deshacer la transacción si falla alguna de las consultas?

Answer 1

Transacción en MySQL Procedimiento almacenado

Para realizar el ROLLBACK en MySQL Stored Procedure, debemos declarar el manejador de salida en el procedimiento almacenado. Hay dos tipos de controladores que podemos tener en MySQL Stored Procedure.

1. sqlexception
2. sqlwarning

sqlexception se ejecutará cuando se produzca algún error durante la ejecución de la consulta y sqlwarning se ejecutará cuando se produzca una advertencia en MySQL Stored Procedure. Veamos cómo podemos tener esos bloqueos en el Procedimiento almacenado.

DELIMITER $$

CREATE PROCEDURE `transaction_sp` ()

BEGIN

DECLARE exit handler for sqlexception
  BEGIN
    -- ERROR
  ROLLBACK;
END;

DECLARE exit handler for sqlwarning
 BEGIN
    -- WARNING
 ROLLBACK;
END;

START TRANSACTION;
  INSERT INTO table_name (id, name, address) values (''1'',''Avinash'',''xpertdeveloper.com'');
  UPDATE second_table set name="xyz" where id=4;
COMMIT;
END
$$

Answer 2

Aquí hay un ejemplo de una transacción que se revertirá por error y devolverá el código de error.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_CREATE_SERVER_USER`(
    IN P_server_id VARCHAR(100),
    IN P_db_user_pw_creds VARCHAR(32),
    IN p_premium_status_name VARCHAR(100),
    IN P_premium_status_limit INT,
    IN P_user_tag VARCHAR(255),
    IN P_first_name VARCHAR(50),
    IN P_last_name VARCHAR(50)
)
BEGIN

    DECLARE errno INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO;
    SELECT errno AS MYSQL_ERROR;
    ROLLBACK;
    END;

    START TRANSACTION;

    INSERT INTO server_users(server_id, db_user_pw_creds, premium_status_name, premium_status_limit)
    VALUES(P_server_id, P_db_user_pw_creds, P_premium_status_name, P_premium_status_limit);

    INSERT INTO client_users(user_id, server_id, user_tag, first_name, last_name, lat, lng)
    VALUES(P_server_id, P_server_id, P_user_tag, P_first_name, P_last_name, 0, 0);

    COMMIT WORK;

END$$
DELIMITER ;

Esto supone que autocommit se establece en 0. Espero que esto ayude.

Answer 3

Eche un vistazo a http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

Básicamente declaras el manejador de errores que llamará al rollback

START TRANSACTION;

DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
        ROLLBACK;
        EXIT PROCEDURE;
    END;
COMMIT;

Answer 4

Solo una alternativa al código de rkosegi,

BEGIN

    .. Declare statements ..

    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN
          .. set any flags etc  eg. SET @flag = 0; ..
          ROLLBACK;
    END;

    START TRANSACTION;

        .. Query 1 ..
        .. Query 2 ..
        .. Query 3 ..

    COMMIT;
    .. eg. SET @flag = 1; ..

END