Comment BEGIN, COMMIT et ROLLBACK regroupent plusieurs modifications en une seule opération dans PostgreSQL

Introduction aux transactions dans PostgreSQL

Les transactions sont fondamentales pour des opérations de base de données fiables. Elles permettent de regrouper plusieurs instructions SQL en une seule unité logique qui s'exécute comme 'tout ou rien'. Cela est crucial pour maintenir l'intégrité des données, notamment dans les systèmes où les erreurs pourraient entraîner des états inconsistants, comme les transferts bancaires.

PostgreSQL implémente les transactions selon les principes ACID : atomicité, cohérence, isolation et durabilité. Comme indiqué dans la documentation, 'une transaction regroupe plusieurs étapes en une seule opération indivisible' - si une erreur survient, aucun changement intermédiaire n'affecte la base de données.

Syntaxe de BEGIN, COMMIT et ROLLBACK

Pour gérer explicitement une transaction, trois commandes sont utilisées : BEGIN, COMMIT et ROLLBACK. BEGIN démarre un bloc de transaction. Toutes les opérations suivantes sont exécutées dans ce contexte transactionnel. COMMIT rend toutes les modifications permanentes. ROLLBACK annule toutes les modifications effectuées depuis BEGIN.

Exemple : le transfert de 100 $ d'Alice vers Bob est réalisé comme une seule transaction :

L'exemple suppose une table accounts avec un nom unique et un solde numérique, contenant déjà des lignes pour Alice et Bob. L'exemple avec point de sauvegarde suppose également une ligne pour Wally. Exécutez les instructions dans une base de données de démonstration contrôlée. Dans le code applicatif, vérifiez les comptages de lignes affectées, les fonds suffisants et les contraintes nécessaires ; un COMMIT réussi seul ne prouve pas que le transfert métier prévu a eu lieu.

BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob';
COMMIT;

Atomicité des modifications

L'atomicité signifie qu'une transaction réussit entièrement ou est complètement annulée. Les états intermédiaires ne sont pas conservés. Par exemple, si les fonds sont déduits du compte d'Alice mais qu'une erreur survient avant de créditer Bob, toute la transaction est annulée, restaurant le solde d'Alice.

Les enregistrements WAL doivent atteindre un stockage durable avant que les pages de données modifiées soient écrites. Les pages de données peuvent être écrites avant COMMIT ; l'atomicité ne signifie pas que les modifications attendent en mémoire jusqu'au commit. La récupération après panne utilise le journal, tandis que les règles de visibilité empêchent les autres sessions de voir les modifications non committées des tables.

Transactions automatiques par défaut

Hors d'un bloc de transaction explicite, PostgreSQL exécute chaque instruction dans sa propre transaction implicite. Les bibliothèques clientes peuvent démarrer automatiquement une transaction ou exposer une option autocommit, donc vérifiez les paramètres de connexion avant de supposer que deux instructions sont indépendantes.

Ce mode est pratique pour les opérations simples, mais lorsque plusieurs commandes doivent s'exécuter de manière cohérente, BEGIN et COMMIT explicites sont nécessaires pour éviter une application partielle des modifications.

Gestion des erreurs : annulation des modifications

Si une condition survient pendant une transaction qui la rend invalide (par exemple, solde négatif), ROLLBACK peut être émis. Toutes les modifications effectuées depuis le début de la transaction sont annulées.

Par exemple, si après avoir déduit des fonds d'Alice, son solde devient négatif, la transaction peut être annulée, restaurant ainsi l'état initial de la base de données.

Visibilité des modifications pour les autres transactions

Les modifications effectuées dans une transaction ne sont pas visibles pour les autres sessions avant COMMIT. Cela garantit l'isolation. Les autres utilisateurs continuent de voir l'état précédent des tables.

Seulement après COMMIT, toutes les modifications deviennent visibles simultanément, empêchant les scénarios où une partie de l'opération (par exemple, la déduction) serait visible alors que l'autre (par exemple, le dépôt) ne l'est pas.

Niveaux d'isolation des transactions

Les noms SQL sont Read Uncommitted, Read Committed, Repeatable Read et Serializable. PostgreSQL traite Read Uncommitted comme Read Committed, donc les quatre noms fournissent trois comportements distincts. Read Committed est le niveau par défaut ; il peut être modifié par configuration de session ou de base de données.

Read Committed permet de voir uniquement les données committées avant le début de la requête. Repeatable Read fournit une vue cohérente au moment du début de la transaction. Serializable simule une exécution séquentielle mais peut nécessiter la gestion des erreurs de sérialisation.

Utilisation des points de sauvegarde pour une annulation partielle

SAVEPOINT permet de créer un point dans une transaction à partir duquel on peut revenir sans annuler toute la transaction. Cela est utile dans la logique complexe où seule une partie de l'opération doit être annulée.

Par exemple, si après avoir transféré de l'argent à Bob, on découvre que Wally devait recevoir cet argent, la transaction peut revenir à un point de sauvegarde et rediriger les fonds.

BEGIN;
UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob';
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Wally';
COMMIT;