Transactions SQL : COMMIT et ROLLBACK

Une transaction SQL regroupe des instructions que le SGBD valide toutes ensemble avec COMMIT, ou annule toutes avec ROLLBACK. Ce chapitre montre comment l'écrire sous MySQL, ce que garantissent les propriétés ACID, et comment les niveaux d'isolation et les verrous protègent les accès simultanés. Les exemples modifient la base d'exemple parc.

Pourquoi une transaction

L'exemple classique est le virement bancaire : débiter un compte, créditer l'autre. Si le serveur tombe entre les deux, l'argent a disparu. Les deux opérations doivent réussir ensemble ou échouer ensemble.

La base parc a le même besoin. Clôturer un ticket, c'est enregistrer la dernière intervention et passer le ticket au statut résolu. Une intervention enregistrée sur un ticket qui reste « en cours », ou un ticket clos sans trace de l'intervention, rend la base incohérente.

Une transaction est une suite d'instructions que le SGBD traite comme une seule : soit toutes ses modifications sont validées, soit aucune.

START TRANSACTION, COMMIT et ROLLBACK

START TRANSACTION;

INSERT INTO intervention (id_ticket, id_technicien, date_intervention, duree_min, compte_rendu)
VALUES (6, 12, '2026-10-28 09:00:00', 25, 'Ventilateur remplacé');

UPDATE ticket
SET statut = 'resolu', date_cloture = '2026-10-28 09:30:00'
WHERE id_ticket = 6;

COMMIT;

• START TRANSACTION (ou BEGIN) ouvre la transaction ;
• COMMIT valide : les modifications deviennent définitives et visibles des autres sessions ;
• ROLLBACK annule tout ce qui a été fait depuis START TRANSACTION.

Si une instruction échoue, MySQL annule cette instruction, mais la transaction reste ouverte avec les instructions précédentes en attente. C'est à toi, ou à ton programme, de décider : corriger et continuer, ou annuler.

START TRANSACTION;

UPDATE ticket
SET statut = 'resolu', date_cloture = '2026-10-28 09:30:00'
WHERE id_ticket = 6;

INSERT INTO intervention (id_ticket, id_technicien, date_intervention, duree_min, compte_rendu)
VALUES (6, 12, '2026-10-28 09:00:00', 0, 'Ventilateur remplacé');
-- ERROR 3819 (HY000): Check constraint 'ck_intervention_duree' is violated.

ROLLBACK;

SELECT statut FROM ticket WHERE id_ticket = 6;

Le ROLLBACK a aussi annulé l'UPDATE qui avait réussi.

Par défaut, MySQL est en mode autocommit (SELECT @@autocommit; renvoie 1) : chaque instruction hors d'un START TRANSACTION est une transaction à elle seule, validée immédiatement. SET autocommit = 0; change ce comportement pour la session : chaque instruction ouvre alors une transaction qui attend un COMMIT explicite. Les clients graphiques ont souvent un bouton pour basculer entre les deux modes.

Autre différence avec PostgreSQL : une erreur à l'intérieur d'une transaction la rend inutilisable, et toutes les instructions suivantes sont refusées (current transaction is aborted) jusqu'au ROLLBACK. MySQL, lui, laisse la transaction continuer. Dans les deux cas, la bonne pratique est la même : sur une erreur, ton programme annule (erreurs et transactions côté code).

SAVEPOINT

Un point de sauvegarde permet d'annuler une partie de la transaction seulement :

START TRANSACTION;

UPDATE logiciel SET nb_licences = 10 WHERE id_logiciel = 5;

SAVEPOINT avant_installation;

INSERT INTO installation (id_poste, id_logiciel, date_installation)
VALUES (9, 5, '2026-10-28');

ROLLBACK TO SAVEPOINT avant_installation;   -- annule l'INSERT, garde l'UPDATE

COMMIT;

Après le COMMIT, Odoo a 10 licences et le poste 9 n'a toujours pas Odoo. RELEASE SAVEPOINT nom supprime un point de sauvegarde sans rien annuler. Les points de sauvegarde servent surtout dans les procédures stockées, pour traiter une erreur sur une étape sans perdre les étapes précédentes.

Les propriétés ACID

Une transaction garantit quatre propriétés, résumées par l'acronyme ACID :

Les anomalies de concurrence

Plusieurs utilisateurs travaillent en même temps sur la même base. Sans isolation, plusieurs anomalies peuvent survenir. Chaque tableau se lit de haut en bas, dans l'ordre chronologique.

Lecture sale (dirty read) : B lit une modification que A n'a pas validée, et que A annule ensuite.

Lecture non reproductible : B lit deux fois la même ligne dans sa transaction et obtient deux valeurs, parce que A l'a modifiée et validée entre les deux.

Lecture fantôme : B exécute deux fois la même requête et voit apparaître une ligne nouvelle (un « fantôme »), insérée et validée par A entre les deux. Par exemple, SELECT COUNT(*) FROM ticket WHERE statut = 'ouvert' donne 3, puis 4.

Mise à jour perdue (lost update) : deux sessions lisent la même valeur, calculent chacune une nouvelle valeur, et la seconde écriture écrase la première. Deux techniciens complètent la durée de l'intervention 9, enregistrée à 15 minutes :

Décision sur une lecture périmée (en anglais write skew, une anomalie de la même famille) : chaque session lit des données, décide d'après ce qu'elle a lu, puis écrit. Rien n'est écrasé, mais chacune a décidé sur une lecture que l'autre a rendue fausse entre-temps. C'est le scénario de la dernière licence Odoo :

Les niveaux d'isolation

La norme SQL définit quatre niveaux d'isolation. Plus le niveau est élevé, moins il y a d'anomalies possibles, et plus les transactions risquent de s'attendre.

InnoDB utilise REPEATABLE READ par défaut, PostgreSQL et SQL Server READ COMMITTED. Dans InnoDB, en REPEATABLE READ, la première lecture ordinaire d'une transaction prend une photo cohérente de la base (un snapshot) : toutes les lectures ordinaires suivantes de la transaction voient cette photo, même si d'autres sessions valident des modifications entre-temps. Les lectures non reproductibles et les fantômes sont donc évités pour les SELECT ordinaires. En READ COMMITTED, chaque lecture prend une nouvelle photo.

SELECT @@transaction_isolation;                          -- REPEATABLE-READ
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- pour la session
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;            -- pour la prochaine transaction seulement

Verrous, FOR UPDATE et interblocages

InnoDB pose des verrous sur les lignes qu'une transaction modifie. Une autre transaction qui veut modifier les mêmes lignes attend que la première se termine par COMMIT ou ROLLBACK. Les lectures ordinaires, elles, ne sont pas bloquées : elles lisent la photo cohérente.

Pour lire une ligne et empêcher les autres de la modifier avant toi, utilise une lecture verrouillante :

• SELECT ... FOR UPDATE pose un verrou exclusif, comme le ferait un UPDATE : les autres transactions qui veulent modifier la ligne ou la lire avec FOR UPDATE attendent ;
• SELECT ... FOR SHARE pose un verrou partagé : les autres peuvent lire, pas modifier. MariaDB écrit LOCK IN SHARE MODE, l'ancienne forme, que MySQL accepte encore.

Une lecture verrouillante lit toujours la dernière version validée, pas la photo. Elle n'a de sens que dans une transaction : en autocommit, le verrou serait libéré aussitôt. L'installation d'Odoo, sûre même si deux techniciens cliquent en même temps :

START TRANSACTION;

-- 1. Verrouiller la ligne du logiciel : un second technicien attendra ici
SELECT nb_licences FROM logiciel WHERE id_logiciel = 5 FOR UPDATE;

-- 2. Compter les installations, une fois le verrou obtenu
SELECT COUNT(*) FROM installation WHERE id_logiciel = 5;

-- 3. Si COUNT(*) < nb_licences : installer, sinon ROLLBACK
INSERT INTO installation (id_poste, id_logiciel, date_installation)
VALUES (9, 5, '2026-10-28');

COMMIT;

L'ordre compte. La photo d'une transaction REPEATABLE READ est prise à sa première lecture ordinaire : en verrouillant d'abord, tu garantis que le comptage a lieu après la validation de l'éventuel autre technicien. Le test « reste-t-il une licence ? » se fait dans le programme, entre l'étape 2 et l'étape 3 (exemple en PHP), ou dans une procédure stockée.

MySQL 8 ajoute deux options : FOR UPDATE NOWAIT échoue immédiatement si la ligne est verrouillée, et FOR UPDATE SKIP LOCKED ignore les lignes verrouillées, utile pour qu'un technicien prenne « le prochain ticket libre » dans une file sans attendre les autres.

Un interblocage (deadlock) se produit quand deux transactions s'attendent mutuellement :

InnoDB détecte le cycle immédiatement et annule l'une des deux transactions, en général celle qui a modifié le moins de lignes : elle reçoit l'erreur 1213, Deadlock found when trying to get lock; try restarting transaction. L'autre continue. Une attente sans interblocage, elle, s'arrête au bout de 50 secondes (variable innodb_lock_wait_timeout) avec l'erreur 1205, Lock wait timeout exceeded.

Exercices

START TRANSACTION;

UPDATE poste SET id_employe = 14 WHERE id_poste = 14;

INSERT INTO intervention (id_ticket, id_technicien, date_intervention, duree_min, compte_rendu)
VALUES (7, 11, '2026-10-28 14:00:00', 40, 'Poste installé, Sage configuré');

INSERT INTO installation (id_poste, id_logiciel, date_installation)
VALUES (14, 4, '2026-10-28');

UPDATE ticket
SET statut = 'resolu', date_cloture = '2026-10-28 14:45:00', id_poste = 14
WHERE id_ticket = 7;

COMMIT;

START TRANSACTION;
UPDATE ticket SET priorite = 'haute' WHERE id_ticket = 6;
SAVEPOINT s1;
UPDATE ticket SET priorite = 'haute' WHERE id_ticket = 12;
SAVEPOINT s2;
UPDATE ticket SET priorite = 'haute' WHERE id_ticket = 14;
ROLLBACK TO SAVEPOINT s1;
UPDATE ticket SET priorite = 'critique' WHERE id_ticket = 14;
COMMIT;
SELECT id_ticket, priorite FROM ticket WHERE id_ticket IN (6, 12, 14) ORDER BY id_ticket;

START TRANSACTION;
DELETE FROM installation WHERE id_poste = 13;
CREATE TABLE poste_archive LIKE poste;
DELETE FROM poste WHERE id_poste = 13;
ROLLBACK;