MySQL : transaction dans une procédure stockée

Question

La structure de base de ma procédure stockée est la suivante,

BEGIN

    .. Declare statements ..

    START TRANSACTION;

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

    COMMIT;

END

Version de MySQL : 5.1.61-0ubuntu0.11.10.1-log

Actuellement, si la 'requête 2' échoue, le résultat de la 'requête 1' est validé.

• Comment puis-je annuler la transaction si l'une des requêtes échoue ?

Answer 1

Jetez un coup d'œil à http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

En fait, vous déclarez un gestionnaire d'erreur qui appellera le retour en arrière.

START TRANSACTION;

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

Answer 2

Juste une alternative au code 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

Answer 3

[ C'est juste une explication qui n'a pas été abordée dans d'autres réponses. ]

Au moins dans les versions récentes de MySQL, votre première requête n'est pas engagé .

Si vous l'interrogez sous la même session, vous verrez les changements, mais si vous l'interrogez depuis une autre session, les changements ne sont pas là, ils ne sont pas engagé .

Qu'est-ce qui se passe ?

Lorsque vous ouvrez une transaction, et qu'une requête à l'intérieur de celle-ci échoue, la transaction reste ouverte, elle ne commettre ni retour en arrière les changements.

Alors SOYEZ PRUDENT toute table/rangée qui a été verrouillée par une requête précédente comme SELECT ... FOR SHARE/UPDATE , UPDATE , INSERT ou toute autre requête de verrouillage, reste verrouillée jusqu'à ce que cette session soit tuée (et exécute un rollback), ou jusqu'à ce qu'une requête ultérieure la commette explicitement ( COMMIT ) ou implicitement ce qui rend les changements partiels permanent (ce qui pourrait se produire des heures plus tard, alors que la transaction était en état d'attente).

C'est pourquoi la solution consiste à déclarer des gestionnaires pour immédiatement ROLLBACK lorsqu'une erreur se produit.

---

Extra

A l'intérieur du gestionnaire, vous pouvez également relancer l'erreur en utilisant RESIGNAL sinon la procédure stockée s'exécute "Avec succès" :

BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
        BEGIN
            ROLLBACK;
            RESIGNAL;
        END;

    START TRANSACTION;
        #.. Query 1 ..
        #.. Query 2 ..
        #.. Query 3 ..
    COMMIT;
END

Answer 4

Voici un exemple de transaction qui fera marche arrière en cas d'erreur et renverra le code d'erreur.

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 ;

Ceci en supposant que le paramètre autocommit est réglé sur 0. J'espère que cela vous aidera.

Answer 5

Transaction dans une procédure stockée MySQL

Pour effectuer le ROLLBACK dans la procédure stockée MySQL, nous devons déclarer gestionnaire de sortie dans la procédure stockée. Il y a deux types de gestionnaire que nous pouvons avoir dans une procédure stockée MySQL.

1. sqlexception
2. sqlwarning

sqlexception exécutera lorsqu'une erreur se produit au cours de l'exécution de la requête et sqlwarning exécutera lorsqu'un avertissement survient dans une procédure stockée MySQL. Voyons comment nous pouvons avoir ces blocs dans la procédure stockée.

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
$$