Introduction
Dans ce laboratoire, vous explorerez la gestion des transactions PostgreSQL, un aspect crucial pour garantir l'intégrité des données. Vous apprendrez à démarrer et à valider des transactions, traitant une série d'opérations comme une seule unité de travail. Vous apprendrez également à annuler les transactions échouées, à définir les niveaux d'isolement et à simuler les verrous avec des mises à jour concurrentes.
Démarrer et valider une transaction
Dans cette étape, vous apprendrez à démarrer et à valider une transaction dans PostgreSQL. Les transactions garantissent l'intégrité des données en traitant une série d'opérations comme une seule unité de travail. Si une opération au sein de la transaction échoue, l'intégralité de la transaction est annulée, évitant ainsi des mises à jour partielles et maintenant la cohérence.
Tout d'abord, connectez-vous à la base de données PostgreSQL en tant qu'utilisateur postgres. Ouvrez un terminal et utilisez la commande suivante :
sudo -u postgres psql
Vous devriez maintenant voir l'invite postgres=#.
Ensuite, créez une table nommée accounts pour démontrer les transactions.
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
balance DECIMAL(10, 2)
);
Insérez des données initiales dans la table accounts.
INSERT INTO accounts (name, balance) VALUES ('Alice', 100.00);
INSERT INTO accounts (name, balance) VALUES ('Bob', 50.00);
Maintenant, démarrons une transaction à l'aide de la commande BEGIN.
BEGIN;
Au sein de la transaction, transférez 20 $ du compte d'Alice vers le compte de Bob.
UPDATE accounts SET balance = balance - 20.00 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 20.00 WHERE name = 'Bob';
Pour rendre ces modifications permanentes, validez la transaction à l'aide de la commande COMMIT.
COMMIT;
Vérifiez que la transaction a réussi en interrogeant la table accounts.
SELECT * FROM accounts;
Vous devriez constater que le solde d'Alice a diminué de 20 $ et que le solde de Bob a augmenté de 20 $.
Enfin, quittez le shell psql.
\q
Annuler une transaction échouée
Dans cette étape, vous apprendrez à annuler une transaction échouée dans PostgreSQL. L'annulation d'une transaction est essentielle lorsqu'une erreur survient au cours d'une série d'opérations sur la base de données, garantissant que la base de données reste dans un état cohérent.
Vous devriez toujours être connecté à la base de données PostgreSQL de l'étape précédente. Sinon, connectez-vous à nouveau en utilisant :
sudo -u postgres psql
Démarrons une nouvelle transaction.
BEGIN;
Au sein de cette transaction, nous allons tenter une opération qui échouera intentionnellement. Essayons d'insérer une clé primaire en double. Trouvons d'abord la prochaine valeur id disponible.
SELECT MAX(id) FROM accounts;
Supposons que le résultat soit 2. Maintenant, essayons d'insérer un nouveau compte avec id = 1, qui existe déjà.
INSERT INTO accounts (id, name, balance) VALUES (1, 'Eve', 25.00);
Cette commande entraînera une erreur ERROR: duplicate key value violates unique constraint "accounts_pkey".
Puisqu'une erreur s'est produite au sein de la transaction, annulez la transaction pour supprimer toutes les modifications apportées. Utilisez la commande ROLLBACK.
ROLLBACK;
Vérifiez que l'annulation a réussi en interrogeant la table accounts.
SELECT * FROM accounts;
Vous devriez constater que la table ne contient toujours que les comptes d'Alice et de Bob avec leurs soldes de la fin de l'étape 1. L'opération INSERT échouée a été correctement annulée.
Enfin, quittez le shell psql.
\q
Configurer et tester les niveaux d'isolement
Dans cette étape, vous apprendrez les niveaux d'isolement des transactions dans PostgreSQL et comment les définir et les tester. Les niveaux d'isolement contrôlent le degré auquel les transactions concurrentes sont isolées les unes des autres. Des niveaux d'isolement plus élevés offrent une meilleure protection contre la corruption des données mais peuvent réduire la concurrence.
Vous devriez toujours être connecté à la base de données PostgreSQL de l'étape précédente. Sinon, connectez-vous à nouveau en utilisant :
sudo -u postgres psql
Ouvrez deux fenêtres de terminal distinctes. Dans chaque terminal, connectez-vous à la base de données PostgreSQL en tant qu'utilisateur postgres. Vous devriez avoir deux invites postgres=#.
Terminal 1 :
sudo -u postgres psql
Terminal 2 :
sudo -u postgres psql
Dans le Terminal 1, définissez le niveau d'isolement sur READ COMMITTED (bien que ce soit la valeur par défaut, nous le définirons explicitement pour la démonstration).
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Ensuite, démarrez une transaction.
BEGIN;
Dans le Terminal 1, lisez le solde d'Alice.
SELECT balance FROM accounts WHERE name = 'Alice';
Notez le solde. Maintenant, dans le Terminal 2, démarrez une transaction et mettez à jour le solde d'Alice.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
UPDATE accounts SET balance = 90.00 WHERE name = 'Alice';
COMMIT;
Dans le Terminal 1, lisez à nouveau le solde d'Alice.
SELECT balance FROM accounts WHERE name = 'Alice';
Étant donné que le niveau d'isolement est READ COMMITTED, vous verrez le solde mis à jour (90,00) validé par le Terminal 2.
Maintenant, testons le niveau d'isolement REPEATABLE READ. Dans le Terminal 1, annulez la transaction en cours et définissez le niveau d'isolement sur REPEATABLE READ.
ROLLBACK;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
Dans le Terminal 1, lisez à nouveau le solde d'Alice.
SELECT balance FROM accounts WHERE name = 'Alice';
Notez le solde. Maintenant, dans le Terminal 2, démarrez une transaction et mettez à nouveau à jour le solde d'Alice.
BEGIN;
UPDATE accounts SET balance = 100.00 WHERE name = 'Alice';
COMMIT;
Dans le Terminal 1, lisez à nouveau le solde d'Alice.
SELECT balance FROM accounts WHERE name = 'Alice';
Étant donné que le niveau d'isolement est REPEATABLE READ, vous verrez toujours le solde initial au moment du démarrage de la transaction, même si le Terminal 2 a validé une nouvelle valeur.
Enfin, dans le Terminal 1, validez la transaction.
COMMIT;
Maintenant, si vous lisez à nouveau le solde d'Alice dans le Terminal 1, vous verrez la dernière valeur validée (100,00).
Quittez les deux shells psql.
\q
Simuler les verrous avec des mises à jour simultanées
Dans cette étape, vous simulerez des verrous avec des mises à jour simultanées dans PostgreSQL. Les verrous sont des mécanismes utilisés pour empêcher les transactions simultanées d'interférer les unes avec les autres, garantissant ainsi l'intégrité des données.
Vous devriez toujours être connecté à la base de données PostgreSQL de l'étape précédente. Sinon, connectez-vous à nouveau en utilisant :
sudo -u postgres psql
Ouvrez deux fenêtres de terminal distinctes. Dans chaque terminal, connectez-vous à la base de données PostgreSQL en tant qu'utilisateur postgres. Vous devriez avoir deux invites postgres=#.
Terminal 1 :
sudo -u postgres psql
Terminal 2 :
sudo -u postgres psql
Dans le Terminal 1, démarrez une transaction et mettez à jour le solde d'Alice. Crucialement, utilisez SELECT ... FOR UPDATE pour verrouiller la ligne.
BEGIN;
SELECT balance FROM accounts WHERE name = 'Alice' FOR UPDATE;
Cette commande récupère le solde d'Alice et place un verrou sur la ligne, empêchant d'autres transactions de la modifier jusqu'à ce que cette transaction soit validée ou annulée.
Dans le Terminal 2, démarrez une transaction et essayez de mettre à jour le solde d'Alice.
BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE name = 'Alice';
Remarquez que cette commande dans le Terminal 2 semble bloquer. Ceci est dû au fait qu'elle attend que le verrou détenu par le Terminal 1 soit libéré.
Maintenant, dans le Terminal 1, validez la transaction.
COMMIT;
Après la validation de la transaction dans le Terminal 1, la commande UPDATE dans le Terminal 2 se poursuivra.
Dans le Terminal 2, validez la transaction.
COMMIT;
Maintenant, dans l'un ou l'autre terminal, interrogez la table accounts pour vérifier les modifications.
SELECT * FROM accounts;
Vous devriez constater que le solde d'Alice a été mis à jour par la transaction du Terminal 2 après que le Terminal 1 a libéré le verrou.
Enfin, quittez les deux shells psql.
\q
Cet exemple démontre comment SELECT ... FOR UPDATE peut être utilisé pour simuler des verrous et empêcher les mises à jour simultanées d'interférer les unes avec les autres. Sans le verrou, les deux transactions pourraient lire le même solde initial et appliquer leurs mises à jour en fonction de cette valeur, ce qui entraînerait une mise à jour perdue.
Résumé
Dans ce laboratoire, vous avez appris à gérer les transactions dans PostgreSQL. Vous avez commencé par vous connecter à la base de données PostgreSQL à l'aide de psql et en créant une table d'exemple accounts avec des données initiales.
Vous vous êtes ensuite concentré sur la démonstration de l'utilisation des transactions. Vous avez appris à initier une transaction à l'aide de la commande BEGIN, à effectuer plusieurs opérations sur la base de données (mettre à jour les soldes d'Alice et de Bob), puis à valider la transaction à l'aide de la commande COMMIT pour rendre les modifications permanentes. Ceci a illustré le principe fondamental des transactions : traiter une série d'opérations comme une seule unité atomique. Vous avez également appris à annuler les transactions et à définir les niveaux d'isolement. Enfin, vous avez simulé des verrous avec des mises à jour simultanées pour comprendre comment prévenir la corruption des données.
