Transactions et niveaux d'isolation

L'objectif de ce TP est de manipuler le mécanisme de transactions de PostgreSQL et d'observer les effets des différents mécanismes d'isolation.

Nous allons exécuter plusieurs transactions en parallèle, il faudra donc avoir deux terminaux ouverts en même temps.

Mécanisme de base BEGIN .. COMMIT / ROLLBACK

Pour commencer, nous allons exécuter des transactions simples pour constater le fonctionnement de base.

• Exécuter la transaction suivante et constater l'effet du ROLLBACK;

psql -p 5432
CREATE DATABASE transactions;
\c transactions
BEGIN;
CREATE TABLE greetings (id numeric, msg varchar);
INSERT INTO greetings VALUES (1, 'hello');
ROLLBACK;
SELECT * FROM greetings;

Pour que les changements d'une transaction soient effectivement appliqués, il faut faire un appel à COMMIT

• Exécuter la même transaction, terminer par COMMIT au lieu de ROLLBACK
• Exécuter la même transaction, provoquer une erreur dans une des commandes

SAVEPOINTS

Il est possible de placer un "point de sauvegarde" dans une transaction pour revenir en arrière au cas où quelque chose se passerait mal dans la suite de la transaction.

• Démarrer une transaction, créer une table et quelques données
• Placer un SAVEPOINT

SAVEPOINT after_init_table;

• Supprimer la table
• Revenir au SAVEPOINT

ROLLBACK TO SAVEPOINT after_init_table;

• Constater que la table est de retour, avec ses données
• COMMIT

Isolation read committed

Pour comprendre les différents niveaux d'isolation nous allons travailler dans deux terminaux différents. Les commandes à exécuter seront précédées de Session 1 ou Session 2 pour indiquer la session concernée.

• Se connecter à la base dans les deux sessions

Session 1

psql -p 5432
\c transactions

Session 2

psql -p 5432
\c transactions

• Démarrer une transaction en mode READ COMMITTED et faire une premier SELECT. On doit retrouver la ligne exécutée précédemment.

Session 1

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM greetings;
 id |  msg
----+-------
  1 | hello
(1 row)

• Démarrer une transaction dans l'autre session et insérer une ligne (ne pas terminer la transaction)

Session 2

BEGIN;
INSERT INTO greetings VALUES (2, 'bonjour');

• Session 1 répéter le SELECT précédent
• Session 2 terminer la transaction par un COMMIT
• Session 1 répéter le SELECT à nouveau

Ces quelques commandes nous permettent de constater le mode de fonctionnement du mode READ COMMITTED. Une transaction ne voit pas les actions d'une transaction concurrente jusqu'à ce que la transaction concurrente effectue un COMMIT.

Conflit de mise à jour

• Session 1 Démarrer une nouvelle transaction en READ COMMITTED
• Session 2 Démarrer une nouvelle transaction
• Session 1 Mettre à jour une ligne, par exemple celle dont l'identifiant est 2

UPDATE greetings SET msg = 'hi' WHERE id = 2;

• Session 2 Faire une mise à jour incompatible avec celle de la session 1. Noter que la commande ne rend pas la main

UPDATE greetings SET msg = 'hola' WHERE id = 2;

• Session 1 Terminer la transaction avec COMMIT
• Session 1 Afficher le contenu de la table
• Session 2 Terminer la transaction avec COMMIT
• Session 1 Afficher le contenu de la table

On voit ici le mécanisme d'isolation des transactions en action. Notre transaction dans la Session 2 est restée bloquée au moment où elle a voulu faire une opération en conflit avec une transaction qui a démarré avant elle. Elle a cependant pu se terminer correctement une fois que la transaction qui avait la priorité s'est terminée.

Anomalie de mise à jour

• Session 1 créer une nouvelle table et insérer deux valeurs telles que ci-dessous :

CREATE TABLE scores (player numeric, score numeric);
INSERT INTO scores VALUES (1, 9), (2, 10);
SELECT * FROM scores;
 player | score
--------+-------
      1 |     9
      2 |    10
(2 rows)

• Session 1 Démarrer une nouvelle transaction en READ COMMITTED
• Session 2 Démarrer une nouvelle transaction
• Session 1 Modifier la valeur de toutes les lignes en ajoutant +1 au score

UPDATE scores SET score = score + 1;

• Session 2 Afficher le contenu de la table

transactions=# SELECT * FROM scores;
 player | score
--------+-------
      1 |     9
      2 |    10
(2 rows)

• Session 2 Supprimer la ligne dont le score est 10

DELETE FROM scores WHERE score = 10;

À ce niveau là, la session 2 est bloquée car la transaction dans la session 1 possède un verrou sur la ligne mise à jour.

• Avant de continuer, faites un pronostic : que va t-il se passer suite au COMMIT de la Session 1 ?

• Session 1 Terminer la transaction avec un COMMIT

• Constater ce qui se produit au niveau de la session 2, puis la terminer.

On peut considérer ici qu'il s'agit d'une anomalie de mise à jour car pendant toute la durée de la transaction 2 il a existé une ligne dont l'attribut score vaut 10.

Pour expliquer pourquoi le DELETE de la session 2 n'a supprimé aucune ligne, il faut bien comprendre l'ordre dans lequel sont faites les opération.

DELETE FROM scores WHERE score = 10; 1. On lit la table scores pour chercher une ligne telle que score = 10 2. On trouve la ligne suivante :

 player | score
--------+-------
      2 |    10

1. La suppression ne peut pas être faite car cette ligne est vérouillée par la transaction dans la Session 1. On garde notre ligne de côté en attendant.
2. La transaction de la Session 1 se termine
3. On réévalue le prédicat sur la ligne mise à jour.

 player | score
--------+-------
      2 |    11

1. Le prédicat score = 10 n'est plus vérifié, la ligne n'est donc plus à mettre à jour
2. Aucune ligne n'est mise à jour

Isolation repeatable read

Le principe du niveau d'isolation REPEATABLE READ est qu'une transaction voit les données telles qu'elles étaient au début de la transaction, quoi que les autres transactions fassent à côté.

Il est simple de mettre ce principe en évidence :

• Session 1 démarrer une transaction en mode REPEATABLE READ
• Session 1 afficher le contenu de la table scores

SELECT * FROM scores;
 player | score
--------+-------
      1 |    10
      2 |    11
(2 rows)

• Session 2 démarrer une transaction, insérer une ligne dans scores, COMMIT

BEGIN;

INSERT INTO scores VALUES (3, 1);

SELECT * FROM scores;
 player | score
--------+-------
      1 |    10
      2 |    11
      3 |     1
(3 rows)

COMMIT;

• Session 1 afficher le contenu de la table

SELECT * FROM scores;
 player | score
--------+-------
      1 |    10
      2 |    11
(2 rows)

Conflit de mise à jour

Contrairement aux transactions READ COMMITTED, les transactions REPEATABLE READ ne peuvent pas effectuer de modifications sur des lignes déjà modifiées par une transaction ayant démarrée avant elles.

Pour illustrer ce principe, nous allons refaire les mêmes manipulations qu`à la section Anomalie de mise à jour précédente.

• Pour ce faire, remettre la table scores à l'état initial

DELETE FROM scores;
INSERT INTO scores VALUES (1, 9), (2, 10);

• Répéter les actions de la section Anomalie de mise à jour (UPDATE de toutes les lignes dans S1, DELETE dans S2, COMMIT dans S1)
• Observer le message qui s'affiche dans Session 2

ERROR:  could not serialize access due to concurrent update

Ce message signifie que PostgreSQL ne peut pas "serialiser" (disposer sous forme de série) les opérations. Le mode REPEATABLE READ nous a protégé de l'anomalie de mise à jour, il faudra en revanche gérer la possibilité de ces erreurs au niveau de l'application (mécanisme de rejeu de la transaction).

Anomalie de mise à jour

• Créer une nouvelle table et insérer des données :

CREATE TABLE category (cat numeric, value numeric)
INSERT INTO category VALUES (1, 10), (1, 20), (2, 100), (2, 200);
SELECT * FROM category

 cat | value
-----+-------
   1 |    10
   1 |    20
   2 |   100
   2 |   200

• Session 1 démarrer une transaction en mode REPEATABLE READ
• Session 2 démarrer une transaction en mode REPEATABLE READ
• Session 1 insérer une nouvelle ligne tel que cat = 2 et value = somme des value pour cat = 1

INSERT INTO category VALUES (2, (SELECT SUM(value) FROM categorie WHERE cat=1));

• Session 2 insérer une nouvelle ligne tel que cat = 1 et value = somme des value pour cat = 2

INSERT INTO category VALUES (1, (SELECT SUM(value) FROM categorie WHERE cat=2));

• COMMIT les deux transactions

En faisant ça nous avons créé une anomalie en créant une dépendance d'ordre entre les deux transactions. En effet, après les deux commit, aucune des lignes nouvellement créées ne contient vraiment la somme des valeurs pour chaque catégorie (alors que c'était l'objectif).

Isolation serializable

Pour que l'anomalie de mise à jour que nous venons de créer ne se produise pas, il faudrait détecter la dépendance cyclique entre les deux transactions. En effet, la transaction 1 a besoin de voir le résultat des mises à jour de la transaction 2 pour renvoyer un résultat juste, et la transaction 2 a besoin des résultats de la transaction 1.

Cette dépendance cyclique n'a pas de solution, il n'existe pas d'ordre (T1 avant T2 ou T2 avant T1) qui puisse empêcher l'anomalie.

L'objectif des transactions SERIALIZABLE est de détecter ces dépendances pour empêcher ce type d'anomalie.

• Reproduire toutes les étapes de l'anomalie précédente, avec des transactions SERIALIZABLE