Transactions dans une procédure stockée : COMMIT et ROLLBACK

Une procédure stockée transactionnelle regroupe ses modifications entre START TRANSACTION et COMMIT, et un gestionnaire d'erreur exécute ROLLBACK si l'une d'elles échoue : la base n'est jamais laissée à moitié modifiée. Ce chapitre donne ce modèle, l'applique à la clôture d'un ticket et à l'installation d'un logiciel sous licence, puis traite les questions que les débutants oublient : qui ouvre la transaction, quel niveau d'isolation, que faire après un interblocage. Les bases (ACID, verrous, isolation) sont dans le chapitre Transactions SQL du cours SQL.

Le modèle de la procédure transactionnelle

DELIMITER //
CREATE PROCEDURE cloturer_ticket(
  IN p_id_ticket     INT,
  IN p_id_technicien INT,
  IN p_duree_min     SMALLINT,
  IN p_compte_rendu  VARCHAR(255)
)
BEGIN
  DECLARE v_statut VARCHAR(10);

  -- En cas d'erreur, n'importe où après ce point : on annule tout, puis on prévient l'appelant
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;

  -- Lire le ticket en le verrouillant : personne ne le modifie avant notre COMMIT
  SELECT statut INTO v_statut
  FROM ticket
  WHERE id_ticket = p_id_ticket
  FOR UPDATE;

  IF v_statut IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Ticket inconnu';
  ELSEIF v_statut = 'resolu' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Ticket déjà résolu';
  END IF;

  INSERT INTO intervention (id_ticket, id_technicien, date_intervention, duree_min, compte_rendu)
  VALUES (p_id_ticket, p_id_technicien, NOW(), p_duree_min, p_compte_rendu);

  UPDATE ticket
  SET statut = 'resolu', date_cloture = NOW()
  WHERE id_ticket = p_id_ticket;

  COMMIT;
END //
DELIMITER ;

Quatre éléments, toujours les mêmes :

1. un gestionnaire EXIT HANDLER FOR SQLEXCEPTION, déclaré au début, qui fait ROLLBACK puis RESIGNAL ;
2. START TRANSACTION avant la première modification (pas BEGIN, qui dans une routine ouvre un bloc) ;
3. les contrôles métier, qui lèvent une erreur avec SIGNAL : elle est rattrapée par le gestionnaire comme n'importe quelle autre ;
4. COMMIT à la fin, quand tout a réussi.

Les essais, sur la base fraîche :

CALL cloturer_ticket(9, 11, 30, 'Contrôle final');
-- ERROR 1644 (45000): Ticket déjà résolu
CALL cloturer_ticket(99, 11, 30, 'Contrôle final');
-- ERROR 1644 (45000): Ticket inconnu
CALL cloturer_ticket(6, 12, 0, 'Ventilateur remplacé');
-- ERROR 3819 (HY000): Check constraint 'ck_intervention_duree' is violated.

SELECT statut, date_cloture FROM ticket WHERE id_ticket = 6;   -- en_cours, NULL : rien n'a bougé

CALL cloturer_ticket(6, 12, 25, 'Ventilateur remplacé');      -- réussit

Compare avec la procédure cloturer_naif du chapitre 6 : ici, l'échec de l'INSERT annule tout, et le ticket 6 reste « en cours » tant que la clôture n'a pas complètement réussi.

Si tu journalises l'erreur dans le gestionnaire (chapitre 6), lis d'abord le diagnostic, puis place le ROLLBACK avant l'INSERT dans le journal :

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  GET STACKED DIAGNOSTICS CONDITION 1 v_num = MYSQL_ERRNO, v_msg = MESSAGE_TEXT;
  ROLLBACK;                                  -- d'abord annuler…
  INSERT INTO journal_erreur (routine, num_erreur, message)
  VALUES ('cloturer_ticket', v_num, v_msg);  -- …puis tracer, hors transaction
  RESIGNAL;
END;

Dans l'ordre inverse, le ROLLBACK effacerait aussi la ligne du journal : l'erreur serait relancée, mais sans trace. GET STACKED DIAGNOSTICS lit l'erreur telle qu'elle était au déclenchement du gestionnaire ; tu pourrais donc aussi le placer après le ROLLBACK, mais le lire en premier évite de se poser la question.

Protéger une décision : la dernière licence

Le cours SQL montre le scénario de la dernière licence : deux techniciens qui installent Odoo au même instant lisent tous les deux 7 installations pour 8 licences, et installent tous les deux. Dans une procédure, la parade s'écrit une fois pour toutes :

DELIMITER //
CREATE PROCEDURE installer_logiciel(IN p_id_poste INT, IN p_id_logiciel INT)
BEGIN
  DECLARE v_nom        VARCHAR(60);
  DECLARE v_licences   INT;
  DECLARE v_installees INT;
  DECLARE v_message    VARCHAR(128);

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

  START TRANSACTION;

  -- 1. Verrouiller la ligne du logiciel : un second appel simultané attend ici
  SELECT nom, nb_licences INTO v_nom, v_licences
  FROM logiciel
  WHERE id_logiciel = p_id_logiciel
  FOR UPDATE;

  IF v_nom IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Logiciel inconnu';
  END IF;

  -- 2. Compter les installations une fois le verrou obtenu (logiciel libre : pas de limite)
  IF v_licences IS NOT NULL THEN
    SELECT COUNT(*) INTO v_installees
    FROM installation
    WHERE id_logiciel = p_id_logiciel;

    IF v_installees >= v_licences THEN
      SET v_message = CONCAT('Plus de licence ', v_nom, ' : ', v_installees,
                             ' installations pour ', v_licences, ' licences');
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = v_message;
    END IF;
  END IF;

  -- 3. Installer
  INSERT INTO installation (id_poste, id_logiciel, date_installation)
  VALUES (p_id_poste, p_id_logiciel, CURDATE());

  COMMIT;
END //
DELIMITER ;

CALL installer_logiciel(9, 5);    -- Odoo sur le poste 9 : 8e licence, acceptée
CALL installer_logiciel(14, 5);
-- ERROR 1644 (45000): Plus de licence Odoo : 8 installations pour 8 licences
CALL installer_logiciel(14, 3);   -- Firefox, logiciel libre : accepté
CALL installer_logiciel(14, 3);
-- ERROR 1062 (23000): Duplicate entry '14-3' for key 'installation.PRIMARY'

Le verrou FOR UPDATE sur la ligne du logiciel fait attendre le second appel jusqu'au COMMIT ou au ROLLBACK du premier ; il compte alors 8 installations et refuse. Le verrou est posé avant le comptage : c'est ce qui garantit que le comptage voit l'installation de l'autre technicien. Et comme la transaction se termine dans la procédure, le verrou ne dure que le temps de l'appel.

Une procédure appelée par l'application protège seulement ceux qui l'appellent. Si le script d'import trimestriel fait des INSERT directs dans installation, il contourne la règle : il faut alors un trigger, comme dans l'épreuve blanche n° 2 de l'E6, qui appelle la même vérification depuis la table.

Qui ouvre la transaction ?

MySQL n'a pas de transactions imbriquées. Un START TRANSACTION exécuté alors qu'une transaction est en cours valide implicitement la transaction en cours, puis en ouvre une nouvelle. Une procédure qui ouvre sa propre transaction, appelée au milieu d'une transaction de l'appelant, casse donc celle de l'appelant :

START TRANSACTION;
UPDATE logiciel SET nb_licences = 5 WHERE id_logiciel = 8;   -- Acrobat Pro : 2 licences, on teste 5
CALL installer_logiciel(13, 6);                              -- son START TRANSACTION valide l'UPDATE
ROLLBACK;                                                    -- n'annule plus rien

SELECT nb_licences FROM logiciel WHERE id_logiciel = 8;      -- 5 : l'essai est devenu définitif

Le COMMIT de la procédure a validé l'installation de KeePassXC, et le START TRANSACTION avant lui avait déjà validé l'UPDATE de l'appelant. Le ROLLBACK final arrive trop tard. (Pour revenir à la base de départ : UPDATE logiciel SET nb_licences = 2 WHERE id_logiciel = 8; et DELETE FROM installation WHERE id_poste = 13 AND id_logiciel = 6;.)

La règle à adopter, et à écrire dans le commentaire de chaque procédure :

• une procédure appelée directement par l'application pour une opération complète gère sa transaction (cloturer_ticket, installer_logiciel) ;
• une procédure utilitaire, appelée par d'autres procédures ou par un trigger, n'ouvre pas de transaction : elle travaille dans celle de son appelant (comme verifier_licence dans l'épreuve blanche de l'E6) ;
• l'application ne mélange pas les deux : elle n'appelle pas une procédure transactionnelle au milieu de sa propre transaction.

Points de sauvegarde dans une procédure

Un SAVEPOINT permet d'annuler une partie de la transaction seulement. C'est utile pour un traitement par lot où une ligne en erreur ne doit pas faire échouer les autres : on pose un point de sauvegarde avant chaque ligne, et le gestionnaire revient à ce point au lieu de tout annuler. L'exercice « Importer sans tout perdre », plus bas, construit cette procédure.

SAVEPOINT avant_ligne;
-- … traitement de la ligne …
ROLLBACK TO SAVEPOINT avant_ligne;   -- dans le gestionnaire : annule la ligne, garde les précédentes

Choisir le niveau d'isolation

Une procédure peut fixer le niveau d'isolation de sa transaction, juste avant de l'ouvrir :

DELIMITER //
CREATE PROCEDURE rapport_coherent()
BEGIN
  SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY;
  START TRANSACTION;
  SELECT COUNT(*) AS en_attente FROM ticket WHERE statut <> 'resolu';
  SELECT COUNT(*) AS interventions FROM intervention;
  COMMIT;
END //
DELIMITER ;

• SET TRANSACTION (sans SESSION) ne vaut que pour la prochaine transaction : la session retrouve ensuite son niveau habituel ;
• en REPEATABLE READ, les deux SELECT lisent la même photo de la base : les chiffres du rapport sont cohérents entre eux, même si un technicien clôture un ticket entre les deux ;
• READ ONLY annonce qu'aucune écriture ne suivra : une écriture provoquerait une erreur, et InnoDB optimise la transaction ;
• SET TRANSACTION est refusé si une transaction est déjà ouverte : erreur 1568, Transaction characteristics can't be changed while a transaction is in progress. C'est encore la question « qui ouvre la transaction ? ».

En pratique, le niveau par défaut d'InnoDB (REPEATABLE READ) convient presque toujours ; les vrais problèmes de concurrence se règlent par des verrous (FOR UPDATE) au bon endroit, comme dans installer_logiciel.

Interblocage : rejouer la transaction

Quand deux transactions s'attendent mutuellement, InnoDB en annule une avec l'erreur 1213 (Deadlock found when trying to get lock; try restarting transaction). Le message dit quoi faire : recommencer. La procédure peut le faire elle-même, quelques fois :

DELIMITER //
CREATE PROCEDURE avec_reprise(IN p_id_ticket INT, IN p_priorite VARCHAR(10))
BEGIN
  DECLARE v_essai INT DEFAULT 0;
  DECLARE v_ok    BOOLEAN DEFAULT FALSE;

  essais: WHILE NOT v_ok AND v_essai < 3 DO
    SET v_essai = v_essai + 1;
    BEGIN
      DECLARE EXIT HANDLER FOR 1213      -- interblocage : on annule, et la boucle recommence
      BEGIN
        ROLLBACK;
        IF v_essai = 3 THEN
          RESIGNAL;                      -- au troisième échec, on abandonne
        END IF;
      END;

      START TRANSACTION;
      UPDATE ticket SET priorite = p_priorite WHERE id_ticket = p_id_ticket;
      COMMIT;
      SET v_ok = TRUE;
    END;
  END WHILE essais;
END //
DELIMITER ;

Le gestionnaire est déclaré dans un sous-bloc : son EXIT termine le sous-bloc, pas la procédure, et la boucle passe à l'essai suivant. Les autres erreurs ne sont pas rattrapées et remontent tout de suite. Le plus souvent, c'est l'application qui rejoue la transaction ; l'important est qu'une couche le fasse, et une seule.

Exercices

DROP PROCEDURE IF EXISTS cloturer_naif;
DELIMITER //
CREATE PROCEDURE cloturer_naif(IN p_id_ticket INT, IN p_id_technicien INT, IN p_duree_min SMALLINT)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
  UPDATE ticket SET statut = 'resolu', date_cloture = NOW() WHERE id_ticket = p_id_ticket;
  INSERT INTO intervention (id_ticket, id_technicien, date_intervention, duree_min)
  VALUES (p_id_ticket, p_id_technicien, NOW(), p_duree_min);
  COMMIT;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE reaffecter(IN p_id_poste INT, IN p_id_employe INT)
BEGIN
  START TRANSACTION;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
  END;
  UPDATE poste SET id_employe = p_id_employe WHERE id_poste = p_id_poste;
  INSERT INTO historique_affectation (id_poste, nouvel_employe, modifie_par)
  VALUES (p_id_poste, p_id_employe, USER());
END //
DELIMITER ;

CREATE TABLE import_installation (
  id_ligne    INT AUTO_INCREMENT PRIMARY KEY,
  id_poste    INT NOT NULL,
  id_logiciel INT NOT NULL
);
INSERT INTO import_installation (id_poste, id_logiciel)
VALUES (14, 2), (14, 1), (1, 3), (99, 6), (14, 6);

DELIMITER //
CREATE PROCEDURE importer_installations(OUT p_ok INT, OUT p_rejets INT)
BEGIN
  DECLARE v_ligne, v_poste, v_logiciel INT;
  DECLARE v_fini BOOLEAN DEFAULT FALSE;
  DECLARE v_msg  VARCHAR(512);
  DECLARE c_import CURSOR FOR
    SELECT id_ligne, id_poste, id_logiciel FROM import_installation ORDER BY id_ligne;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_fini = TRUE;

  SET p_ok = 0, p_rejets = 0;
  START TRANSACTION;
  OPEN c_import;
  lecture: LOOP
    FETCH c_import INTO v_ligne, v_poste, v_logiciel;
    IF v_fini THEN
      LEAVE lecture;
    END IF;

    SAVEPOINT avant_ligne;
    BEGIN
      DECLARE EXIT HANDLER FOR SQLEXCEPTION
      BEGIN
        GET DIAGNOSTICS CONDITION 1 v_msg = MESSAGE_TEXT;
        ROLLBACK TO SAVEPOINT avant_ligne;
        INSERT INTO journal_erreur (routine, message)
        VALUES ('importer_installations', CONCAT('ligne ', v_ligne, ' : ', v_msg));
        SET p_rejets = p_rejets + 1;
      END;

      INSERT INTO installation (id_poste, id_logiciel, date_installation)
      VALUES (v_poste, v_logiciel, CURDATE());
      SET p_ok = p_ok + 1;
    END;
  END LOOP lecture;
  CLOSE c_import;
  COMMIT;
END //
DELIMITER ;

CALL importer_installations(@ok, @rejets);
SELECT @ok, @rejets;   -- 3 et 2

START TRANSACTION;
CALL affecter_poste(14, 14);
CALL installer_logiciel(14, 4);
COMMIT;