Comprendre les Transactions en SQL

Les transactions sont un concept clé dans la gestion des bases de données relationnelles. Elles permettent de regrouper plusieurs opérations SQL en une seule unité de travail, garantissant que toutes les opérations réussissent ensemble ou échouent ensemble, ce qui est crucial pour maintenir l’intégrité des données.

Qu’est-ce qu’une transaction ?

Une transaction en SQL est une séquence d’une ou plusieurs opérations (telles que des INSERT, UPDATE, DELETE, etc.) qui sont exécutées comme une unité indivisible. Si toutes les opérations d’une transaction réussissent, les modifications sont enregistrées dans la base de données. Si l’une des opérations échoue, toutes les modifications effectuées par la transaction sont annulées.

Les transactions suivent le modèle ACID, qui garantit les propriétés suivantes :

Atomicité : Une transaction est atomique, ce qui signifie que soit toutes les opérations de la transaction sont exécutées avec succès, soit aucune ne l’est. Si une partie de la transaction échoue, la transaction entière est annulée.
Cohérence : Une transaction assure que la base de données passe d’un état cohérent à un autre. Après la transaction, toutes les règles d’intégrité des données sont respectées.
Isolation : Les modifications faites par une transaction ne sont pas visibles pour les autres transactions jusqu’à ce que la transaction soit validée (committed). Cela empêche les transactions concurrentes d’interférer entre elles.
Durabilité : Une fois qu’une transaction est validée, ses effets sont permanents, même en cas de panne système.

Comment fonctionne une transaction ?

Démarrer une transaction

La plupart des systèmes de gestion de bases de données démarrent implicitement une transaction pour chaque requête SQL. Cependant, vous pouvez explicitement démarrer une transaction en utilisant la commande BEGIN ou START TRANSACTION :

BEGIN;

Effectuer des opérations dans une transaction

Après avoir démarré une transaction, vous pouvez exécuter plusieurs opérations SQL :

BEGIN;

INSERT INTO comptes (numero, solde) VALUES (123, 1000);
UPDATE comptes SET solde = solde - 200 WHERE numero = 123;
INSERT INTO transactions (compte_id, montant, type) VALUES (123, 200, 'retrait');

Valider une transaction

Une fois que toutes les opérations d’une transaction ont été exécutées avec succès, vous pouvez valider (commit) la transaction pour enregistrer les modifications dans la base de données :

COMMIT;

La commande COMMIT confirme toutes les opérations effectuées pendant la transaction. Les modifications deviennent alors permanentes et visibles par d’autres transactions.

Annuler une transaction

Si une erreur se produit ou si vous décidez de ne pas valider la transaction, vous pouvez l’annuler en utilisant la commande ROLLBACK :

ROLLBACK;

La commande ROLLBACK annule toutes les modifications effectuées depuis le début de la transaction, restaurant la base de données à l’état où elle se trouvait avant le début de la transaction.

Exemple complet d’une transaction

Supposons que vous gériez une base de données bancaire, et que vous souhaitiez transférer 500 € d’un compte à un autre. Voici comment cela pourrait être fait dans une transaction :

BEGIN;

UPDATE comptes SET solde = solde - 500 WHERE numero = 12345; -- Retirer 500 € du compte 12345
UPDATE comptes SET solde = solde + 500 WHERE numero = 67890; -- Ajouter 500 € au compte 67890

-- Si toutes les opérations réussissent, valider la transaction
COMMIT;

-- Si une erreur se produit (par exemple, solde insuffisant), annuler la transaction
-- ROLLBACK;

Si les deux UPDATE réussissent, la transaction est validée et les changements sont enregistrés. Si l’une des opérations échoue (par exemple, en raison d’un solde insuffisant), vous pouvez annuler la transaction avec ROLLBACK, et aucun des changements ne sera appliqué.

Pourquoi utiliser des transactions ?

Les transactions sont essentielles pour plusieurs raisons :

Assurer l’intégrité des données : Les transactions garantissent que toutes les opérations sont traitées comme une seule unité. Cela empêche les situations où une partie des modifications est enregistrée, mais pas les autres, ce qui pourrait laisser la base de données dans un état incohérent.
Gérer les erreurs : En cas d’erreur, les transactions permettent d’annuler les opérations effectuées pour que la base de données reste dans un état cohérent.
Support des opérations complexes : Lorsqu’une série d’opérations doit être exécutée ensemble (comme un transfert d’argent entre comptes), les transactions assurent que ces opérations sont effectuées en toute sécurité et entièrement.
Isolation des transactions : Les transactions garantissent que les modifications apportées par une transaction ne sont pas visibles par les autres transactions tant qu’elles ne sont pas validées, empêchant ainsi les interactions indésirables entre transactions concurrentes.