PostgreSQL : Transactions, Verrous, Niveaux d'isolation, MVCC, VACUUM

PostgreSQL : Plongée au cœur des transactions, verrous et isolation sérialisable

Les développeurs rencontrent souvent des comportements inattendus avec PostgreSQL : des blocages, des résultats d'opérations UPDATE surprenants lors d'exécutions concurrentes, ou des échecs de transactions. Comprendre les mécanismes internes comme les niveaux d'isolation, les verrous et le fonctionnement de VACUUM est crucial pour bâtir des applications fiables et performantes. Cet article décortique en profondeur ces concepts, les illustre avec des exemples SQL pratiques et met en lumière les récentes améliorations de PostgreSQL.

Le temps dans les transactions : `now()` vs `clock_timestamp()`

PostgreSQL propose deux fonctions principales pour récupérer l'heure actuelle, dont le comportement diffère dans le contexte des transactions. La fonction now() renvoie l'heure de début de la transaction courante et reste inchangée tout au long de son exécution. Cela garantit la cohérence des horodatages pour les opérations au sein d'une même transaction, par exemple, lors de la définition des champs created_at et updated_at.

BEGIN;
SELECT now();
-- ... autres opérations ...
SELECT now(); -- Retournera la même heure que le premier appel
SELECT clock_timestamp(); -- Peut différer
COMMIT;

Contrairement à now(), la fonction clock_timestamp() fournit l'heure d'exécution réelle et actuelle, qui peut changer même au sein d'une seule requête ou transaction. Comprendre cette différence permet d'éviter des erreurs lors de la manipulation de données temporelles.

Niveaux d'isolation des transactions dans PostgreSQL

PostgreSQL présente un modèle simplifié de niveaux d'isolation par rapport à d'autres SGBDR, n'en offrant que trois :

Read Committed (Lecture Validée) : Le niveau par défaut, où chaque commande au sein d'une transaction voit un instantané frais des données. Les lectures non validées (dirty reads) sont fondamentalement impossibles dans PostgreSQL, donc Read Uncommitted est équivalent à Read Committed.
Repeatable Read (Lecture Répétable) : Garantit que toutes les opérations de lecture au sein d'une transaction voient le même instantané des données, fixé au moment de la première requête. Cela empêche les lectures fantômes (phantom reads) et les lectures non répétables (non-repeatable reads).
Serializable (Sérialisable) : Le niveau le plus strict, garantissant que les transactions exécutées concurremment produisent le même résultat que si elles avaient été exécutées séquentiellement. Ceci est réalisé grâce au mécanisme d'Isolation d'Instantané Sérialisable (SSI).

Une caractéristique importante de PostgreSQL est son support du LDD (Langage de Définition de Données) au sein des transactions. Cela permet de créer, modifier ou supprimer des structures de données et de les annuler si nécessaire, améliorant ainsi la sécurité des migrations et des changements de schéma complexes.

Mécanismes de verrouillage et contrôle de concurrence

Malgré l'utilisation du contrôle de concurrence multi-version (MVCC), PostgreSQL emploie activement des verrous pour les opérations d'écriture. Lorsque deux transactions tentent de modifier la même ligne, la seconde transaction attendra que la première se termine. Considérez cet exemple :

-- Session 1 :
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 WHERE id = 1 RETURNING *;
-- ... ne pas valider ...

-- Session 2 :
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE t_test1 SET id = id + 1 WHERE id = 1 RETURNING *;
-- Cette requête se bloquera, attendant la fin de la Session 1.

Le tableau ci-dessous présente les principaux types de verrous dans PostgreSQL, leur objectif et les modes conflictuels :

| Type de Verrou | Quand il est acquis | Entre en conflit avec |

| :-------------------- | :------------------------------------------ | :---------------------------- |

| ACCESS SHARE | SELECT | ACCESS EXCLUSIVE |

| ROW SHARE | SELECT FOR UPDATE/SHARE | EXCLUSIVE, ACCESS EXCLUSIVE |

| ROW EXCLUSIVE | INSERT, UPDATE, DELETE | SHARE et supérieurs |

| SHARE UPDATE EXCLUSIVE | CREATE INDEX CONCURRENTLY, ANALYZE, VACUUM | SHARE UPDATE EXCLUSIVE et supérieurs |

| SHARE | CREATE INDEX | ROW EXCLUSIVE et supérieurs |

| SHARE ROW EXCLUSIVE | CREATE TRIGGER, ALTER TABLE (certains) | ROW SHARE et supérieurs |

| EXCLUSIVE | N'autorise que les lectures | ROW SHARE et supérieurs |

| ACCESS EXCLUSIVE | DROP TABLE, ALTER TABLE, VACUUM FULL | Tous |

Plus le verrou est strict, plus son numéro est élevé dans le tableau et plus l'éventail des opérations conflictuelles est large. Les opérations avec des verrous conflictuels ne peuvent pas s'exécuter simultanément sur le même objet ; l'une attendra. Il est important de noter que CREATE INDEX CONCURRENTLY permet de construire des index sans bloquer les opérations DML, contrairement à CREATE INDEX standard.

Anomalies de lecture : Données fantômes et non répétables

Au niveau d'isolation Read Committed, des anomalies de lecture peuvent survenir, entraînant des résultats incohérents :

Lectures fantômes (Phantom Reads) : Lorsqu'une transaction exécute la même requête deux fois, et que la deuxième fois elle voit de nouvelles lignes insérées par une autre transaction validée entre ces deux requêtes.

```sql

-- Session 1 :

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT sum(value) FROM t_test2; -- Par exemple, 330

-- Session 2 (concurremment) :

INSERT INTO t_test2 VALUES (1, 30); COMMIT;

-- Session 1 :

SELECT sum(value) FROM t_test2; -- Maintenant 360, de nouvelles lignes sont apparues.

COMMIT;

```

Lectures non répétables (Non-Repeatable Reads) : Lorsqu'une transaction lit les mêmes données deux fois, et que la deuxième fois elle voit des données modifiées, altérées par une autre transaction validée.

```sql

-- Session 1 :

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT value FROM t_test2 WHERE class = 1 AND value = 10; -- Retourne une ligne

-- Session 2 (concurremment) :

UPDATE t_test2 SET value = 15 WHERE class = 1 AND value = 10; COMMIT;

-- Session 1 :

SELECT value FROM t_test2 WHERE class = 1 AND value = 10; -- Vide ! Les données ont changé.

COMMIT;

```

Pour éviter ces anomalies, le niveau d'isolation Repeatable Read est utilisé. À ce niveau, une transaction opère sur un instantané de données fixe qui ne change pas jusqu'à sa complétion, quelles que soient les modifications apportées par d'autres transactions.

Particularités du niveau d'isolation Serializable

Le niveau Serializable est conçu pour offrir une isolation maximale, simulant une exécution séquentielle des transactions. Cependant, cela peut entraîner des erreurs de sérialisation (ERROR: could not serialize access) si PostgreSQL détecte un conflit potentiel qui pourrait violer l'ordre d'exécution. Dans de tels cas, l'une des transactions sera annulée et devra être relancée.

Un aspect intéressant de Serializable est celui des faux conflits, causés par les verrous de prédicat (SSI). PostgreSQL stocke ces verrous au niveau de la page, et non des lignes individuelles. Si deux transactions travaillent avec des ensembles de données logiquement différents, mais que ces données sont physiquement situées sur la même page disque (ce qui arrive souvent avec de petites tables), un conflit de sérialisation peut survenir, même s'il n'y en a pas logiquement. Dans les grandes tables, où les données sont réparties sur différentes pages, de tels conflits sont moins courants.

Résoudre les problèmes de concurrence

Considérons un problème classique de course aux données : deux transactions tentent simultanément de modifier des données basées sur des lectures obsolètes. Par exemple, Alice et Bob sont d'astreinte, et chacun veut partir si au moins une personne reste en service. Si les deux transactions utilisent Repeatable Read et voient deux personnes d'astreinte, elles se valident toutes les deux avec succès, ce qui conduit à ce que personne ne soit d'astreinte.

Pour résoudre de tels problèmes, les approches suivantes sont utilisées :

Serializable : La seconde transaction échouera avec une erreur de sérialisation, et elle pourra alors être relancée. Cela garantit un résultat correct mais nécessite une gestion des erreurs dans l'application.
SELECT ... FOR UPDATE : L'utilisation de FOR UPDATE lors d'une lecture verrouille explicitement les lignes sélectionnées, empêchant d'autres transactions de les modifier jusqu'à ce que la transaction courante se termine. La seconde session attendra que la première se termine, puis elle verra les données mises à jour.

```sql

BEGIN ISOLATION LEVEL REPEATABLE READ;

SELECT count(*) FROM d_test WHERE on_call = true FOR UPDATE; -- Verrouille les lignes

-- ... autres opérations ...

COMMIT;

```

Opération atomique : Combiner la vérification de la condition et l'action en une seule requête. Si la condition n'est pas remplie au moment de l'UPDATE, la requête n'affectera aucune ligne, éliminant ainsi la fenêtre de course.

```sql

UPDATE d_test

SET on_call = false

WHERE name = 'Alice'

AND (SELECT count(*) FROM d_test WHERE on_call = true) > 1;

```

Verrous consultatifs (Advisory Locks)

PostgreSQL fournit un mécanisme de verrous consultatifs (pg_advisory_lock, pg_advisory_xact_lock) qui permet de contrôler l'accès concurrent à des ressources arbitraires non directement liées aux tables ou aux lignes. Ces verrous opèrent sur des valeurs entières et peuvent être utilisés pour coordonner des processus.

pg_advisory_lock : Verrou au niveau de la session. Persiste jusqu'à sa libération explicite (pg_advisory_unlock) ou jusqu'à la fermeture de la connexion.
pg_advisory_xact_lock : Verrou au niveau de la transaction. Libéré automatiquement lors d'un COMMIT ou ROLLBACK.

Il est important de se rappeler que les verrous consultatifs ne fonctionnent qu'au sein d'une seule base de données. Pour les systèmes distribués avec plusieurs instances PostgreSQL, des solutions externes sont nécessaires (par exemple, Redis, ZooKeeper, etcd).

Le rôle de VACUUM dans MVCC

Le MVCC (Multi-Version Concurrency Control) dans PostgreSQL implique qu'à chaque UPDATE ou DELETE, les anciennes versions des lignes ne sont pas immédiatement supprimées mais sont marquées comme des 'tuples morts'. Ces tuples morts occupent de l'espace et doivent être nettoyés pour libérer de l'espace pour de nouvelles données et éviter le gonflement de la table. Le processus VACUUM est responsable de cette tâche.

Par défaut, VACUUM ne réduit pas la taille physique du fichier de la table. Il marque simplement l'espace libéré comme disponible pour réutilisation. Cela signifie que les opérations INSERT ou UPDATE ultérieures peuvent occuper cet espace sans augmenter la taille du fichier.

CREATE TABLE t_test (id int) WITH (autovacuum_enabled = off);
INSERT INTO t_test SELECT * FROM generate_series(1, 100000);
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~3.5 Mo

UPDATE t_test SET id = id + 1; -- Crée de nouvelles versions de lignes
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~7 Mo (taille doublée)

VACUUM t_test; -- Nettoie les tuples morts, mais la taille du fichier ne change pas
SELECT pg_size_pretty(pg_relation_size('t_test')); -- Toujours ~7 Mo

La réduction physique de la taille du fichier de la table ne se produit que si VACUUM peut 'tronquer la fin' — c'est-à-dire, si les pages libérées se trouvent à la fin du fichier. Par exemple, après la suppression d'un grand nombre de lignes avec des id élevés :

DELETE FROM t_test WHERE id > 50000;
VACUUM t_test;
SELECT pg_size_pretty(pg_relation_size('t_test')); -- ~3.5 Mo (taille réduite)

L'autovacuum automatique est un processus d'arrière-plan d'une importance capitale qui surveille constamment les changements dans les tables et exécute VACUUM pour maintenir la santé de la base de données. Le désactiver sans une compréhension approfondie des conséquences est un anti-patron dans les versions modernes de PostgreSQL.

Évolution de PostgreSQL : Ce qui a changé au cours des 5 dernières années

Bien que les principes de base des transactions et des verrous restent constants, PostgreSQL évolue continuellement, offrant des améliorations visant la performance et la convivialité :

Optimisation de VACUUM : PostgreSQL 13 a introduit le traitement parallèle des index pendant VACUUM, et PostgreSQL 16 a ajouté le saut des pages inchangées depuis le dernier passage. Ces améliorations ont considérablement accéléré les opérations VACUUM sur les grandes tables, rendant la gestion manuelle moins pertinente.
REINDEX CONCURRENTLY (PG 12) : Permet de reconstruire les index sans bloquer les opérations DML sur la table, ce qui est essentiel pour les systèmes à forte charge.
Amélioration de la surveillance des verrous : Les vues système pg_stat_activity et pg_locks ont été enrichies de nouveaux champs, et l'extension pg_wait_sampling fournit des statistiques d'attente détaillées, simplifiant le diagnostic des problèmes de performance.

Points clés à retenir :

now() vs clock_timestamp() : now() fixe l'heure de début de la transaction pour la cohérence, clock_timestamp() fournit l'heure actuelle réelle.
Niveaux d'isolation : PostgreSQL offre Read Committed (par défaut), Repeatable Read (pour prévenir les anomalies de lecture) et Serializable (pour une cohérence maximale avec le risque d'erreurs de sérialisation).
Verrous : Malgré le MVCC, PostgreSQL utilise des verrous d'écriture. Comprendre la table des verrous et leurs conflits est essentiel pour optimiser l'accès concurrent.
Serializable et faux conflits : Le niveau Serializable peut provoquer des erreurs dues aux verrous de prédicat au niveau de la page, nécessitant la relance des transactions.
VACUUM et MVCC : VACUUM nettoie les tuples morts mais ne réduit généralement pas la taille du fichier ; il marque seulement l'espace pour réutilisation. La réduction physique se produit si les pages libérées se trouvent à la fin du fichier.