Cours MariaDB / Valider ou annuler une transaction avec COMMIT et ROLLBACK

Une transaction regroupe plusieurs modifications de données dans une même unité de validation. Avec InnoDB, commencez par START TRANSACTION, puis terminez par COMMIT si le résultat est correct, ou par ROLLBACK pour annuler les changements encore non validés ; toutes ces instructions doivent utiliser la même connexion.

Définir les changements qui doivent réussir ensemble

Cet exercice modifie deux soldes fictifs pour comparer validation et annulation. L’objectif est de ne pas conserver un état où une seule des deux modifications a été appliquée. Il s’agit d’un exemple de transaction, pas d’un système de paiement complet : un véritable traitement financier exige aussi un registre d’opérations, la gestion des demandes répétées, des autorisations et une piste d’audit.

Les vérifications des lignes avant UPDATE et DELETE appliquent cette logique à des modifications concrètes. Ici, gardez une seule connexion et un petit jeu de données pour observer clairement chaque étape.

Commande ou réglage Rôle Limite à vérifier
START TRANSACTION Commencer une transaction dans cette connexion Ne pas la lancer sans connaître la transaction déjà ouverte
COMMIT Valider les changements Un ROLLBACK ultérieur ne les annule plus
ROLLBACK Annuler les changements non validés Ne couvre pas les autres connexions ni toutes les opérations de structure
autocommit Régler la validation automatique hors transaction explicite Sa valeur seule ne décrit pas l’état de la transaction courante

La référence START TRANSACTION, COMMIT et ROLLBACK décrit ces limites. MariaDB ne traite pas un nouveau START TRANSACTION comme une transaction imbriquée indépendante : commencer une nouvelle transaction peut valider celle qui était en cours. Terminez donc le travail précédent consciemment avant de démarrer cet exercice.

Préparer une table InnoDB et ses valeurs initiales

Connectez-vous au serveur d’apprentissage avec course_user, qui doit pouvoir créer, lire, modifier et supprimer les objets de cet exercice dans shopcourse. Vérifiez le serveur et la base avec SELECT VERSION(), DATABASE();, puis les noms existants avec SHOW TABLES;. Si accounts_35_lab existe déjà, examinez sa définition et ses lignes avant de décider si son contenu peut être supprimé. Ne remplacez pas des données à conserver.

Le guide du client MariaDB et des fichiers SQL UTF-8 explique la connexion. Vous pouvez enregistrer le bloc de préparation suivant en UTF-8 dans C:/mariadb-lab/c35-preparation.sql, après avoir créé le dossier, puis lancer SOURCE C:/mariadb-lab/c35-preparation.sql; dans le client MariaDB avec le chemin réel. Ne mettez pas tous les blocs de ce guide dans un fichier exécuté sans pause : les contrôles de lignes ci-dessous imposent de décider si vous continuez ou annulez.

USE shopcourse;
CREATE TABLE accounts_35_lab (
 account_id INT PRIMARY KEY,balance DECIMAL(12,2) NOT NULL CHECK(balance>=0)
) ENGINE=InnoDB;
INSERT INTO accounts_35_lab VALUES(1,10000),(2,5000);

Contrôlez ensuite le mode de validation, la présence d’une transaction et le moteur :

SELECT @@autocommit,@@in_transaction;
SHOW CREATE TABLE accounts_35_lab;

Le moteur attendu est InnoDB et les soldes initiaux sont 10000.00 pour l’identifiant 1 et 5000.00 pour l’identifiant 2. Une nouvelle connexion standard utilise généralement autocommit=1 et n’a pas de transaction active avant ces essais ; vérifiez les valeurs réelles de votre session. Un START TRANSACTION explicite reste utile même si autocommit vaut 1. Ne changez pas le réglage global du serveur pour ce guide.

Terminez la création et l’insertion initiale avant les essais de transaction. Continuez ensuite dans la même connexion : un outil qui ouvre une nouvelle connexion ou valide automatiquement chaque étape modifie le sens de la procédure.

Contrôler les deux modifications avant de les annuler

Le bloc suivant ouvre la transaction, lit et verrouille les deux lignes avec FOR UPDATE, puis tente de diminuer le solde 1 de 3000 uniquement s’il est suffisant. Vérifiez aussi que la lecture initiale contient bien les deux identifiants attendus.

START TRANSACTION;
SELECT account_id,balance FROM accounts_35_lab
WHERE account_id IN (1,2) ORDER BY account_id FOR UPDATE;
UPDATE accounts_35_lab SET balance=balance-3000
WHERE account_id=1 AND balance>=3000;
SELECT ROW_COUNT() AS debit_rows;

Continuez seulement si debit_rows vaut 1. Si le résultat vaut 0 ou si une erreur apparaît, exécutez ROLLBACK; dans cette connexion et arrêtez l’essai. N’exécutez pas l’UPDATE suivant. Un résultat nul peut notamment signaler un identifiant absent ou un solde insuffisant ; il ne faut pas augmenter le second solde malgré cet échec.

Lorsque le premier contrôle réussit, augmentez le solde 2 et vérifiez immédiatement le nombre de lignes :

UPDATE accounts_35_lab SET balance=balance+3000 WHERE account_id=2;
SELECT ROW_COUNT() AS credit_rows;

credit_rows doit également valoir 1. Sinon, exécutez ROLLBACK pour annuler l’ensemble des changements non validés et arrêtez-vous. La fonction ROW_COUNT() porte sur l’instruction précédente : faites le contrôle juste après chaque UPDATE, avant une autre requête susceptible de modifier cette information.

Si les deux contrôles valent 1, lisez les soldes dans la même connexion :

SELECT account_id,balance FROM accounts_35_lab ORDER BY account_id;

Depuis les valeurs initiales, vous devez voir 7000.00 et 8000.00, pour un total inchangé de 15000.00. Ces valeurs sont encore non validées. Ce sont des instructions pour un exercice surveillé : une application doit traduire les contrôles de résultats et d’erreurs en véritables branches de traitement, et ne pas simplement envoyer toutes les instructions à la suite.

Pour cet essai, annulez maintenant :

ROLLBACK;
SELECT account_id,balance FROM accounts_35_lab ORDER BY account_id;

Les soldes reviennent à 10000.00 et 5000.00. Si ce n’est pas le cas, vérifiez la connexion utilisée, un éventuel COMMIT intermédiaire ou une validation automatique de l’outil. Voir ses propres écritures dans la transaction ne signifie pas qu’elles ont déjà été validées.

Répéter les contrôles et valider avec COMMIT

Après l’annulation, reprenez le bloc qui commence par START TRANSACTION, puis le bloc d’augmentation du second solde, et enfin la lecture des deux soldes. Contrôlez de nouveau debit_rows et credit_rows : chacun doit valoir 1, et les valeurs doivent être 7000.00 et 8000.00. Si un contrôle échoue, annulez et arrêtez-vous comme précédemment.

Cette fois, lorsque tous les contrôles réussissent, exécutez le COMMIT suivant à la place du ROLLBACK de l’essai précédent :

COMMIT;
SELECT account_id,balance FROM accounts_35_lab ORDER BY account_id;

Les soldes validés sont 7000.00 et 8000.00. Un ROLLBACK ordinaire exécuté ensuite ne rétablit pas les valeurs initiales. Pour refaire cet exercice depuis le début, utilisez volontairement sa procédure de nettoyage puis de préparation ; sur des données réelles, ajouter le montant opposé n’équivaut pas nécessairement à une restauration complète.

Reconnaître les limites des erreurs et des validations implicites

Une erreur dans une instruction n’annule pas systématiquement toute la transaction. Le comportement dépend de l’erreur et du traitement effectué par le pilote. Lorsqu’une opération de l’ensemble échoue, l’application doit choisir explicitement la sortie appropriée, libérer les ressources et ne pas continuer comme si tout avait réussi. Gardez la transaction courte ; évitez d’attendre une saisie utilisateur tout en conservant des verrous.

Les instructions de définition des données, ou DDL (Data Definition Language), ont d’autres règles. Des opérations comme CREATE TABLE, ALTER TABLE, DROP TABLE ou TRUNCATE TABLE peuvent provoquer une validation implicite ; elles n’appartiennent pas à cet essai d’annulation des données. La liste des instructions provoquant un COMMIT implicite détaille les cas et exceptions.

Ne transposez pas non plus le comportement InnoDB à un moteur non transactionnel comme MyISAM. Enfin, un appel d’API externe ou un transfert de fichier ne s’annule pas simplement en envoyant ROLLBACK à la base. Définissez la frontière de l’opération réellement couverte par la transaction.

Terminer le travail ouvert avant une nouvelle préparation

Si l’essai s’est arrêté en cours de route, exécutez ROLLBACK dans la connexion concernée et contrôlez @@in_transaction. Avec les réglages ordinaires de cet exercice, vous attendez 0 après la fin de transaction. Examinez les réglages de session si l’état diffère ; ne lancez pas une nouvelle transaction pour masquer une transaction encore ouverte.

Quand la table ne contient aucune donnée à conserver, le bloc suivant permet de supprimer uniquement l’objet de cet exercice. Contrôlez son nom et la base avant de le lancer. DROP TABLE supprime structure et lignes et ne constitue pas une annulation réversible ; gardez ce nettoyage hors de l’essai de transaction.

ROLLBACK;
DROP TABLE shopcourse.accounts_35_lab;