Comment PWR détecte un lock dans PostgreSQL

Introduction : quand un lock devient critique

Lorsqu'un système PostgreSQL commence à ralentir, les symptômes sont souvent visibles dans les métriques globales avant même d'identifier une cause précise : un AAS (Average Active Sessions) élevé, une latence P95 extrême, des sessions bloquées, des wait events dominés par l'IO, et un CPU saturé ou proche de sa limite.

Dans le cas réel analysé par PWR ci-dessous, l'AAS atteint 4.72, la latence P95 grimpe à 24 430 ms, la limite CPU est de 8 (utilisée à 59%), une session est bloquée, et les wait events dominants sont l'IO et le CPU. Ce type de profil est typique d'un lock non libéré ou d'une transaction laissée ouverte trop longtemps.

PWR détecte automatiquement ces blocages grâce à l'analyse ASH (Active Session History) et aux snapshots réguliers de l'activité — sans qu'aucune configuration manuelle ne soit nécessaire.

Reproduire un lock PostgreSQL : scénario de test

Pour comprendre concrètement comment PWR identifie un lock, voici un scénario simple et reproductible sur une table de test dédiée.

CREATE TABLE public.lock_test (
  id bigserial PRIMARY KEY,
  name text NOT NULL,
  value int NOT NULL DEFAULT 0,
  updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO public.lock_test (name, value)
VALUES ('row-1', 10);

Simuler un verrou avec deux sessions

Étape 2 consiste à ouvrir deux sessions psql en parallèle. La session A prend un verrou et ne le libère pas : elle démarre une transaction, met à jour la ligne id = 1, puis reste ouverte sans COMMIT ni ROLLBACK. PostgreSQL place alors un verrou ROW EXCLUSIVE sur cette ligne, actif tant que la transaction n'est pas terminée.

La session B tente ensuite de modifier la même ligne. Sans lock_timeout, elle attend indéfiniment que la session A libère le verrou. Avec un lock_timeout de 10 secondes par exemple, elle reçoit une erreur explicite après ce délai plutôt que de bloquer sans limite.

BEGIN;
UPDATE public.lock_test
SET value = value + 1, updated_at = now()
WHERE id = 1;
-- On garde la transaction ouverte, sans COMMIT ni ROLLBACK

BEGIN;
SET lock_timeout = '10s';  -- optionnel
UPDATE public.lock_test
SET value = value + 5, updated_at = now()
WHERE id = 1;

Comment PWR détecte un lock ?

PWR analyse en continu plusieurs sources : pg_locks, pg_stat_activity, la fonction pg_blocking_pids(), les snapshots ASH, les deltas calculés entre deux snapshots, ainsi que les états de session tels que idle in transaction ou session-lock-wait.

Il distingue deux catégories de blocages, car elles n'ont ni les mêmes symptômes ni le même impact : les blocages LOCK-LEVEL, correspondant aux verrous explicites visibles dans pg_locks, et les blocages SESSION-LEVEL, correspondant aux transactions ouvertes trop longtemps ou aux attentes invisibles dans les verrous classiques mais bien réelles dans pg_stat_activity.

Blocages LOCK-LEVEL : les verrous explicites (pg_locks)

Les blocages LOCK-LEVEL sont les verrous visibles dans pg_locks : une ligne avec granted = false, portant sur une relation, un tuple, une page ou un transactionid, avec un mode ShareLock, ExclusiveLock, etc.

Sur le cas réel détecté par PWR (snapshots 241 à 269), un verrou explicite est identifié : type transactionid, mode ShareLock, PID bloqué 1590247, PID bloquant 1589265. La requête bloquée est UPDATE public.lock_test SET value = value + 5 ..., et la requête bloquante est UPDATE public.lock_test SET value = value + 1 .... PWR affiche ce verrou dans sa section LOCK-LEVEL Blocking.

Blocages SESSION-LEVEL : les blocages transactionnels invisibles dans pg_locks

Les blocages SESSION-LEVEL sont invisibles dans pg_locks mais bien visibles dans l'ASH : ils se traduisent par des états comme session-lock-wait, session-blocked, idle-in-transaction ou idle-in-tx-wait, parfois associés eux aussi à un transactionid.

Sur le même cas réel (snapshots 241 à 269), PWR détecte 7 sessions bloquantes, avec des états observés session-lock-wait, idle-in-transaction et session-blocked. Les requêtes concernées incluent UPDATE public.lock_test SET value = value + 1 ..., UPDATE public.lock_test SET value = value + 5 ..., un simple END;, ainsi que des requêtes applicatives comme UPDATE pgbench_branches ... et SELECT abalance FROM pgbench_accounts .... Ces blocages n'apparaissent dans aucun verrou classique, mais impactent directement les performances de la base.

Pourquoi PWR détecte les deux types de blocage ?

Parce que les deux causent des ralentissements, mais pour des raisons différentes. Les blocages LOCK-LEVEL correspondent à des verrous explicites : une contention directe sur une table ou une ligne, résolue dès que la transaction bloquante se termine.

Les blocages SESSION-LEVEL correspondent à des transactions ouvertes trop longtemps : des blocages invisibles dans les verrous classiques, mais tout aussi critiques puisqu'ils retiennent des ressources et bloquent d'autres sessions sans qu'aucun verrou explicite n'apparaisse. PWR combine les deux pour donner une vision complète de la contention réelle sur la base.

Recommandations générées par PWR

Pour les blocages SESSION-LEVEL (transactionnels), PWR recommande de vérifier les transactions laissées ouvertes, de s'assurer que chaque BEGIN est bien suivi d'un COMMIT ou d'un ROLLBACK, de surveiller les sessions idle in transaction, et d'activer idle_in_transaction_session_timeout pour couper automatiquement les transactions abandonnées. Sur le cas analysé, le commentaire généré est : « 7 session(s) bloquante(s) détectée(s) dans les snapshots 241–269 ».

Pour les blocages LOCK-LEVEL (verrous explicites), PWR recommande d'optimiser les requêtes qui génèrent des verrous lourds, d'ajouter des index pour réduire les scans larges, de réduire la contention sur les tables très mises à jour, et d'envisager le partitionnement si la contention persiste. Sur le cas analysé, le commentaire généré est : « 1 verrou bloqué détecté dans les snapshots 241–269 ».

Résumé : comment PWR détecte un lock

PWR détecte un lock en combinant plusieurs sources en continu : les verrous explicites de pg_locks, les blocages transactionnels de pg_stat_activity, les snapshots ASH, les deltas calculés entre deux snapshots, et les états de session comme idle in transaction ou session-lock-wait.

Il distingue systématiquement deux catégories — LOCK-LEVEL pour les verrous explicites, SESSION-LEVEL pour les transactions ouvertes ou les attentes invisibles — et fournit pour chacune un diagnostic complet, un résumé des blocages détectés et des recommandations actionnables plutôt qu'une simple liste de métriques brutes.