Mécanique du moteur transactionnel & MVCC

17 avril 2024

Dalibo SCOP

Sur ce document

Formation Module M4
Titre Mécanique du moteur transactionnel & MVCC
Révision 24.04
PDF https://dali.bo/m4_pdf
EPUB https://dali.bo/m4_epub
HTML https://dali.bo/m4_html
Slides https://dali.bo/m4_slides
TP https://dali.bo/m4_tp
TP (solutions) https://dali.bo/m4_solutions

Vous trouverez en ligne les différentes versions complètes de ce document.


Chers lectrices & lecteurs,

Nos formations PostgreSQL sont issues de nombreuses années d’études, d’expérience de terrain et de passion pour les logiciels libres. Pour Dalibo, l’utilisation de PostgreSQL n’est pas une marque d’opportunisme commercial, mais l’expression d’un engagement de longue date. Le choix de l’Open Source est aussi le choix de l’implication dans la communauté du logiciel.

Au‑delà du contenu technique en lui‑même, notre intention est de transmettre les valeurs qui animent et unissent les développeurs de PostgreSQL depuis toujours : partage, ouverture, transparence, créativité, dynamisme… Le but premier de nos formations est de vous aider à mieux exploiter toute la puissance de PostgreSQL mais nous espérons également qu’elles vous inciteront à devenir un membre actif de la communauté en partageant à votre tour le savoir‑faire que vous aurez acquis avec nous.

Nous mettons un point d’honneur à maintenir nos manuels à jour, avec des informations précises et des exemples détaillés. Toutefois malgré nos efforts et nos multiples relectures, il est probable que ce document contienne des oublis, des coquilles, des imprécisions ou des erreurs. Si vous constatez un souci, n’hésitez pas à le signaler via l’adresse !

À propos de DALIBO

DALIBO est le spécialiste français de PostgreSQL. Nous proposons du support, de la formation et du conseil depuis 2005.

Retrouvez toutes nos formations sur https://dalibo.com/formations

Remerciements

Ce manuel de formation est une aventure collective qui se transmet au sein de notre société depuis des années. Nous remercions chaleureusement ici toutes les personnes qui ont contribué directement ou indirectement à cet ouvrage, notamment :

Jean‑Paul Argudo, Alexandre Anriot, Carole Arnaud, Alexandre Baron, David Bidoc, Sharon Bonan, Franck Boudehen, Arnaud Bruniquel, Pierrick Chovelon, Damien Clochard, Christophe Courtois, Marc Cousin, Gilles Darold, Jehan‑Guillaume de Rorthais, Ronan Dunklau, Vik Fearing, Stefan Fercot, Pierre Giraud, Nicolas Gollet, Dimitri Fontaine, Florent Jardin, Virginie Jourdan, Luc Lamarle, Denis Laxalde, Guillaume Lelarge, Alain Lesage, Benoit Lobréau, Jean‑Louis Louër, Thibaut Madelaine, Adrien Nayrat, Alexandre Pereira, Flavie Perette, Robin Portigliatti, Thomas Reiss, Maël Rimbault, Julien Rouhaud, Stéphane Schildknecht, Julien Tachoires, Nicolas Thauvin, Be Hai Tran, Christophe Truffier, Cédric Villemain, Thibaud Walkowiak, Frédéric Yhuel.

Forme de ce manuel

Les versions PDF, EPUB ou HTML de ce document sont structurées autour des slides de nos formations. Le texte suivant chaque slide contient le cours et de nombreux détails qui ne peuvent être données à l’oral.

Licence Creative Commons CC-BY-NC-SA

Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :

  • Paternité
  • Pas d’utilisation commerciale
  • Partage des conditions initiales à l’identique

Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.

Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.

Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.

Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.

Marques déposées

PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.

Versions de PostgreSQL couvertes

Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 12 à 16.

Sur les versions précédentes susceptibles d’être encore rencontrées en production, seuls quelques points très importants sont évoqués, en plus éventuellement de quelques éléments historiques.

Sauf précision contraire, le système d’exploitation utilisé est Linux.

Mécanique du moteur transactionnel & MVCC

PostgreSQL

Introduction

PostgreSQL utilise un modèle appelé MVCC (Multi-Version Concurrency Control).

  • Gestion concurrente des transactions
  • Excellente concurrence
  • Impacts sur l’architecture

PostgreSQL s’appuie sur un modèle de gestion de transactions appelé MVCC. Nous allons expliquer cet acronyme, puis étudier en profondeur son implémentation dans le moteur.

Cette technologie a en effet un impact sur le fonctionnement et l’administration de PostgreSQL.


Au menu

  • Présentation de MVCC
  • Niveaux d’isolation
  • Implémentation de MVCC de PostgreSQL
  • Les verrous
  • Le mécanisme TOAST

Présentation de MVCC

  • MultiVersion Concurrency Control
  • Contrôle de Concurrence Multi-Version
  • Plusieurs versions du même enregistrement
  • Granularité : l’enregistrement (pas le champ !)

MVCC est un sigle signifiant MultiVersion Concurrency Control, ou « contrôle de concurrence multi-version ».

Le principe est de faciliter l’accès concurrent de plusieurs utilisateurs (sessions) à la base en disposant en permanence de plusieurs versions différentes d’un même enregistrement. Chaque session peut travailler simultanément sur la version qui s’applique à son contexte (on parle d’« instantané » ou de snapshot).

Par exemple, une transaction modifiant un enregistrement va créer une nouvelle version de cet enregistrement. Mais celui-ci ne devra pas être visible des autres transactions tant que le travail de modification n’est pas validé en base. Les autres transactions verront donc une ancienne version de cet enregistrement. La dénomination technique est « lecture cohérente » (consistent read en anglais).

Précisons que la granularité des modifications est bien l’enregistrement (ou ligne) d’une table. Modifier un champ (colonne) revient à modifier la ligne. Deux transactions ne peuvent pas modifier deux champs différents d’un même enregistrement sans entrer en conflit, et les verrous portent toujours sur des lignes entières.


Alternative à MVCC : un seul enregistrement en base

  • Verrouillage en lecture et exclusif en écriture
  • Nombre de verrous ?
  • Contention ?
  • Cohérence ?
  • Annulation ?

Avant d’expliquer en détail MVCC, voyons l’autre solution de gestion de la concurrence qui s’offre à nous, afin de comprendre le problème que MVCC essaye de résoudre.

Une table contient une liste d’enregistrements.

  • Une transaction voulant consulter un enregistrement doit le verrouiller (pour s’assurer qu’il n’est pas modifié) de façon partagée, le consulter, puis le déverrouiller.
  • Une transaction voulant modifier un enregistrement doit le verrouiller de façon exclusive (personne d’autre ne doit pouvoir le modifier ou le consulter), le modifier, puis le déverrouiller.

Cette solution a l’avantage de la simplicité : il suffit d’un gestionnaire de verrous pour gérer l’accès concurrent aux données. Elle a aussi l’avantage de la performance, dans le cas où les attentes de verrous sont peu nombreuses, la pénalité de verrouillage à payer étant peu coûteuse.

Elle a par contre des inconvénients :

  • Les verrous sont en mémoire. Leur nombre est donc probablement limité. Que se passe-t-il si une transaction doit verrouiller 10 millions d’enregistrements ? Des mécanismes de promotion de verrou sont implémentés. Les verrous lignes deviennent des verrous bloc, puis des verrous table. Le nombre de verrous est limité, et une promotion de verrou peut avoir des conséquences dramatiques ;
  • Un processus devant lire un enregistrement devra attendre la fin de la modification de celui-ci. Ceci entraîne rapidement de gros problèmes de contention. Les écrivains bloquent les lecteurs, et les lecteurs bloquent les écrivains. Évidemment, les écrivains se bloquent entre eux, mais cela est normal (il n’est pas possible que deux transactions modifient le même enregistrement simultanément, chacune sans conscience de ce qu’a effectué l’autre) ;
  • Un ordre SQL (surtout s’il dure longtemps) n’a aucune garantie de voir des données cohérentes du début à la fin de son exécution : si, par exemple, durant un SELECT long, un écrivain modifie à la fois des données déjà lues par le SELECT, et des données qu’il va lire, le SELECT n’aura pas une vue cohérente de la table. Il pourrait y avoir un total faux sur une table comptable par exemple, le SELECT ayant vu seulement une partie des données validées par une nouvelle transaction ;
  • Comment annuler une transaction ? Il faut un moyen de défaire ce qu’une transaction a effectué, au cas où elle ne se terminerait pas par une validation mais par une annulation.

Implémentation de MVCC par undo

  • MVCC par undo :
    • une version de l’enregistrement dans la table
    • sauvegarde des anciennes versions
    • l’adresse physique d’un enregistrement ne change pas
    • la lecture cohérente est complexe
    • l’undo est complexe à dimensionner… et parfois insuffisant
    • l’annulation est lente
  • Exemple : Oracle

C’est l’implémentation d’Oracle, par exemple. Un enregistrement, quand il doit être modifié, est recopié précédemment dans le tablespace d’UNDO. La nouvelle version de l’enregistrement est ensuite écrite par-dessus. Ceci implémente le MVCC (les anciennes versions de l’enregistrement sont toujours disponibles), et présente plusieurs avantages :

  • Les enregistrements ne sont pas dupliqués dans la table. Celle-ci ne grandit donc pas suite à une mise à jour (si la nouvelle version n’est pas plus grande que la version précédente) ;
  • Les enregistrements gardent la même adresse physique dans la table. Les index correspondant à des données non modifiées de l’enregistrement n’ont donc pas à être modifiés eux-mêmes, les index permettant justement de trouver l’adresse physique d’un enregistrement par rapport à une valeur.

Elle a aussi des défauts :

  • La gestion de l’undo est très complexe : comment décider ce qui peut être purgé ? Il arrive que la purge soit trop agressive, et que des transactions n’aient plus accès aux vieux enregistrements (erreur SNAPSHOT TOO OLD sous Oracle, par exemple) ;
  • La lecture cohérente est complexe à mettre en œuvre : il faut, pour tout enregistrement modifié, disposer des informations permettant de retrouver l’image avant modification de l’enregistrement (et la bonne image, il pourrait y en avoir plusieurs). Il faut ensuite pouvoir le reconstituer en mémoire ;
  • Il est difficile de dimensionner correctement le fichier d’undo. Il arrive d’ailleurs qu’il soit trop petit, déclenchant l’annulation d’une grosse transaction. Il est aussi potentiellement une source de contention entre les sessions ;
  • L’annulation (ROLLBACK) est très lente : il faut, pour toutes les modifications d’une transaction, défaire le travail, donc restaurer les images contenues dans l’undo, les réappliquer aux tables (ce qui génère de nouvelles écritures). Le temps d’annulation peut être supérieur au temps de traitement initial devant être annulé.

L’implémentation MVCC de PostgreSQL

  • Copy On Write (duplication à l’écriture)
  • Une version d’enregistrement n’est jamais modifiée
  • Toute modification entraîne une nouvelle version
  • Pas d’undo : pas de contention, ROLLBACK instantané

Dans une table PostgreSQL, un enregistrement peut être stocké dans plusieurs versions. Une modification d’un enregistrement entraîne l’écriture d’une nouvelle version de celui-ci. Une ancienne version ne peut être recyclée que lorsqu’aucune transaction ne peut plus en avoir besoin, c’est-à-dire qu’aucune transaction n’a un instantané de la base plus ancien que l’opération de modification de cet enregistrement, et que cette version est donc invisible pour tout le monde. Chaque version d’enregistrement contient bien sûr des informations permettant de déterminer s’il est visible ou non dans un contexte donné.

Les avantages de cette implémentation stockant plusieurs versions dans la table principale sont multiples :

  • La lecture cohérente est très simple à mettre en œuvre : à chaque session de lire la version qui l’intéresse. La visibilité d’une version d’enregistrement est simple à déterminer ;
  • Il n’y a pas d’undo. C’est un aspect de moins à gérer dans l’administration de la base ;
  • Il n’y a pas de contention possible sur l’undo ;
  • Il n’y a pas de recopie dans l’undo avant la mise à jour d’un enregistrement. La mise à jour est donc moins coûteuse ;
  • L’annulation d’une transaction est instantanée : les anciens enregistrements sont toujours disponibles.

Cette implémentation a quelques défauts :

  • Il faut supprimer régulièrement les versions obsolètes des enregistrements ;
  • Il y a davantage de maintenance d’index (mais moins de contentions sur leur mise à jour) ;
  • Les enregistrements embarquent des informations de visibilité, qui les rendent plus volumineux.

Niveaux d’isolation

  • Chaque transaction (et donc session) est isolée à un certain point :
    • elle ne voit pas les opérations des autres
    • elle s’exécute indépendamment des autres
  • Le niveau d’isolation au démarrage d’une transaction peut être spécifié :
    • BEGIN ISOLATION LEVEL xxx;

Chaque transaction, en plus d’être atomique, s’exécute séparément des autres. Le niveau de séparation demandé est un compromis entre le besoin applicatif (pouvoir ignorer sans risque ce que font les autres transactions) et les contraintes imposées au niveau de PostgreSQL (performances, risque d’échec d’une transaction). Quatre niveaux sont définis, ils ne sont pas tous implémentés par PostgreSQL.


Niveau READ UNCOMMITTED

  • Non disponible sous PostgreSQL
    • si demandé, s’exécute en READ COMMITTED
  • Lecture de données modifiées par d’autres transactions non validées
  • Aussi appelé dirty reads
  • Dangereux
  • Pas de blocage entre les sessions

Ce niveau d’isolation n’est disponible que pour les SGBD non-MVCC. Il est très dangereux : il est possible de lire des données invalides, ou temporaires, puisque tous les enregistrements de la table sont lus, quels que soient leurs états. Il est utilisé dans certains cas où les performances sont cruciales, au détriment de la justesse des données.

Sous PostgreSQL, ce mode n’est pas disponible. Une transaction qui demande le niveau d’isolation READ UNCOMMITTED s’exécute en fait en READ COMMITTED.


Niveau READ COMMITTED

  • Niveau d’isolation par défaut
  • La transaction ne lit que les données validées en base
  • Un ordre SQL s’exécute dans un instantané (les tables semblent figées sur la durée de l’ordre)
  • L’ordre suivant s’exécute dans un instantané différent

Ce mode est le mode par défaut, et est suffisant dans de nombreux contextes. PostgreSQL étant MVCC, les écrivains et les lecteurs ne se bloquent pas mutuellement, et chaque ordre s’exécute sur un instantané de la base (ce n’est pas un prérequis de READ COMMITTED dans la norme SQL). Il n’y a plus de lectures d’enregistrements non valides (dirty reads). Il est toutefois possible d’avoir deux problèmes majeurs d’isolation dans ce mode :

  • Les lectures non-répétables (non-repeatable reads) : une transaction peut ne pas voir les mêmes enregistrements d’une requête sur l’autre, si d’autres transactions ont validé des modifications entre temps ;
  • Les lectures fantômes (phantom reads) : des enregistrements peuvent ne plus satisfaire une clause WHERE entre deux requêtes d’une même transaction.

Niveau REPEATABLE READ

  • Instantané au début de la transaction
  • Ne voit donc plus les modifications des autres transactions
  • Voit toujours ses propres modifications
  • Peut entrer en conflit avec d’autres transactions si modification des mêmes enregistrements

Ce mode, comme son nom l’indique, permet de ne plus avoir de lectures non-répétables. Deux ordres SQL consécutifs dans la même transaction retourneront les mêmes enregistrements, dans la même version. Ceci est possible car la transaction voit une image de la base figée. L’image est figée non au démarrage de la transaction, mais à la première commande non TCL (Transaction Control Language) de la transaction, donc généralement au premier SELECT ou à la première modification.

Cette image sera utilisée pendant toute la durée de la transaction. En lecture seule, ces transactions ne peuvent pas échouer. Elles sont entre autres utilisées pour réaliser des exports des données : c’est ce que fait pg_dump.

Dans le standard, ce niveau d’isolation souffre toujours des lectures fantômes, c’est-à-dire de lecture d’enregistrements différents pour une même clause WHERE entre deux exécutions de requêtes. Cependant, PostgreSQL est plus strict et ne permet pas ces lectures fantômes en REPEATABLE READ. Autrement dit, un même SELECT renverra toujours le même résultat.

En écriture, par contre (ou SELECT FOR UPDATE, FOR SHARE), si une autre transaction a modifié les enregistrements ciblés entre temps, une transaction en REPEATABLE READ va échouer avec l’erreur suivante :

ERROR: could not serialize access due to concurrent update

Il faut donc que l’application soit capable de la rejouer au besoin.


Niveau SERIALIZABLE

  • Niveau d’isolation le plus élevé
  • Chaque transaction se croit seule sur la base
    • sinon annulation d’une transaction en cours
  • Avantages :
    • pas de « lectures fantômes »
    • évite des verrous, simplifie le développement
  • Inconvénients :
    • pouvoir rejouer les transactions annulées
    • toutes les transactions impliquées doivent être sérialisables

PostgreSQL fournit un mode d’isolation appelé SERIALIZABLE :

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE ;

COMMIT / ROLLBACK ;

Dans ce mode, toutes les transactions déclarées comme telles s’exécutent comme si elles étaient seules sur la base, et comme si elles se déroulaient les unes à la suite des autres. Dès que cette garantie ne peut plus être apportée, PostgreSQL annule celle qui entraînera le moins de perte de données.

Le niveau SERIALIZABLE est utile quand le résultat d’une transaction peut être influencé par une transaction tournant en parallèle, par exemple quand des valeurs de lignes dépendent de valeurs d’autres lignes : mouvements de stocks, mouvements financiers… avec calculs de stocks. Autrement dit, si une transaction lit des lignes, elle a la garantie que leurs valeurs ne seront pas modifiées jusqu’à son COMMIT, y compris par les transactions qu’elle ne voit pas — ou bien elle tombera en erreur.

Au niveau SERIALIZABLE (comme en REPEATABLE READ), il est donc essentiel de pouvoir rejouer une transaction en cas d’échec. Par contre, nous simplifions énormément tous les autres points du développement. Il n’y a plus besoin de SELECT FOR UPDATE, solution courante mais très gênante pour les transactions concurrentes. Les triggers peuvent être utilisés sans soucis pour valider des opérations.

Ce mode doit être mis en place globalement, car toute transaction non sérialisable peut en théorie s’exécuter n’importe quand, ce qui rend inopérant le mode sérialisable sur les autres.

La sérialisation utilise le « verrouillage de prédicats ». Ces verrous sont visibles dans la vue pg_locks sous le nom SIReadLock, et ne gênent pas les opérations habituelles, du moins tant que la sérialisation est respectée. Un enregistrement qui « apparaît » ultérieurement suite à une mise à jour réalisée par une transaction concurrente déclenchera aussi une erreur de sérialisation.

Le wiki PostgreSQL, et la documentation officielle donnent des exemples, et ajoutent quelques conseils pour l’utilisation de transactions sérialisables. Afin de tenter de réduire les verrous et le nombre d’échecs :

  • faire des transactions les plus courtes possibles (si possible uniquement ce qui a trait à l’intégrité) ;
  • limiter le nombre de connexions actives ;
  • utiliser les transactions en mode READ ONLY dès que possible, voire en SERIALIZABLE READ ONLY DEFERRABLE (au risque d’un délai au démarrage) ;
  • augmenter certains paramètres liés aux verrous, c’est-à-dire augmenter la mémoire dédiée ; car si elle manque, des verrous de niveau ligne pourraient être regroupés en verrous plus larges et plus gênants ;
  • éviter les parcours de tables (Seq Scan), et donc privilégier les accès par index.

Structure d’un bloc

  • 1 bloc = 8 ko
  • ctid = (bloc, item dans le bloc)
Répartition des lignes au sein d’un bloc (schéma de la documentation officielle, licence PostgreSQL)

Le bloc (ou page) est l’unité de base de transfert pour les I/O, le cache mémoire… Il fait généralement 8 ko (ce qui ne peut être modifié qu’en recompilant). Les lignes y sont stockées avec des informations d’administration telles que décrites dans le schéma ci-dessus. Une ligne ne fait jamais partie que d’un seul bloc (si cela ne suffit pas, un mécanisme que nous verrons plus tard, nommé TOAST, se déclenche).

Nous distinguons dans ce bloc :

  • un entête de page avec diverses informations, notamment la somme de contrôle (si activée) ;
  • des identificateurs de 4 octets, pointant vers les emplacements des lignes au sein du bloc ;
  • les lignes, stockées à rebours depuis la fin du bloc ;
  • un espace spécial, vide pour les tables ordinaires, mais utilisé par les blocs d’index.

Le ctid identifie une ligne, en combinant le numéro du bloc (à partir de 0) et l’identificateur dans le bloc (à partir de 1). Comme la plupart des champs administratifs liés à une ligne, il suffit de l’inclure dans un SELECT pour l’afficher. L’exemple suivant affiche les premiers et derniers éléments des deux blocs d’une table et vérifie qu’il n’y a pas de troisième bloc :

# CREATE TABLE deuxblocs AS SELECT i, i  AS j FROM generate_series(1, 452) i;
SELECT 452

# SELECT ctid, i,j FROM deuxblocs
WHERE ctid in ( '(1, 1)', '(0, 226)', '(1, 1)', '(1, 226)', '(1, 227)', '(2, 0)' );

  ctid   |  i  |  j
---------+-----+-----
 (0,1)   |   1 |   1
 (0,226) | 226 | 226
 (1,1)   | 227 | 227
 (1,226) | 452 | 452

Un ctid ne doit jamais servir à désigner une ligne de manière pérenne et ne doit pas être utilisé dans des requêtes ! Il peut changer n’importe quand, notamment en cas d’UPDATE ou de VACUUM FULL !

La documentation officielle contient évidemment tous les détails.

Pour observer le contenu d’un bloc, à titre pédagogique, vous pouvez utiliser l’extension pageinspect.


xmin & xmax

Table initiale :

xmin xmax Nom Solde
100 M. Durand 1500
100 Mme Martin 2200

PostgreSQL stocke des informations de visibilité dans chaque version d’enregistrement.

  • xmin : l’identifiant de la transaction créant cette version.
  • xmax : l’identifiant de la transaction invalidant cette version.

Ici, les deux enregistrements ont été créés par la transaction 100. Il s’agit peut-être, par exemple, de la transaction ayant importé tous les soldes à l’initialisation de la base.


xmin & xmax (suite)

BEGIN;
UPDATE soldes SET solde = solde - 200 WHERE nom = 'M. Durand';
xmin xmax Nom Solde
100 150 M. Durand 1500
100 Mme Martin 2200
150 M. Durand 1300

Nous décidons d’enregistrer un virement de 200 € du compte de M. Durand vers celui de Mme Martin. Ceci doit être effectué dans une seule transaction : l’opération doit être atomique, sans quoi de l’argent pourrait apparaître ou disparaître de la table.

Nous allons donc tout d’abord démarrer une transaction (ordre SQL BEGIN). PostgreSQL fournit donc à notre session un nouveau numéro de transaction (150 dans notre exemple). Puis nous effectuerons :

UPDATE soldes SET solde = solde - 200 WHERE nom = 'M. Durand';

xmin & xmax (suite)

UPDATE soldes SET solde = solde + 200 WHERE nom = 'Mme Martin';
xmin xmax Nom Solde
100 150 M. Durand 1500
100 150 Mme Martin 2200
150 M. Durand 1300
150 Mme Martin 2400

Puis nous effectuerons :

UPDATE soldes SET solde = solde + 200 WHERE nom = 'Mme Martin';

Nous avons maintenant deux versions de chaque enregistrement.

Notre session ne voit bien sûr plus que les nouvelles versions de ces enregistrements, sauf si elle décidait d’annuler la transaction, auquel cas elle reverrait les anciennes données.

Pour une autre session, la version visible de ces enregistrements dépend de plusieurs critères :

  • La transaction 150 a-t-elle été validée ? Sinon elle est invisible ;
  • La transaction 150 a-t-elle été validée après le démarrage de la transaction en cours, et sommes-nous dans un niveau d’isolation (repeatable read ou serializable) qui nous interdit de voir les modifications faites depuis le début de notre transaction ? ;
  • La transaction 150 a-t-elle été validée après le démarrage de la requête en cours ? Une requête, sous PostgreSQL, voit un instantané cohérent de la base, ce qui implique que toute transaction validée après le démarrage de la requête doit être ignorée.

Dans le cas le plus simple, 150 ayant été validée, une transaction 160 ne verra pas les premières versions : xmax valant 150, ces enregistrements ne sont pas visibles. Elle verra les secondes versions, puisque xmin = 150, et pas de xmax.


xmin & xmax (suite)

xmin xmax Nom Solde
100 150 M. Durand 1500
100 150 Mme Martin 2200
150 M. Durand 1300
150 Mme Martin 2400
  • Comment est effectuée la suppression d’un enregistrement ?
  • Comment est effectuée l’annulation de la transaction 150 ?
  • La suppression d’un enregistrement s’effectue simplement par l’écriture d’un xmax dans la version courante ;
  • Il n’y a rien à écrire dans les tables pour annuler une transaction. Il suffit de marquer la transaction comme étant annulée dans la CLOG.

CLOG

  • La CLOG (Commit Log) enregistre l’état des transactions.
  • Chaque transaction occupe 2 bits de CLOG
  • COMMIT ou ROLLBACK très rapide

La CLOG est stockée dans une série de fichiers de 256 ko, stockés dans le répertoire pg_xact/ de PGDATA (répertoire racine de l’instance PostgreSQL).

Chaque transaction est créée dans ce fichier dès son démarrage et est encodée sur deux bits puisqu’une transaction peut avoir quatre états :

  • TRANSACTION_STATUS_IN_PROGRESS signifie que la transaction en cours, c’est l’état initial ;
  • TRANSACTION_STATUS_COMMITTED  signifie que la la transaction a été validée ;
  • TRANSACTION_STATUS_ABORTED  signifie que la transaction a été annulée ;
  • TRANSACTION_STATUS_SUB_COMMITTED signifie que la transaction comporte des sous-transactions, afin de valider l’ensemble des sous-transactions de façon atomique.

Nous avons donc un million d’états de transactions par fichier de 256 ko.

Annuler une transaction (ROLLBACK) est quasiment instantané sous PostgreSQL : il suffit d’écrire TRANSACTION_STATUS_ABORTED dans l’entrée de CLOG correspondant à la transaction.

Toute modification dans la CLOG, comme toute modification d’un fichier de données (table, index, séquence, vue matérialisée), est bien sûr enregistrée tout d’abord dans les journaux de transactions (dans le répertoire pg_wal/).


Avantages du MVCC PostgreSQL

  • Avantages :
    • avantages classiques de MVCC (concurrence d’accès)
    • implémentation simple et performante
    • peu de sources de contention
    • verrouillage simple d’enregistrement
    • ROLLBACK instantané
    • données conservées aussi longtemps que nécessaire

Reprenons les avantages du MVCC tel qu’implémenté par PostgreSQL :

  • Les lecteurs ne bloquent pas les écrivains, ni les écrivains les lecteurs ;
  • Le code gérant les instantanés est simple, ce qui est excellent pour la fiabilité, la maintenabilité et les performances ;
  • Les différentes sessions ne se gênent pas pour l’accès à une ressource commune (l’undo) ;
  • Un enregistrement est facilement identifiable comme étant verrouillé en écriture : il suffit qu’il ait une version ayant un xmax correspondant à une transaction en cours ;
  • L’annulation est instantanée : il suffit d’écrire le nouvel état de la transaction annulée dans la CLOG. Pas besoin de restaurer les valeurs précédentes, elles sont toujours là ;
  • Les anciennes versions restent en ligne aussi longtemps que nécessaire. Elles ne pourront être effacées de la base qu’une fois qu’aucune transaction ne les considérera comme visibles.

(Précisons toutefois que ceci est une vision un peu simplifiée pour les cas courants. La signification du xmax est parfois altérée par des bits positionnés dans des champs systèmes inaccessibles par l’utilisateur. Cela arrive, par exemple, quand des transactions insèrent des lignes portant une clé étrangère, pour verrouiller la ligne pointée par cette clé, laquelle ne doit pas disparaître pendant la durée de cette transaction.)


Inconvénients du MVCC PostgreSQL

  • Nettoyage des enregistrements
    • VACUUM
    • automatisation : autovacuum
  • Tables plus volumineuses
  • Pas de visibilité dans les index
  • Colonnes supprimées impliquent reconstruction

Comme toute solution complexe, l’implémentation MVCC de PostgreSQL est un compromis. Les avantages cités précédemment sont obtenus au prix de concessions.

VACUUM

Il faut nettoyer les tables de leurs enregistrements morts. C’est le travail de la commande VACUUM. Il a un avantage sur la technique de l’undo : le nettoyage n’est pas effectué par un client faisant des mises à jour (et créant donc des enregistrements morts), et le ressenti est donc meilleur.

VACUUM peut se lancer à la main, mais dans le cas général on s’en remet à l’autovacuum, un démon qui lance les VACUUM (et bien plus) en arrière-plan quand il le juge nécessaire. Tout cela sera traité en détail par la suite.

Bloat

Les tables sont forcément plus volumineuses que dans l’implémentation par undo, pour deux raisons :

  • les informations de visibilité y sont stockées, il y a donc un surcoût d’une douzaine d’octets par enregistrement ;
  • il y a toujours des enregistrements morts dans une table, une sorte de fond de roulement, qui se stabilise quand l’application est en régime stationnaire.

Ces enregistrements sont recyclés à chaque passage de VACUUM.

Visibilité

Les index n’ont pas d’information de visibilité. Il est donc nécessaire d’aller vérifier dans la table associée que l’enregistrement trouvé dans l’index est bien visible. Cela a un impact sur le temps d’exécution de requêtes comme SELECT count(*) sur une table : dans le cas le plus défavorable, il est nécessaire d’aller visiter tous les enregistrements pour s’assurer qu’ils sont bien visibles. La visibility map permet de limiter cette vérification aux données les plus récentes.

Colonnes supprimées

Un VACUUM ne s’occupe pas de l’espace libéré par des colonnes supprimées (fragmentation verticale). Un VACUUM FULL est nécessaire pour reconstruire la table.


Le wraparound (1)

  • Wraparound : bouclage du compteur de xmin/xmax
  • 32 bits ~ 4 milliards