Le problème que les transactions résolvent
Un virement retire d'un compte et ajoute à un autre. Deux instructions. Si la seconde échoue après la première, l'argent a disparu.
Une transaction regroupe plusieurs instructions en une seule opération indivisible : soit toutes s'appliquent, soit aucune.
BEGIN;
UPDATE compte SET solde = solde - 100 WHERE id = 1;
UPDATE compte SET solde = solde + 100 WHERE id = 2;
COMMIT;
Si quelque chose échoue entre les deux, ROLLBACK annule l'ensemble et la base revient exactement à son état de départ.
Le geste qui sauve, avant tout UPDATE
C'est le conseil le plus rentable de cette leçon, et il ne coûte rien.
Écrivez toujours le SELECT avant l'UPDATE, avec exactement le même WHERE :
SELECT * FROM client WHERE statut = 'inactif'; -- combien de lignes ?
UPDATE client SET statut = 'archive' WHERE statut = 'inactif';
Si le SELECT renvoie 3 000 lignes alors que vous en attendiez 12, vous venez d'éviter un incident majeur, gratuitement.
Le WHERE oublié
L'erreur la plus coûteuse de tout SQL, et la plus banale :
UPDATE client SET statut = 'archive'; -- toute la table
DELETE FROM commande; -- toute la table
Deux protections concrètes.
Travaillez dans une transaction explicite. Lancez BEGIN, exécutez, vérifiez le nombre de lignes affectées, puis COMMIT ou ROLLBACK. Tant que le COMMIT n'est pas passé, l'erreur est réparable.
Activez le mode qui refuse les mises à jour sans clé dans votre outil quand il le propose.
Ce qu'une transaction ne fait pas
Elle ne protège pas d'une mauvaise donnée : une transaction validée avec une valeur fausse est une erreur définitivement enregistrée.
Elle ne remplace pas une sauvegarde : ROLLBACK n'est possible que tant que la transaction est ouverte.
Et une transaction laissée ouverte est nuisible : elle conserve des verrous, bloque d'autres écritures, et sur certains moteurs empêche le nettoyage interne. Une transaction s'ouvre et se ferme dans la même minute.
Les verrous, en deux phrases
Deux transactions qui modifient la même ligne attendent l'une l'autre. C'est normal et souhaitable.
Deux transactions qui modifient les mêmes lignes dans un ordre différent peuvent se bloquer mutuellement. Le moteur en tue une et signale l'erreur. La parade est d'ordonner les mises à jour de la même façon partout, par exemple toujours par identifiant croissant.
À retenir
Un SELECT avec le même WHERE avant chaque UPDATE. Travaillez dans une transaction explicite et vérifiez le nombre de lignes avant de valider. Fermez vite. Et une transaction n'est pas une sauvegarde.