SOS Fantômes !
Les systèmes de gestion de bases de données relationnelles sont connus pour leurs propriétés ACID. Le I signifie ISOLATION. De multiples transactions peuvent être gérées simultanément par un SGBDR. Le standard SQL définit plusieurs niveaux d’isolation fixant les règles régissant la concurrence entre ces transactions. Dans cet article nous allons nous intéresser au troisième niveau, le REPEATABLE READ. Ce niveau n’autorise ni les lectures sales (dirty reads) ni les lectures non reproductibles (nonrepeatable read). En revanche, les lectures fantômes (phantom reads) sont autorisées. Cela signifie qu’en exécutant 2 fois la même requête dans une transaction, les données lues la première fois seront inchangées à la deuxième lecture. Mais cela ne signifie pas pour autant que le jeu de résultats obtenu lors des 2 exécutions sera identique. Des données supplémentaires ont pu modifier le deuxième jeu de résultats si elles ont été insérées par une autre autre transaction validée entre les 2 exécutions de la requête.
Démonstration avec PostgreSQL 9.6 :
select version();
version
------------------------------------------------------------------------------------------
PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 ligne)
- session 0
CREATE TABLE t1(c1 SMALLINT PRIMARY KEY);
CREATE TABLE
INSERT INTO t1(c1) VALUES(1);
INSERT 0 1
COMMIT;
COMMIT
-- session 1
START TRANSACTION;
START TRANSACTION
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET
SELECT * FROM t1 WHERE c1 = 1;
c1
----
1
(1 ligne)
SELECT sum(c1) FROM t1;
sum
-----
1
(1 ligne)
SELECT c1 FROM t1 WHERE c1 BETWEEN 1 and 2;
c1
----
1
(1 ligne)
SELECT * FROM t1;
c1
----
1
(1 ligne)
-- session 2
START TRANSACTION;
START TRANSACTION
INSERT INTO t1 VALUES(2);
INSERT 0 1
COMMIT;
COMMIT
-- session 1 suite
SELECT * FROM t1 WHERE c1 = 1;
c1
----
1
(1 ligne)
SELECT sum(c1) FROM t1;
sum
-----
1
(1 ligne)
SELECT c1 FROM t1 WHERE c1 BETWEEN 1 and 2;
c1
----
1
(1 ligne)
SELECT * FROM t1;
c1
----
1
(1 ligne)
Les ordres SELECT de la session 1 ont donné les mêmes résultats dans la première partie de la transaction et la deuxième. Les lectures sont reproductibles, ce qui êtait attendu. Il n’y a pas non plus de lectures fantômes, ce qui ne l’était pas. L’explication est la même que dans le premier chapitre. Si les niveaux d’isolation reposaient sur le verrouillage dans PostgreSQL, l’unique ligne présente au départ aurait été verrouillée après la première série de SELECT pour assurer les lectures reproductibles. Les résultats des deux "SELECT * FROM t1 WHERE c1 = 1" auraient donc été identiques alors que les résultats des autres SELECT auraient été différents d’une exécution à l’autre.
Mais PostgreSQL s’appuie sur un mécanisme MVCC (multiversion concurrencty control), le principe est de présenter une vision adéquate de TOUTE la base. Les lectures fantômes et les lectures non reproductibles sont éliminées simultanément. Il est donc impossible d’avoir des lectures fantômes au niveau REPEATABLE READ avec PostgreSQL. A noter encore une fois que ce comportement est compatible avec le standard SQL. En effet, le standard impose de NE PAS avoir de lecture fantômes à partir du niveau SERIALIZABLE mais il n’impose pas d’avoir des lectures fantômes aux niveaux inférieurs comme le REPEATABLE READ.
Pas de lectures fantômes au niveau REPEATABLE READ, cela signifie-t-il que les niveaux d’isolation REPEATABLE READ et SERIALIZABLE sont équivalents avec PostgreSQL ? Pour atteindre le niveau SERIALIZABLE, les transactions exécutées en concurrence doivent pouvoir être exécutées séquentiellement en donnant les mêmes résultats. Si c’est impossible on parle d’anomalie de sérialisation.
Démonstration, toujours avec PostgreSQL 9.6 :
select version();
version
------------------------------------------------------------------------------------------
PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 ligne)
- session 0
CREATE TABLE t1(c1 SMALLINT PRIMARY KEY);
CREATE TABLE
INSERT INTO t1(c1) VALUES(1),(2);
INSERT 0 2
COMMIT;
COMMIT
-- session 1
START TRANSACTION;
START TRANSACTION
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET
SELECT * FROM t1 WHERE c1 = 1;
c1
----
1
(1 ligne)
-- session 2
START TRANSACTION;
START TRANSACTION
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET
SELECT * FROM t1 WHERE c1 = 2;
c1
----
2
(1 ligne)
-- suite session 1
UPDATE t1 SET c1 = 4 WHERE c1 = 2;
UPDATE 1
COMMIT;
COMMIT
-- suite session 2
UPDATE t1 SET c1 = 3 WHERE c1 = 1;
UPDATE 1
COMMIT;
COMMIT
Pas d’anomalie de sérialisation ! Il serait pourtant impossible d’obtenir ces résultats en exécutant séquentiellement les transactions présentées dans les sessions 1 et 2. La transaction de la session 1 modifie la ligne lue dans la session 2 et la transaction de la session 2 modifie la ligne lue dans la session 1. Si la transaction de la session 1 est intégralement exécutée avant la transaction de la session 2 alors le "SELECT * FROM t1 WHERE c1 = 2" ne renverra aucune ligne. Et si c’est la transaction de la session 2 qui est intégralement exécutée avant la transaction de la session 1 alors c’est le "SELECT * FROM t1 WHERE c1 = 1" qui ne renverra aucune ligne.
Les niveaux REPEATABLE READ et SERIALIZATION ne sont donc PAS équivalents avec PostgreSQL, en tous cas en version 9.6. Le comportement observé est en fait valable depuis la version 9.1 donc cet article est pertinent pour toutes les versions supportées.
Quel est le comportement d’Oracle Database en ce qui concerne ce niveau d’isolation à présent ? Démonstration avec Oracle Database 11.2.0.2 :
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
*
ERROR at line 1:
ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }
Pas de niveau d’isolation REPEATABLE READ avec Oracle Database. Il faut directement passer au niveau SERIALIZABLE avec ce SGBD pour obtenir des lectures reproductibles.