data / sql

Transactions et ACID

Leçon 91 exercice

Explication

Ce que vous allez apprendre

Regrouper plusieurs instructions en une transaction atomique avec BEGIN/COMMIT/ROLLBACK
Comprendre les quatre garanties ACID : Atomicité, Cohérence, Isolation, Durabilité
Utiliser SAVEPOINT pour annuler une partie seulement d'une transaction
Identifier pourquoi une transaction trop longue bloque d'autres utilisateurs
Reconnaître qu'une instruction seule, hors BEGIN, est déjà sa propre transaction (autocommit)

Dans quel contexte ?

Une application bancaire doit exécuter un virement entre deux comptes de la table comptes : débiter le compte source de 100 euros puis créditer le compte destinataire du même montant. Si le serveur plante entre les deux UPDATE, sans transaction, l'argent disparaît purement et simplement. C'est exactement le risque que cette leçon élimine grâce au mécanisme de transaction.

D'abord, un scénario catastrophe concret

Imagine un virement de 100 euros d'un compte A vers un compte B. Cela demande DEUX opérations distinctes : débiter A, puis créditer B.

Que se passe-t-il si ça plante entre les deux ?

Sans protection, un plantage du serveur juste entre le débit et le crédit produirait un compte A débité et un compte B jamais crédité : 100 euros disparus dans la nature.

La solution : regrouper en un bloc indivisible

Une transaction regroupe plusieurs instructions en un seul bloc : soit toutes réussissent ensemble, soit aucune n'est appliquée. C'est exactement ce qui empêche le scénario catastrophe ci-dessus.

Le vocabulaire à connaître en premier

BEGIN ouvre la transaction, COMMIT la valide définitivement, ROLLBACK l'annule entièrement comme si rien ne s'était passé. Sans BEGIN explicite, chaque instruction est automatiquement sa propre mini-transaction.

Une fois ce mécanisme compris, on peut nommer ses garanties : ACID

Ce mot désigne quatre promesses. Atomicité : tout ou rien, comme pour le virement. Cohérence : les contraintes vues en leçon 8 restent toujours respectées, même en cas d'échec en cours de route.

Les deux dernières lettres d'ACID

Isolation : deux transactions simultanées ne se voient jamais mutuellement dans un état à moitié terminé. Durabilité : une fois validée par COMMIT, une transaction survit même à une coupure de courant immédiate.

Lettre ACID	Garantie	Ce qui se passerait sans elle
Atomicité	Tout ou rien	Un virement à moitié appliqué
Cohérence	Les contraintes restent respectées	Un `CHECK`/`FOREIGN KEY` violé après COMMIT
Isolation	Pas d'état intermédiaire visible	Une lecture voit un solde "à moitié" débité
Durabilité	Persistance après COMMIT	Une donnée validée perdue lors d'un crash

Un piège à éviter une fois les bases posées

Une transaction retient des verrous sur les lignes qu'elle modifie jusqu'à son COMMIT. Une transaction qui traîne trop longtemps bloque donc potentiellement d'autres utilisateurs pendant tout ce temps.

Piège fréquent

Ouvrir une transaction avec BEGIN, puis attendre une action utilisateur (par exemple une confirmation dans l'interface) avant de faire COMMIT, retient des verrous sur les lignes concernées pendant tout ce temps d'attente - potentiellement plusieurs minutes. D'autres transactions qui tentent de modifier les mêmes lignes restent bloquées jusqu'au COMMIT ou au timeout.

Bonne pratique

Garde toujours tes transactions aussi courtes que possible : prépare toutes les données nécessaires AVANT d'ouvrir le BEGIN, puis exécute les instructions SQL et referme immédiatement par COMMIT ou ROLLBACK.

La règle professionnelle, et la suite

Une transaction doit rester aussi courte que possible. Ce sujet du blocage entre transactions concurrentes sera approfondi dans la leçon sur le verrouillage, plus loin dans ce cours.

Commandes & code

Transactions et ACID

-- Une transaction regroupe plusieurs instructions en une unité atomique
BEGIN;

UPDATE comptes SET solde = solde - 100 WHERE id = 1;  -- débit
UPDATE comptes SET solde = solde + 100 WHERE id = 2;  -- crédit

-- Si tout va bien, on valide : les deux updates deviennent visibles ensemble
COMMIT;

-- Si une erreur survient, on annule TOUT (aucun changement partiel)
BEGIN;
UPDATE comptes SET solde = solde - 100 WHERE id = 1;
-- ... une erreur applicative détectée ici ...
ROLLBACK;   -- annule le débit, le solde du compte 1 est inchangé

-- SAVEPOINT : point de restauration partiel à l'intérieur d'une transaction
BEGIN;
UPDATE stocks SET quantite = quantite - 5 WHERE produit_id = 1;
SAVEPOINT avant_paiement;

UPDATE comptes SET solde = solde - 200 WHERE id = 1;
-- si le paiement échoue, on n'annule QUE cette partie
ROLLBACK TO SAVEPOINT avant_paiement;

COMMIT;  -- valide la mise à jour du stock, malgré l'annulation du paiement

-- Propriétés ACID en résumé opérationnel :
-- Atomicity  : tout ou rien (le ROLLBACK ci-dessus)
-- Consistency: les contraintes (CHECK, FK, UNIQUE) restent respectées après COMMIT
-- Isolation  : les transactions concurrentes ne se voient pas leurs états intermédiaires
-- Durability : après COMMIT, la donnée survit à un crash (écrite sur disque / WAL)

-- Exemple d'atomicité indispensable : transfert bancaire
CREATE OR REPLACE FUNCTION transferer(compte_source INT, compte_dest INT, montant NUMERIC)
RETURNS VOID AS $$
BEGIN
    UPDATE comptes SET solde = solde - montant WHERE id = compte_source;
    IF (SELECT solde FROM comptes WHERE id = compte_source) < 0 THEN
        RAISE EXCEPTION 'Solde insuffisant';  -- déclenche un ROLLBACK automatique
    END IF;
    UPDATE comptes SET solde = solde + montant WHERE id = compte_dest;
END;
$$ LANGUAGE plpgsql;

-- Transaction en lecture seule (optimisation, empêche les écritures accidentelles)
BEGIN TRANSACTION READ ONLY;
SELECT * FROM rapports_financiers;
COMMIT;

-- Autocommit : par défaut, chaque instruction hors BEGIN...COMMIT est sa propre transaction
INSERT INTO logs (message) VALUES ('événement');  -- committé immédiatement

Résumé

BEGIN / COMMIT / ROLLBACK délimitent une transaction ; sans BEGIN, chaque requête est auto-committée.
ACID = Atomicité, Cohérence, Isolation, Durabilité : le socle de fiabilité des SGBD relationnels.
SAVEPOINT permet un rollback partiel sans annuler toute la transaction.
Une transaction doit rester courte : elle retient des verrous et bloque potentiellement d'autres transactions.

Exercices pratiques

1 disponible

Mission : sécuriser un virement bancaire qui peut planter à tout moment

Objectif : Encadrer un virement entre deux comptes dans une transaction ACID, avec un rollback partiel maîtrisé.

Contexte

Une application bancaire doit virer 100 euros du compte 1 vers le compte 2 dans la table comptes. Le serveur qui héberge cette application a déjà planté une fois en plein milieu d'un virement lors d'un test de charge, ce qui a débité un compte sans créditer l'autre sur l'environnement de test. Tu dois garantir que cela ne puisse plus arriver en production.

Résoudre l’exercice →