Les transactions SQL et les propriétés ACID, expliquées avec des exemples
Une transaction regroupe plusieurs opérations SQL en une seule unité indivisible : soit toutes les opérations réussissent, soit aucune n'est appliquée. C'est essentiel dès qu'une opération métier touche plusieurs tables — comme un virement bancaire.
BEGIN, COMMIT, ROLLBACK
BEGIN;
UPDATE comptes SET solde = solde - 100 WHERE id = 1;
UPDATE comptes SET solde = solde + 100 WHERE id = 2;
COMMIT;Si une erreur survient entre les deux UPDATE (panne, contrainte violée, etc.), on peut annuler l'ensemble avec ROLLBACK plutôt que COMMIT, et le solde des deux comptes reste inchangé.
BEGIN;
UPDATE comptes SET solde = solde - 100 WHERE id = 1;
-- une erreur est détectée ici
ROLLBACK;
-- aucun changement n'est appliquéLes propriétés ACID
| Propriété | Signification |
|---|---|
| Atomicité | Toutes les opérations d'une transaction réussissent ensemble, ou aucune n'est appliquée |
| Cohérence | La base passe d'un état valide à un autre état valide, en respectant toutes les contraintes |
| Isolation | Deux transactions concurrentes ne se perturbent pas l'une l'autre |
| Durabilité | Une fois validée (COMMIT), la transaction survit même à une panne du serveur |
Pourquoi l'isolation est délicate
Quand plusieurs transactions s'exécutent en même temps, plusieurs problèmes peuvent survenir sans un niveau d'isolation adéquat :
- Lecture sale (dirty read) : lire des données modifiées par une transaction non encore validée
- Lecture non répétable : relire la même ligne deux fois dans une transaction et obtenir des valeurs différentes
- Lecture fantôme (phantom read) : une même requête renvoie un nombre de lignes différent à deux moments de la même transaction
Les niveaux d'isolation standards
| Niveau | Protège contre |
|---|---|
| READ UNCOMMITTED | Rien (le plus permissif) |
| READ COMMITTED | Lectures sales |
| REPEATABLE READ | Lectures sales + lectures non répétables |
| SERIALIZABLE | Tous les problèmes ci-dessus (le plus strict) |
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- opérations sensibles à la concurrence
COMMIT;Astuce : un niveau d'isolation plus strict réduit les anomalies mais augmente les risques de blocages (locks) et peut ralentir votre application. Choisissez le niveau le plus permissif qui reste suffisant pour votre cas d'usage.
Points clés à retenir
- Enveloppez toujours les opérations liées (ex. transfert d'argent) dans une transaction explicite
- Gardez les transactions courtes pour limiter les verrous et les conflits
- Testez votre code applicatif avec des
ROLLBACKvolontaires pour vérifier qu'aucune donnée partielle n'est écrite