Introduction
Dans ce laboratoire, vous allez découvrir la gestion des transactions dans PostgreSQL, un aspect essentiel pour garantir l’intégrité des données. Vous apprendrez à démarrer et à valider des transactions afin de traiter une série d’opérations comme une seule unité de travail. Vous apprendrez également à annuler les transactions qui échouent, à définir les niveaux d’isolation et à simuler des verrous avec des mises à jour concurrentes.
Démarrer et valider une transaction
Dans cette étape, vous allez apprendre à 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 de la transaction échoue, PostgreSQL annule toute la transaction. Cela évite les mises à jour partielles et préserve la cohérence des données.
Commencez par vous connecter à la base de données PostgreSQL en tant qu’utilisateur postgres. Ouvrez un terminal et exécutez la commande suivante :
sudo -u postgres psql
L’invite postgres=# doit maintenant s’afficher.
Créez ensuite une table nommée accounts pour illustrer le fonctionnement des transactions.
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
balance DECIMAL(10, 2)
);
Insérez quelques 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);
Démarrez maintenant une transaction avec la commande BEGIN.
BEGIN;
Dans cette transaction, transférez 20 $ du compte d’Alice vers celui 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 changements permanents, validez la transaction avec la commande COMMIT.
COMMIT;
Vérifiez que la transaction a réussi en interrogeant la table accounts.
SELECT * FROM accounts;
Le solde d’Alice doit avoir diminué de 20 $ et celui de Bob augmenté de 20 $.
Pour terminer, quittez le shell psql.
\q
Annuler une transaction qui a échoué
Dans cette étape, vous allez apprendre à annuler une transaction qui a échoué dans PostgreSQL. Il est essentiel d’annuler une transaction lorsqu’une erreur survient au cours d’une série d’opérations sur la base de données : cela permet de préserver la cohérence de la base.
Vous devriez toujours être connecté à la base de données PostgreSQL depuis l’étape précédente. Si ce n’est pas le cas, reconnectez-vous avec la commande suivante :
sudo -u postgres psql
Démarrez une nouvelle transaction.
BEGIN;
Dans cette transaction, vous allez tenter une opération qui échouera volontairement : insérer une clé primaire en double. Commencez par rechercher la prochaine valeur disponible pour id.
SELECT MAX(id) FROM accounts;
Supposons que le résultat soit 2. Essayez maintenant d’insérer un compte avec id = 1, une valeur qui existe déjà.
INSERT INTO accounts (id, name, balance) VALUES (1, 'Eve', 25.00);
Cette commande génère l’erreur ERROR: duplicate key value violates unique constraint "accounts_pkey".
Une erreur s’étant produite dans la transaction, annulez-la avec la commande ROLLBACK pour abandonner les changements effectués.
ROLLBACK;
Vérifiez que l’annulation a réussi en interrogeant la table accounts.
SELECT * FROM accounts;
La table doit toujours contenir uniquement les comptes d’Alice et de Bob, avec les soldes qu’ils avaient à la fin de l’étape 1. L’opération INSERT qui a échoué a bien été annulée.
Pour terminer, quittez le shell psql.
\q
Définir et tester les niveaux d’isolation
Dans cette étape, vous allez découvrir les niveaux d’isolation des transactions dans PostgreSQL et apprendre à les définir et à les tester. Les niveaux d’isolation déterminent dans quelle mesure les transactions concurrentes sont isolées les unes des autres. Un niveau d’isolation plus élevé protège davantage contre la corruption des données, mais peut réduire le degré de concurrence.
Vous devriez toujours être connecté à la base de données PostgreSQL depuis l’étape précédente. Si ce n’est pas le cas, reconnectez-vous avec la commande suivante :
sudo -u postgres psql
Ouvrez deux fenêtres de terminal distinctes. Dans chacune, connectez-vous à la base de données PostgreSQL en tant qu’utilisateur postgres. Vous devez voir deux invites postgres=#.
Terminal 1 :
sudo -u postgres psql
Terminal 2 :
sudo -u postgres psql
Dans le Terminal 1, démarrez une transaction, puis définissez son niveau d’isolation sur READ COMMITTED (le niveau par défaut). Exécutez SET TRANSACTION dans la transaction, avant sa première requête.
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Dans le Terminal 1, lisez le solde d’Alice.
SELECT balance FROM accounts WHERE name = 'Alice';
Notez le solde. Dans le Terminal 2, démarrez maintenant une transaction et mettez à jour le solde d’Alice.
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
UPDATE accounts SET balance = 90.00 WHERE name = 'Alice';
COMMIT;
Dans le Terminal 1, lisez de nouveau le solde d’Alice.
SELECT balance FROM accounts WHERE name = 'Alice';
Comme le niveau d’isolation est READ COMMITTED, vous verrez le solde mis à jour (90.00) et validé par le Terminal 2.
Testez maintenant le niveau d’isolation REPEATABLE READ. Dans le Terminal 1, annulez la transaction en cours, démarrez-en une nouvelle et définissez son niveau d’isolation sur REPEATABLE READ avant de lire des données.
ROLLBACK;
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Dans le Terminal 1, lisez de nouveau le solde d’Alice.
SELECT balance FROM accounts WHERE name = 'Alice';
Notez le solde. Dans le Terminal 2, démarrez maintenant une transaction et mettez de nouveau à jour le solde d’Alice.
BEGIN;
UPDATE accounts SET balance = 100.00 WHERE name = 'Alice';
COMMIT;
Dans le Terminal 1, lisez de nouveau le solde d’Alice.
SELECT balance FROM accounts WHERE name = 'Alice';
Comme le niveau d’isolation est REPEATABLE READ, vous verrez toujours 90.00, le solde du snapshot établi par la première requête de cette transaction, même si le Terminal 2 a validé la valeur 100.00.
Pour terminer, validez la transaction dans le Terminal 1.
COMMIT;
Lisez maintenant de nouveau le solde d’Alice dans le Terminal 1. La transaction étant terminée, vous verrez la dernière valeur validée (100.00).
SELECT balance FROM accounts WHERE name = 'Alice';
Quittez les deux shells psql.
\q
Simuler des verrous avec des mises à jour concurrentes
Dans cette étape, vous allez simuler des verrous avec des mises à jour concurrentes dans PostgreSQL. Les verrous empêchent les transactions concurrentes d’interférer les unes avec les autres et contribuent ainsi à préserver l’intégrité des données.
Vous devriez toujours être connecté à la base de données PostgreSQL depuis l’étape précédente. Si ce n’est pas le cas, reconnectez-vous avec la commande suivante :
sudo -u postgres psql
Ouvrez deux fenêtres de terminal distinctes. Dans chacune, connectez-vous à la base de données PostgreSQL en tant qu’utilisateur postgres. Vous devez voir 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. Utilisez impérativement SELECT ... FOR UPDATE pour verrouiller la ligne.
BEGIN;
SELECT balance FROM accounts WHERE name = 'Alice' FOR UPDATE;
Cette commande renvoie le solde d’Alice et verrouille la ligne. Les autres transactions ne peuvent pas la modifier avant que cette transaction soit validée ou annulée.
Dans le Terminal 2, démarrez une transaction et tentez de mettre à jour le solde d’Alice.
BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE name = 'Alice';
Vous remarquerez que la commande du Terminal 2 semble bloquée. Elle attend que le Terminal 1 libère le verrou.
Dans le Terminal 1, validez maintenant la transaction.
COMMIT;
Une fois la transaction validée dans le Terminal 1, la commande UPDATE du Terminal 2 s’exécute.
Dans le Terminal 2, validez la transaction.
COMMIT;
Dans l’un ou l’autre terminal, interrogez maintenant la table accounts pour vérifier les changements.
SELECT * FROM accounts;
Le solde d’Alice doit refléter la mise à jour effectuée par la transaction du Terminal 2, après la libération du verrou par le Terminal 1.
Pour terminer, quittez les deux shells psql.
\q
Cet exemple montre comment utiliser SELECT ... FOR UPDATE pour simuler des verrous et empêcher des mises à jour concurrentes d’interférer les unes avec les autres. Sans verrou, les deux transactions pourraient lire le même solde initial et effectuer leurs mises à jour à partir de cette valeur, ce qui entraînerait une perte de mise à jour.
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 avec psql, puis par créer une table accounts d’exemple contenant des données initiales.
Vous avez ensuite mis en pratique les transactions : démarrer une transaction avec 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 avec la commande COMMIT pour rendre les changements permanents. Vous avez ainsi découvert le principe fondamental des transactions : traiter une série d’opérations comme une seule unité atomique. Vous avez également appris à annuler des transactions et à définir des niveaux d’isolation. Enfin, vous avez simulé des verrous avec des mises à jour concurrentes afin de comprendre comment prévenir la corruption des données.
