Cet article est la traduction d’un article de Tom Kyte publié dans Oracle Magazine en Novembre 2005. L’article original en anglais se trouve ici.
Il peut être utile de lire avant Lectures cohérentes et multi-versionnage (traduit aussi de Tom Kyte).

Question posée sur AskTom:

J’ai lu le manuel ‘Database Concepts’ de la documentation Oracle, au chapitre « Data Concurrency and Consistency » mais je n’ai pas vraiment compris la différence entre les niveaux d’isolation serializable et read-committed. Pouvez-vous donner des exemples qui expliquent cela clairement ?

Réponse de Tom Kyte:

Avant de lire ce qui suit, vous pouvez aller voir l’article d’Oracle Magazine de Mai/Juin 2005 (en anglais) où je décris la fonctionnalité que j’ai toujours préféré dans Oracle: le multi-versioning. Sa compréhension est cruciale pour réussir avec Oracle, mais il vous aidera aussi à comprendre les concepts décrits ci dessous. (Voir la traduction d’un article similaire ici)

[La suite est un extrait de ‘Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions‘]

Le standard SQL ANSI/ISO SQL définit 4 niveaux d’isolation des transactions (transaction isolation levels), qui peuvent donner des résultats différents pour le même scénario de transactions. Cela veut dire que le même travail, effectué de la même manière, avec les mêmes données, peut avoir des résultats différents, en fonction du niveau d’isolation. Ces niveaux sont définis par les 3 phénomènes qui sont autorisés ou non pour un certain niveau d’isolation:

Lecture ‘sale’ (Dirty read): Sa signification est aussi négative que son nom. Vous avez le droit de lire des données non-committées, ou ‘sales’. Vous pouvez avoir le même effet en ouvrant un fichier du système d’exploitation sur lequel quelqu’un est en train d’écrire, et lire n’importe quelle donnée qui s’y trouve. L’intégrité des données est compromise, les clés étrangères sont violées, et les contraintes d’unicité sont ignorées.

Lecture ‘non-répétable’ (Nonrepeatable read): Cela veut simplement dire que si vous lisez un enregistrement à un temps t1, et essayez de le relire à un temps t2, l’enregistrement peut avoir changé. Il a pu disparaître, il a pu être modifié, etc.

Lecture ‘fantôme’ (Phantom read): Cela veut dire que si vous faites une requête à un temps t1, et que vous la relancez à un temps t2, des nouveaux enregistrements ont pu être ajoutés dans la base, ce qui peut modifier votre résultat. C’est différent de la lecture non-répétable dans le sens où les enregistrements que vous avez lu précédemment n’ont pas changé. Mais de nouveaux enregistrements répondent à votre critère de requête.

Il faut noter que le standard ANSI/ISO définit les caractéristiques des niveaux d’isolation au niveau des transactions, et pas seulement au niveau de chaque requête. Je parlerai de l’isolation au niveau transaction, et pas seulement au niveau requête.

Les niveaux d’isolations SQL sont définis par leur le fait d’autoriser ou non chacun des phénomènes précédents. Il est important de noter que le standard SQL n’impose aucun système de verrouillage spécifique, et ne demande aucun comportement particulier, mais il décrit plutôt les niveaux d’isolation en termes de phénomènes permis, afin que puissent exister différentes implémentations pour les mécanismes de verrouillage et de concurrence d’accès).

Les niveaux d’isolation ANSI:

Oracle supporte explicitement les niveaux d’isolation READ COMMITTED et SERIALIZABLE tels qu’ils sont définis dans le standard. Mais ce n’est pas suffisant de dire cela. Les standard SQL voulaient essayer de permettre des degrés de cohérence différents en fonction du niveau d’isolation où la requête est exécutée. REPEATABLE READ est le niveau à partir duquel, d’après le standard SQL, le résultat d’une requête est censé être cohérent. READ COMMITTED, pour le standard SQL, ne garantit pas un résultat cohérent en lecture, et READ UNCOMMITTED est supposé être le niveau qui utilise des lectures non-bloquantes.

Mais sous Oracle, READ COMMITTED a tous les attributs nécessaires pour avoir une lecture cohérente au niveau de la requête. Dans les autres SGBD, READ COMMITTED peut et va retourner des résultats qui n’ont jamais existé en base de donnée. Et en plus, Oracle supporte l’idée de READ UNCOMMITTED. La lecture ‘sale’ a pour but de fournir une lecture non-bloquante, où les requêtes ne bloquent pas et ne sont pas bloquées par les mises à jour concurrentes des mêmes données. Mais Oracle na pas besoin de lectures ‘sales’ pour atteindre ce but, et il ne les implémente pas. La lecture sale (‘dirty reads‘) est une implémentation que les autre SGBD doivent utiliser pour permettre des lectures non-bloquantes.

En plus des 4 nivaux d’isolation définis par le standard SQL, le SGBD Oracle en fournit un autre: READ ONLY (lecture seule). Une transaction READ ONLY est equivalente à une transaction REPEATABLE READ ou SERIALIZABLE, mais qui ne peut faire aucune mise à jour. Une transaction qui utilise le niveau d’isolation READ ONLY ne voit que les modifications qui ont été committées avant que la transaction ne commence. Les insert, update et delete ne sont pas permis dans ce mode (les autres sessions peuvent mettre à jour les données, mais pas la transaction READ ONLY). En utilisant ce mode, vous avez les niveaux d’isolation REPEATABLE READ et SERIALIZABLE.

Je vais maintenant expliquer le rôle du multi-versioning et de la lecture cohérente (read consistency) dans le système d’isolation, et comment les SGBD qui ne font pas du multi-versioning arrivent au même résultat. Cette explication est utile pour tous ceux qui ont utilisé un autre SGBD et qui croient qu’ils ont compris comment les niveaux d’isolation doivent fonctionner. C’est aussi intéressant pour voir comment le standard SQL ISO/ANSI , qui était supposé supprimer les différences entre les SGBD, au contraire les permet. Le standard, même s’il est très détaillé, peut être implémenté de différentes manières.

READ UNCOMMITTED.

Le niveau d’isolation READ UNCOMMITTED permet les lectures ‘sales’ (dirty reads). Le SGBD Oracle n’utilise pas ces dirty reads, et ne les permet même pas. READ UNCOMMITTED est défini dans le standard pour permettre les lectures non bloquantes. Comme vous avez vu, Oracle fait des lectures non bloquantes par défaut. Vous auriez du mal à faire un SELECT en base qui bloque et attends (il y a un cas particulier pour les transactions distribuées). Chaque requête, que ce soit un SELECT, INSERT, UPDATE, MERGE, ou DELETE, fait ses lecture de manière cohérente. Cela peut sembler amusant de dire que l’UPDATE est une requête qui lit, mais c’est le cas. UPDATE a 2 composants: une lecture, qui est définie par la clause WHERE, et une écriture qui est définie par la clause SET. UPDATE lit et écrit dans la base de donnée, comme tous les ordres DML. La seule exception est l’INSERT d’une ligne unique, avec la clause VALUES, et qui n’a pas de composante de lecture, mais seulement d’écriture.

Dans le chapitre 1 de ‘Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions’ (ou en français ici), J’ai montré comment Oracle fait une lecture cohérente: avec un requête, sur une seule table, qui retourne pourtant les lignes qui ont été supprimées depuis l’ouverture du curseur. Je vais maintenant explorer un exemple du monde réel pour voir ce qu’il se passe sous Oracle avec le multi-versioning, et aussi ce qu’il se passe dans un grand nombre d’autres SGBD.

Je vais commencer avec la même table et encore une requête très simple:

Avant de lancer la requête, j’affiche les données:

Ligne      Numéro du Compte              Montant du Compte 

-----      ----------------              ----------------- 

1          123                           500,00 € 

2          456                           240,25 € 

...        ...                           ... 

342023     987                           100,00 €

A ce moment, mon SELECT démarre et va lire la ligne 1, puis la ligne 2, etc. A un certain moment lorsque cette requête est en cours d’exécution, une transaction fait un virement de 400€ du compte 123 vers compte 987. Cette transaction a fait les deux UPDATE mais ne fait pas encore le COMMIT. La table COMPTES ressemble maintenant à cela:

Ligne      Numéro du Compte              Montant du Compte  

-----      ----------------              ---------------------- 

1          123                           (500,00 €) -> 100,00 €  [vérouillé] 

2          456                           240,25 € 

...        ...                           ... 

342023     987                           (100,00 €) -> 500,00 €  [vérouillé]

Nous voyons que 2 enregistrements sont verrouillés (locked). Si quelqu’un essaie de les modifier, il sera bloqué. Jusque là le comportement qu’on voit est sensiblement le même sur tous les SGBD. La différence va se faire lorsque notre SELECT va tomber sur les données verrouillées.

Lorsque la requête que j’exécute arrive au bloc qui contient la ligne verrouillée (ligne 342023) tout au bout de la table, elle voit que les données ont changé depuis le début de la requête. Pour fournir une réponse cohérente (consistent) et juste, le SGBD Oracle va créer une copie du bloc, mais où cette ligne sera comme elle était au moment où la requête a commencé. C’est à dire qu’on va lire la valeur de 100€, qui était le montant du compte à ce moment là. En effet, au lieu de lire l’information modifiée, Oracle utilise l’undo (aussi appelé rollback segment) pour reconstruire l’information. Une réponse cohérente et juste est alors retournée, sans avoir à attendre que la transaction concurrent ne soit committée.

Par contre, un SGBD qui autorise les lectures ‘sales’ va simplement retourner la valeur qu’il voit pour le compte 987 au moment où il la lit, c’est à dire 500€. La requête va donc compter deux fois les 400€ du montant du virement. Non seulement il retourne la mauvaise réponse, mais en plus il renvoi un total qui n’a jamais existé dans la base. Dans un environnement multi-utilisateur, une lecture ‘sale’ peut être une fonctionnalité très dangereuse. Personnellement, je n’en ai jamais vu l’utilité.
Et si l’on prends le cas où, au lieu d’un virement, la transaction fait juste un dépôt de 400€ sur le compte 987. La lecture sale va compter les 400€ et retourner alors une réponse juste ? Et bien non. Supposons que la transaction fasse un rollback. Alors j’aurais compté les 400€ qui n’ont jamais été réellement en base.

L’idée ici, c’est que la lecture ‘sale’ n’est pas une fonctionnalité, mais un handicap. Et sous Oracle, elle n’est simplement pas nécessaire. Vous avez tous les avantages d’une lecture non-bloquante, mais sans avoir des résultats faux.

READ COMMITTED.

Le niveau d’isolation READ COMMITTED précise que la transction ne doit lire que les données qui ont été committées. Il n’y a pas de lectures ‘sales’ (qui sont les lectures de données non committées). Il peut y avoir des lectures ‘non-répétable’ (nonrepeatable read), ce qui veut dire que la relecture de la même ligne dans la même transaction peut donner un résultat différent. Il peut y avoir des Lecture ‘fantôme’ (phantom read), ce qui veut dire que, dans la même transaction, une ligne nouvellement insérée et committée devient visible par une requête alors qu’elle ne l’était pas avant.
READ COMMITTED est probablement le niveau d’isolation le plus courant dans les applications de base de données, et c’est le mode par défaut pour Oracle. Il est rare de voir un autre niveau d’utilisation utilisé dans une base Oracle.

Cependant, arriver au niveau d’isolation READ COMMITTED est plus compliqué qu’il n’y parait. Si vous regardez le premier tableau (Les niveaux d’isolation ANSI), cela semble simple. On a l’impression qu’une requête qui utilise READ COMMITTED se comporte de la même manière dans n’importe quel SGBD, non ? Et bien ce n’est pas le cas. Dans la pluspart des autres SGBD, si vous lisez plusieurs lignes en une seule requête, le niveau READ COMMITTED peut être aussi mauvais que que des lectures ‘sales’, en fonction de la manière dont il est implémenté.

Sous Oracle, en utilisant le système de multi-versionnage et les lectures cohérentes, la requête sur la table COMPTES donne le même résultat en READ COMMITTED que dans l’exemple du READ UNCOMMITTED. Oracle va reconstruire les données modifiées pour qu’elles apparaissent telles qu’elles étaient lorsque la requête a commencé, retournant la réponse qui était en base de donnée à ce moment là.

Maintenant, je vais regarder comment mon exemple précédant fonctionne en READ COMMITTED dans les autres SGBD. Je reprends au même endroit:
– Je suis au milieu de la table. J’ai lu et additionné les N premières lignes.
– L’autre transaction a viré 400€ du compte 123 vers le compte 987.
– Cette autre transaction n’a pas encore été committée, donc les lignes contenant les informations des comptes 123 et 987 sont verrouillées.

Je sais ce qu’il arrive sous Oracle lorsqu’on arrive au compte 987: il voit la modification, calcule que le montant devait être 100€ et c’est terminé. Voici comment un autre SGBD, dans un mode READ COMMITTED par défaut, peut arriver à donner la réponse.

La première chose à remarquer, c’est que cet autre SGBD va bloquer ma requête lorsque elle lit le compte 987. Ma session doit attendre que la transaction qui détient le verrou exclusif fasse un commit. C’est pourquoi beaucoup de gens ont la mauvaise habitude de faire un commit à chaque requête, au lieu de faire des transaction bien construites, regroupant toutes les requêtes qui font passer la base de donnée d’un état cohérent à un autre. Dans beaucoup de SGBD les mises à jour interfèrent avec les lectures. Mais le pire dans ce scénario, c’est que l’on fait attendre l’utilisateur pour lui donner au final un mauvais résultat. J’ai encore un résultat qui n’a jamais existé en base de donnée, tout comme avec les lectures ‘sales’, mais cette fois je fais attendre l’utilisateur pour lui donner cette mauvaise réponse.

Dans la suite, j’étudierai ce que doivent faire les autres SGBD pour arriver à un résultat cohérent en lecture, un résultat juste.

Ce qu’il faut retenir, c’est que avec le même niveau d’isolation, apparemment sans risque, des SGBD différent peuvent donner des résultats très différents dans les mêmes circonstances. Et ce cas n’est pas seulement une possibilité, ce cas arrivera forcément. Il est important de comprendre qu’avec le SGBD Oracle, les lectures non bloquantes ne se font pas au prix de mauvais résultats. Vous pouvez avoir le beurre et l’argent du beurre, parfois.

REPEATABLE READ.

Le but de REPEATABLE READ est de fournir un niveau d’isolation qui donne des résultats cohérents et justes, et qui évite les mises à jour perdues (lost updates). Je vais donner des exemples de ce que vous devez faire sous Oracle pour atteindre ce but, et étudier ce qu’il se passe dans les autres SGBD. Si vous êtes en niveau d’isolation REPEATABLE READ, le résultat d’une requête donnée doit être cohérente par rapport à un instant donné. La plupart des SGBD (mais pas Oracle) accomplissent cela en utilisant des verrous partagés en lecture, au niveau enregistrement (row-level shared read locks). Un verrou partagé en lecture empêche les autres session de modifier les données que vous avez lu. Bien sûr, cela diminue la capacité multi-utilisateur (concurrency). Le SGBD Oracle a choisi le système de multi-versionnage, meilleur pour la concurrence d’accès, pour fournir des résultats cohérents en lecture.

Avec le multi-versionnage d’Oracle, le résultat de la requête est cohérent par rapport à l’instant où la requête a démarré. Dans les autres SGBD, avec les verrous partagés en lecture, vous avez une réponse qui est cohérente par rapport à l’instant de fin de la requête, c’est à dire au moment où vous pouvez avoir le résultat complet (plus de détail là dessus un peu plus loin).

Dans un SGBD qui utilise les verrous partagés en lecture pour permettre les lectures répétables, vous verrez que les lignes des tables sont verrouillées au fur et à mesure que la requête les lit. Donc, en utilisant l’exemple précédant, je vais laisser des verrous partagés en lecture (shared read locks) sur chaque ligne, comme le montre le scénario suivant:

Avec ce scénario, j’ai maintenant le résultat correct, mais au prix d’avoir bloqué une transaction, et d’avoir dû exécuter les transactions, l’une après l’autre. C’est l’un des effets de bord des verrous partagés en lecture: ceux qui lisent bloquent ceux qui écrivent. Cela se rajoute au fait que, dans ces systèmes, ceux qui écrivent bloquent ceux qui lisent. Imaginez si les Guichet Automatiques Bancaires fonctionnaient comme çà.

Vous voyez donc comment les verrous partagés en lecture inhibent la concurrence d’accès, mais ils peuvent aussi être à l’origine de fausses erreurs. Dans le scénario suivant, je part de la table originale, mais cette fois dans le but de transférer 50€ du compte 987 vers le compte 123.

Et je suis arrivé dans la situation classique du verrou mortel (deadlock). Ma requête bloque des ressources dont l’update a besoin, et vice versa. Ma requête est en deadlock avec la transaction. Une des deux doit être choisie pour être tuée. J’ai attendu un long moment, et j’ai utilisé beaucoup de ressources, juste pour être en erreur et finir par un rollback. C’est le deuxième effet de bord des verrous partagés en lecture: ceux qui écrivent et ceux qui lisent peuvent être en deadlock, et c’est le cas assez souvent.

Comme on l’a vu, sous Oracle, vous avez une lecture cohérente au niveau de chaque requête sans que les lectures ne bloquent les écritures, ou ne fassent de deadlock. Le SGBD Oracle n’utilise jamais de verrous partagés en lecture au niveau des lignes. Oracle a choisi le système de multi-versionnage, plus difficile à implémenter, mais infiniment meilleur en capacité de concurrence d’accès.

SERIALIZABLE

C’est le niveau généralement considéré comme le plus restrictif, mais c’est celui qui fournit le plus haut degré d’isolation. Une transaction SERIALIZABLE opère dans un environnement qui lui apparaît comme si il n’y avait uncun autre utilisateur en train de modifier la base de données. Vous êtes garantis que chaque ligne que vous lisez sera la même si vous la relisez, et que chaque requête que vous exécutez aura le même résultat durant toute la vie de la transaction.

Si vous exécutez par exemple:

Les 2 réponses seront les mêmes, alors que vous avez attendu 24 heures (Mais vous pouvez aussi avoir une erreur ORA-1555, snapshot too old). Ce niveau d’isolation vous assure que les 2 requêtes retournent le même résultat. Votre requête ne voit pas les effets de bord, ni les mises à jour faites par les autres transactions, et ce quel que soit la durée de son exécution.

Sous Oracle, l’implémentation des transactions SERIALIZABLE est faite de telle sorte que la lecture cohérente que vous avez au niveau de la requête est étendue au niveau de la transaction. (Comme il est noté plus haut, il y a une niveau d’isolation sous Oracle qui est appelé READ ONLY. Il a toutes les caractéristiques de SERIALIZABLE, sauf qu’il empêche les mises à jour. Il faut noter aussi que le user SYS (ou les utilisateurs connectés en SYSDBA) ne peuvent pas faire des transactions READ ONLY ou SERIALIZABLE. SYS est spécial pour cela).

Au lieu que les résultats soient cohérents par rapport au début de la requête, ils sont déterminés par le début de la transaction. Autrement dit, Oracle va utiliser les rollback segments pour reconstruire les données telles qu’elles étaient avant que votre transaction ne commence (le début de la transaction au lieu du début de la requête). C’est une pensée assez profonde: la base connait par avance les réponses à toutes les questions que vous pouvez poser, avant que vous le les posiez.

Mais ce degré d’isolation a un prix, vous pouvez vous retrouver avec l’erreur suivante:

Vous aurez ce message à chaque fois que vous essaierez de mettre à jour une ligne qui a été modifiée depuis le début de votre transaction. (Notez que Oracle essaie de faire cela au niveau ligne, mais vous pouvez recevoir l’erreur ORA-08177 même si la ligne que vous voulez mettre à jour n’a pas été modifiée. Cette erreur peut arriver si une autre ligne du même bloc a changé)

Oracle choisit une approche optimiste de la sérialisation: il parie sur le fait que les données que vous voulez mettre à jour dans votre transaction ne seront pas modifiées par une autre transaction. C’est en principe le cas, et le pari est payant, surtout pour des transactions courtes, dans les systèmes transactionnels (OLTP). Si personne ne modifie vos données durant votre transaction, ce niveau d’isolation garde le même degré de concurrence que sans transactions SERIALIZABLE, alors que dans les autres SGBD, il diminue souvent la capacité en concurrence d’accès. Le revers de la médaille, c’est que vous pouvez avoir l’erreur ORA-08177 si le pari ne paie pas. Mais en y réfléchissant, c’est un risque qui vaut le coup d’être pris. Si vous utilisez le niveau SERIALIZABLE, vous ne devriez pas vous attendre à modifier les mêmes données que les autres transactions.

Si c’est le cas, alors vous devriez utiliser SELECT ... FOR UPDATE comme décrit dans le Chapitre 1 [de Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions]. Cela permet de sérialiser l’accès.

Donc vous pouvez utiliser le niveau d’isolation SERIALIZABLE si:

• il y a une forte probabilité que personne d’autre ne modifie les mêmes données que vous
• vous avez besoin de lecture cohérente au niveau de toute la transaction
• vous faites des transactions courtes (pour avoir plus de chance de vérifier le 1er point)

Mais, car il y a toujours un ‘mais’, vous devez comprendre ces différents niveaux d’isolation et leur conséquences. Souvenez-vous, avec le niveau SERIALIZABLE, vous ne verrez aucune modification faite dans la base depuis le début de votre transaction, jusqu’à ce que vous ne committiez. Les applications qui cherchent à vérifier leurs propres contraintes d’intégrité, comme le planificateur de ressources du chapitre 1 [de Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions], doivent faire très attention à cela. Dans le Chapitre 1 le problème était que vous ne pouviez pas vérifier vos contrainte d’intégrité dans un système multi-utilisateur, puisque vous ne pouvez pas voir les modifications faites par les sessions concurrentes, avant qu’elles ne soient committées. En SERIALIZABLE, vous ne verrez toujours pas les modifications non committées, mais vous ne verrez pas non plus les modifications committées depuis le début de votre transaction.

Et pour finir, soyez prévenus que SERIALIZABLE ne veut pas dire que toutes les transactions des utilisateurs vont se comporter comme si elles avaient été exécutées les une après les autres, séquentiellement. Le niveau d’isolation n’impose pas qu’il y ait une sorte d’ordonnancement pour arriver au résultat de SERIALIZABLE. Les phénomènes expliqués précédemment, décrits par le standard SQL, n’imposent pas cela. Ce dernier point est un concept qui est souvent mal compris, et une petite démonstration permet de l’éclaircir.
Le scénario suivant montre comment deux sessions se comportent au cours du temps. Les tables A et B sont vides au démarrage, créées par:

Et voici le scénario:

A la fin de ce scénario, les tables A et B ont chacune une ligne avec la valeur 0. S’il y avait eu un ordonnancement en série des transactions, je ne pourrais pas avoir 0 sur chaque table. Si la session 1 s’était exécutée avant la session 2, la table B aurait la valeur 1. Et dans le cas contraire, c’est la table A qui aurait eu la valeur 1. Mais ici, les deux tables ont la valeur zéro. Chaque transaction s’est exécutée comme si elle était la seule transaction s’exécutant à ce moment là dans la base. Quelle que soit le nombre de fois à la session 1 exécute sa requête, le résultat du count() est celui qui correspond aux données committées en base au moment t1. Même chose pour la session 2 où le résultat du count() sera toujours celui qui correspond aux données committées en base au moment t2.

Le livre en Anglais de Tom Kyte dont est extrait l’article original: Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions Apress (September 15, 2005) La seconde édition (mise à jour avec la 11g) va paraître bientôt.