Transactions (BEGIN, COMMIT, ROLLBACK)

Une transaction est une série d'opérations SQL qui sont exécutées comme une unité indivisible. Les transactions permettent de garantir que, même en cas d'erreurs ou de pannes, les données restent dans un état cohérent. Elles suivent le modèle ACID : Atomicité, Cohérence, Isolation et Durabilité.

6.1.1 Syntaxe de base des transactions

Dans MySQL, une transaction commence avec START TRANSACTION et se termine soit par COMMIT, soit par ROLLBACK.

  • START TRANSACTION : Débute une transaction.

  • COMMIT : Valide toutes les modifications effectuées durant la transaction.

  • ROLLBACK : Annule toutes les modifications effectuées durant la transaction, ramenant la base de données à son état initial.

Exemple de transaction simple :

Supposons que nous souhaitons transférer de l'argent d'un compte à un autre. Nous voulons nous assurer que les deux opérations (débit et crédit) se produisent dans le cadre d'une même transaction pour garantir l'intégrité des données. 

START TRANSACTION;

UPDATE comptes SET solde = solde - 500 WHERE id_compte = 1;
UPDATE comptes SET solde = solde + 500 WHERE id_compte = 2;

COMMIT;

Dans cet exemple :

  • Si les deux requêtes s'exécutent sans problème, les modifications sont validées avec COMMIT.

  • Si une erreur survient après la première requête mais avant la seconde, un ROLLBACK pourrait être exécuté pour annuler la transaction entière, garantissant ainsi que le compte 1 ne soit pas débité sans que le compte 2 soit crédité.

6.1.2 Utilisation de ROLLBACK :

Si une erreur survient au milieu d'une transaction, vous pouvez annuler toutes les modifications effectuées jusqu'à ce point en utilisant ROLLBACK.

START TRANSACTION;

UPDATE comptes SET solde = solde - 500 WHERE id_compte = 1;

-- Supposons qu'une erreur se produit ici
ROLLBACK;

Dans cet exemple, même si la première requête a réussi, ROLLBACK annule la modification, et la base de données revient à son état initial.

6.1.4 Types de transactions

Il existe plusieurs types de transactions, chacune ayant des caractéristiques spécifiques qui influencent la manière dont les données sont manipulées et isolées des autres transactions.

  1. Transactions explicites :

    • Dans une transaction explicite, vous devez démarrer la transaction avec START TRANSACTION et la terminer explicitement avec COMMIT ou ROLLBACK.

    • Avantage : Plus de contrôle sur le moment où les modifications sont appliquées.

    Exemple : 

START TRANSACTION;
INSERT INTO comptes (nom, solde) VALUES ('Alice', 1000);
UPDATE comptes SET solde = solde - 500 WHERE id_compte = 1;
COMMIT;

  1. Transactions implicites :

  • Certaines opérations comme INSERT, UPDATE, et DELETE sont automatiquement commises sans qu'il soit nécessaire d'utiliser COMMIT. Cela est appelé une transaction implicite.

  • Limitation : Moins de contrôle en cas d'échec car chaque opération est immédiatement appliquée.

  1. Transactions autonomes :

  • MySQL ne prend pas en charge les transactions autonomes dans une transaction plus large. Ce type de transaction permet de commettre des modifications en cours, même si la transaction principale échoue.

6.1.5 Utilisation de SAVEPOINT et ROLLBACK TO SAVEPOINT

Les savepoints permettent de créer des points de restauration intermédiaires dans une transaction. Vous pouvez revenir à un état précédent de la transaction sans annuler l'intégralité des modifications effectuées avant ce point.

  • SAVEPOINT : Crée un point de restauration intermédiaire dans une transaction.

  • ROLLBACK TO SAVEPOINT : Annule les modifications effectuées après le SAVEPOINT spécifié.

  • RELEASE SAVEPOINT : Libère un savepoint.

Exemple avec SAVEPOINT : 

START TRANSACTION;

UPDATE comptes SET solde = solde - 500 WHERE id_compte = 1;
SAVEPOINT apres_debit;

UPDATE comptes SET solde = solde + 500 WHERE id_compte = 2;

-- Supposons qu'une erreur survienne ici
ROLLBACK TO apres_debit;

-- Cette ligne ne sera plus modifiée
UPDATE comptes SET solde = solde + 200 WHERE id_compte = 3;

COMMIT;

6.1.6 Gestion des erreurs dans les transactions

Les transactions peuvent être sujettes à des erreurs en raison de contraintes (ex. contrainte de clé étrangère, contrainte unique) ou de pannes. Il est donc important de gérer ces erreurs proprement.

Voici quelques conseils :

  • Vérification d'erreurs après chaque requête : Si vous développez des scripts SQL ou des programmes utilisant des transactions, assurez-vous de capturer et gérer les erreurs après chaque instruction SQL.

  • Utilisation de ROLLBACK en cas d'erreur : Si une erreur survient, utiliser ROLLBACK permet de garantir que les modifications partielles ne soient pas appliquées.

Exemple de gestion d'erreur : 

START TRANSACTION;

BEGIN TRY
    UPDATE comptes SET solde = solde - 500 WHERE id_compte = 1;
    UPDATE comptes SET solde = solde + 500 WHERE id_compte = 2;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
END TRY;

PreviousTransaction et gestion des verrousNextExercices

Last updated