Transactions MySQL et intégrité des données

Introduction

Dans ce laboratoire, vous allez explorer les transactions MySQL et leur importance pour maintenir l'intégrité des données. Vous apprendrez à utiliser les transactions pour garantir qu'une série d'opérations SQL est traitée comme une unité de travail unique, soit toutes réussissant, soit toutes échouant ensemble.

Le laboratoire couvre le démarrage d'une transaction avec BEGIN, l'insertion de données et la validation de la transaction pour rendre les modifications permanentes, l'annulation d'une mise à jour échouée pour abandonner les modifications, et l'utilisation de points de sauvegarde (savepoints) pour des annulations partielles au sein d'une transaction. Vous vous connecterez à un serveur MySQL, créerez une base de données et une table, puis pratiquerez ces opérations liées aux transactions.

Skills Graph

Démarrer une transaction avec BEGIN

Dans cette étape, vous apprendrez à démarrer une transaction dans MySQL en utilisant l'instruction BEGIN. Les transactions sont un concept fondamental en gestion de base de données, assurant l'intégrité et la cohérence des données. Une transaction est une séquence d'une ou plusieurs opérations SQL traitées comme une unité de travail unique. Soit toutes les opérations au sein de la transaction réussissent, soit aucune d'elles ne réussit.

Avant de plonger dans la partie pratique, comprenons pourquoi les transactions sont importantes. Imaginez que vous transférez de l'argent d'un compte bancaire à un autre. Cela implique deux opérations : débiter le montant du compte de l'émetteur et créditer le montant sur le compte du bénéficiaire. Si la première opération réussit mais la deuxième échoue (par exemple, en raison d'un plantage du système), l'argent serait perdu. Les transactions empêchent de telles incohérences en garantissant que les deux opérations réussissent ensemble ou échouent ensemble.

Pour démarrer une transaction dans MySQL, vous utilisez l'instruction BEGIN (ou son alias START TRANSACTION). Cette instruction signale le début d'une nouvelle transaction. Toutes les instructions SQL suivantes feront partie de cette transaction jusqu'à ce que vous COMMIT les modifications (les rendre permanentes) ou ROLLBACK les modifications (les abandonner).

Commençons par nous connecter au serveur MySQL. Ouvrez votre terminal et exécutez la commande suivante :

mysql -u root -p

Vous serez invité à saisir le mot de passe root. Entrez le mot de passe que vous avez défini lors de l'installation de MySQL. Si vous n'avez pas défini de mot de passe, appuyez simplement sur Entrée.

Maintenant que vous êtes connecté au serveur MySQL, créons une base de données et une table avec lesquelles travailler. Exécutez les instructions SQL suivantes :

CREATE DATABASE IF NOT EXISTS labex_db;
USE labex_db;
CREATE TABLE IF NOT EXISTS accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_number VARCHAR(50) NOT NULL,
    balance DECIMAL(10, 2) NOT NULL
);

Ces commandes créent une base de données nommée labex_db et une table nommée accounts au sein de cette base de données. La table accounts a trois colonnes : id, account_number et balance.

Ensuite, insérons des données initiales dans la table accounts :

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

Maintenant, démarrons une transaction. Exécutez l'instruction suivante :

BEGIN;

Cette commande démarre une nouvelle transaction. Toutes les instructions SQL suivantes feront partie de cette transaction.

Pour vérifier que la transaction a démarré, vous pouvez exécuter une simple instruction de mise à jour. Par exemple, déduisons 100 du compte 'ACC001' :

UPDATE accounts SET balance = balance - 100 WHERE account_number = 'ACC001';

Maintenant, vérifions le solde de 'ACC001' :

SELECT * FROM accounts WHERE account_number = 'ACC001';

Vous devriez voir que le solde de 'ACC001' a été mis à jour à 900.00. Cependant, ces modifications ne sont pas encore permanentes. Elles ne sont visibles qu'au sein de la transaction actuelle. Pour rendre les modifications permanentes, vous devrez COMMIT la transaction, ce que vous apprendrez à la prochaine étape. Si vous fermiez la connexion MySQL maintenant sans valider ou annuler la transaction, les modifications seraient perdues.

Insérer des données et valider la transaction

Dans l'étape précédente, vous avez appris à démarrer une transaction en utilisant l'instruction BEGIN. Maintenant, vous allez apprendre à insérer des données au sein d'une transaction puis à valider la transaction pour rendre les modifications permanentes.

En continuant là où vous en étiez à l'étape précédente, vous devriez toujours être connecté au serveur MySQL et avoir une transaction en cours. Si ce n'est pas le cas, reconnectez-vous au serveur MySQL en utilisant la commande suivante :

mysql -u root -p

Entrez le mot de passe si vous y êtes invité, puis sélectionnez la base de données labex_db :

USE labex_db;

Si vous n'avez pas de transaction en cours, démarrez-en une :

BEGIN;

Maintenant, insérons un nouvel enregistrement dans la table accounts au sein de la transaction actuelle :

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

Cette commande insère un nouveau compte avec le numéro de compte 'ACC003' et un solde de 200,00.

Pour vérifier que les données ont été insérées (au sein de la transaction), exécutez la requête suivante :

SELECT * FROM accounts WHERE account_number = 'ACC003';

Vous devriez voir le nouvel enregistrement inséré. Cependant, comme mentionné précédemment, ces modifications ne sont pas encore permanentes. Elles ne sont visibles qu'au sein de la transaction actuelle.

Pour rendre les modifications permanentes, vous devez COMMIT la transaction. L'instruction COMMIT enregistre toutes les modifications apportées pendant la transaction dans la base de données. Exécutez la commande suivante :

COMMIT;

Cette commande valide la transaction, rendant les données insérées permanentes.

Pour vérifier que les modifications ont été validées, exécutez la même requête à nouveau :

SELECT * FROM accounts WHERE account_number = 'ACC003';

Vous devriez toujours voir le nouvel enregistrement inséré. Maintenant, même si vous vous déconnectez du serveur MySQL et vous reconnectez, l'enregistrement sera toujours là.

Vous pouvez également vérifier les modifications en sélectionnant tous les enregistrements de la table accounts :

SELECT * FROM accounts;

Vous devriez voir les trois comptes : 'ACC001', 'ACC002' et 'ACC003', avec leurs soldes respectifs. Le solde de 'ACC001' devrait être de 900,00 (si vous avez exécuté l'instruction de mise à jour à l'étape précédente et n'avez pas annulé la transaction).

En résumé, cette étape a démontré comment insérer des données au sein d'une transaction puis valider la transaction pour rendre les modifications permanentes. L'instruction COMMIT est essentielle pour garantir que toutes les opérations au sein d'une transaction sont appliquées à la base de données.

Annuler une mise à jour échouée

Dans cette étape, vous apprendrez à annuler une transaction pour annuler les modifications apportées pendant la transaction. Cela est particulièrement utile lorsqu'une erreur se produit ou lorsque vous décidez que les modifications apportées au sein de la transaction ne sont pas souhaitables.

En continuant depuis l'étape précédente, vous devriez toujours être connecté au serveur MySQL. Si ce n'est pas le cas, reconnectez-vous en utilisant la commande suivante :

mysql -u root -p

Entrez le mot de passe si vous y êtes invité, puis sélectionnez la base de données labex_db :

USE labex_db;

Maintenant, démarrons une nouvelle transaction :

BEGIN;

Simulons une mise à jour échouée. Supposons que vous souhaitiez transférer 500 de 'ACC001' à 'ACC004', mais que 'ACC004' n'existe pas. Tout d'abord, essayons de déduire 500 de 'ACC001' :

UPDATE accounts SET balance = balance - 500 WHERE account_number = 'ACC001';

Maintenant, essayons d'ajouter 500 à 'ACC004' :

UPDATE accounts SET balance = balance + 500 WHERE account_number = 'ACC004';

Cette mise à jour n'affectera probablement aucune ligne car 'ACC004' n'existe pas. Vous pouvez vérifier cela en vérifiant le nombre de lignes affectées :

SELECT ROW_COUNT();

Le résultat devrait être 0, indiquant qu'aucune ligne n'a été mise à jour.

Étant donné que la deuxième mise à jour a échoué (ou aurait échoué s'il y avait des contraintes empêchant la mise à jour), vous souhaitez également annuler la première mise à jour. Pour ce faire, vous pouvez ROLLBACK la transaction. L'instruction ROLLBACK annule toutes les modifications apportées pendant la transaction, ramenant la base de données à son état avant le début de la transaction.

Exécutez la commande suivante :

ROLLBACK;

Cette commande annule la transaction, annulant la déduction de 500 de 'ACC001'.

Pour vérifier que l'annulation de la transaction a réussi, vérifiez le solde de 'ACC001' :

SELECT * FROM accounts WHERE account_number = 'ACC001';

Le solde de 'ACC001' devrait être revenu à sa valeur initiale (1000 si vous avez commencé depuis le début, ou 900 si vous n'avez validé que la première mise à jour à l'étape 1).

Vous pouvez également vérifier que 'ACC003' existe toujours et a un solde de 200,00 (si vous avez terminé l'étape 2).

En résumé, cette étape a démontré comment annuler une transaction en utilisant l'instruction ROLLBACK. Cela est essentiel pour gérer les erreurs et garantir la cohérence des données lorsque les opérations au sein d'une transaction échouent.

Utiliser un point de sauvegarde (savepoint) pour un annulation partielle de transaction

Dans cette étape, vous apprendrez à utiliser des points de sauvegarde (savepoints) au sein d'une transaction pour permettre des annulations partielles. Les points de sauvegarde sont des marqueurs au sein d'une transaction vers lesquels vous pouvez revenir, annulant ainsi seulement certaines des modifications apportées pendant la transaction, plutôt que l'ensemble de la transaction.

En continuant depuis l'étape précédente, vous devriez toujours être connecté au serveur MySQL. Si ce n'est pas le cas, reconnectez-vous en utilisant la commande suivante :

mysql -u root -p

Entrez le mot de passe si vous y êtes invité, puis sélectionnez la base de données labex_db :

USE labex_db;

Maintenant, démarrons une nouvelle transaction :

BEGIN;

Tout d'abord, créons un point de sauvegarde nommé savepoint1 :

SAVEPOINT savepoint1;

Cette commande crée un point de sauvegarde nommé savepoint1. Toute modification apportée après ce point de sauvegarde peut être annulée jusqu'à ce point.

Maintenant, mettons à jour le solde de 'ACC001' :

UPDATE accounts SET balance = balance - 200 WHERE account_number = 'ACC001';

Ensuite, créons un autre point de sauvegarde nommé savepoint2 :

SAVEPOINT savepoint2;

Maintenant, insérons un nouvel enregistrement dans la table accounts :

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

Supposons que vous décidiez maintenant d'annuler l'insertion de 'ACC004', mais que vous souhaitiez conserver la mise à jour de 'ACC001'. Vous pouvez revenir au point de sauvegarde savepoint2 :

ROLLBACK TO savepoint2;

Cette commande annule la transaction jusqu'à l'état qu'elle avait lorsque le point de sauvegarde savepoint2 a été créé, annulant ainsi l'insertion de 'ACC004'.

Pour vérifier que l'annulation jusqu'au point de sauvegarde savepoint2 a réussi, vérifiez si 'ACC004' existe :

SELECT * FROM accounts WHERE account_number = 'ACC004';

Cette requête ne devrait retourner aucune ligne, indiquant que 'ACC004' a été supprimé.

Maintenant, vérifions le solde de 'ACC001' :

SELECT * FROM accounts WHERE account_number = 'ACC001';

Le solde de 'ACC001' devrait être mis à jour (diminué de 200).

Si vous êtes satisfait des modifications restantes (la mise à jour de 'ACC001'), vous pouvez maintenant valider la transaction :

COMMIT;

Cette commande valide la transaction, rendant la mise à jour de 'ACC001' permanente.

En résumé, cette étape a démontré comment utiliser des points de sauvegarde au sein d'une transaction pour permettre des annulations partielles. Cela offre un contrôle plus granulaire sur la gestion des transactions, vous permettant d'annuler des modifications spécifiques tout en conservant d'autres.

Résumé

Dans ce laboratoire (lab), vous avez appris les bases des transactions MySQL et leur importance pour maintenir l'intégrité des données. Le laboratoire a commencé par démontrer comment initier une transaction en utilisant l'instruction BEGIN (ou START TRANSACTION). Vous avez ensuite créé une base de données nommée labex_db et une table accounts à l'intérieur pour préparer les opérations de transaction suivantes.

Les étapes initiales se sont concentrées sur la configuration de l'environnement et la compréhension de la syntaxe de base pour démarrer une transaction, posant les bases pour explorer les opérations COMMIT et ROLLBACK dans les étapes suivantes afin de gérer les modifications de données au sein d'une transaction.