Niveau d’isolation : SERIALIZABLE

Le plus haut niveau

      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 niveau le plus élevé, le SERIALIZABLE. Ce niveau n’autorise ni les lectures sales (dirty reads), ni les lectures non reproductibles (nonrepeatable read), ni les lectures fantômes (phantom reads), ni les anomalies de sérialisation (serialization anomaly). Cela signifie que les transactions s’exécutant en concurrence doivent pouvoir être exécutées séquentiellement et donner strictement les mêmes résultats.
      Nous avons vu dans cet article que les anomalies de sérialisation étaient les seules anomalies résiduelles au niveau d’isolation REPEATABLE READ avec PostgreSQL. Le niveau SERIALIZABLE permet-il de les éliminer ? 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),(2); INSERT 0 2 COMMIT; COMMIT -- session 1 START TRANSACTION; START TRANSACTION SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET SELECT * FROM t1 WHERE c1 = 1; c1 ---- 1 (1 ligne) -- session 2 START TRANSACTION; START TRANSACTION SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 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; ERREUR: n’a pas pu sérialiser un accès à cause des dépendances de lecture/écriture

      Il serait impossible d’exécuter séquentiellement les transactions des 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. Cette anomalie a été détectée et remontée alors que ce n’était pas le cas au niveau REPEATABLE READ. Ce comportement est valable depuis la version 9.1. PostgreSQL respecte donc le standard SQL en ce qui concerne les niveaux d’isolation dans toutes ses version supportées. Dans les version 9.0 et inférieures, le niveau SERIALIZABLE était identique à l’actuel niveau REPEATABLE READ. Attention donc si vous passez d’une version 9.0 ou inférieure à une version 9.1 ou supérieure.
      Nous avions laissé Oracle au niveau READ COMMITTED et nous passons directement au niveau SERIALIZABLE puisque le niveau REPEATABLE READ n’est pas proposé par ce SGBD. En théorie, aucune anomalie n’est admise à ce niveau. Nous allons commencer par les lectures non reproductibles et les lectures fantômes, démonstration avec Oracle Database 11.2.0.2 :

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production - session 0 SQL> CREATE TABLE t1(c1 SMALLINT PRIMARY KEY); Table created. SQL> INSERT INTO t1(c1) VALUES(1); 1 row created. SQL> COMMIT; Commit complete. -- session 1 SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; Transaction set. SQL> SELECT * FROM t1 WHERE c1 = 1; C1 ---------- 1 SQL> SELECT sum(c1) FROM t1; SUM(C1) ---------- 1 SQL> SELECT c1 FROM t1 WHERE c1 BETWEEN 1 and 2; C1 ---------- 1 SQL> SELECT * FROM t1; C1 ---------- 1 -- session 2 SQL> INSERT INTO t1 VALUES(2); 1 row created. SQL> COMMIT; Commit complete. -- session 1 suite SQL> SELECT * FROM t1 WHERE c1 = 1; C1 ---------- 1 SQL> SELECT sum(c1) FROM t1; SUM(C1) ---------- 1 SQL> SELECT c1 FROM t1 WHERE c1 BETWEEN 1 and 2; C1 ---------- 1 SQL> SELECT * FROM t1; C1 ---------- 1

      Comme prévu, les lectures non reproductibles comme les lectures fantômes sont éliminées au niveau SERIALIZABLE avec Oracle. Mais qu’en est-il des anomalies de sérialisation ? Démonstration avec Oracle Database 11.2.0.2 :

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production - session 0 SQL> CREATE TABLE t1(c1 SMALLINT PRIMARY KEY); Table created. SQL> INSERT INTO t1(c1) VALUES(1); 1 row created. SQL> INSERT INTO t1(c1) VALUES(2); 1 row created. SQL> COMMIT; Commit complete. -- session 1 SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; Transaction set. SQL> SELECT * FROM t1 WHERE c1 = 1; C1 ---------- 1 -- session 2 SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; Transaction set. SQL> SELECT * FROM t1 WHERE c1 = 2; C1 ---------- 2 -- suite session 1 SQL> UPDATE t1 SET c1 = 4 WHERE c1 = 2; 1 row updated. SQL> COMMIT; Commit complete. -- suite session 2 SQL> UPDATE t1 SET c1 = 3 WHERE c1 = 1; UPDATE t1 SET c1 = 3 WHERE c1 = 1 * ERROR at line 1: ORA-08177: can't serialize access for this transaction

      Une erreur ORA-08177 a été obtenue. Cependant, Tom Kyte présente dans cet article que cette erreur est juste la conséquence du fait qu’Oracle Database travaille au niveau bloc et non au niveau ligne en ce qui concerne le niveau d’isolation SERIALIZABLE. Les 2 lignes mises à jour dans la démonstration devaient donc être situées dans le même bloc. Réalisons la même démonsration avec des lignes situées dans des blocs différents, toujours avec Oracle Database 11.2.0.2 :

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production - session 0 SQL> CREATE TABLE t1(c1 SMALLINT PRIMARY KEY); Table created. SQL> INSERT INTO t1 SELECT level FROM dual CONNECT BY LEVEL < 100001; 100000 rows created. SQL> COMMIT; Commit complete. -- session 1 SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; Transaction set. SQL> SELECT * FROM t1 WHERE c1 = 1; C1 ---------- 1 -- session 2 SQL> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; Transaction set. SQL> SELECT * FROM t1 WHERE c1 = 100000; C1 ---------- 100000 -- suite session 1 SQL> UPDATE t1 SET c1 = 100001 WHERE c1 = 100000; 1 row updated. SQL> COMMIT; Commit complete. -- suite session 2 SQL> UPDATE t1 SET c1 = 0 WHERE c1 = 1; 1 row updated. SQL> COMMIT; Commit complete.

      Cette fois pas d’erreur, les lignes modifiées par chacune des transactions n’appartenaient pas au même bloc. Oracle Database ne cherche PAS à remonter les anomalies de sérialisation et l’ORA-08177 n’est PAS obtenue systématiquement. Cette implémentation relève de la cuisine interne d’Oracle mais ce qu’il faut retenir est que le niveau SERIALIZABLE de ce SGBD correspond conceptuellement au niveau REPEABLE READ du standard SQL avec en plus l’élimination des lectures fantômes. En pratique, en dehors de ces ORA-08177, le niveau SERIALIZABLE d’Oracle correspond donc au niveau REPEATABLE READ des versions 9.1 et supérieures de PostgreSQL.

Mise à jour : 12/11/2016