Module 1Maîtriser le langage de programmation procédurale sous MySQL
Chapitre 5 sur 12
L'autocommit, START TRANSACTION, COMMIT et ROLLBACK, l'exemple du virement bancaire et les événements planifiés.
Objectifs
Avec ce chapitre, tu sauras…
COMMIT ou ROLLBACK ;Leçon 1
INSERT, il faut une transaction, ouverte avec BEGIN ou START TRANSACTION.COMMIT ou ROLLBACK.BEGIN; -- début de la transaction
INSERT INTO mytable VALUES (1, 'foo', 'bar', 'bas');
SELECT * FROM mytable;
COMMIT; -- ou ROLLBACK pour annuler l'opérationRemarque : pour désactiver le commit automatique pour la session :
SET autocommit = 0;
-- ou
SET autocommit = OFF;Toutes les instructions suivantes font alors partie d'une transaction qui reste ouverte jusqu'au prochain COMMIT ou ROLLBACK.
Tableau: Le contrôle des transactions
| Commande | Rôle |
|---|---|
COMMIT | enregistrer les modifications (elles deviennent définitives et visibles par les autres sessions) |
ROLLBACK | annuler les modifications depuis le début de la transaction |
Ces commandes ne concernent que les commandes LMD : INSERT, UPDATE et DELETE.
Vidéo · À regarder
FRBase de données : Transactions, commit, rollback
Algomius · YouTube
Vidéo · À regarder
FRBase de données : Transactions, commit, rollback
Algomius · YouTube
Vidéo · À regarder
FRBase de données : Transactions, commit, rollback
Algomius · YouTube
Leçon 2
| Clause | Rôle |
|---|---|
ON SCHEDULE EVERY 1 MINUTE | répéter toutes les minutes (aussi HOUR, DAY, WEEK…) |
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY | exécuter une seule fois, à une date précise |
STARTS / ENDS | la date de début et de fin de la répétition |
DO | l'instruction à exécuter (ou un bloc BEGIN … END avec DELIMITER) |
1Par défaut, MySQL est en mode…
2Pour annuler les modifications de la transaction, on écrit…
3COMMIT et ROLLBACK concernent…
4Dans une procédure, pour ouvrir une transaction on écrit…
5Quel moteur gère les transactions ?
6Un événement MySQL s'exécute…
7SET autocommit = 0; signifie…
L'essentiel du chapitre
START TRANSACTION (ou BEGIN hors programme stocké) … COMMIT (valider) ou ROLLBACK (annuler).SET autocommit = 0 pour gérer soi-même les validations.INSERT, UPDATE, DELETE) ; le LDD fait un commit implicite ; il faut InnoDB.CREATE EVENT nom ON SCHEDULE EVERY n unité [STARTS …] [ENDS …] DO … ou AT date ; planificateur visible avec SHOW PROCESSLIST, activé par SET GLOBAL event_scheduler = ON.Vidéo · À regarder
ENMySQL Event Tutorials for Beginners #2 - Concept of Event Scheduler & Syntax of Event Create
Online Web Tutor · YouTube
Ce que tu vas voir (16 min) : les chapitres « Transaction en MySQL », « Transaction explicite ». Le formateur démarre des transactions dans MySQL, les valide avec COMMIT ou les annule avec ROLLBACK, puis montre la transaction explicite.
Ce que tu vas voir (3 min) : les chapitres « Commit implicite ». Il montre le commit implicite : les instructions qui valident d'elles-mêmes la transaction en cours.
L'exemple du virement bancaire
Le meilleur exemple pour comprendre une transaction est un transfert d'argent entre deux comptes de la même banque : on débite 200 DH sur le compte émetteur et on crédite 200 DH sur le compte récepteur.
Supposons que le débit réussisse mais que le crédit échoue (un problème de base de données) : la base est dans un état incohérent, 200 DH ont disparu. On veut que les deux opérations se produisent, ou aucune. Dans une transaction, l'échec du crédit annule aussi le débit : l'état de la base ne change pas.
START TRANSACTION;
UPDATE compte SET solde = solde - 200 WHERE accountno = 'ACC1'; -- débiter le compte ACC1
UPDATE compte SET solde = solde + 200 WHERE accountno = 'ACC2'; -- créditer le compte ACC2
COMMIT; -- validerUne transaction, c'est comme un paiement par carte au supermarché : soit le paiement passe en entier (ton compte est débité et le magasin est payé), soit il est refusé et rien ne bouge. Jamais « débité mais pas payé ».
Ce que tu vas voir (3 min) : les chapitres « Pourquoi utiliser les transactions ? », « Fonctionnement des transactions ». Il explique pourquoi on utilise les transactions et comment elles fonctionnent, sur un exemple proche du virement.
Les événements MySQL sont des tâches qui s'exécutent selon un calendrier. On les appelle aussi événements planifiés. MySQL utilise un thread spécial, le planificateur d'événements (event scheduler), pour les exécuter.
On voit l'état du planificateur avec SHOW PROCESSLIST : une ligne event_scheduler « Waiting for next activation » indique qu'il fonctionne.
CREATE EVENT test_event_03
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
INSERT INTO messages (message, created_at)
VALUES ('Test MySQL recurring Event', NOW());Crée l'événement ev_purge_messages qui, chaque jour, supprime les lignes de la table messages de plus de 7 jours.
Dans une transaction : (1) ajoute un produit « Clavier » ; (2) affiche les produits ; (3) annule ; (4) affiche à nouveau. Le clavier est-il encore là ? Recommence avec COMMIT. Puis refais le test avec CREATE TABLE test2 (id INT); au milieu de la transaction, avant ROLLBACK : que se passe-t-il ?
Crée la table compte (accountno VARCHAR(10) PRIMARY KEY, solde DECIMAL(10,2) CHECK (solde >= 0)) avec ACC1 = 1000 et ACC2 = 500. Écris la procédure sp_virement(p_src, p_dst, p_montant) qui fait le virement dans une transaction. Si une erreur se produit (solde négatif refusé par le CHECK), elle annule tout avec ROLLBACK et affiche « Virement annulé » (utilise un EXIT HANDLER FOR SQLEXCEPTION, vu au chapitre 6). Teste avec 5 000 DH, puis avec 100 DH.
Il manque une parenthèse : INSERT INTO messages (message, created_at doit être (message, created_at). La table messages n'est pas créée dans le résumé : CREATE TABLE messages (id INT PRIMARY KEY AUTO_INCREMENT, message VARCHAR(255), created_at DATETIME);
Si rien ne s'insère, le planificateur est peut-être arrêté. On l'active avec SET GLOBAL event_scheduler = ON; (il faut le droit SYSTEM_VARIABLES_ADMIN ou être root). SHOW EVENTS; liste les événements, DROP EVENT test_event_03; en supprime un.
Ce que tu vas voir (vidéo courte, entièrement sur ce sujet) : le planificateur d'événements de MySQL et la syntaxe de CREATE EVENT : ON SCHEDULE AT pour une exécution unique, EVERY pour une répétition, STARTS, ENDS et DO (en anglais, active les sous-titres français). C'est la création d'un événement de cette section.