Bonjour, Habr! Avec cet article, je commence une série de boucles (ou une boucle de séries? En général, une grande idée) sur la structure interne de PostgreSQL.

Le matériel sera basé sur des cours de formation en administration que nous faisons avec Pavel pluzanov . Tout le monde n'aime pas regarder une vidéo (je n'aime vraiment pas ça), mais lire des diapositives, même avec des commentaires, est complètement «faux».

Bien sûr, les articles ne répéteront pas le contenu des cours un à un. Je ne parlerai que de la façon dont tout fonctionne, en omettant l'administration elle-même, mais j'essaierai de le faire plus en détail et en détail. Et je crois que ces connaissances sont utiles au développeur d'applications tout autant qu'à l'administrateur.

Je vais me concentrer sur ceux qui ont déjà une certaine expérience de l’utilisation de PostgreSQL et au moins en termes généraux imaginer ce qui se passe. Pour les débutants, le texte sera un peu lourd. Par exemple, je ne dirai pas un mot sur la façon d'installer PostgreSQL et d'exécuter psql.

Les choses qui seront discutées ne changent pas beaucoup d'une version à l'autre, mais j'utiliserai le 11e PostgreSQL «vanille» actuel.

Le premier cycle est consacré aux questions liées à l'isolement et au multiversion, et son plan est le suivant:

1. Isolement, tel que compris par la norme et PostgreSQL (cet article);
2. Calques, fichiers, pages - ce qui se passe au niveau physique;
3. Versions de lignes, transactions virtuelles et imbriquées ;
4. Instantanés de données et visibilité des versions des lignes, horizon des événements ;
5. Nettoyage sur la page et mises à jour à chaud ;
6. Nettoyage normal (aspirateur);
7. Nettoyage automatique (autovacuum);
8. Débordement et gel du compteur de transactions .

Eh bien, allons-y.

Qu'est-ce que l'isolation et pourquoi est-elle importante?

Tout le monde connaît probablement au moins l'existence de transactions, a rencontré l'acronyme ACID et a entendu parler des niveaux d'isolement. Mais il faut encore répondre à l'opinion qu'il s'agit d'une théorie 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 ravi si l'application reçoit 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 qu'au niveau de la base de données, vous pouvez créer des contraintes d'intégrité (telles que NOT NULL ou UNIQUE). Si les données satisfont toujours aux contraintes d'intégrité (et c'est parce que le SGBD le garantit), alors elles sont holistiques.

Est-ce correct et intégral - la même chose? Pas vraiment. Toutes les restrictions ne peuvent pas être formulées au niveau de la base de données. Une partie des restrictions est trop compliquée, par exemple, elle couvre plusieurs tableaux à la fois. Et même si la restriction, en principe, pouvait être définie dans la base de données, mais pour une raison inconnue, cela ne signifie pas qu'elle peut être violée.

Donc, l' exactitude est plus stricte que l' intégrité , mais nous ne savons pas exactement ce que c'est. Il reste à reconnaître que la norme de la décision correcte est une application qui, comme nous voulons le croire, est écrite correctement et ne se trompe jamais. Dans tous les cas, si l'application ne viole pas l'intégrité, mais viole l'exactitude, le SGBD ne le saura pas et ne prendra pas la main.

Désormais, nous appellerons exactitude le terme cohérence.

Supposons cependant que l'application exécute uniquement la séquence d'instructions correcte. Quel est alors le rôle du SGBD, si l'application est correcte?

Premièrement, il s'avère qu'une séquence correcte d'instructions peut perturber temporairement la cohérence des données, ce qui - curieusement - est normal. Un exemple galvaudé mais compréhensible est de transférer des 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 dans les comptes (une telle règle est plutôt difficile à écrire en SQL comme 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 dans un compte, la seconde - augmente dans un autre. La première opération viole la cohérence des données, la seconde - restaure.

Un bon exercice consiste à appliquer la règle décrite ci-dessus au niveau des contraintes d'intégrité. Êtes-vous faible? ©

Que faire si la première opération est terminée et la seconde ne l'est pas? Après tout, c'est facile: lors de la deuxième opération, l'électricité peut être perdue, le serveur peut tomber, la division par zéro peut se produire - mais on ne sait jamais. Il est clair que la cohérence est violée et cela ne devrait pas être autorisé. En principe, il est possible de résoudre de telles situations au niveau de l'application au prix d'efforts incroyables, mais, heureusement, ce n'est pas nécessaire: le SGBD s'en charge. Mais pour cela, elle doit savoir que deux opérations constituent un tout indivisible. C'est une transaction .

Cela s'avère intéressant: sachant que les opérations constituent une transaction, le SGBD permet de maintenir la cohérence en garantissant l'atomicité des transactions, 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 une par une, elles peuvent fonctionner ensemble de manière incorrecte. Cela est dû au fait que l'ordre des opérations est mixte: on ne peut pas supposer que toutes les opérations d'une transaction sont effectuées en premier, et ensuite seulement toutes les opérations d'une autre.

Une note sur la simultanéité. En effet, en même temps, les transactions peuvent fonctionner sur un système avec un processeur multicœur, avec une matrice de disques, etc. Mais toutes les mêmes considérations sont vraies pour un serveur qui exécute des commandes séquentiellement, en mode temps partagé: tant de cycles, une transaction est exécutée, tant de cycles sont différents . Parfois, le terme exécution concurrentielle est utilisé pour résumer.

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

Un exemple simple: si une application veut obtenir les données correctes de la base de données, au moins elle ne devrait pas voir de changements dans les 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 lecture sale .

S'il existe d'autres anomalies plus complexes, que nous traiterons un peu plus tard.

Bien sûr, il est impossible de refuser l'exécution simultanée: sinon, quel type de performance peut-on discuter? Mais vous ne pouvez pas travailler avec des données incorrectes.

Et encore une fois, le SGBD vient à la rescousse. Vous pouvez exécuter les transactions comme si elles étaient séquentielles, comme si elles se succédaient. En d'autres termes, isolément les uns des autres. En réalité, le SGBD peut effectuer des opérations mélangées, mais en même temps garantir que le résultat de l'exécution simultanée coïncidera avec le résultat de l'une des exécutions séquentielles possibles. Et cela élimine toutes les anomalies possibles.

Donc, nous arrivons à la définition:

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

Cette définition combine les trois premières lettres de l'acronyme ACID. Ils sont si étroitement liés les uns aux autres qu'il est tout simplement insensé de considérer l'un sans l'autre. En fait, il est difficile d'arracher la lettre D (durabilité). Après tout, en cas de panne du système, les modifications apportées aux transactions non validées y restent, avec lesquelles vous devez faire quelque chose pour restaurer la cohérence des données.

Tout irait bien, mais la mise en œuvre d'une isolation complète est une tâche techniquement difficile, couplée à une diminution du débit du système. Par conséquent, dans la pratique, une isolation très souvent (pas toujours, mais presque toujours) affaiblie est appliquée, ce qui empêche certaines anomalies, mais pas toutes. Et cela signifie qu'une partie du travail visant à garantir l'exactitude des données incombe à l'application. C'est pourquoi il est très important de comprendre quel niveau d'isolement est utilisé dans le système, quelles garanties il donne et lesquelles il ne donne pas, et comment écrire le code correct dans de telles conditions.

Niveaux d'isolation SQL et anomalies

Le standard SQL décrit depuis longtemps quatre niveaux d'isolement. Ces niveaux sont déterminés en répertoriant les anomalies autorisées ou non lors de l'exécution de transactions à ce niveau. Par conséquent, pour parler de ces niveaux, vous devez vous familiariser avec les anomalies.

Je souligne que dans cette partie, nous parlons de la norme, c'est-à-dire d'une certaine théorie sur laquelle la pratique s'appuie fortement, mais qui est en même temps en contradiction avec. Par conséquent, tous les exemples ici sont spéculatifs. Ils utiliseront les mêmes opérations sur les comptes clients: c'est assez évident, même si, certes, cela n'a rien à voir avec la façon dont les opérations bancaires sont réellement organisées.

Mise à jour perdue

Commençons par la mise à jour perdue . Cette anomalie se produit lorsque deux transactions lisent la même ligne dans le tableau, puis une transaction met à jour cette ligne, puis la deuxième transaction met également à jour la même ligne, sans tenir compte des modifications apportées par la première transaction.

Par exemple, deux transactions vont augmenter le montant sur le même compte de 100 ₽. 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 (il s'avère 1100 ₽) et écrit cette valeur. La deuxième transaction fait de même - obtient les mêmes 1 100 ₽ et les écrit. En conséquence, le client a perdu 100 ₽.

Les mises à jour perdues ne sont autorisées par la norme à aucun niveau d'isolement.

Lecture sale et lecture sans engagement

Avec une lecture sale, nous nous sommes déjà rencontrés ci-dessus. Cette anomalie se produit lorsqu'une transaction lit les modifications en attente apporté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’enregistre pas la modification. Une autre transaction lit l'état du compte, reçoit 0 ₽ et refuse d'émettre de l'argent au client - malgré le fait que la première transaction est interrompue et annule ses modifications, de sorte que la valeur 0 n'a jamais existé dans la base de données.

Une lecture sale est autorisée par la norme au niveau de lecture non validée.

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

L'anomalie de lecture non répétée se produit lorsqu'une transaction lit deux fois la même ligne et, dans l'intervalle 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 la règle de cohérence interdire les montants négatifs dans les comptes clients . La première transaction va réduire le montant du compte de 100 ₽. Elle vérifie la valeur actuelle, obtient 1000 ₽ et décide qu'une réduction est possible. À ce stade, la deuxième transaction réduit à zéro le montant du compte et enregistre les modifications. Si maintenant la première transaction revérifiait le montant, elle recevrait 0 ₽ (mais elle avait déjà décidé de diminuer la valeur, et le compte «passe à moins»).

La lecture sans répétition est autorisée par la norme aux niveaux Lecture non validée et Lecture validée. Mais une lecture sale Read Committed ne le permet pas.

Lecture fantôme et lecture répétable

La lecture fantôme se produit lorsqu'une transaction lit deux fois un ensemble de lignes dans la même condition, et dans l'intervalle entre les lectures, la deuxième transaction ajoute des lignes qui satisfont à cette condition (et valide les modifications). Ensuite, la première transaction recevra différents ensembles de lignes.

Par exemple, supposons qu'une règle de cohérence interdit à un client d'avoir plus de 3 comptes . La première transaction va ouvrir un nouveau compte, vérifie leur numéro actuel (disons, 2) et décide que l'ouverture est possible. À ce stade, la deuxième transaction ouvre également un nouveau compte pour le client et enregistre les modifications. Si maintenant la première transaction revérifie la quantité, elle en recevra 3 (mais elle ouvre déjà un autre compte et le client en a 4).

La lecture fantôme est autorisée par la norme aux niveaux de lecture non validée, de lecture validée et de lecture répétable. Mais au niveau de lecture répétable, la lecture non répétitive n'est pas autorisée.

Manque d'anomalies et sérialisables

La norme définit un autre niveau - sérialisable - auquel aucune anomalie n'est autorisée. Et ce n'est pas du tout la même chose que l'interdiction d'une mise à jour perdue et d'une lecture sale, non répétitive et fantôme.

Le fait est qu'il y a beaucoup plus d'anomalies connues que celles listées dans la norme, et un nombre inconnu est toujours inconnu.

La sérialisation devrait empêcher toutes les anomalies en général . Cela signifie qu'à ce niveau, le développeur de l'application n'a pas besoin de penser à s'exécuter simultanément. Si les transactions effectuent les séquences d'instructions correctes, en travaillant seules, les données seront cohérentes avec le fonctionnement simultané de ces transactions.

Plaque de résumé

Vous pouvez maintenant apporter une table connue à tout le monde. Mais ici, pour plus de clarté, la dernière colonne y est ajoutée, qui n'est pas dans la norme.

Pourquoi exactement ces anomalies?

Pourquoi seulement quelques-unes des nombreuses anomalies possibles dans la norme sont-elles répertoriées, et pourquoi sont-elles?

Apparemment, personne ne semble le savoir avec certitude. Mais la pratique ici a définitivement dépassé la théorie, il est donc possible que nous n'ayons pas pensé à d'autres anomalies (discours sur la norme SQL: 92).

De plus, on a supposé que l'isolation devait être construite sur des verrouillages. L'idée du protocole de blocage à deux phases largement utilisé (2PL) est que pendant la transaction, la transaction bloque les lignes avec lesquelles elle travaille et, une fois terminée, elle libère les verrous. En simplifiant considérablement, plus une transaction capture de verrous, mieux elle est isolée des autres transactions. Mais les performances du système souffrent encore plus, car au lieu de travailler ensemble, les transactions commencent à s'aligner pour les mêmes lignes.

Il me semble que la différence entre les niveaux d'isolement de la norme s'explique précisément par le nombre de verrous nécessaires.

Si une transaction empêche les lignes modifiées de changer, mais pas de lire, nous obtenons le niveau Lecture non validée: les modifications perdues ne sont pas autorisées, mais les données non validées peuvent être lues.

Si la transaction empêche les lignes mutables de lire et de changer, nous obtenons le niveau de lecture validée: vous ne pouvez pas lire les données non validées, mais lorsque vous accédez à nouveau à la ligne, vous pouvez obtenir une valeur différente (lecture non répétée).

Si une transaction bloque à la fois la lecture et la modification des lignes lisibles et mutables, nous obtenons le niveau de lecture répétable: la lecture répétée de la ligne produira la même valeur.

Mais il y a un problème avec Serializable: il n'est pas possible de verrouiller une ligne qui n'existe pas encore. Pour cette raison, la possibilité de lecture fantôme reste: une autre transaction peut ajouter (mais pas supprimer) une ligne qui tombe dans les conditions d'une requête précédemment exécutée, et cette ligne sera récupérée.

Par conséquent, pour implémenter le niveau de sérialisable, les verrous ordinaires ne suffisent pas - vous devez bloquer non pas les lignes, mais les conditions (prédicats). 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 combiner deux prédicats différents. Pour autant que je sache, il n'a pas été possible de mettre en œuvre de tels verrous dans aucun système.

Niveaux d'isolement PostgreSQL

Au fil du temps, Snapshot Isolation a remplacé les protocoles de gestion des transactions bloquantes. Son idée est que chaque transaction fonctionne avec un instantané cohérent des données à un certain moment, dans lequel ne tombent que les modifications enregistrées avant la création de l'instantané.

Une telle isolation ne permet pas automatiquement une lecture sale. Formellement, dans PostgreSQL, vous pouvez spécifier le niveau de lecture non validée, mais cela fonctionnera exactement comme Read Committed. Par conséquent, nous ne parlerons plus du niveau de lecture non validée.

PostgreSQL implémente une multi- version de ce protocole. L'idée du multi-version est que plusieurs versions de la même chaîne peuvent coexister dans un SGBD. Cela vous permet de créer un instantané des données en utilisant les versions disponibles et de vous en tirer avec un minimum de verrous. En fait, seuls les changements répétés sur la même ligne sont bloqués. Toutes les autres opérations sont effectuées en même temps: les transactions d'écriture ne bloquent jamais les transactions de lecture et les transactions de lecture ne bloquent jamais personne.

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

Comment le multi-versioning est mis en œuvre «sous le capot», nous parlerons dans les articles suivants, et maintenant nous examinerons en détail chacun des trois niveaux à travers les yeux de l'utilisateur (comme vous le savez, le plus intéressant se cache derrière «d'autres anomalies»). Pour ce faire, créez un tableau des comptes. Alice et Bob ont chacun 1 000 $, 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é

Manque de lecture sale

Il est facile de vérifier que les données sales ne peuvent pas être lues. 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, il 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'enregistrons pas les changements. 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 démarrons une autre transaction avec le même niveau de lecture validée. Pour distinguer les différentes transactions, les commandes de la deuxième transaction seront mises en retrait et barrées.

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

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

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

Comme prévu, une autre transaction ne voit pas de modifications non validées - une lecture incorrecte n'est pas autorisée.

Lecture non répétée

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

 => COMMIT; 

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

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

 | => COMMIT; 

La demande reçoit déjà de nouvelles données - c'est l'anomalie de la lecture non répétée , qui est autorisée au niveau de lecture validée.

Conclusion pratique : dans une transaction, il est impossible de prendre des décisions sur la base des données lues par l'instruction précédente - car tout peut changer entre le moment où les instructions sont exécutées. Voici un exemple dont les variations sont si courantes dans le code d'application qu'il s'agit d'un antipattern 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 comme souhaité, de sorte qu'un tel «chèque» ne sauve 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 ceci:

  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, en réorganisant les opérateurs, vous pouvez tout gâcher, le code est mal écrit. Et ne vous trompez pas qu'une telle combinaison de circonstances ne se produira pas - elle se produira.

Comment écrire correctement le code? Les opportunités se résument généralement à ce qui suit:

• N'écrivez pas 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;
  Désormais, aucune vérification n'est nécessaire: il suffit d'exécuter simplement l'action et, si nécessaire, de gérer l'exception qui se produira en cas de tentative de violation de l'intégrité.
  
• Utilisez une seule instruction SQL.
  Des problèmes de cohérence surviennent du fait que dans l'intervalle entre les opérateurs une autre transaction peut se terminer et les données visibles changeront. Et s'il n'y a qu'un seul opérateur, il n'y a pas de lacunes.
  PostgreSQL dispose de suffisamment d'outils pour résoudre des problèmes complexes avec une seule instruction SQL. Nous notons les expressions de table générales (CTE), dans lesquelles, entre autres, vous pouvez utiliser les instructions INSERT / UPDATE / DELETE, ainsi que l'instruction INSERT ON CONFLICT, qui implémente la logique "insérer, et s'il y a déjà une ligne, mettre à jour" dans une instruction.
  
• Verrous utilisateur.
  Le dernier recours consiste à définir manuellement un verrou exclusif soit sur toutes les lignes nécessaires (SELECT FOR UPDATE), soit sur toute la table (LOCK TABLE). Cela fonctionne toujours, mais annule les avantages de la multi-version: au lieu de s'exécuter simultanément, une partie des opérations sera effectuée séquentiellement.
  

Lecture incohérente

Avant de se lancer dans le prochain niveau d'isolement, il faut admettre que tout n'est pas si simple. 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.

Disons que la première transaction a commencé à transférer des fonds d'un compte Bob à un autre:

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

À ce moment, une autre transaction calcule le solde de Bob, le calcul étant effectué dans un cycle sur tous les comptes de Bob. En fait, la transaction commence à partir du 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à une nouvelle valeur):

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

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

 | => COMMIT; 

Ainsi, la deuxième transaction a reçu un total de 1100 ₽, c'est-à-dire des données incorrectes. Il s'agit d'une anomalie de lecture incohérente .

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

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

Jusqu'à présent, j'ai soutenu que la visibilité des données ne peut changer qu'entre opérateurs, mais est-ce si évident? Et si la demande est exécutée pendant une longue période, peut-elle voir une partie des données dans un état et une partie dans un autre?

Vérifiez-le. Un moyen pratique de le faire consiste à insérer un retard artificiel dans l'opérateur en appelant la fonction pg_sleep. Son paramètre définit le temps de retard en secondes.

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

Pendant que cette construction est en cours, dans une autre transaction, nous reversons des fonds:

 | => 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 dans lequel elles se trouvaient au moment de leur démarrage. C'est certainement vrai.

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

Mais ici, ce n'est pas si simple. PostgreSQL vous permet de définir des fonctions, tandis que les fonctions ont le concept d'une catégorie de variabilité . Si une fonction volatile (avec la catégorie VOLATILE) est appelée dans une demande et qu'une autre demande est exécutée dans cette fonction, cette demande à l'intérieur de la fonction verra des données qui ne sont pas cohérentes avec les données de la demande 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 qu'un tel effet n'est possible qu'au niveau d'isolement Read Committed, et uniquement avec la catégorie de variabilité VOLATILE. Le problème est que ce niveau d'isolement et cette catégorie de variabilité sont utilisés par défaut, donc je dois admettre - le râteau est très bien. Ne marchez pas!

Lecture incohérente en échange de modifications perdues

Une lecture incohérente dans le cadre d'un opérateur peut - de manière quelque peu inattendue - être obtenue lors d'une mise à jour.

Voyons ce qui se passe lorsque vous essayez de modifier la même ligne avec deux transactions. Bob a maintenant 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, une autre transaction porte intérêt sur tous les comptes clients avec un solde total égal ou supérieur à 1000 ₽:

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

L'exécution d'une instruction UPDATE se compose de deux parties. Tout d'abord, un SELECT est réellement exécuté, qui sélectionne les lignes correspondant à la condition de mise à jour. Le changement de la première transaction n'étant pas figé, la deuxième transaction ne peut pas le voir et n'affecte pas le choix des lignes de calcul des intérêts. Ainsi, les comptes de Bob sont soumis à la condition et une fois la mise à jour terminée, son solde devrait augmenter de 10 ₽.

La deuxième étape de l'exécution - les lignes sélectionnées sont mises à jour l'une après l'autre. Ici, la deuxième transaction est forcée de «geler», car la ligne 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) 

Oui, d'une part, la commande UPDATE ne doit pas voir de changements dans la deuxième transaction. Mais d'un autre côté, il ne devrait pas perdre les changements enregistrés lors de la deuxième transaction.

Une fois le verrou libéré, UPDATE relit la ligne qu'il tente de mettre à jour (mais une seule!). Le résultat est que Bob a accumulé 9 ₽, sur la base du montant de 900 ₽. Mais si Bob en avait 900 ₽, ses comptes n'auraient pas du tout dû être inclus dans l'échantillon.

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

Les lecteurs attentifs notent qu'avec l'aide de l'application au niveau de lecture validée, vous pouvez obtenir une mise à jour perdue. Par exemple, comme ceci:

  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 reçoit deux instructions SQL et ne sait rien que la valeur de x + 100 est en quelque sorte liée à accounts.amount. N'écrivez pas de code de cette façon.

Lecture répétable

Manque de lectures non répétitives et fantômes

Le nom du niveau d'isolement lui-même indique que la lecture est répétable. Nous vérifierons cela, et en même temps nous serons convaincus de l'absence de lectures fantômes. Pour ce faire, dans la première transaction, remettez les comptes de Bob à leur état précédent et créez 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 l'indiquant dans la commande BEGIN (le niveau de la première transaction n'est pas important).

 | => 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) 

À présent, la première transaction valide les modifications et la seconde réexécute la même demande.

 => 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 continue de voir exactement les mêmes données qu'au début: ni les modifications des lignes existantes ni les nouvelles lignes ne sont visibles.

À ce niveau, vous n'avez pas à vous soucier de quelque chose qui change entre les deux opérateurs.

Erreur de sérialisation en échange de modifications perdues

Nous avons indiqué ci-dessus que lors de la mise à jour de la même ligne avec deux transactions au niveau de lecture validée, une anomalie de lecture incohérente peut se produire. Cela est dû au fait que 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 lecture répétable, une telle anomalie n'est pas autorisée, mais si elle se produit toujours, rien ne peut être fait - par conséquent, la transaction se termine avec une erreur de sérialisation. Nous vérifions en répétant le même scénario avec des pourcentages:

 => 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; | => UPDATE accounts SET amount = amount * 1.01 | WHERE client IN ( | SELECT client | FROM accounts | GROUP BY client | HAVING sum(amount) >= 1000 | ); 

 => 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 de ligne concurrentiel, même si les colonnes qui nous intéressent n'ont pas réellement changé.

Conclusion pratique : si l'application utilise le niveau d'isolement Lecture répétable pour l'écriture de transactions, 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, un tel résultat n'est pas possible.

Entrée incohérente

Ainsi, dans PostgreSQL, au niveau d'isolement de la lecture répétable, toutes les anomalies décrites dans la norme sont évitées. Mais pas du tout. Il s'avère qu'il y a exactement deux anomalies qui restent possibles. (Cela est vrai non seulement pour PostgreSQL, mais aussi pour d'autres implémentations d'isolement basées sur des instantanés.)

La première de ces anomalies est un enregistrement incohérent .

Laissez cette règle de cohérence s'appliquer: les montants négatifs sont autorisés sur les comptes du client si le montant total sur tous les comptes de ce client reste non négatif .

La première transaction reçoit le montant dans 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 reçoit 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 réduit un autre score:

 | => 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 à réduire le solde de Bob, bien que chacune des transactions fonctionne correctement individuellement.

Anomalie 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 - en lecture seule.

Mais d'abord, restaurez l'état du compte 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) 

La première transaction facture à Bob des intérêts sur le montant des fonds de tous les comptes. 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; 

Puis une autre transaction retire de l'argent d'un autre compte de Bob et capture ses modifications:

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

Si à ce moment la première transaction est validée, il n'y aura pas d'anomalie: on pourrait supposer que la première transaction a été réalisée en premier, puis la seconde (mais pas l'inverse, car la première transaction a vu l'état du compte id = 3 avant que ce compte ne soit modifié par la deuxième transaction).

Mais supposons qu'à ce moment la troisième transaction (en lecture seule) commence, qui lit le statut 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'; 

Après avoir commencé, la troisième transaction pourrait voir des changements dans la deuxième transaction (qui était déjà validée), mais pas la première (qui n'a pas encore été validée). En revanche, nous avons déjà établi ci-dessus que la deuxième transaction doit être considérée comme ayant commencé après la première. Quel que soit l'état de la troisième transaction, il sera incohérent - c'est l'anomalie de la transaction de lecture uniquement. 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

Au niveau sérialisable, toutes les anomalies possibles sont évitées. En fait, Serializable est implémenté comme un complément d'isolement basé sur des instantanés de données. Les anomalies qui ne se produisent pas pendant la lecture répétable (telles que la lecture sale, non répétable et fantôme) ne se produisent pas au niveau de sérialisable. Et les anomalies qui surviennent (enregistrement incohérent et anomalie de la transaction de lecture uniquement) sont détectées et la transaction est abandonnée - l'erreur de sérialisation déjà connue n'a pas pu sérialiser l'accès.

Entrée incohérente

Pour illustrer, nous répétons le scénario avec une anomalie d'enregistrement incohérent:

 => 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. 

En plus du niveau de lecture répétable, une application utilisant le niveau d'isolement sérialisable doit répéter les transactions qui se sont terminées par une erreur de sérialisation, qui nous est également signalée par l'indication dans le message d'erreur.

Nous obtenons la simplicité de la programmation, mais le prix à payer est la rupture forcée d'une certaine proportion de transactions et la nécessité de les répéter. Toute la question, bien sûr, est de savoir quelle est cette part. Si seulement ces transactions étaient terminées qui se croisent vraiment de manière incompatiable dans les données avec d'autres transactions, tout serait bien. Mais une telle mise en œuvre se révélerait inévitablement gourmande en ressources et inefficace, car elle devrait suivre les opérations avec chaque ligne.

En fait, l'implémentation de PostgreSQL est telle qu'elle autorise de faux déclencheurs négatifs: certaines transactions tout à fait normales qui «n'ont simplement pas de chance» seront interrompues. Comme nous le verrons plus loin, cela dépend de nombreuses raisons, par exemple, la disponibilité d'index appropriés ou la quantité de RAM disponible. De plus, il existe d'autres restrictions d'implémentation (plutôt sérieuses), par exemple, les demandes au niveau sérialisable ne fonctionneront pas sur les répliques, les plans d'exécution parallèles ne seront pas utilisés pour elles. Et bien que le travail d'amélioration de la mise en œuvre ne s'arrête pas, mais les restrictions existantes réduisent l'attractivité de ce niveau d'isolement.

Des plans parallèles apparaîtront dans PostgreSQL 12 ( patch ). Et les requêtes sur les répliques peuvent gagner dans PostgreSQL 13 ( un autre patch ).

Anomalie en lecture seule

Pour que seule une transaction de lecture ne puisse pas conduire à une anomalie et n'en souffre pas, PostgreSQL propose un mécanisme intéressant: une telle transaction peut être bloquée jusqu'à ce que son exécution soit sûre. C'est le seul cas où une instruction SELECT peut être bloquée par des mises à jour de lignes. Voici à quoi ça 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 n'est explicitement déclarée que par le lecteur (LIRE UNIQUEMENT) et différée (DEFERRABLE):

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

Lorsque vous essayez d'exécuter une demande, la transaction est bloquée, car sinon son exécution entraînera une anomalie.

 => COMMIT; 

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

 | 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 les transactions Read Committed (ou Repeatable Read) avec Serializable. Autrement dit, vous pouvez mélanger quelque chose, mais alors Serializable se comportera comme une lecture répétable sans avertissement. , , .

Serializble — ( , , ):

  ALTER SYSTEM SET default_transaction_isolation = 'serializable'; 

, , , « ».

?

Read Committed PostgreSQL , , . , , . , .

, . , . SQL-, . , , , .

Repeatable Read , , , . , , . , , . Read Committed , , , SQL-.

, Serializable , . , — . , , . , Serializable , , .

.