Concurrence PostgreSQL sous le Capot : MVCC, Niveaux d’Isolation et Verrous

1. Le fonctionnement du MVCC dans PostgreSQL

PostgreSQL n’écrase jamais une ligne en place lors d’un UPDATE. Il écrit une nouvelle version de la ligne (un nouveau tuple) avec un xmin mis à jour (l’identifiant de transaction qui l’a créée), et marque le xmax de l’ancienne version avec l’identifiant de la transaction qui l’a remplacée. Les deux versions coexistent physiquement sur disque jusqu’à ce que vacuum récupère l’ancienne, ce qui permet aux lecteurs et aux écrivains d’avancer simultanément sans se bloquer mutuellement — un lecteur voit un instantané cohérent des lignes visibles au début de sa transaction, indépendamment des écrivains concurrents modifiant les mêmes lignes.

C’est pourquoi les lecteurs PostgreSQL ne bloquent jamais les écrivains et les écrivains ne bloquent jamais les lecteurs sous les règles de visibilité MVCC normales — un contraste net avec les modèles de concurrence à base de verrous. Le coût se paie ailleurs : chaque UPDATE est en réalité un INSERT plus un marquage de mort, chaque DELETE ne fait que marquer un tuple mort sans le supprimer, et la table grossit physiquement à chaque écriture jusqu’à ce que le processus VACUUM d’autovacuum la balaie et récupère l’espace occupé par les tuples non visibles par aucune transaction active.

C’est aussi la racine mécanique du bloat de table évoqué dans l’article EXPLAIN ANALYZE de cette série : une table à fort trafic UPDATE/DELETE avec un vacuum à la traîne (parce que désactivé, mal configuré, ou bloqué par une transaction longue retenant l’horizon de vacuum) accumule des tuples morts que les Seq Scans, et même les scans d’index, doivent contourner, gonflant à la fois la taille de la table et le coût des requêtes avec des lignes qu’aucune transaction vivante ne peut même voir.

Une transaction longue est dangereuse précisément à cause de ce mécanisme : tant qu’elle reste ouverte, PostgreSQL ne peut pas nettoyer une version de tuple que l’instantané de cette transaction pourrait encore avoir besoin de voir, même si cette transaction ne touche jamais la table en question. Une simple connexion inactive-en-transaction laissée ouverte pendant des heures sur une base active peut bloquer silencieusement vacuum sur toute l’instance, d’où l’intérêt de `idle_in_transaction_session_timeout` et de surveiller `pg_stat_activity` pour repérer les vieilles transactions.

2. Les niveaux d’isolation SQL

Le standard SQL définit quatre niveaux d’isolation selon les anomalies qu’ils évitent : Read Uncommitted (autorise les lectures sales — voir les changements non validés d’une autre transaction), Read Committed (évite les lectures sales mais autorise les lectures non répétables — la même requête exécutée deux fois dans une transaction peut retourner des résultats différents si une autre transaction valide entre-temps), Repeatable Read (évite les lectures non répétables mais autorise selon le standard les lectures fantômes — de nouvelles lignes correspondant à un WHERE apparaissant à la relecture), et Serializable (évite tout cela, se comportant comme si les transactions s’exécutaient une par une).