Propriétés ACID

Un ensemble d’opérations sur les données d’une base de données est appelée une transaction. Dans les années 1970, les théoriciens des bases de données ont défini des critères pour assurer la fiabilité d’une transaction ; ce sont les propriétés ACID (atomicité, cohérence, isolation et durabilité).

  • Atomicité

    La propriété d’atomicité assure qu’une transaction se fait au complet ou pas du tout : si une partie d’une transaction ne peut être faite, il faut effacer toute trace de la transaction et remettre les données dans l’état où elles étaient avant la transaction. L’atomicité doit être respectée dans toutes situations (par exemple défaillance de l’ordinateur ).

  • Cohérence

    La propriété de cohérence assure que chaque transaction amènera le système d’un état valide à un autre état valide. Tout changement à la base de données doit être valide selon toutes les règles définies.

  • Isolation

    Toute transaction doit s’exécuter comme si elle était la seule sur le système. La propriété d’isolation assure que l’exécution simultanée de transactions produit le même état que celui qui serait obtenu par l’exécution en série des transactions.

  • Durabilité

    La propriété de durabilité assure que lorsqu’une transaction a été confirmée, elle demeure enregistrée même à la suite d’une panne de l’ordinateur.

Comprendre ce qu’est une transaction

Mise en place : pour tester les transactions, vous devez au préalable créer sur le serveur PostgreSQL data un schéma test contenant une table comptes avec deux colonnes :

  • id (identifiant du compte, format integer, clé primaire)

  • montant (montant du compte, format decimal)

On définira ensuite plusieurs comptes dans cette base, avec des valeurs de votre choix.

Si on veut faire un virement de 10€ du compte 1 vers le compte 2, on va lancer des commandes SQL :

UPDATE test.comptes SET montant = montant - 10 WHERE id = 1 ;
UPDATE test.comptes SET montant = montant + 10 WHERE id = 2 ;

Efectuez ces commandes sur la base.

Si la connexion entre le client et le SGBD s’arrête entre les deux commandes, le compte 1 a été débité, mais le compte 2 n’a pas été crédité. Ce n’est pas admissible. On fait donc une transaction.

Une transaction commence par l’instruction BEGIN ; et se termine par l’instruction COMMIT (qui valide toutes les opérations de la transaction) ou ROLLBACK (qui annule toutes opérations).

Tapez les instructions précédentes dans une transaction, et terminez-la par un COMMIT ou un ROLLBACK. Dans chacun des cas regardez le résultat obtenu.

Quand on tape une instruction SQL dans le terminal psql, elle est immédiatement enregistrée. Autrement dit, elle constitue une transaction en elle-même, et le système l’interprète comme si elle était suivie d’un COMMIT.

On dit que dit que psql est par défaut en mode AUTOCOMMIT. On peut changer cet état en tapant

\set AUTOCOMMIT OFF

Dans ce cas, les commandes entrées formeront une transaction, à finir par un COMMIT ou un ROLLBACK.

Refaites les tests précédents en désactivant le mode AUTOCOMMIT.

Une transaction est soit réalisée entièrement, soit pas du tout.

Effectuez les opérations suivantes dans psql :

BEGIN;
INSERT INTO test.comptes VALUES(35,1250);
INSERT INTO test.comptes VALUES(null,50);
INSERT INTO test.comptes VALUES(36,5000);
COMMIT;

Regardez ensuite ce que contient votre table.

Normalement, la deuxième insertion devrait faire une erreur, puisque id est une clé primaire. En conséquence, l’ensemble de la transaction est annulée, et les autres commandes, pourtant valides, n’ont pas été enregistrées.

Comportement de psycopg2

psycopg2 fonctionne en mode AUTOCOMMIT OFF. Donc chaque suite de commandes SQL envoyées par pscycopg2 est gérée par postgresql comme une transaction, et doit donc être terminée par une commande :

nom_de_la_connexion.commit()

Le problème de la concurrence d’accès

On s’intéresse ici aux problèmes liés à ce que deux transactions chrechent à accéder aux mêmes données. On est dans le "I" (Isolation) des propriétés ACID.

On distingue plusieurs types d’anomalies qu’on veut pouvoir traiter :

  • Lecture sale (dirty read)

    Une transaction lit des données écrites par une transaction concurrente non validée.

    Temps Transaction A Transaction B

    1

    Lire compte 1 → 500

    2

    Mettre compte 1 à 1000

    3

    Lire compte 1 → 1000

    4

    ROLLBACK

    La transaction B va donc travailler avec une valeur (1000) qui n’a pas été validée.

  • Lecture non reproductible (non repeatable read)

    Une transaction relit des données qu’elle a lu précédemment et trouve que les données ont été modifiées par une autre transaction (validée depuis la lecture initiale).

    Temps Transaction A Transaction B

    1

    Lire compte 1 → 500

    2

    Mettre compte 1 à 1000

    3

    COMMIT

    4

    Lire compte 1 → 1000

    La transaction B a lu deux valeurs différentes pour une même requête.

  • Lecture fantôme (phantom read)

    Une transaction ré-exécute une requête renvoyant un ensemble de lignes satisfaisant une condition de recherche et trouve que l’ensemble des lignes satisfaisant la condition a changé du fait d’une autre transaction récemment validée.

    Temps Transaction A Transaction B

    1

    SELECT * from comptes

    2

    Créer un nouveau compte (INSERT)

    3

    COMMIT

    4

    SELECT * from comptes

    La liste des comptes a changé entre les deux lectures de B.

  • Anomalie de sérialisation

    Le résultat de la validation réussie d’un groupe de transactions est incohérent avec tous les ordres possibles d’exécutions de ces transactions, une par une.

Différents niveaux d’isolation

La norme SQL définit différents niveaux d’isolation, permettant ou non certaines des anomalies listées précédemment.

Niveau d’isolation Lecture sale Lecture non reproductible Lecture fantôme

Uncommited Read (lecture de données non validées)

Possible

Possible

Possible

Commited Read (lecture de données validées)

Impossible

Possible

Possible

Repeatable Read (lecture répétée)

Impossible

Impossible

Possible

Serializable (sérialisable)

Impossible

Impossible

Impossible

Read Commited est le niveau d’isolation par défaut de PostgreSQL. Le phénomène de lectures sales est donc impossible, du fait de la conception même de PostgreSQL.

On pourrait penser que le mode serializable est idéal, car il règle tous les problèmes. Cependant, il impose de grosses contraintes à la base de données en empêchant souvent des accès simultanés, ce qui risque de rendre un travail multi-utilisateur compliqué.

Quelques expérimentations

Vous allez ouvrir deux terminaux sur data (pour cela, il vous faudra donc lancer deux connexions ssh sur la machine savennieres). Pour la commodité de l’explication, j’appelerai ces deux terminaux T1 et T2.

On part du principe que vous avez déjà créé une table test.comptes, comme demandé précédemment. Vous allez exécuter des commandes sur T1 et T2, dans l’ordre où elles sont indiquées dans les énoncés.

On travail en mode normal dans psql (c’est à dire AUTOCOMMIT, les COMMIT sont faits à chaque nouvelle commande entrée)

  • T1 : insérer un nouvel enregistrement

  • T2 : SELECT * from test.comptes;

Vous constaterez que la ligne insérée par T1 est visible par T2.

Pour la suite, on place les deux terminaux en mode AUTOCOMMIT OFF, en tapant dans chacun d’eux

\set AUTOCOMMIT OFF

Effectuer les commandes suivantes :

  • T1 : insérer un nouvel enregistrement (INSERT)

  • T1 : lister tous les enregistrements (SELECT)

  • T2 : lister tous les enregistrements (SELECT).

T2 ne voit pas l’enregistrement fait par T1.

  • T1 : Valider la transaction (COMMIT)

  • T1 : lister tous les enregistrements (SELECT)

  • T2 : lister tous les enregistrements (SELECT).

L’enregistrement fait par T1 est visible par T1 et T2.

Refaire la suite de commandes précédentes, en remplaçant le COMMIT par ROLLBACK.

Ces deux exercices ont bien montré que PostgreSQL empêche les lectures sales.

Effectuer les commandes suivantes :

  • T1 : Ajouter 100 aux montants de tous les comptes (UPDATE …​)

  • T1 : Afficher la valeur de tous les comptes (SELECT …​)

  • T2 : Ajouter 200 aux montants de tous les comptes (UPDATE …​)

Le terminal T2 est en attente

  • T1 : Afficher la valeur de tous les comptes (SELECT …​)

  • T1 : COMMIT;

Le terminal T2 est débloqué

  • T2 : Afficher la valeur de tous les comptes (SELECT …​)

  • T1 : Afficher la valeur de tous les comptes (SELECT …​)

  • T2 : COMMIT;

  • T2 : Afficher la valeur de tous les comptes (SELECT …​)

  • T1 : Afficher la valeur de tous les comptes (SELECT …​)

Les deux terminaux affichent les mêmes valeurs (comptes augmentés de 300).

PostgreSQL met en oeuvre plusieurs techniques pour protéger les transactions :

  • Une transaction travaille sur une image instantanée de la table ("snapshot"), prise au début de la transaction.

  • Lors d’une action d’écriture (UPDATE), un verrou est posé sur la table, empêchant ainsi une autre transaction d’écrire dessus.

Le phénomène d’interblocage

Le système des verrous peut conduire à un phénomène de blocage (interblocage ou deadlock), que l’on met en évidence dans l’exercice suivant.

Créer une deuxième table comptes2 avec la même structure que comptes, et au moins une ligne enregistrée.

Effectuer ensuite la suite de commandes dans les deux terminaux :

  • T1 : UPDATE test.comptes SET montant = montant + 10;

  • T2 : UPDATE test.comptes2 SET montant = montant + 20;

  • T2 : UPDATE test.comptes SET montant = montant + 30;

comme T1 a posé un verrou sur comptes, T2 est bloqué

  • T1 : UPDATE test.comptes2 SET montant = montant + 40;

comme T2 a posé un verrou sur comptes2, T1 est bloqué

Cela pourrait bloquer le système, mais PostgreSQL détecte le problème :

ERROR:  deadlock detected
DÉTAIL : Process 278591 waits for ShareLock on transaction 150256; blocked by process 278576.
Process 278576 waits for ShareLock on transaction 150257; blocked by process 278591.

et il et tue les deux transactions.

Changer le niveau d’isolation d’une transaction

Lorsqu’on lance une transaction sous PostgreSQL, la transaction est par défaut dans le niveau d’isolation READ COMMITED. On a vu que cela nous évite les problèmes de lecture sale, mais les problèmes de lecture répétée.

Pour cela, vous pouvez définir un autre niveau d’isolation, en entrant :

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

ou

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Pour les plus patients :

Refaites l’exercice 9, en lançant les deux transactions en mode REPEATABLE READ. Est-ce que cela change quelque chose ?