Monitoring des verrous et des conflits

Imagine que tu bosses dans un open space où toutes les portes sont bloquées parce qu’une personne a oublié ses clés dans la salle. C’est un peu pareil avec les verrous dans PostgreSQL. Si une requête ou une transaction a verrouillé une ressource, toutes les autres opérations qui veulent accéder à cette même ressource doivent attendre qu’elle ait fini. Ce genre de situation peut causer des délais, des scénarios de conflit et, dans le pire des cas, stopper tout le système.

Quand est-ce que les verrous apparaissent ?

Les verrous (Locks) dans PostgreSQL servent à gérer l’accès concurrent aux données. Ils apparaissent :

1. Quand tu fais des opérations d’écriture : UPDATE, DELETE, INSERT.
2. Quand tu utilises des transactions qui gardent une ressource plus longtemps que nécessaire.
3. Quand il y a des conflits entre différentes transactions qui veulent la même ressource.

Une vraie base de données, c’est un “champ de bataille” pour les ressources, et même si tu penses que ton système tourne nickel, une transaction maladroite peut tout “bloquer”, comme un merge foireux dans Git.

Outils d’analyse des verrous : pg_locks

pg_locks — c’est une vue système PostgreSQL qui montre les verrous actuels, détenus ou en attente par les transactions. Elle répond à la question : "Qui détient le verrou et qui attend ?"

Les champs principaux de pg_locks :

• locktype : type de verrou (par exemple, relation, transaction, page, tuple).
• database : identifiant de la base de données.
• relation : identifiant de la table (si le verrou concerne une table).
• mode : mode du verrou (par exemple, RowExclusiveLock, AccessShareLock).
• granted : flag qui montre si le verrou est accordé (true) ou si la transaction attend encore (false).

Remarque : PostgreSQL utilise ce qu’on appelle un “mode hiérarchique de verrous”. Ça veut dire que différentes opérations peuvent poser des verrous plus ou moins stricts (genre AccessShareLock pour la lecture, ou ExclusiveLock pour modifier la structure d’une table).

Exemple : voir tous les verrous actuels

SELECT *
FROM pg_locks;

Mais si tu affiches tout le contenu de pg_locks, tu risques d’avoir trop de bruit. Essayons un truc plus utile !

Exemple : les verrous qui ne sont pas encore accordés (donc les transactions attendent)

SELECT pid, locktype, relation::regclass AS table_name, mode, granted
FROM pg_locks
WHERE NOT granted;

Qu’est-ce qui se passe ici ?

• On filtre les lignes où granted = false, c’est-à-dire que le verrou n’a pas encore été accordé.
• relation::regclass convertit l’identifiant de la table en son nom pour que ce soit plus lisible.

Le résultat peut ressembler à ça :

Ces requêtes t’aident à identifier quelle table/ressource est bloquée et quelle transaction peut être la cause.

Analyse des conflits : pg_blocking_pids()

Les verrous, c’est déjà chiant, mais que faire si une transaction en bloque une autre ? PostgreSQL propose un moyen pratique de trouver le “coupable” avec la fonction pg_blocking_pids().

La fonction pg_blocking_pids() renvoie la liste des identifiants de processus (pid) qui bloquent l’exécution de la transaction courante.

Exemple : trouver les transactions qui en bloquent d’autres

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Qu’est-ce qui se passe ici ?

• On utilise la vue pg_stat_activity pour récupérer les processus actifs dans le système.
• La fonction pg_blocking_pids(pid) renvoie la liste des processus qui bloquent chaque pid. Si la liste n’est pas vide (longueur > 0), c’est que le process est bloqué.

Exemple de résultat :

La transaction avec pid = 4567 est bloquée par les processus 1234 et 5678. On a trouvé nos “coupables”.

Terminer les processus qui bloquent

Quand tu as identifié les processus qui bloquent, tu peux les stopper avec la fonction pg_terminate_backend() :

SELECT pg_terminate_backend(1234); -- On "tue" le process 1234

Mais fais gaffe ! Forcer l’arrêt d’un process peut entraîner un rollback des données de la transaction en cours. Utilise ce bouton “nucléaire” seulement en dernier recours.

Application pratique : scénario d’analyse des verrous

Imaginons qu’on a une base de données pour une université avec les tables students et enrollments. Plusieurs transactions essaient d’écrire en même temps dans la table enrollments, et on se retrouve avec des verrous.

1. Identifier les verrous :

SELECT pid, locktype, relation::regclass AS table_name, mode, granted
FROM pg_locks
WHERE NOT granted;

2. Trouver les processus qui bloquent :

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

3. Résoudre les verrous :

On termine de force un des processus en conflit :

SELECT pg_terminate_backend(1234); -- On termine le process 1234

Remarque : Avant de “tuer” un process, essaie de comprendre pourquoi le verrou est apparu. Peut-être qu’il faut revoir la logique des transactions.

Erreurs classiques et comment les éviter

Les verrous apparaissent souvent à cause d’une mauvaise gestion des transactions. Par exemple :

Erreur : une transaction garde un verrou trop longtemps sans rien faire (état "idle in transaction").

Solution : surveille bien l’état des transactions avec pg_stat_activity et termine les transactions “bloquées”.

SELECT pid, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Erreur : tu as oublié de mettre des index dans tes requêtes, ce qui provoque des verrous sur toute la table.

Solution : optimise tes requêtes en ajoutant des index sur les conditions utilisées souvent.

Affichage du tableau "qui attend qui"

Pour diagnostiquer plus facilement, tu peux construire un arbre de dépendances qui montre quelle transaction bloque laquelle :

WITH RECURSIVE blocking_tree AS (
  SELECT pid, pg_blocking_pids(pid) AS blocked_by
  FROM pg_stat_activity
  WHERE cardinality(pg_blocking_pids(pid)) > 0
  UNION ALL
  SELECT a.pid, pg_blocking_pids(a.pid)
  FROM pg_stat_activity a
  JOIN blocking_tree b ON a.pid = ANY(b.blocked_by)
)
SELECT pid, blocked_by FROM blocking_tree;

Résultat :

Ici, on voit que le process 5678 bloque le process 1234, qui lui-même bloque 4567.