Bonjour, Habr! Avec cet article, je commence un ensemble de séries (ou une série d'ensembles? - En un mot, l'idée est grandiose) sur la structure interne de PostgreSQL.

Le matériel sera basé sur des cours de formation (en russe) sur l'administration que Pavel pluzanov et moi créons. Tout le monde n'aime pas regarder des vidéos (ce n'est certainement pas le cas), et lire des diapositives, même avec des commentaires, n'est pas bon du tout.

Malheureusement, le seul cours disponible en anglais pour le moment est l' introduction de 2 jours à PostgreSQL 11 .

Bien sûr, les articles ne seront pas exactement les mêmes que le contenu des cours. Je ne parlerai que de la façon dont tout est organisé, en omettant l'administration elle-même, mais j'essaierai de le faire plus en détail et de manière plus approfondie. Et je crois que ces connaissances sont aussi utiles à un développeur d'applications qu'à un administrateur.

Je vais cibler ceux qui ont déjà une certaine expérience de l'utilisation de PostgreSQL et au moins en général comprendre ce qui est quoi. Le texte sera trop difficile pour les débutants. Par exemple, je ne dirai pas un mot sur la façon d'installer PostgreSQL et d'exécuter psql.

Le contenu en question ne varie pas beaucoup d'une version à l'autre, mais j'utiliserai le 11e PostgreSQL vanilla actuel.

La première série traite des problèmes liés à l'isolement et à la concurrence multiversionnelle, et le plan de la série est le suivant:

1. Isolement tel que compris par le standard et PostgreSQL (cet article).
2. Forks, fichiers, pages - ce qui se passe au niveau physique.
3. Versions de ligne , transactions virtuelles et sous-transactions.
4. Instantanés de données et visibilité des versions de ligne; l'horizon des événements.
5. Vide sur la page et mises à jour CHAUDES .
6. Vide normal .
7. Autovacuum .
8. Transformation et gel de l'ID de transaction .

C'est parti!

Et avant de commencer, je voudrais remercier Elena Indrupskaya d'avoir traduit les articles en anglais.

Qu'est-ce que l'isolement et pourquoi est-il important?

Probablement, tout le monde est au moins conscient de l'existence de transactions, est tombé sur l'abréviation ACID et a entendu parler des niveaux d'isolement. Mais nous nous trouvons toujours face à l'opinion que cela relève de la théorie, ce qui n'est pas nécessaire dans la pratique. Je vais donc passer un peu de temps à expliquer pourquoi c'est vraiment important.

Il est peu probable que vous soyez satisfait si une application obtient des données incorrectes de la base de données ou si l'application écrit des données incorrectes dans la base de données.

Mais que sont les données «correctes»? Il est connu que des contraintes d'intégrité , telles que NOT NULL ou UNIQUE, peuvent être créées au niveau de la base de données. Si les données respectent toujours les contraintes d'intégrité (et c'est le cas puisque le SGBD le garantit), alors elles sont intégrales.

Les mêmes choses sont-elles correctes et intégrales ? Pas exactement. Toutes les contraintes ne peuvent pas être spécifiées au niveau de la base de données. Certaines contraintes sont trop compliquées, par exemple, qui couvrent plusieurs tables à la fois. Et même si une contrainte en général aurait pu être définie dans la base de données, mais pour une raison inconnue, cela ne signifie pas que la contrainte peut être violée.

Ainsi, l' exactitude est plus forte que l' intégrité , mais nous ne savons pas exactement ce que cela signifie. Nous devons admettre que «l'étalon-or» de la rectitude est une application qui, comme nous aimerions le croire, est écrite correctement et ne fonctionne jamais mal. Dans tous les cas, si une application ne viole pas l'intégrité, mais viole l'exactitude, le SGBD n'en sera pas informé et n'attrapera pas l'application «en flagrant délit».

De plus, nous utiliserons le terme cohérence pour désigner l'exactitude.

Supposons cependant qu'une application exécute uniquement des séquences d'opérateurs correctes. Quel est le rôle du SGBD si l'application est correcte en l'état?

Tout d'abord, il s'avère qu'une séquence correcte d'opérateurs peut temporairement rompre la cohérence des données et, curieusement, c'est normal. Un exemple galvaudé mais clair est un transfert de fonds d'un compte à un autre. La règle de cohérence peut ressembler à ceci: un transfert ne modifie jamais le montant total d'argent sur les comptes (cette règle est assez difficile à spécifier dans SQL en tant que contrainte d'intégrité, elle existe donc au niveau de l'application et est invisible pour le SGBD). Un transfert se compose de deux opérations: la première réduit les fonds sur un compte, et la seconde - les augmente sur l'autre. La première opération rompt la cohérence des données, tandis que la seconde la restaure.

Un bon exercice consiste à appliquer la règle ci-dessus au niveau des contraintes d'intégrité.

Que faire si la première opération est effectuée et que la seconde ne l'est pas? En fait, sans trop de bruit: lors de la deuxième opération, il peut se produire une panne d'électricité, une panne de serveur, une division par zéro - peu importe. Il est clair que la cohérence sera rompue et cela ne peut pas être autorisé. En général, il est possible de résoudre ces problèmes au niveau de l'application, mais au prix d'efforts considérables; cependant, heureusement, ce n'est pas nécessaire: cela se fait par le SGBD. Mais pour ce faire, le SGBD doit savoir que les deux opérations sont un tout indivisible. Autrement dit, une transaction .

Cela s'avère intéressant: comme le SGBD sait que les opérations constituent une transaction, il aide à maintenir la cohérence en s'assurant que les transactions sont atomiques, et il le fait sans rien savoir des règles de cohérence spécifiques.

Mais il y a un deuxième point, plus subtil. Dès que plusieurs transactions simultanées apparaissent dans le système, qui sont absolument correctes séparément, elles peuvent ne pas fonctionner correctement ensemble. En effet, l'ordre des opérations est mélangé: vous ne pouvez pas supposer que toutes les opérations d'une transaction sont effectuées en premier, puis toutes les opérations de l'autre.

Une note sur la simultanéité. En effet, les transactions peuvent s'exécuter simultanément sur un système doté d'un processeur multicœur, d'une baie de disques, etc. Mais le même raisonnement vaut pour un serveur qui exécute des commandes séquentiellement, dans un mode de partage de temps: pendant certains cycles d'horloge, une transaction est exécutée et pendant certains cycles suivants, l'autre. Parfois, le terme exécution simultanée est utilisé pour une généralisation.

Les situations où les transactions correctes fonctionnent incorrectement sont appelées anomalies d'exécution simultanée.

Pour un exemple simple: si une application veut obtenir des données correctes de la base de données, elle ne doit pas, au moins, voir les changements d'autres transactions non validées. Sinon, vous pouvez non seulement obtenir des données incohérentes, mais également voir quelque chose qui n'a jamais été dans la base de données (si la transaction est annulée). Cette anomalie est appelée une lecture sale .

Il y a d'autres anomalies, plus complexes, que nous traiterons un peu plus tard.

Il est certainement impossible d'éviter l'exécution simultanée: sinon, de quel type de performance peut-on parler? Mais vous ne pouvez pas non plus travailler avec des données incorrectes.

Et encore une fois, le SGBD vient à la rescousse. Vous pouvez effectuer des transactions comme si elles étaient séquentielles, comme si elles se succédaient. En d'autres termes - isolés les uns des autres. En réalité, le SGBD peut effectuer des opérations mélangées, mais assurez-vous que le résultat d'une exécution simultanée sera le même que le résultat de certaines des exécutions séquentielles possibles. Et cela élimine toutes les anomalies possibles.

Nous sommes donc arrivés à la définition:

Une transaction est un ensemble d'opérations effectuées par une application qui transfère une base de données d'un état correct à un autre état correct (cohérence), à ​​condition que la transaction soit terminée (atomicité) et sans interférence d'autres transactions (isolement).

Cette définition unit les trois premières lettres de l'acronyme ACID. Ils sont si étroitement liés les uns aux autres qu'il est insensé de considérer l'un sans les autres. En fait, il est également difficile de détacher la lettre D (durabilité). En effet, lorsqu'un système se bloque, il présente toujours des modifications de transactions non validées, avec lesquelles vous devez faire quelque chose pour restaurer la cohérence des données.

Tout aurait été parfait, mais la mise en œuvre d'une isolation complète est une tâche techniquement difficile entraînant une réduction du débit du système. Par conséquent, dans la pratique très souvent (pas toujours, mais presque toujours) l'isolement affaibli est utilisé, ce qui empêche certaines, mais pas toutes les anomalies. Cela signifie qu'une partie du travail visant à garantir l'exactitude des données incombe à l'application. Pour cette raison, il est très important de comprendre quel niveau d'isolement est utilisé dans le système, ce qui le garantit et ce qu'il ne donne pas, et comment écrire du code correct dans de telles conditions.

Niveaux d'isolement et anomalies dans la norme SQL

Le standard SQL décrit depuis longtemps quatre niveaux d'isolement. Ces niveaux sont définis en répertoriant les anomalies autorisées ou non lorsque les transactions sont exécutées simultanément à ce niveau. Par conséquent, pour parler de ces niveaux, il est nécessaire de connaître les anomalies.

J'insiste sur le fait que, dans cette partie, nous parlons de la norme, c'est-à-dire d'une théorie, sur laquelle la pratique se fonde de manière significative, mais à partir de laquelle elle diverge de manière significative. Par conséquent, tous les exemples ici sont spéculatifs. Ils utiliseront les mêmes opérations sur les comptes clients: c'est assez démonstratif, même si, certes, cela n'a rien à voir avec l'organisation des opérations bancaires dans la réalité.

Mise à jour des pertes

Commençons par une mise à jour perdue . Cette anomalie se produit lorsque deux transactions lisent la même ligne de la table, puis une transaction met à jour cette ligne, puis la deuxième transaction met également à jour la même ligne sans prendre en compte les modifications apportées par la première transaction.

Par exemple, deux transactions vont augmenter le montant sur le même compte de ₽100 (₽ est le symbole monétaire du rouble russe). La première transaction lit la valeur actuelle (₽1000), puis la deuxième transaction lit la même valeur. La première transaction augmente le montant (cela donne ₽1100) et écrit cette valeur. La deuxième transaction agit de la même manière: elle obtient le même ₽1100 et écrit cette valeur. En conséquence, le client a perdu 100 ₽.

La norme n'autorise pas les mises à jour perdues à n'importe quel niveau d'isolement.

Lecture sale et lecture non validée

Une lecture sale est ce que nous avons déjà appris. Cette anomalie se produit lorsqu'une transaction lit des modifications qui n'ont pas encore été validées par une autre transaction.

Par exemple, la première transaction transfère tout l'argent du compte du client vers un autre compte, mais n'engage pas la modification. Une autre transaction lit le solde du compte, pour obtenir ₽0, et refuse de retirer de l'argent au client, bien que la première transaction abandonne et annule ses modifications, de sorte que la valeur de 0 n'a jamais existé dans la base de données.

La norme autorise les lectures incorrectes au niveau de lecture non validée.

Lecture non répétable et lecture validée

Une anomalie de lecture non répétable se produit lorsqu'une transaction lit deux fois la même ligne, et entre les lectures, la deuxième transaction modifie (ou supprime) cette ligne et valide les modifications. Ensuite, la première transaction obtiendra des résultats différents.

Par exemple, laissez une règle de cohérence interdire les montants négatifs sur les comptes clients . La première transaction va réduire le montant du compte de ₽100. Il vérifie la valeur actuelle, obtient ₽1000 et décide que la diminution est possible. Dans le même temps, la deuxième transaction réduit à zéro le montant du compte et valide les modifications. Si la première transaction revérifiait maintenant le montant, elle obtiendrait ₽0 (mais elle a déjà décidé de réduire la valeur, et le compte «passe au rouge»).

La norme autorise les lectures non répétables aux niveaux Lecture non validée et Lecture validée. Mais Read Committed ne permet pas de lectures incorrectes.

Lecture fantôme et lecture répétable

Une lecture fantôme se produit lorsqu'une transaction lit un ensemble de lignes par la même condition deux fois, et entre les lectures, la deuxième transaction ajoute des lignes qui remplissent cette condition (et valide les modifications). Ensuite, la première transaction obtiendra un ensemble différent de lignes.

Par exemple, laissez une règle de cohérence empêcher un client d'avoir plus de 3 comptes . La première transaction va ouvrir un nouveau compte, vérifie le nombre actuel de comptes (disons, 2) et décide que l'ouverture est possible. Dans le même temps, la deuxième transaction ouvre également un nouveau compte pour le client et valide les modifications. Maintenant, si la première transaction revérifiait le nombre, il obtiendrait 3 (mais il ouvre déjà un autre compte, et le client semble en avoir 4).

La norme autorise les lectures fantômes aux niveaux Lecture non validée, Lecture validée et Lecture répétable. Cependant, la lecture non répétable n'est pas autorisée au niveau de la lecture répétable.

L'absence d'anomalies et sérialisables

La norme définit un niveau de plus - Sérialisable - qui ne permet aucune anomalie. Et ce n'est pas la même chose que d'interdire les mises à jour perdues et les lectures sales, non répétables ou fantômes.

Le fait est qu'il y a beaucoup plus d'anomalies connues que celles énumérées dans la norme et aussi un nombre inconnu de celles encore inconnues.

Le niveau sérialisable doit empêcher absolument toutes les anomalies. Cela signifie qu'à ce niveau, un développeur d'applications n'a pas besoin de penser à l'exécution simultanée. Si les transactions exécutent une séquence correcte d'opérateurs travaillant séparément, les données seront également cohérentes lorsque ces transactions seront exécutées simultanément.

Tableau récapitulatif

Nous pouvons maintenant fournir un tableau bien connu. Mais ici, la dernière colonne, qui manque dans la norme, est ajoutée pour plus de clarté.

Pourquoi exactement ces anomalies?

Pourquoi la norme ne répertorie-t-elle que quelques-unes des nombreuses anomalies possibles et pourquoi sont-elles exactement celles-ci?

Personne ne semble le savoir avec certitude. Mais ici, la pratique est évidemment en avance sur la théorie, il est donc possible qu'à cette époque (de la norme SQL: 92) d'autres anomalies n'aient pas été simplement pensées.

De plus, on a supposé que l'isolement devait être construit sur des serrures. L'idée derrière le protocole de verrouillage à deux phases largement utilisé (2PL) est que lors de l'exécution, une transaction verrouille les lignes avec lesquelles elle travaille et libère les verrous à la fin. Simplifiant considérablement, plus une transaction acquiert de verrous, mieux elle est isolée des autres transactions. Mais les performances du système souffrent également davantage, car au lieu de travailler ensemble, les transactions commencent à se mettre en file d'attente pour les mêmes lignes.

Mon sentiment est que c'est juste le nombre de verrous requis, qui explique la différence entre les niveaux d'isolement de la norme.

Si une transaction verrouille les lignes à modifier de la mise à jour, mais pas de la lecture, nous obtenons le niveau de lecture non validée: les modifications perdues ne sont pas autorisées, mais les données non validées peuvent être lues.

Si une transaction verrouille les lignes à modifier à la fois de la lecture et de la mise à jour, nous obtenons le niveau de lecture validée: vous ne pouvez pas lire les données non validées, mais vous pouvez obtenir une valeur différente (lecture non répétable) lorsque vous accédez à nouveau à la ligne.

Si une transaction verrouille les lignes à lire et à modifier et à la fois de la lecture et de la mise à jour, nous obtenons le niveau de lecture répétable: relire la ligne retournera la même valeur.

Mais il y a un problème avec Serializable: vous ne pouvez pas verrouiller une ligne qui n'existe pas encore. Par conséquent, une lecture fantôme est toujours possible: une autre transaction peut ajouter (mais pas supprimer) une ligne qui remplit les conditions d'une requête précédemment exécutée, et cette ligne sera incluse dans la resélection.

Par conséquent, pour implémenter le niveau Sérialisable, les verrous normaux ne suffisent pas - vous devez verrouiller les conditions (prédicats) plutôt que les lignes. Par conséquent, ces verrous étaient appelés prédicats . Ils ont été proposés en 1976, mais leur applicabilité pratique est limitée par des conditions assez simples pour lesquelles il est clair comment joindre deux prédicats différents. Pour autant que je sache, de tels verrous n'ont jamais été implémentés dans aucun système jusqu'à présent.

Niveaux d'isolement dans PostgreSQL

Au fil du temps, les protocoles de gestion des transactions basés sur les verrous ont été remplacés par le protocole Snapshot Isolation (SI). Son idée est que chaque transaction fonctionne avec un instantané cohérent des données à un certain point dans le temps, et seules ces modifications entrent dans l'instantané qui ont été validées avant sa création.

Cette isolation empêche automatiquement les lectures sales. Formellement, vous pouvez spécifier le niveau Read Uncommitted dans PostgreSQL, mais cela fonctionnera exactement de la même manière que Read Committed. Par conséquent, nous ne parlerons pas du tout du niveau Read Uncommitted.

PostgreSQL implémente une variante multiversionnelle de ce protocole. L'idée de la concurrence multiversionnelle est que plusieurs versions de la même ligne peuvent coexister dans un SGBD. Cela vous permet de créer un instantané des données à l'aide des versions existantes et d'utiliser un minimum de verrous. En fait, seules les modifications ultérieures de la même ligne sont verrouillées. Toutes les autres opérations sont effectuées simultanément: les transactions d'écriture ne verrouillent jamais les transactions en lecture seule et les transactions en lecture seule ne verrouillent jamais rien.

En utilisant des instantanés de données, l'isolement dans PostgreSQL est plus strict que requis par la norme: le niveau de lecture répétable ne permet pas seulement des lectures non répétables, mais aussi des lectures fantômes (bien qu'il ne fournisse pas une isolation complète). Et cela est réalisé sans perte d'efficacité.

Nous parlerons dans les prochains articles de la façon dont la concurrence multiversionnelle est mise en œuvre «sous le capot», et maintenant nous examinerons en détail chacun des trois niveaux avec l'œil d'un utilisateur (comme vous le savez, le plus intéressant se cache derrière «d'autres anomalies»). ”). Pour ce faire, créons un tableau des comptes. Alice et Bob ont chacun 1000 livres sterling, mais Bob a deux comptes ouverts:

=> CREATE TABLE accounts( id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, number text UNIQUE, client text, amount numeric ); => INSERT INTO accounts VALUES (1, '1001', 'alice', 1000.00), (2, '2001', 'bob', 100.00), (3, '2002', 'bob', 900.00); 

Lire engagé

L'absence de lecture sale

Il est facile de s'assurer que les données sales ne peuvent pas être lues. Nous commençons la transaction. Par défaut, il utilisera le niveau d'isolement Read Committed:

 => BEGIN; => SHOW transaction_isolation; 

  transaction_isolation ----------------------- read committed (1 row) 

Plus précisément, le niveau par défaut est défini par le paramètre, qui peut être modifié si nécessaire:

 => SHOW default_transaction_isolation; 

  default_transaction_isolation ------------------------------- read committed (1 row) 

Ainsi, dans une transaction ouverte, nous retirons des fonds du compte, mais n'engageons pas les modifications. La transaction voit ses propres changements:

 => UPDATE accounts SET amount = amount - 200 WHERE id = 1; => SELECT * FROM accounts WHERE client = 'alice'; 

  id | number | client | amount ----+--------+--------+-------- 1 | 1001 | alice | 800.00 (1 row) 

Dans la deuxième session, nous commencerons une autre transaction avec le même niveau de lecture validée. Pour distinguer les transactions, les commandes de la deuxième transaction seront mises en retrait et marquées d'une barre.

Afin de répéter les commandes ci-dessus (ce qui est utile), vous devez ouvrir deux terminaux et exécuter psql dans chacun d'eux. Dans le premier terminal, vous pouvez entrer les commandes d'une transaction, et dans le second - celles de l'autre.

 | => BEGIN; | => SELECT * FROM accounts WHERE client = 'alice'; 

 | id | number | client | amount | ----+--------+--------+--------- | 1 | 1001 | alice | 1000.00 | (1 row) 

Comme prévu, l'autre transaction ne voit pas de modifications non validées car les lectures incorrectes ne sont pas autorisées.

Lecture non répétable

Maintenant, laissez la première transaction valider les modifications et la seconde réexécute la même requête.

 => COMMIT; 

 | => SELECT * FROM accounts WHERE client = 'alice'; 

 | id | number | client | amount | ----+--------+--------+-------- | 1 | 1001 | alice | 800.00 | (1 row) 

 | => COMMIT; 

La requête obtient déjà de nouvelles données - et c'est l'anomalie de lecture non répétable , qui est autorisée au niveau de lecture validée.

Conclusion pratique : dans une transaction, vous ne pouvez pas prendre de décisions basées sur des données lues par un opérateur précédent car les choses peuvent changer entre l'exécution des opérateurs. Voici un exemple dont les variations se produisent si souvent dans le code d'application qu'il est considéré comme un contre-modèle classique:

  IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN UPDATE accounts SET amount = amount - 1000 WHERE id = 1; END IF; 

Pendant le temps qui s'écoule entre la vérification et la mise à jour, d'autres transactions peuvent changer l'état du compte de quelque façon que ce soit, donc un tel «chèque» ne sécurise rien. Il est commode d'imaginer qu'entre les opérateurs d'une transaction, tout autre opérateur d'autres transactions peut se «coincer», par exemple, comme suit:

  IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN ----- | UPDATE accounts SET amount = amount - 200 WHERE id = 1; | COMMIT; ----- UPDATE accounts SET amount = amount - 1000 WHERE id = 1; END IF; 

Si tout peut être gâché en réorganisant les opérateurs, le code est mal écrit. Et ne vous trompez pas qu'une telle coïncidence ne se produira pas - elle le sera, c'est certain.

Mais comment écrire du code correctement? Les options sont généralement les suivantes:

• Ne pas écrire de code.
  Ce n'est pas une blague. Par exemple, dans ce cas, la vérification se transforme facilement en contrainte d'intégrité:
  ALTER TABLE accounts ADD CHECK amount >= 0;
  Aucune vérification n'est nécessaire maintenant: effectuez simplement l'opération et, si nécessaire, gérez l'exception qui se produira si une violation d'intégrité est tentée.
  
• Pour utiliser une seule instruction SQL.
  Des problèmes de cohérence se posent car dans l'intervalle de temps entre les opérateurs, une autre transaction peut être effectuée, ce qui modifiera les données visibles. Et s'il y a un opérateur, il n'y a pas d'intervalle de temps.
  PostgreSQL dispose de suffisamment de techniques pour résoudre des problèmes complexes avec une seule instruction SQL. Notons les expressions de table communes (CTE), dans lesquelles, parmi les autres, vous pouvez utiliser les instructions INSERT / UPDATE / DELETE, ainsi que l'instruction INSERT ON CONFLICT, qui implémente la logique de "insert, mais si la ligne existe déjà, mise à jour »dans une déclaration.
  
• Serrures personnalisées.
  Le dernier recours consiste à définir manuellement un verrou exclusif sur toutes les lignes nécessaires (SELECT FOR UPDATE) ou même sur la table entière (LOCK TABLE). Cela fonctionne toujours, mais annule les avantages de la concurrence multiversionnelle: certaines opérations seront exécutées séquentiellement au lieu d'une exécution simultanée.
  

Lecture incohérente

Avant de passer au niveau d'isolement suivant, vous devez admettre que ce n'est pas aussi simple qu'il y paraît. L'implémentation de PostgreSQL est telle qu'elle permet d'autres anomalies, moins connues, qui ne sont pas réglementées par la norme.

Supposons que la première transaction ait commencé le transfert de fonds d'un compte Bob à l'autre:

 => BEGIN; => UPDATE accounts SET amount = amount - 100 WHERE id = 2; 

Dans le même temps, une autre transaction compte le solde de Bob et le calcul est effectué en boucle sur tous les comptes de Bob. En fait, la transaction commence avec le premier compte (et, évidemment, voit l'état précédent):

 | => BEGIN; | => SELECT amount FROM accounts WHERE id = 2; 

 | amount | -------- | 100.00 | (1 row) 

À ce stade, la première transaction se termine avec succès:

 => UPDATE accounts SET amount = amount + 100 WHERE id = 3; => COMMIT; 

Et l'autre lit l'état du deuxième compte (et voit déjà la nouvelle valeur):

 | => SELECT amount FROM accounts WHERE id = 3; 

 | amount | --------- | 1000.00 | (1 row) 

 | => COMMIT; 

Par conséquent, la deuxième transaction a obtenu un total de 001100, c'est-à-dire des données incorrectes. Et ceci est une anomalie de lecture incohérente .

Comment éviter une telle anomalie tout en restant au niveau Read Committed? Bien sûr, utilisez un seul opérateur. Par exemple:

  SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

Jusqu'à présent, j'ai affirmé que la visibilité des données ne pouvait changer qu'entre opérateurs, mais est-ce si évident? Et si la requête prend du temps, peut-elle voir une partie des données dans un état et une partie dans un autre?

Vérifions. Une façon pratique de le faire consiste à insérer un retard forcé dans l'opérateur en appelant la fonction pg_sleep. Son paramètre spécifie le temps de retard en secondes.

 => SELECT amount, pg_sleep(2) FROM accounts WHERE client = 'bob'; 

Pendant que cet opérateur est exécuté, nous transférons les fonds dans une autre transaction:

 | => BEGIN; | => UPDATE accounts SET amount = amount + 100 WHERE id = 2; | => UPDATE accounts SET amount = amount - 100 WHERE id = 3; | => COMMIT; 

Le résultat montre que l'opérateur voit les données dans l'état qu'elles avaient au moment où l'exécution de l'opérateur a commencé. C'est sans aucun doute correct.

  amount | pg_sleep ---------+---------- 0.00 | 1000.00 | (2 rows) 

Mais ce n'est pas aussi simple ici non plus. PostgreSQL vous permet de définir des fonctions, et les fonctions ont le concept d'une catégorie de volatilité . Si une fonction VOLATILE est appelée dans une requête et qu'une autre requête est exécutée dans cette fonction, la requête à l'intérieur de la fonction verra des données qui ne sont pas cohérentes avec les données de la requête principale.

 => CREATE FUNCTION get_amount(id integer) RETURNS numeric AS $$ SELECT amount FROM accounts a WHERE a.id = get_amount.id; $$ VOLATILE LANGUAGE sql; 

 => SELECT get_amount(id), pg_sleep(2) FROM accounts WHERE client = 'bob'; 

 | => BEGIN; | => UPDATE accounts SET amount = amount + 100 WHERE id = 2; | => UPDATE accounts SET amount = amount - 100 WHERE id = 3; | => COMMIT; 

Dans ce cas, nous obtenons des données incorrectes - ₽100 sont perdus:

  get_amount | pg_sleep ------------+---------- 100.00 | 800.00 | (2 rows) 

Je souligne que cet effet n'est possible qu'au niveau d'isolement Read Committed et uniquement avec les fonctions VOLATILE. Le problème est que par défaut, c'est exactement ce niveau d'isolement et cette catégorie de volatilité qui sont utilisés. Ne tombez pas dans le piège!

Lecture incohérente en échange de modifications perdues

Nous pouvons également obtenir une lecture incohérente au sein d'un seul opérateur lors d'une mise à jour, bien que d'une manière quelque peu inattendue.

Voyons ce qui se passe lorsque deux transactions tentent de modifier la même ligne. Maintenant, Bob a ₽1000 sur deux comptes:

 => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+-------- 2 | 2001 | bob | 200.00 3 | 2002 | bob | 800.00 (2 rows) 

Nous commençons une transaction qui réduit le solde de Bob:

 => BEGIN; => UPDATE accounts SET amount = amount - 100 WHERE id = 3; 

Dans le même temps, dans une autre transaction, des intérêts courent sur tous les comptes clients avec un solde total égal ou supérieur à 1 000::

 | => UPDATE accounts SET amount = amount * 1.01 | WHERE client IN ( | SELECT client | FROM accounts | GROUP BY client | HAVING sum(amount) >= 1000 | ); 

L'exécution de l'opérateur UPDATE se compose de deux parties. Tout d'abord, SELECT est réellement exécuté, qui sélectionne les lignes à mettre à jour qui remplissent la condition appropriée. Étant donné que la modification de la première transaction n'est pas validée, la deuxième transaction ne peut pas la voir et la modification n'affecte pas la sélection des lignes pour l'accumulation des intérêts. Eh bien, les comptes de Bob remplissent la condition et une fois la mise à jour effectuée, son solde devrait augmenter de ₽10.

La deuxième étape de l'exécution consiste à mettre à jour les lignes sélectionnées une par une. Ici, la deuxième transaction est forcée de «se bloquer» car la ligne avec id = 3 est déjà verrouillée par la première transaction.

Pendant ce temps, la première transaction valide les modifications:

 => COMMIT; 

Quel sera le résultat?

 => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+---------- 2 | 2001 | bob | 202.0000 3 | 2002 | bob | 707.0000 (2 rows) 

Eh bien, d'une part, la commande UPDATE ne doit pas voir les modifications de la deuxième transaction. Mais d'un autre côté, il ne faut pas perdre les modifications engagées lors de la deuxième transaction.

Une fois le verrou libéré, UPDATE relit la ligne qu'il tente de mettre à jour (mais uniquement celle-ci). En conséquence, Bob a accumulé ₽9, sur la base du montant de 00900. Mais si Bob avait ₽900, ses comptes n'auraient pas du tout été dans la sélection.

Ainsi, la transaction obtient des données incorrectes: certaines des lignes sont visibles à un moment donné et d'autres à un autre. Au lieu d'une mise à jour perdue, nous obtenons à nouveau l'anomalie d' une lecture incohérente .

Les lecteurs attentifs notent qu'avec l'aide de l'application, vous pouvez obtenir une mise à jour perdue même au niveau de Read Committed. Par exemple:

  x := (SELECT amount FROM accounts WHERE id = 1); UPDATE accounts SET amount = x + 100 WHERE id = 1; 

La base de données n'est pas à blâmer: elle obtient deux instructions SQL et ne sait rien du fait que la valeur de x + 100 est en quelque sorte liée au montant des comptes. Évitez d'écrire du code de cette façon.

Lecture répétable

L'absence de lectures non reproductibles et fantômes

Le nom même du niveau d'isolement suppose que la lecture est reproductible. Vérifions-le, et en même temps assurez-vous qu'il n'y a pas de lectures fantômes. Pour ce faire, lors de la première transaction, nous rétablissons les comptes de Bob à leur état précédent et créons un nouveau compte pour Charlie:

 => BEGIN; => UPDATE accounts SET amount = 200.00 WHERE id = 2; => UPDATE accounts SET amount = 800.00 WHERE id = 3; => INSERT INTO accounts VALUES (4, '3001', 'charlie', 100.00); => SELECT * FROM accounts ORDER BY id; 

  id | number | client | amount ----+--------+---------+-------- 1 | 1001 | alice | 800.00 2 | 2001 | bob | 200.00 3 | 2002 | bob | 800.00 4 | 3001 | charlie | 100.00 (4 rows) 

Dans la deuxième session, nous démarrons la transaction avec le niveau de lecture répétable en le spécifiant dans la commande BEGIN (le niveau de la première transaction est inessentiel).

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; | => SELECT * FROM accounts ORDER BY id; 

 | id | number | client | amount | ----+--------+--------+---------- | 1 | 1001 | alice | 800.00 | 2 | 2001 | bob | 202.0000 | 3 | 2002 | bob | 707.0000 | (3 rows) 

Maintenant, la première transaction valide les modifications et la seconde réexécute la même requête.

 => COMMIT; 

 | => SELECT * FROM accounts ORDER BY id; 

 | id | number | client | amount | ----+--------+--------+---------- | 1 | 1001 | alice | 800.00 | 2 | 2001 | bob | 202.0000 | 3 | 2002 | bob | 707.0000 | (3 rows) 

 | => COMMIT; 

La deuxième transaction voit toujours exactement les mêmes données qu'au début: aucune modification des lignes existantes ou de nouvelles lignes n'est visible.

À ce niveau, vous pouvez éviter de vous soucier de quelque chose qui pourrait changer entre deux opérateurs.

Erreur de sérialisation en échange de modifications perdues

Nous avons expliqué précédemment que lorsque deux transactions mettent à jour la même ligne au niveau de lecture validée, une anomalie de lecture incohérente peut se produire. En effet, la transaction en attente relit la ligne verrouillée et ne la voit donc pas au même moment que les autres lignes.

Au niveau de la lecture répétable, cette anomalie n'est pas autorisée, mais si elle se produit, rien ne peut être fait - la transaction se termine donc avec une erreur de sérialisation. Vérifions-le en répétant le même scénario avec l'accumulation des intérêts:

 => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+-------- 2 | 2001 | bob | 200.00 3 | 2002 | bob | 800.00 (2 rows) 

 => BEGIN; => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3; 

 | => BEGIN ISOLATION LEVEL REPEATABLE READ;<span/> | => UPDATE accounts SET amount = amount * 1.01<span/> | WHERE client IN (<span/> | SELECT client<span/> | FROM accounts<span/> | GROUP BY client<span/> | HAVING sum(amount) >= 1000<span/> | );<span/> 

 => COMMIT; 

 | ERROR: could not serialize access due to concurrent update 

 | => ROLLBACK; 

Les données sont restées cohérentes:

 => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+-------- 2 | 2001 | bob | 200.00 3 | 2002 | bob | 700.00 (2 rows) 

La même erreur se produira dans le cas de tout autre changement concurrentiel d'une ligne, même si les colonnes de notre préoccupation n'ont pas été réellement modifiées.

Conclusion pratique : si votre application utilise le niveau d'isolement Lecture répétable pour les transactions d'écriture, elle doit être prête à répéter les transactions qui se sont terminées par une erreur de sérialisation. Pour les transactions en lecture seule, ce résultat n'est pas possible.

Écriture incohérente

Ainsi, dans PostgreSQL, au niveau d'isolement en lecture répétable, toutes les anomalies décrites dans la norme sont évitées. Mais pas toutes les anomalies en général. Il s'avère qu'il y a exactement deux anomalies qui sont encore possibles. (Cela est vrai non seulement pour PostgreSQL, mais aussi pour d'autres implémentations de Snapshot Isolation.)

La première de ces anomalies est une écriture incohérente .

Soit la règle de cohérence suivante: les montants négatifs sur les comptes clients sont autorisés si le montant total sur tous les comptes de ce client reste non négatif .

La première transaction obtient le montant sur les comptes de Bob: ₽900.

 => BEGIN ISOLATION LEVEL REPEATABLE READ; => SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

  sum -------- 900.00 (1 row) 

La deuxième transaction obtient le même montant.

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; | => SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

 | sum | -------- | 900.00 | (1 row) 

La première transaction estime à juste titre que le montant de l'un des comptes peut être réduit de ₽600.

 => UPDATE accounts SET amount = amount - 600.00 WHERE id = 2; 

Et la deuxième transaction arrive à la même conclusion. Mais cela réduit un autre compte:

 | => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3; | => COMMIT; 

 => COMMIT; => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+--------- 2 | 2001 | bob | -400.00 3 | 2002 | bob | 100.00 (2 rows) 

Nous avons réussi à faire passer le solde de Bob dans le rouge, bien que chaque transaction fonctionne correctement seule.

Anomalie de transaction en lecture seule

Il s'agit de la deuxième et dernière des anomalies possibles au niveau de la lecture répétable. Pour le démontrer, vous aurez besoin de trois transactions, dont deux modifieront les données, et la troisième ne fera que les lire.

Mais d'abord, restaurons l'état des comptes de Bob:

 => UPDATE accounts SET amount = 900.00 WHERE id = 2; => SELECT * FROM accounts WHERE client = 'bob'; 

  id | number | client | amount ----+--------+--------+-------- 3 | 2002 | bob | 100.00 2 | 2001 | bob | 900.00 (2 rows) 

Lors de la première transaction, les intérêts sur le montant disponible sur tous les comptes de Bob s'accumulent. Les intérêts sont crédités sur l'un de ses comptes:

 => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 1 => UPDATE accounts SET amount = amount + ( SELECT sum(amount) FROM accounts WHERE client = 'bob' ) * 0.01 WHERE id = 2; 

Ensuite, une autre transaction retire de l'argent d'un autre compte Bob et valide ses modifications:

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 2 | => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3; | => COMMIT; 

Si la première transaction est validée à ce stade, aucune anomalie ne se produira: on pourrait supposer que la première transaction a été exécutée en premier puis la seconde (mais pas l'inverse car la première transaction a vu l'état du compte avec id = 3 avant cela a été modifié lors de la deuxième transaction).

Mais imaginez qu'à ce stade, la troisième transaction (en lecture seule) commence, qui lit l'état d'un compte qui n'est pas affecté par les deux premières transactions:

 | => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 3 | => SELECT * FROM accounts WHERE client = 'alice'; 

 | id | number | client | amount | ----+--------+--------+-------- | 1 | 1001 | alice | 800.00 | (1 row) 

Et seulement après que la première transaction soit terminée:

 => COMMIT; 

Quel état la troisième transaction devrait-elle voir maintenant?

 | SELECT * FROM accounts WHERE client = 'bob'; 

Une fois lancée, la troisième transaction pouvait voir les changements de la deuxième transaction (qui avait déjà été validée), mais pas de la première (qui n'avait pas encore été validée). En revanche, nous avons déjà constaté ci-dessus que la deuxième transaction doit être considérée comme commencée après la première. Quel que soit l'état de la troisième transaction, il ne sera pas cohérent - ce n'est que l'anomalie d'une transaction en lecture seule. Mais au niveau de lecture répétable, il est autorisé:

 | id | number | client | amount | ----+--------+--------+-------- | 2 | 2001 | bob | 900.00 | 3 | 2002 | bob | 0.00 | (2 rows) 

 | => COMMIT; 

Sérialisable

Le niveau Sérialisable empêche toutes les anomalies possibles. En fait, Serializable est construit au-dessus de l'isolement de l'instantané. Les anomalies qui ne se produisent pas avec la lecture répétable (comme une lecture sale, non répétable ou fantôme) ne se produisent pas non plus au niveau sérialisable. Et les anomalies qui se produisent (une écriture incohérente et une anomalie de transaction en lecture seule) sont détectées et la transaction s'interrompt - une erreur de sérialisation familière se produit: impossible de sérialiser l'accès .

Écriture incohérente

Pour illustrer cela, répétons le scénario avec une anomalie d'écriture incohérente:

 => BEGIN ISOLATION LEVEL SERIALIZABLE; => SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

  sum ---------- 910.0000 (1 row) 

 | => BEGIN ISOLATION LEVEL SERIALIZABLE; | => SELECT sum(amount) FROM accounts WHERE client = 'bob'; 

 | sum | ---------- | 910.0000 | (1 row) 

 => UPDATE accounts SET amount = amount - 600.00 WHERE id = 2; 

 | => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3; | => COMMIT; 

 => COMMIT; 

 ERROR: could not serialize access due to read/write dependencies among transactions DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt. HINT: The transaction might succeed if retried. 

Tout comme au niveau de lecture répétable, une application qui utilise le niveau d'isolement sérialisable doit répéter les transactions qui se sont terminées par une erreur de sérialisation, comme le message d'erreur nous l'indique.

Nous gagnons en simplicité de programmation, mais le prix à payer est la résiliation forcée d'une partie des transactions et la nécessité de les répéter. La question, bien sûr, est de savoir quelle est la taille de cette fraction. Si seules les transactions terminées qui se chevauchaient de manière incompétente avec d'autres transactions, cela aurait été bien. Mais une telle implémentation serait inévitablement gourmande en ressources et inefficace car il faudrait suivre les opérations sur chaque ligne.

En fait, l'implémentation de PostgreSQL est telle qu'elle autorise les faux négatifs: certaines transactions absolument normales qui sont simplement «malchanceuses» seront également abandonnées. Comme nous le verrons plus loin, cela dépend de nombreux facteurs, tels que la disponibilité des index appropriés ou la quantité de RAM disponible. En outre, il existe d'autres restrictions d'implémentation (assez sévères), par exemple, les requêtes au niveau sérialisable ne fonctionneront pas sur les réplicas et n'utiliseront pas de plans d'exécution parallèles. Bien que le travail d'amélioration de la mise en œuvre se poursuive, les limites existantes rendent ce niveau d'isolement moins attrayant.

Les plans parallèles apparaîtront dès dans PostgreSQL 12 ( patch ). Et les requêtes sur les répliques peuvent commencer à fonctionner dans PostgreSQL 13 ( un autre patch ).

Anomalie de transaction en lecture seule

Pour qu'une transaction en lecture seule n'entraîne pas d'anomalie et n'en souffre pas, PostgreSQL propose une technique intéressante: une telle transaction peut être verrouillée jusqu'à ce que son exécution soit sécurisée. C'est le seul cas où un opérateur SELECT peut être verrouillé par des mises à jour de lignes. Voici à quoi cela ressemble:

 => UPDATE accounts SET amount = 900.00 WHERE id = 2; => UPDATE accounts SET amount = 100.00 WHERE id = 3; => SELECT * FROM accounts WHERE client = 'bob' ORDER BY id; 

  id | number | client | amount ----+--------+--------+-------- 2 | 2001 | bob | 900.00 3 | 2002 | bob | 100.00 (2 rows) 

 => BEGIN ISOLATION LEVEL SERIALIZABLE; -- 1 => UPDATE accounts SET amount = amount + ( SELECT sum(amount) FROM accounts WHERE client = 'bob' ) * 0.01 WHERE id = 2; 

 | => BEGIN ISOLATION LEVEL SERIALIZABLE; -- 2 | => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3; | => COMMIT; 

La troisième transaction est explicitement déclarée LIRE SEULEMENT et DEFERRABLE:

 | => BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; -- 3 | => SELECT * FROM accounts WHERE client = 'alice'; 

Lorsque vous essayez d'exécuter la requête, la transaction est verrouillée car sinon cela provoquerait une anomalie.

 => COMMIT; 

Et seulement après la validation de la première transaction, la troisième continue l'exécution:

 | id | number | client | amount | ----+--------+--------+-------- | 1 | 1001 | alice | 800.00 | (1 row) 

 | => SELECT * FROM accounts WHERE client = 'bob'; 

 | id | number | client | amount | ----+--------+--------+---------- | 2 | 2001 | bob | 910.0000 | 3 | 2002 | bob | 0.00 | (2 rows) 

 | => COMMIT; 

Autre remarque importante: si l'isolement sérialisable est utilisé, toutes les transactions de l'application doivent utiliser ce niveau. Vous ne pouvez pas mélanger des transactions validées en lecture (ou en lecture répétable) avec sérialisables. Autrement dit, vous pouvez mélanger, mais Serializable se comportera comme une lecture répétable sans aucun avertissement. Nous expliquerons pourquoi cela se produit plus tard, lorsque nous parlerons de la mise en œuvre.

Donc, si vous décidez d'utiliser Serializble, il est préférable de définir globalement le niveau par défaut (bien que cela, bien sûr, ne vous empêche pas de spécifier explicitement un niveau incorrect):

 ALTER SYSTEM SET default_transaction_isolation = 'serializable'; 

Vous pouvez trouver une présentation plus rigoureuse des problèmes liés aux transactions, à la cohérence et aux anomalies dans le livre et le cours magistral de Boris Novikov «Fondamentaux des technologies de base de données» (disponible en russe uniquement).

Quel niveau d'isolement utiliser?

Le niveau d'isolation Read Committed est utilisé par défaut dans PostgreSQL, et il est probable que ce niveau soit utilisé dans la grande majorité des applications. Cette valeur par défaut est pratique car à ce niveau, un abandon de transaction n'est possible qu'en cas d'échec, mais pas comme moyen d'éviter une incohérence. En d'autres termes, une erreur de sérialisation ne peut pas se produire.

L'autre côté de la médaille est un grand nombre d'anomalies possibles, qui ont été discutées en détail ci-dessus. L'ingénieur logiciel doit toujours les garder à l'esprit et écrire du code afin de ne pas leur permettre d'apparaître. Si vous ne pouvez pas coder les actions nécessaires dans une seule instruction SQL, vous devez recourir au verrouillage explicite. Le plus gênant est que le code est difficile à tester pour les erreurs associées à l'obtention de données incohérentes, et les erreurs elles-mêmes peuvent se produire de manière imprévisible et non reproductible et sont donc difficiles à corriger.

Le niveau d'isolement de lecture répétable élimine certains des problèmes d'incohérence, mais hélas, pas tous. Par conséquent, vous devez non seulement vous souvenir des anomalies restantes, mais également modifier l'application afin qu'elle gère correctement les erreurs de sérialisation. C'est certainement gênant. Mais pour les transactions en lecture seule, ce niveau complète parfaitement Read Committed et est très pratique, par exemple, pour créer des rapports qui utilisent plusieurs requêtes SQL.

Enfin, le niveau Sérialisable vous permet de ne pas vous soucier du tout d'incohérence, ce qui facilite grandement le codage. La seule chose qui est requise de l'application est de pouvoir répéter n'importe quelle transaction lors de l'obtention d'une erreur de sérialisation. Mais la fraction des transactions abandonnées, les frais supplémentaires et l'incapacité de paralléliser les requêtes peuvent réduire considérablement le débit du système. Notez également que le niveau Serializable n'est pas applicable sur les réplicas et qu'il ne peut pas être mélangé avec d'autres niveaux d'isolement.

Continuez à lire .