Transactions MySQL et intégrité des données

Introduction

Dans ce laboratoire, vous apprendrez à utiliser les transactions MySQL pour garantir l'intégrité des données. Les transactions regroupent une série d'opérations SQL en une seule unité de travail, soit tout ou rien. Ceci est essentiel pour des tâches telles que les transferts financiers, où toutes les étapes doivent s'achever avec succès.

Vous vous entraînerez à démarrer une transaction avec BEGIN, à rendre les modifications permanentes avec COMMIT, à annuler les modifications avec ROLLBACK, et à effectuer des annulations partielles à l'aide de SAVEPOINT. À la fin de ce laboratoire, vous comprendrez comment gérer les opérations de base de données de manière sûre et fiable.

Configurer la base de données et les tables

Avant de travailler avec les transactions, vous devez configurer une base de données et une table. Dans cette étape, vous allez créer une base de données nommée bank_db et une table accounts pour simuler des opérations bancaires.

Tout d'abord, ouvrez le terminal depuis votre bureau.

Connectez-vous au serveur MySQL en tant qu'utilisateur root. Cet environnement de laboratoire ne nécessite pas de mot de passe pour l'utilisateur root.

sudo mysql -u root

Une fois connecté, vous verrez l'invite MySQL (mysql>), indiquant que vous pouvez maintenant exécuter des commandes SQL.

Créez la base de données bank_db et basculez vers celle-ci pour la session en cours. La clause IF NOT EXISTS empêche une erreur si la base de données existe déjà.

CREATE DATABASE IF NOT EXISTS bank_db;
USE bank_db;

Ensuite, créez la table accounts. Cette table stockera les informations de compte, y compris un ID, un numéro de compte et un solde.

CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_number VARCHAR(20) UNIQUE NOT NULL,
    balance DECIMAL(10, 2) NOT NULL
);

Maintenant, insérez deux comptes d'exemple dans la table accounts. Ceux-ci serviront de données de départ.

INSERT INTO accounts (account_number, balance) VALUES
('ACC001', 1000.00),
('ACC002', 500.00);

Pour confirmer que la table et les données ont été créées correctement, exécutez la requête suivante :

SELECT * FROM accounts;

Vous devriez voir les deux comptes que vous venez d'insérer. La sortie ressemblera à ceci :

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         | 1000.00 |
|  2 | ACC002         |  500.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

Avec la base de données et la table préparées, vous êtes prêt à découvrir les transactions.

Créer une transaction réussie avec BEGIN et COMMIT

Une transaction est une séquence d'opérations effectuées comme une seule unité logique. Pour rendre les modifications permanentes, vous devez les COMMIT. Dans cette étape, vous allez simuler un transfert bancaire réussi de ACC001 vers ACC002.

Vous devriez toujours être dans le shell MySQL de l'étape précédente.

Tout d'abord, démarrez une nouvelle transaction en utilisant la commande BEGIN. Toutes les commandes suivantes feront partie de cette transaction jusqu'à ce que vous exécutiez COMMIT ou ROLLBACK.

BEGIN;

Maintenant, effectuez les deux opérations pour le transfert : déduisez 100 $ de ACC001 et ajoutez 100 $ à ACC002.

UPDATE accounts SET balance = balance - 100.00 WHERE account_number = 'ACC001';
UPDATE accounts SET balance = balance + 100.00 WHERE account_number = 'ACC002';

À ce stade, les modifications ne sont visibles que dans votre session actuelle. Vous pouvez vérifier l'état temporaire des comptes :

SELECT * FROM accounts;

La sortie affichera les soldes mis à jour, mais ils ne sont pas encore enregistrés de manière permanente.

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  900.00 |
|  2 | ACC002         |  600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

Étant donné que les deux opérations ont réussi, vous pouvez rendre les modifications permanentes en validant la transaction.

COMMIT;

La commande COMMIT enregistre toutes les modifications apportées pendant la transaction. Le transfert est maintenant terminé et visible par toutes les autres connexions à la base de données. Vous pouvez le vérifier en interrogeant à nouveau la table. Les soldes resteront à 900 $ et 600 $.

Annuler une transaction avec ROLLBACK

Parfois, une opération au sein d'une transaction échoue, ou vous pouvez décider de l'annuler. La commande ROLLBACK annule toutes les modifications apportées pendant la transaction en cours, restaurant la base de données à son état antérieur au début de la transaction.

Commençons une nouvelle transaction pour ajouter un nouveau compte.

BEGIN;

Maintenant, insérez un nouveau compte ACC003 dans la table accounts.

INSERT INTO accounts (account_number, balance) VALUES ('ACC003', 150.00);

Vérifiez la table pour voir le nouveau compte dans le cadre de la transaction.

SELECT * FROM accounts;

Vous verrez trois comptes listés.

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  900.00 |
|  2 | ACC002         |  600.00 |
|  3 | ACC003         |  150.00 |
+----+----------------+---------+
3 rows in set (0.00 sec)

Maintenant, imaginez que vous réalisez que ce compte n'aurait pas dû être ajouté. Au lieu de valider (COMMIT), vous pouvez annuler toute la transaction en utilisant ROLLBACK.

ROLLBACK;

Cette commande annule l'opération INSERT. Pour le vérifier, interrogez à nouveau la table accounts.

SELECT * FROM accounts;

Le compte ACC003 a disparu, et la table est revenue à l'état dans lequel elle se trouvait avant que vous ne tapiez BEGIN.

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  900.00 |
|  2 | ACC002         |  600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

ROLLBACK est un outil essentiel pour maintenir l'intégrité des données en cas d'erreurs.

Utiliser SAVEPOINT pour les annulations partielles

Pour les transactions longues, vous pourriez vouloir annuler seulement une partie du travail au lieu de la transaction entière. SAVEPOINT vous permet de définir un marqueur au sein d'une transaction vers lequel vous pourrez revenir plus tard.

Commençons une nouvelle transaction.

BEGIN;

Premièrement, mettez à jour le solde de ACC001, en l'augmentant de 50 $.

UPDATE accounts SET balance = balance + 50.00 WHERE account_number = 'ACC001';

Maintenant, créez un point de sauvegarde (savepoint). Cela agit comme un marque-page pour l'état actuel de la transaction.

SAVEPOINT after_update;

Ensuite, effectuez une autre opération : insérez un nouveau compte ACC004.

INSERT INTO accounts (account_number, balance) VALUES ('ACC004', 300.00);

Supposons que l'insertion de ACC004 était une erreur, mais que la mise à jour de ACC001 était correcte. Vous pouvez revenir au point de sauvegarde (savepoint) pour annuler uniquement l'instruction INSERT.

ROLLBACK TO after_update;

Cette commande ramène la transaction à l'état dans lequel elle se trouvait lorsque vous avez créé le point de sauvegarde after_update. L'insertion de ACC004 est annulée, mais la mise à jour de ACC001 reste.

Interrogez la table pour confirmer. ACC004 devrait avoir disparu, mais le solde de ACC001 devrait être mis à jour.

SELECT * FROM accounts;

La sortie devrait montrer ACC001 avec un solde de 950,00 $ et aucun ACC004.

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  950.00 |
|  2 | ACC002         |  600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

Puisque vous souhaitez conserver la mise à jour de ACC001, vous pouvez maintenant valider (COMMIT) la partie restante de la transaction.

COMMIT;

Vous avez utilisé avec succès un point de sauvegarde (savepoint) pour effectuer un rollback partiel. Vous pouvez maintenant quitter le shell MySQL.

exit;

Résumé

Dans ce laboratoire, vous avez appris les bases des transactions MySQL pour maintenir l'intégrité des données. Vous avez pratiqué le cycle de vie complet d'une transaction, depuis le démarrage d'une transaction avec BEGIN jusqu'à sa finalisation avec COMMIT ou son annulation avec ROLLBACK.

Vous avez simulé avec succès un virement bancaire, en vous assurant que plusieurs opérations étaient traitées comme une seule unité atomique. Vous avez également appris à utiliser SAVEPOINT pour obtenir un contrôle plus fin sur les transactions complexes en permettant des rollbacks partiels. Ces compétences sont cruciales pour développer des applications de base de données robustes et fiables où la cohérence des données est primordiale.