PostgreSQLLa base de données la plus sophistiquée au monde.

Version anglaise

13.2. Isolation des transactions

Le standard SQL définit quatre niveaux d'isolation de transaction. Le plus strict est Serializable, qui est défini par le standard dans un paragraphe qui déclare que toute exécution concurrente d'un jeu de transactions sérialisables doit apporter la garantie de produire le même effet que l'exécution consécutive de chacun d'entre eux dans un certain ordre. Les trois autres niveaux sont définis en terme de phénomènes, résultant de l'intéraction entre les transactions concurrentes, qui ne doivent pas se produire à chaque niveau. Le standard note qu'en raison de la définition de Serializable, aucun de ces phénomènes n'est possible à ce niveau. (Cela n'a rien de surprenant -- si l'effet des transactions doit être cohérent avec l'exécution consécutive de chacune d'entre elles, comment pourriez vous voir un phénomène causé par des interactions?).

Les phénomènes qui sont interdits à chaque niveau sont:

lecture sale

Une transaction lit des données écrites par une transaction concurrente non validée (dirty read).

lecture non reproductible

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) (non repeatable read).

lecture fantôme

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 (phantom read).

Les quatre niveaux d'isolation de transaction et les comportements correspondants sont décrits dans le Tableau 13.1, « Niveaux d'isolation standards des transactions SQL ».

Tableau 13.1. Niveaux d'isolation standards des transactions SQL

Niveau d'isolation Lecture sale Lecture non reproductible Lecture fantôme
Uncommited Read (en français, « Lecture de données non validées ») Possible Possible Possible
Commited Read (en français, « Lecture de données validées ») Impossible Possible Possible
Repeatable Read (en français, « Lecture répétée ») Impossible Impossible Possible
Serializable (en français, « Sérialisable ») Impossible Impossible Impossible

Dans PostgreSQL™, vous pouvez demander un des quatre niveaux standards d'isolation de transaction. Mais, en interne, il existe seulement trois niveaux distincts d'isolation, qui correspondent aux niveaux Read Committed et Repeatable Read, and Serializable. Lorsque vous sélectionnez le niveau Read Uncommitted, vous obtenez réellement Read Committed, et les lectures fantômes ne sont pas possibles dans l'implémentation PostgreSQL™ de Repeatable Read. Le niveau d'isolation actuel pourrait donc être plus strict que ce que vous sélectionnez. Ceci est permis par le standard SQL. Les quatre niveaux d'isolation définissent seulement quel phénomène ne doit pas survenir, ils ne définissent pas ce qui doit arriver. La raison pour laquelle PostgreSQL™ fournit seulement trois niveaux d'isolation est qu'il s'agit de la seule façon raisonnable de faire correspondre les niveaux d'isolation standards avec l'architecture de contrôle des accès simultanés multiversion. Le comportement des niveaux standards d'isolation est détaillé dans les sous-sections suivantes.

Pour initialiser le niveau d'isolation d'une transaction, utilisez la commande SET TRANSACTION(7).

[Important]

Important

Certains types de données et certaines fonctions de PostgreSQL™ ont des règles spéciales sur le comportement des transactions. En particulier, les modifications réalisées sur une séquence (et du coup sur le compteur d'une colonne déclarée serial) sont immédiatement visibles de toutes les autres transactions et ne sont pas annulées si la transaction qui a fait la modification est annulée. Voir Section 9.16, « Fonctions de manipulation de séquences » et Section 8.1.4, « Types seriés ».

13.2.1. Niveau d'isolation Read committed (lecture uniquement des données validées)

Read Committed est le niveau d'isolation par défaut dans PostgreSQL™. Quand une transaction utilise ce niveau d'isolation, une requête SELECT (sans clause FOR UPDATE/SHARE) voit seulement les données validées avant le début de la requête ; il ne voit jamais les données non validées et les modifications validées pendant l'exécution de la requête par des transactions exécutées en parallèle. En effet, une requête SELECT voit une image de la base de données datant du moment où l'exécution de la requête commence. Néanmoins, SELECT voit les effets de mises à jour précédentes exécutées dans sa propre transaction, même si celles-ci n'ont pas encore été validées. De plus, notez que deux commandes SELECT successives peuvent voir des données différentes, même si elles sont exécutées dans la même transaction si d'autres transactions valident des modifications pendant l'exécution du premier SELECT.

Les commandes UPDATE, DELETE, SELECT FOR UPDATE et SELECT FOR SHARE se comportent de la même façon que SELECT en ce qui concerne la recherche des lignes cibles : elles ne trouveront que les lignes cibles qui ont été validées avant le début de la commande. Néanmoins, une telle ligne cible pourrait avoir déjà été mise à jour (ou supprimée ou verrouillée) par une autre transaction concurrente au moment où elle est découverte. Dans ce cas, le processus de mise à jour attendra que la première transaction soit validée ou annulée (si elle est toujours en cours). Si la première mise à jour est annulée, alors ses effets sont niés et le deuxième processus peut exécuter la mise à jour des lignes originellement trouvées. Si la première mise à jour est validée, la deuxième mise à jour ignorera la ligne si la première mise à jour l'a supprimée, sinon elle essaiera d'appliquer son opération à la version mise à jour de la ligne. La condition de la recherche de la commande (la clause WHERE) est ré-évaluée pour savoir si la version mise à jour de la ligne correspond toujours à la condition de recherche. Dans ce cas, la deuxième mise à jour continue son opération en utilisant la version mise à jour de la ligne. Dans le cas des commandes SELECT FOR UPDATE et SELECT FOR SHARE, cela signifie que la version mise à jour de la ligne est verrouillée et renvoyée au client.

À cause de la règle ci-dessus, une commande de mise à jour a la possibilité de voir une image non cohérente : elle peut voir les effets de commandes de mises à jour concurrentes sur les mêmes lignes que celles qu'elle essaie de mettre à jour mais elle ne voit pas les effets de ces commandes sur les autres lignes de la base de données. Ce comportement rend le mode de lecture validée non convenable pour les commandes qui impliquent des conditions de recherche complexes ; néanmoins, il est intéressant pour les cas simples. Par exemple, considérons la mise à jour de balances de banque avec des transactions comme :

BEGIN;
UPDATE comptes SET balance = balance + 100.00 WHERE no_compte = 12345;
UPDATE comptes SET balance = balance - 100.00 WHERE no_compte = 7534;
COMMIT;

Si deux transactions comme celle-ci essaient de modifier en même temps la balance du compte 12345, nous voulons clairement que la deuxième transaction commence à partir de la version mise à jour de la ligne du compte. Comme chaque commande n'affecte qu'une ligne prédéterminée, la laisser voir la version mise à jour de la ligne ne crée pas de soucis de cohérence.

Des utilisations plus complexes peuvent produire des résultats non désirés dans le mode Read Committed. Par exemple, considérez une commande DELETE opérant sur des données qui sont à la fois ajoutées et supprimées du critère de restriction par une autre commande. Supposons que website est une table sur deux lignes avec website.hits valant 9 et 10 :

BEGIN;
UPDATE website SET hits = hits + 1;
-- exécuté par une autre session :  DELETE FROM website WHERE hits = 10;
COMMIT;

La commande DELETE n'aura pas d'effet même s'il existe une ligne website.hits = 10 avant et après la commande UPDATE. Cela survient parce que la valeur 9 de la ligne avant mise à jour est ignorée et que lorsque l'UPDATE termine et que DELETE obtient un verrou, la nouvelle valeur de la ligne n'est plus 10, mais 11, ce qui ne correspond plus au critère.

Comme le mode Read Committed commence chaque commande avec une nouvelle image qui inclut toutes les transactions validées jusqu'à cet instant, les commandes suivantes dans la même transaction verront les effets de la transaction validée en parallèle dans tous les cas. Le problème en question est de savoir si une seule commande voit une vue absolument cohérente ou non de la base de données.

L'isolation partielle des transactions fournie par le mode Read Committed est adéquate pour de nombreuses applications, et ce mode est rapide et simple à utiliser. Néanmoins, il n'est pas suffisant dans tous les cas. Les applications qui exécutent des requêtes et des mises à jour complexes pourraient avoir besoin d'une vue plus rigoureusement cohérente de la base de données, une vue que le mode Read Committed ne fournit pas.

13.2.2. Repeatable Read Isolation Level

Le niveau d'isolation Repeatable Read ne voit que les données validées avant que la transaction ait démarré; il ne voit jamais ni les données non validées, ni les données validées par des transactions concurrentes durant son exécution. (Toutefois, la requête voit les effets de mises à jour précédentes effectuées dans sa propre transaction, bien qu'elles ne soient pas encore validées). C'est une garantie plus élevée que requise par le standard SQL pour ce niveau d'isolation, et elle évite le phénomène décrit dans Tableau 13.1, « Niveaux d'isolation standards des transactions SQL ». Comme mentionné plus haut, c'est permis par le standard, qui ne définit que la protection minimale que chaque niveau d'isolation doit fournir.

Ce niveau est différent de Read Committed parce qu'une requête dans une transaction repeatable read voit un instantané au début de la transaction, et non pas du début de la requête en cours à l'intérieur de la transaction. Du coup, les commandes SELECT successives à l'intérieur d'une seule transaction voient toujours les mêmes données, c'est-à-dire qu'elles ne voient jamais les modifications faites par les autres transactions qui ont validé après le début de leur propre transaction.

Les applications utilisant ce niveau d'isolation doivent être préparées à retenter des transactions à cause d'échecs de sérialisation.

Les commandes UPDATE, DELETE, SELECT FOR UPDATE et SELECT FOR SHARE se comportent de la même façon que SELECT en ce qui concerne la recherche de lignes cibles : elles trouveront seulement les lignes cibles qui ont été validées avant le début de la transaction. Néanmoins, une telle ligne cible pourrait avoir été mise à jour (ou supprimée ou verrouillée) par une autre transaction concurrente au moment où elle est utilisée. Dans ce cas, la transaction repeatable read attendra que la première transaction de mise à jour soit validée ou annulée (si celle-ci est toujours en cours). Si la première mise à jour est annulée, les effets sont inversés et la transaction repeatable read peut continuer avec la mise à jour de la ligne trouvée à l'origine. Mais si la mise à jour est validée (et que la ligne est mise à jour ou supprimée, pas simplement verrouillée), alors la transaction repeatable read sera annulée avec le message

              ERROR:  could not serialize access due to concurrent update

parce qu'une transaction sérialisable ne peut pas modifier ou verrouiller les lignes changées par d'autres transactions après que la transaction sérialisable ait commencé.

Quand une application reçoit ce message d'erreurs, elle devrait annuler la transaction actuelle et ré-essayer la transaction complète. La seconde fois, la transaction voit les modifications déjà validées comme faisant partie de sa vue initiale de la base de données, donc il n'y a pas de conflit logique en utilisant la nouvelle version de la ligne comme point de départ pour la mise à jour de la nouvelle transaction.

Notez que seules les transactions de modifications ont besoin d'être tentées de nouveau ; les transactions en lecture seule n'auront jamais de conflits de sérialisation.

Le mode Repeatable Repeatable fournit une garantie rigoureuse que chaque transaction voit un état complètement stable de la base de données. Toutefois cette vue ne sera pas nécessairement toujours cohérente avec l'exécution sérielle (un à la fois) de transactions concurrentes du même niveau d'isolation. Par exemple, même une transaction en lecture seule à ce niveau pourrait voire un enregistrement de contrôle mis à jour pour indiquer qu'un traitement par lot a été terminé mais ne pas voir un des enregistrements de détail qui est une partie logique du traitement par lot parce qu'il a lu une ancienne version de l'enregistrement de contrôle. L'implémentation correcte de règles de gestion par des transactions s'exécutant à ce niveau d'isolation risque de ne pas marcher correctement sans une utilisation prudente de verrouillages explicites qui bloquent les transactions en conflits.

Avant la version 9.1 de PostgreSQL™, une demande d'isolation de transaction Serializable fournissait exactement le comportement décrit ici. Pour maintenir l'ancien niveau Serializable, il faudra maintenant demander Repeatable Read.

13.2.3. Niveau d'Isolation Serializable

Le niveau d'isolation Serializable fournit le niveau d'isolation le plus strict. Ce niveau émule l'exécution sérielle de transaction pour toutes les transactions validées, comme si les transactions avaient été exécutées les unes après les autres, séquentiellement, plutôt que simultanément. Toutefois, comme pour le niveau Repeatable Read, les applications utilisant ce niveau d'isolation doivent être prêtes à répéter leurs transactions en cas d'échec de sérialisation. En fait, ce niveau d'isolation fonctionne exactement comme Repeatable Read, excepté qu'il surveille les conditions qui pourraient amener l'exécution d'un jeu de transactions concurrentes à se comporter d'une manière incomptible avec les exécutions sérielles (une à la fois) de toutes ces transactions. Cette surveillance n'introduit aucun blocage supplémentaire par rapport à repeatable read, mais il y a un coût à cette surveillance, et la détection des conditions pouvant amener une anomalie de sérialisation déclenchera un échec de sérialisation.

Comme exemple, considérez la table ma_table, contenant initialement

 classe | valeur
--------+-------
     1  |    10
     1  |    20
     2  |   100
     2  |   200

Supposons que la transaction sérialisable A traite

              SELECT SUM(valeur) FROM ma_table WHERE classe = 1;

puis insère le résultat (30) comme valeur dans une nouvelle ligne avec classe = 2. Simultanément, la transaction serialisable B traite

              SELECT SUM(valeur) FROM ma_table WHERE classe = 2;

et obtient le résultat 300, qu'il insère dans une nouvelle ligne avec classe = 1. À ce moment là les deux transactions essayent de valider. Si l'une des transactions fonctionnait au niveau d'isolation Repeatable Read, les deux seraient autorisées à valider; mais puisqu'il n'y a pas d'ordre d'exécution sériel cohérent avec le résultat, l'utilisation de transactions Serializable permettra à une des deux transactions de valider, et annulera l'autre avec ce message:

ERREUR:  n'a pas pu sérialiser un accès à cause d'une mise à jour en parallèle"

C'est parce que si A a été exécuté avant B, B aurait trouvé la somme 330, et non pas 300. De façon similaire, l'autre ordre aurait eu comme résultat une somme différente pour le calcul par A.

Si on se fie aux transactions sérialisées pour empêcher les anomalies, il est important que toute donnée lue à partir d'une table utilisateur permanente soit considérée valide jusqu'à ce que la transaction qui l'a lu soit validée avec succès. Ceci est vrai même pour les transactions en lecture seule, sauf pour les données lues dans une transaction déferrable en lecture seule qui sont garanties être valides à leur lecture car une telle transaction attend jusqu'à l'obtention d'une image garantie libre de tout problème avant lecture. Dans tous les autres cas, les applications ne doivent pas dépendre des lectures d'une transaction qui a été par la suite annulée. À la place, elles doivent tenter de nouveau la transaction jusqu'à ce qu'elle réussisse.

Pour garantir une vraie sérialisation PostgreSQL™ utilise le verrouillage de prédicats, ce qui signifie qu'il conserve des verrous qui permettent de déterminer quand une écriture aurait eu un impact sur le résultat d'une lecture antérieure par une transaction concurrente, si elle s'était exécutée d'abord. Dans PostgreSQL™, ces verrous ne causent pas de blocage et ne peuvent donc pas jouer un rôle dans l'avènement d'un verrou mortel (deadlock). Ils sont utilisés pour identifier et marquer les dépendances entre des transactions sérialisables concurrentes qui dans certaines combinaisons peuvent entrainer des anomalies de sérialisation. Par contraste, une transaction Read Committed ou Repeatable Read qui voudrait garantir la cohérence des données devra prendre un verrou sur la table entière, ce qui pourrait bloquer d'autres utilisateurs voulant utiliser cette table, ou pourrait utiliser SELECT FOR UPDATE ou SELECT FOR SELECT qui non seulement peut bloquer d'autres transactions, mais entraîne un accès au disque.

Les verrous de prédicats dans PostgreSQL™, comme dans la plupart des autres systèmes de bases de données, s'appuient sur les données réellement accédées par une transaction. Ils seront visibles dans la vue système pg_locks avec un mode de SIReadLock. Les verrous acquis pendant l'exécution d'une requête dépendront du plan utilisé par la requête, et plusieurs verrous fins (par exemple, des verrous d'enregistrement) pourraient être combinés en verrous plus grossiers (par exemple, des verrous de page) pendant le déroulement de la transaction afin d'éviter d'épuiser la mémoire utilisée pour suivre les verrous. Une transaction READ ONLY pourra libérer ses verrous SIRead avant sa fin, si elle détecte qu'aucun conflit ne peut encore se produire pouvant potentiellement entrainer une anomalie de sérialisation. En fait, les transaction READ ONLY seront souvent capable d'établir ce fait au moment de leur démarrage, et ainsi éviter de prendre des verrous de prédicat. Si vous demandez explicitement une transaction SERIALIZABLE READ ONLY DEFERRABLE, elle bloquera jusqu'à ce qu'elle puisse établir ce fait. (C'est le seul cas où_une transaction Serializable bloque mais pas une transaction Repeatable Read.) D'autre part, les verrous SIRead doivent souvent être gardés après la fin d'une transaction, jusqu'à ce que toutes les lectures-écritures s'étant déroulées simultanément soient terminées.

L'utilisation systématique de transactions Serializable peut simplifier le développement. La garantie que tout ensemble de transactions Serializable validées avec succès auront le même effet que si elles avaient été exécutées une à la fois signifie que vous pouvez démontrer qu'une seule transaction, telle qu'elle est écrite, aura le même bon résultat dans n'importe quel mélange de transactions Serializable, même sans aucune information sur ce que font les autres transactions. Il est important qu'un environnement qui utilise cette technique ait une façon généralisée de traiter les erreurs de sérialisation (qui retournent toujours un SQLSTATE valant '40001'), parce qu'il sera très difficile de prédire exactement quelles transactions pourraient contribuer à des dépendances lecture/écriture et auront besoin d'être annulées pour éviter les anomalies de sérialisation. La surveillance des dépendances lecture/écriture a un coût, tout comme l'échec, mais mis en face du coût et du blocage entrainés par les verrous explicites et SELECT FOR UPDATE ou SELECT FOR SHARE, les transactions serializable sont le meilleur choix en termes de performances pour certains environnements.

Bien que le niveau d'isolation Serializable des transactions pour PostgreSQL™ permet seulement à des transactions parallèles de valider leurs modifications que s'il est prouvé qu'un ordre d'exécution en série produirait le même résultat, cela n'empêche pas toujours la montée d'erreurs qui ne surviendrait pas dans une véritable exécution en série. En particulier, il est possible de voir des violations de contraintes uniques causées par des conflits sur des transactions Serializable qui se surchargent même après avoir vérifié explicitement que la clé n'est pas présente avant de tenter son insertion. Ceci peut s'éviter en s'assurant que toutes les transactions Serializable qui peuvent insérer des clés en conflit vérifient explicitement avant si elles peuvent l'insérer. Par exemple, imaginez une application qui demande à un utilisateur une nouvelle clé, puis vérifie si elle n'existe pas déjà, ou génère une nouvelle clé en sélectionne la clé maximale déjà existante et en ajoutant la suivante. Si certaines transactions Serializable insèrent de nouvelles clés directement sans suivre ce protocole, les violations de contraintes uniques doivent être reportées même dans les cas où elles ne pourraient pas survenir dans le cas d'une exécution en série de transactions concurrentes.

Pour une performance optimale quand on s'appuie sur les transactions Serializable pour le contrôle de la concurrence, ces points doivent être pris en considération:

  • Déclarer les transactions comme READ ONLY quand c'est possible.

  • Contrôler le nombre de connexions actives, en utilisant un pool de connexions si nécessaire. C'est toujours un point important pour les performances, mais cela peut être particulièrement important pour un système chargé qui utilise des transactions Serializable.

  • Ne mettez jamais plus dans une transaction seule qu'il n'est nécessaire dans un but d'intégrité.

  • Ne laissez pas des connexions trainer en « idle in transaction » plus longtemps que nécessaire.

  • Supprimez les verrous explicites, SELECT FOR UPDATE, et SELECT FOR SHARE qui ne sont plus nécessaires grâce aux protections fournies automatiquement par les transactions Serializable.

  • Quand le système est forcé à combiner plusieurs verrous de prédicat au niveau page en un seul verrou de prédicat au niveau relation (si la table des verrous de prédicat est à court de mémoire), une augmentation du taux d'échecs de sérialisation peut survenir. Vous pouvez éviter ceci en augmentant max_pred_locks_per_transaction.

  • Un parcours séquentiel nécessitera toujours un verrou de prédicat au niveau relation. Ceci peut résulter en un taux plus important d'échecs de sérialisation. Il peut être utile d'encourager l'utilisation de parcours d'index en diminuant random_page_cost et/ou en augmentant cpu_tuple_cost. Assurez-vous de bien mesurer toute diminution du nombre d'annulation de transactions et restarts against any overall change in query execution time.

[Avertissement]

Avertissement

Le support pour le niveau d'isolation Serializable n'a pas encore été ajouté aux cibles de réplication Hot Standby (décrites dans Section 25.5, « Hot Standby »). Bien que les écritures permanentes dans la base effectuées dans des transactions Serializable sur le maître garantiront que toutes les standbys atteindront un état cohérent, une transaction Repeatable Read sur la standby pourra quelquefois voir un état transitoire qui sera incohérent avec une exécution sérielle sur le maître.