Les moteurs SQL permettent aux transactions concurrentes d’être isolées les unes des autres pour éviter les interférences. Cette propriété d’isolation correspond à la lettre I de l’acronyme bien connu “ACID”, les autres propriétés étant Atomicité, Cohérence (Consistency en anglais) et Durabilité.

Une particularité de l’isolation est qu’il y a plusieurs niveaux offerts au choix de l’utilisateur, qui diffèrent par leurs comportements en présence de transactions concurrentes.

Ces niveaux définis par le standard SQL dès la version 1992 sont dans l’ordre de l’isolation la plus faible à la plus forte:

  • Read Uncommitted: une transaction voit les changements des autres transactions avant qu’elle ne soient validées. Ce mode n’est pas mis en oeuvre dans PostgreSQL.

  • Read Committed: une transaction voit les changements d’une autre dès que l’autre a validé.

  • Repeatable Read: quand une transaction relit une ligne déjà lue via une requête précédente, elle doit relire les mêmes valeurs, même si la ligne a été changée par une autre transaction validée entre temps.

  • Serializable: une transaction ne peut pas voir ou produire des résultats qui n’auraient pas été possibles si d’autres transactions ne passaient pas concurremment.

Le niveau par défaut est Read Committed dans PostgreSQL et Repeatable Read dans MySQL ou MariaDB (en tout cas avec le moteur InnoDB). Au passage, le standard SQL indique que par défaut, c’est le mode Serializable qui doit être utilisé, donc cette recommandation n’est pas suivie (elle ne l’est pas non plus d’ailleurs par Oracle, ni MS SQL Server, ni DB2, ni Sybase ASE…).

Voyons un premier exemple très simple pour illustrer la différence entre MySQL et PostgreSQL dans leurs niveaux d’isolation par défaut:

Exemple 1

On a une table avec 4 valeurs:

CREATE TABLE a(x int);
INSERT INTO a VALUES (1),(2),(3),(4);

Une transaction Tx1 fait la somme et la moyenne des valeurs, pendant qu’une transaction Tx2 ajoute une valeur, avec un ordre d’exécution tel que l’insertion est committée entre les lectures.

Avec PostgreSQL dans son mode par défaut Read Committed:

-- Tx1                              -- Tx2
=# BEGIN;                           =# BEGIN;
BEGIN                               BEGIN

=# SELECT SUM(x) FROM a;
 sum
-----
  10
(1 ligne)
                                    =# INSERT INTO a VALUES(50);
                                    INSERT 0 1

                                    =# COMMIT;
                                    COMMIT
=# SELECT AVG(x) FROM a;
        avg
---------------------
 11.6666666666666667
   (1 ligne)

=# COMMIT;
COMMIT

La valeur 50 n’a pas été prise en compte dans la somme mais a été intégrée dans la moyenne. Si on regarde uniquement Tx1, on a donc deux résultats mathématiquement incohérents entre eux. C’est parce qu’en Read Committed, chaque nouvelle instruction SQL démarre à l’instant T sur un état de la base incluant tous les changements des autres transactions validées à cet instant T, en l’occurrence ici l’insertion de 50 par Tx2 et le COMMIT de Tx2 qui précèdent T.

Avec MySQL/MariaDB dans son isolation par défaut Repeatable Read, le résultat est différent:


-- Tx1                                      -- Tx2
mysql> BEGIN;                               mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)        Query OK, 0 rows affected (0.00 sec)

> SELECT SUM(x) FROM a;
+--------+             
| SUM(x) |             
+--------+             
|     10 |             
+--------+             
1 row in set (0.00 sec)

                                          > INSERT INTO a VALUES(50);         
                                          Query OK, 1 row affected (0.00 sec) 
                                          
                                          > COMMIT;
                                          Query OK, 0 rows affected (0.02 sec)


> SELECT AVG(x) FROM a;                   
+--------+                                
| AVG(x) |
+--------+
| 2.5000 |
+--------+
1 row in set (0.00 sec)

> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Dans le cas de MySQL/MariaDB, la valeur 50 insérée par Tx2 est ignorée par Tx1, en application de l’isolation Repeatable Read. Pour avoir la même chose dans PostgreSQL sur cet exemple, il faudrait passer à un niveau d’isolation supérieur (Repeatable Read ou Serializable)

Ce qu’il faut retenir ici, c’est que dans les configurations par défaut, des requêtes SQL très simples exécutées dans des sessions concurrentes peuvent donner des résultats différents d’un moteur à l’autre.

On pourrait penser que pour aligner systématiquement les comportements entre MySQL et PostgreSQL, il suffit de les régler au mêmes niveaux d’isolation. Mais en réalité, les comportements à un même niveau d’isolation peuvent différer sensiblement d’un moteur SQL à l’autre. Voyons un autre exemple qui illustre ça.

Exemple 2

A présent on va utiliser l’isolation Repeatable Read dans les deux moteurs. Pour passer la session à ce niveau sans préjuger de la valeur par défaut, on peut exécuter les ordres suivants:

pour MySQL/MariaDB:

> SET SESSION transaction isolation level Repeatable Read;

pour PostgreSQL:

=# SET default_transaction_isolation TO 'Repeatable Read';

On considère deux tables vides:

CREATE TABLE a(xa int);
CREATE TABLE b(xb int);

Ensuite on exécute deux transactions concurrentes qui vont insérer dans chaque table le count(*) de l’autre table. Voici ce que ça donne avec PostgreSQL:

-- Tx1                              -- Tx2
=# BEGIN;                           =# BEGIN;
BEGIN                               BEGIN

=# INSERT INTO a
   SELECT count(*) FROM b;
INSERT 0 1
                                    =# INSERT INTO b
                                       SELECT count(*) FROM a;
                                    INSERT 0 1
=# COMMIT;
COMMIT
                                    =# SELECT COUNT(*) FROM a;
                                     count 
                                    -------
                                         0
                                    (1 ligne)
                                    
                                    =# COMMIT;
                                    COMMIT

Une fois que Tx1 et Tx2 sont terminées, voici les résultats:

=# SELECT * FROM a;
 xa 
----
  0
(1 ligne)

=# SELECT * FROM b;
 xb 
----
  0
(1 ligne)

Au final, la valeur 0 se retrouve dans les deux tables car pour chacune des transactions, la table dont elle calcule le count(*) est vide dans le cadre de sa visibilité.

Mais avec MySQL/MariaDB, le comportement et le résultat final sont différents, comme montrés dans la transcription ci-dessous. Tx2 se met en attente de Tx1 et récupère ses résultats avant de continuer, plutôt que d’être isolée de Tx1.

-- Tx1                                      -- Tx2
mysql> BEGIN;                               mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)        Query OK, 0 rows affected (0.00 sec)

> INSERT INTO a SELECT count(*) FROM b;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0


                                            > INSERT INTO b SELECT count(*)
                                              FROM a;
                                            -- (Ici Tx2 se trouve bloquée)

> COMMIT;
Query OK, 0 rows affected (0.01 sec)

                                            -- (Ici Tx2 est débloquée)
                                            
                                            Query OK, 1 row affected (5.03 sec)
                                            Records: 1  Duplicates: 0  Warnings: 0
                                            
                                            > -- résultat différent de PG
                                            > SELECT COUNT(*) FROM a;
                                            +----------+
                                            | count(*) |
                                            +----------+
                                            |        1 |
                                            +----------+
                                            
                                            > COMMIT;
                                            Query OK, 0 rows affected (0.01 sec)

-- Tx1 et Tx2 sont terminées
> SELECT * FROM a;
+------+
| xa   |
+------+
|    0 |
+------+
1 row in set (0.01 sec)

> -- résultat différent de PG
> SELECT * FROM b;
+------+
| xb   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Bien que la séquence d’instructions soit très simple et que les transactions soient en Repeatable Read pour les deux moteurs, le résultat obtenu diffère entre Postgres et MySQL/MariaDB au sens où la table b contient au final une ligne avec 0 dans PostgreSQL et 1 dans MySQL.

Dans la mesure où Tx1 et Tx2 n’écrivent pas sur la même ligne (et en l’occurrence même pas dans la même table), pour PostgreSQL l’INSERT de Tx1 n’interfère pas du tout avec ce que fait Tx2. Tx2 n’a pas besoin d’attendre que Tx1 finisse, elle peut compter les lignes de a via son “snapshot”.

En revanche avec MySQL, Tx2 attend la fin de Tx1, et compte les lignes de a en intégrant ce que Tx1 a fait (soit une insertion). Tx1 et Tx2 sont donc moins isolées. Du point de vue d’un utilisateur habitué au Repeatable Read de PostgreSQL, ce comportement est plutôt étonnant (c’est-à-dire qu’il correspond en fait au Read Committed de PostgreSQL)

Et on va voir que les différences ne s’arrêtent pas là avec une variante de cet exemple, dans laquelle Tx2 interroge la table a en début de transaction.

Exemple 3

Voici donc une variante de l’exemple 2 dans laquelle Tx2 va lire et renvoyer le count(*) FROM a en début de transaction. Pour PostgreSQL, ça ne change rien, tout au long de Tx2, count(*) FROM a vaut toujours 0, que ce soit pour le renvoyer ou l’insérer.

Mais pour MySQL, il y a une nette différence de comportement pour éviter le phénomène “Non Repeatable Read”, qui est le minimum attendu pour le niveau d’isolation Repeatable Read.

Voyons la séquence suivante avec MySQL/MariaDB:

-- Tx1                                      -- Tx2
mysql> BEGIN;                               mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)        Query OK, 0 rows affected (0.00 sec)

> INSERT INTO a SELECT count(*) FROM b;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
                                            > -- initie la lecture répétable
                                            > SELECT count(*) FROM a;
                                            +----------+
                                            | count(*) |
                                            +----------+
                                            |        0 |
                                            +----------+
                                            1 row in set (0.00 sec)


                                            > INSERT INTO b SELECT count(*)
                                              FROM a;
                                            -- (Ici Tx2 se trouve bloquée)

> COMMIT;
Query OK, 0 rows affected (0.01 sec)

                                            -- (Ici Tx2 est débloquée)
                                            
                                            Query OK, 1 row affected (3.13 sec)
                                            Records: 1  Duplicates: 0  Warnings: 0
                                            

                                            > SELECT * FROM b;
                                            +------+
                                            | xb   |
                                            +------+
                                            |    1 |
                                            +------+
                                            1 row in set (0.00 sec)
                                            
                                            > -- répète la lecture répétable 
                                            > SELECT COUNT(*) FROM a;
                                            +----------+
                                            | count(*) |
                                            +----------+
                                            |        0 |
                                            +----------+
                                            1 row in set (0.00 sec)
                                            
                                            > COMMIT;
                                            Query OK, 0 rows affected (0.01 sec)

Le résultat final dans a et b est le même que précédemment avec la table b qui contient 1, mais cette fois en fin de Tx2, il s’avère que SELECT COUNT(*) FROM a renvoie 0 alors que précédemment il renvoyait 1.

La différence est que l’interdiction du phénomène “Non Repeatable Read” est mise en oeuvre parce qu’il y a eu un SELECT count(*) FROM a qui renvoyait 0 en début de transaction. Il faut donc qu’un SELECT count(*) FROM a ultérieur continue de produire 0. Mais ceci est vrai uniquement quand il est exécuté directement, et pas quand il est exécuté en tant que sous-requête via INSERT INTO b SELECT count(*) FROM a, C’est pourquoi il y a cette incohérence assez troublante entre le 1 qu’on trouve dans b.xb et le 0 renvoyé au client, alors que c’est censé être le résultat d’un même calcul dans la même transaction.

Il faut voir aussi que cette différence de résultat perdure dans Tx2 jusqu’à sa fin. Par exemple avant le COMMIT on pourrait avoir la séquence suivante:

mysql> SELECT * FROM b;
+------+
| xb   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> INSERT INTO b SELECT COUNT(*) FROM a;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM b;
+------+
| xb   |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM a;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Exemple 4

Ce dernier exemple illustre une spécificité du Repeatable Read de PostgreSQL qui évite un conflit d’écriture sur une même ligne au prix d’une annulation de la transaction. A contrario, MySQL/MariaDB au même niveau d’isolation n’annule pas la transaction, mais empêche l’effacement sans pour autant provoquer une erreur.

On va reprendre une table avec une colonne portant 4 nombres entiers, de 1 à 4. Les deux transactions concurrentes sont d’un côté une transaction Tx1 qui soustrait 1 à chaque valeur, et de l’autre une transaction Tx2 qui supprime la ligne portant la valeur maximale de la table. L’idée est que Tx2 veut supprimer une ligne que Tx1 change concurremment.

CREATE TABLE list(x int);
INSERT INTO list VALUES (1),(2),(3),(4);

Transcript PostgreSQL:

-- Tx1                                 -- Tx2
=# BEGIN;                              =# BEGIN;
BEGIN                                  BEGIN

=# UPDATE list SET x=x-1;              =# SELECT * FROM list; 
                                        x                     
                                       ---                    
                                        1                     
                                        2                     
                                        3                     
                                        4                     
                                       (4 lignes)             
                                       
                                       =# DELETE FROM list WHERE x=4;
                                       -- (Ici Tx2 se trouve bloquée)

=# COMMIT
COMMIT
                                        -- Tx2 part en erreur
                                        ERROR:  could not serialize access
                                        due to concurrent update
                                        
                                        =# \echo :SQLSTATE
                                        40001
                                        
                                        =# ROLLBACK;
                                        ROLLBACK

En isolation Repeatable Read, le moteur rejete l’écriture de Tx2 (au sens large du terme, la suppression d’une ligne étant une forme d’écriture) sur une ligne que Tx1 a déjà modifié. Ce refus se manifeste par une erreur spécifique (SQLSTATE 40001) et l’annulation de la transaction.

Avec MySQL/MariaDB ci-dessous, il n’y a pas d’annulation de la transaction. Le DELETE ne provoque pas d’erreur mais n’efface pas la ligne x=4, bien que cette ligne reste perpétuellement visible de Tx2.

-- Tx1                                      -- Tx2
mysql> BEGIN;                               mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)        Query OK, 0 rows affected (0.00 sec)

> UPDATE list SET x=x-1;                    > SELECT * FROM list;   
Query OK, 4 rows affected (0.00 sec)        +------+                
Rows matched: 4  Changed: 4  Warnings: 0    | x    |                
                                            +------+                
                                            |    1 |                
                                            |    2 |                
                                            |    3 |                
                                            |    4 |                
                                            +------+                
                                            4 rows in set (0.00 sec)
                                            
                                            > DELETE FROM list WHERE x=4;
                                            -- (Ici Tx2 se trouve bloquée)
                                            Query OK, 0 rows affected (5.73 sec)
> COMMIT;
Query OK, 0 rows affected (0.01 sec)

                                            > SELECT * FROM list;
                                            +------+
                                            | x    |
                                            +------+
                                            |    1 |
                                            |    2 |
                                            |    3 |
                                            |    4 |
                                            +------+
                                            4 rows in set (0.01 sec)

                                            > DELETE FROM list WHERE x=4;
                                            Query OK, 0 rows affected (0.00 sec)

                                            > SELECT * FROM list;
                                            +------+
                                            | x    |
                                            +------+
                                            |    1 |
                                            |    2 |
                                            |    3 |
                                            |    4 |
                                            +------+
                                            4 rows in set (0.00 sec)
                                            
                                            > COMMIT;
                                            Query OK, 0 rows affected (0.00 sec)

En répétant le SELECT * FROM list puis l’effacement, on voit que la ligne avec x=4 est toujours là mais que le DELETE n’arrive pas à l’atteindre. La seule indication que le moteur ne veut pas la supprimer est le 0 rows affected dans l’information d’accompagnement du retour du DELETE.

Par comparaison avec PostgreSQL, le non-effacement de la ligne dans Tx2 correspondrait au comportement de Read Committed de PostgreSQL, mais la persistence à montrer cette ligne alors que Tx1 l’a supprimée correspondrait plutôt à Repeatable Read. En ce sens le Repeatable Read de MySQL est une sorte d’entre-deux entre les deux niveaux Read Committed et Repeatable Read de Postgres.

Conclusion

Quand on porte des applications de MySQL à PostgreSQL ou inversement, ou qu’on veut faire des services qui fonctionnent sur les deux, il faut s’attendre à des comportements différents avec les transactions concurrentes, même quand on les configure sur le même niveau d’isolation.

Le standard SQL dit que certains niveaux d’isolation doivent éviter certaines anomalies d’accès concurrents (dits “phenomena” dans les termes du standard), mais chaque implémentation SQL interprète ça de manière plus ou moins étendue et contraignante, avec des résultats visibles clairement différents pour le code SQL.

PostgreSQL utilise Read Committed par défaut alors que MySQL a choisi Repeatable Read qui est mieux isolé, mais lorsque PostgreSQL est utilisé au niveau Repeatable Read, ses transactions sont concrètement mieux isolées qu’avec MySQL.