🔝 Retour au Sommaire

19.4.1. Diagnostic des verrous (pg_locks, pg_blocking_pids)

Introduction : Qu'est-ce qu'un verrou ?

Avant de plonger dans le diagnostic, comprenons d'abord ce qu'est un verrou (ou lock en anglais) dans PostgreSQL.

Imaginez une bibliothèque où plusieurs personnes veulent consulter ou modifier le même livre en même temps. Pour éviter le chaos, il faut établir des règles : si quelqu'un est en train de corriger une page, personne d'autre ne peut modifier cette page en même temps. C'est exactement le rôle des verrous dans une base de données.

Pourquoi les verrous sont-ils nécessaires ?

Les verrous garantissent l'intégrité des données dans un environnement concurrent. Ils empêchent :

Les écritures simultanées conflictuelles : Deux transactions ne peuvent pas modifier la même ligne en même temps
Les lectures incohérentes : Une transaction ne doit pas lire des données en cours de modification
Les modifications de structure dangereuses : Empêcher la suppression d'une table pendant qu'elle est utilisée

PostgreSQL gère automatiquement la majorité des verrous grâce à son système MVCC (Multiversion Concurrency Control), mais il arrive parfois que des blocages (blocking) se produisent.

Les symptômes d'un problème de verrous

Comment savoir si vous avez un problème de verrous ? Voici les signes typiques :

Des requêtes qui "pendent" : Une requête qui devrait prendre 100ms prend maintenant plusieurs secondes, voire minutes
L'application devient lente : Les utilisateurs se plaignent de temps de réponse anormalement longs
Des timeouts : L'application reçoit des erreurs de type "timeout" ou "deadlock detected"
Une base de données qui semble "figée" : Certaines opérations ne se terminent jamais

Comprendre les types de verrous

PostgreSQL utilise différents types de verrous. Voici les principaux que vous rencontrerez :

Verrous au niveau des lignes (Row-Level Locks)

FOR UPDATE : Verrou exclusif sur une ligne, empêche toute modification
FOR SHARE : Verrou partagé, permet la lecture mais pas la modification

Verrous au niveau des tables (Table-Level Locks)

PostgreSQL définit 8 modes de verrous de table, du moins au plus restrictif :

ACCESS SHARE : Acquis par SELECT (lecture simple)
ROW SHARE : Acquis par SELECT FOR UPDATE
ROW EXCLUSIVE : Acquis par INSERT, UPDATE, DELETE
SHARE UPDATE EXCLUSIVE : Acquis par VACUUM, CREATE INDEX CONCURRENTLY
SHARE : Acquis par CREATE INDEX (sans CONCURRENTLY)
SHARE ROW EXCLUSIVE : Protection contre modifications concurrentes
EXCLUSIVE : Bloque tous sauf ACCESS SHARE
ACCESS EXCLUSIVE : Le plus restrictif, acquis par ALTER TABLE, DROP TABLE, TRUNCATE

Important pour les débutants : Vous n'avez pas besoin de mémoriser tous ces modes. Retenez simplement que :

Les lectures (SELECT) sont généralement non-bloquantes
Les écritures (INSERT, UPDATE, DELETE) peuvent se bloquer mutuellement
Les modifications de structure (ALTER TABLE, DROP) bloquent tout

La vue `pg_locks` : Votre outil de diagnostic principal

La vue système pg_locks est la fenêtre qui vous permet de voir tous les verrous actifs dans votre base de données.

Structure de base de pg_locks

SELECT * FROM pg_locks LIMIT 5;

Cette vue contient de nombreuses colonnes. Voici les plus importantes pour débuter :

Colonne	Description
`locktype`	Type de verrou (relation, tuple, transactionid, etc.)
`database`	OID de la base de données concernée
`relation`	OID de la table concernée (NULL si non applicable)
`pid`	ID du processus PostgreSQL qui détient le verrou
`mode`	Mode du verrou (AccessShareLock, RowExclusiveLock, etc.)
`granted`	`true` si le verrou est acquis, `false` s'il est en attente
`fastpath`	`true` si acquisition rapide (optimisation interne)

Interpréter la colonne `granted`

C'est la colonne la plus importante pour diagnostiquer un problème :

granted = true : Le processus détient actuellement le verrou
granted = false : Le processus attend d'obtenir le verrou (il est bloqué !)

Première requête de diagnostic : Identifier les verrous en attente

Voici une requête simple pour voir quels processus sont bloqués en attente d'un verrou :

SELECT
    pid,
    usename,
    pg_blocking_pids(pid) AS blocked_by,
    query AS current_query
FROM pg_stat_activity  
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Explication ligne par ligne :

pid : L'identifiant du processus bloqué
usename : Le nom de l'utilisateur qui a lancé la requête
pg_blocking_pids(pid) : Fonction magique qui retourne les PID des processus bloquants
query : La requête qui est bloquée
WHERE cardinality(pg_blocking_pids(pid)) > 0 : Filtre pour ne garder que les processus effectivement bloqués

Exemple de résultat

 pid  | usename  | blocked_by |           current_query
------+----------+------------+-----------------------------------
 1234 | app_user | {5678}     | UPDATE orders SET status = 'paid' WHERE id = 42;

Interprétation : Le processus 1234 est bloqué par le processus 5678.

La fonction `pg_blocking_pids()` : Votre meilleure amie

Introduite dans PostgreSQL 9.6, pg_blocking_pids() est une fonction qui simplifie énormément le diagnostic.

Syntaxe

pg_blocking_pids(pid integer) → integer[]

Elle retourne un tableau contenant les PID de tous les processus qui bloquent le processus spécifié.

Exemple d'utilisation

-- Trouver qui bloque le processus 1234
SELECT pg_blocking_pids(1234);

-- Résultat possible : {5678}

Construire une vue complète du blocage

Pour avoir une vue d'ensemble, vous pouvez créer cette requête qui montre à la fois les bloquants et les bloqués :

SELECT
    blocked_activity.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_activity.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_query,
    blocking_activity.query AS blocking_query
FROM pg_stat_activity AS blocked_activity  
JOIN pg_stat_activity AS blocking_activity  
    ON blocking_activity.pid = ANY(pg_blocking_pids(blocked_activity.pid))
WHERE cardinality(pg_blocking_pids(blocked_activity.pid)) > 0;

Cette requête vous donne :

Qui est bloqué (blocked_pid, blocked_user, blocked_query)
Qui bloque (blocking_pid, blocking_user, blocking_query)

Requête avancée : Vue détaillée avec informations de verrous

Pour aller plus loin, voici une requête qui combine pg_locks et pg_stat_activity :

SELECT
    l.pid,
    l.mode,
    l.granted,
    l.locktype,
    l.relation::regclass AS table_name,
    a.usename,
    a.query,
    a.state,
    age(now(), a.query_start) AS query_duration
FROM pg_locks l  
JOIN pg_stat_activity a ON l.pid = a.pid  
WHERE NOT l.granted  
ORDER BY a.query_start;

Que montre cette requête ?

Les verrous non accordés (NOT l.granted)
Le nom de la table concernée (l.relation::regclass)
La durée d'attente (age(now(), a.query_start))
L'état du processus (a.state)

Exemple de résultat

 pid  |       mode        | granted | locktype |   table_name   | usename  | query_duration
------+-------------------+---------+----------+----------------+----------+----------------
 1234 | RowExclusiveLock  | false   | tuple    | orders         | app_user | 00:02:15

Interprétation : Le processus 1234 attend depuis 2 minutes et 15 secondes un verrou de type "RowExclusiveLock" sur la table orders.

Diagnostic en temps réel : Trouver la chaîne de blocage

Parfois, vous avez une chaîne de blocages : A bloque B, qui bloque C, qui bloque D...

Voici une requête récursive (avec CTE) pour visualiser toute la chaîne :

WITH RECURSIVE lock_chain AS (
    -- Ancrage : Les processus qui ne sont pas bloqués mais qui bloquent d'autres
    SELECT
        pid,
        pg_blocking_pids(pid) AS blocking_pids,
        array[pid] AS chain,
        1 AS depth
    FROM pg_stat_activity
    WHERE cardinality(pg_blocking_pids(pid)) > 0

    UNION ALL

    -- Récursion : Remonter la chaîne des bloquants
    SELECT
        a.pid,
        pg_blocking_pids(a.pid),
        lc.chain || a.pid,
        lc.depth + 1
    FROM lock_chain lc
    JOIN pg_stat_activity a ON a.pid = ANY(lc.blocking_pids)
    WHERE lc.depth < 10  -- Limite de profondeur pour éviter boucles infinies
)
SELECT
    chain AS blocking_chain,
    depth,
    a.pid,
    a.usename,
    a.query
FROM lock_chain lc  
JOIN pg_stat_activity a ON a.pid = lc.chain[1]  
ORDER BY depth DESC, chain;

Cette requête avancée montre toute la hiérarchie des blocages, ce qui est très utile pour comprendre un problème complexe.

Identifier les deadlocks (interblocages)

Un deadlock (ou interblocage) se produit quand deux transactions s'attendent mutuellement :

Transaction A détient le verrou sur la ligne X et attend la ligne Y
Transaction B détient le verrou sur la ligne Y et attend la ligne X

PostgreSQL détecte automatiquement les deadlocks et en annule un (avec l'erreur deadlock detected).

Comment voir les deadlocks dans les logs

Les deadlocks sont enregistrés dans les logs PostgreSQL. Configurez votre postgresql.conf :

log_lock_waits = on  
deadlock_timeout = 1s

Puis consultez vos logs pour voir les détails des deadlocks détectés.

Résoudre un blocage : Les actions possibles

Une fois que vous avez identifié le processus bloquant, plusieurs options s'offrent à vous :

1. Attendre (Solution la plus simple)

Si la transaction bloquante devrait se terminer rapidement, attendez simplement. Les verrous sont libérés automatiquement à la fin de la transaction.

2. Terminer la transaction bloquante (Solution radicale)

Si une transaction est restée ouverte par erreur ou bloque tout le monde, vous pouvez la terminer :

-- Terminer proprement la connexion
SELECT pg_terminate_backend(5678);

-- Si ça ne marche pas, forcer l'arrêt (plus violent)
SELECT pg_cancel_backend(5678);

⚠️ ATTENTION :

pg_cancel_backend() annule uniquement la requête en cours
pg_terminate_backend() ferme complètement la connexion
Utilisez ces commandes avec précaution en production !

3. Optimiser les requêtes pour réduire les blocages

À plus long terme, les solutions préventives sont meilleures :

Raccourcir les transactions : Ne gardez pas les transactions ouvertes longtemps
Indexer correctement : Les requêtes plus rapides retiennent les verrous moins longtemps
Utiliser FOR UPDATE SKIP LOCKED : Permet de sauter les lignes verrouillées plutôt que d'attendre
Ordonner les accès : Si plusieurs transactions accèdent aux mêmes ressources, faites-le dans le même ordre

Requête pratique : Vue de surveillance complète

Voici une requête "tout-en-un" à garder sous la main pour surveiller votre base :

SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.state AS blocked_state,
    age(now(), blocked.query_start) AS blocked_duration,
    blocked.query AS blocked_query,
    blocker.pid AS blocker_pid,
    blocker.usename AS blocker_user,
    blocker.state AS blocker_state,
    age(now(), blocker.query_start) AS blocker_duration,
    blocker.query AS blocker_query
FROM pg_stat_activity blocked  
JOIN (  
    SELECT DISTINCT
        blocking.pid,
        blocked_activity.pid AS blocked_by_pid
    FROM pg_stat_activity blocking
    JOIN pg_stat_activity blocked_activity
        ON blocking.pid = ANY(pg_blocking_pids(blocked_activity.pid))
) blocker_info ON blocker_info.blocked_by_pid = blocked.pid
JOIN pg_stat_activity blocker ON blocker.pid = blocker_info.pid  
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0  
ORDER BY blocked.query_start;

Comprendre la vue `pg_locks` en détail

Pour les curieux qui veulent aller plus loin, voici une exploration des principaux locktype :

Types de verrous (`locktype`)

Type	Description
`relation`	Verrou sur une table entière
`tuple`	Verrou sur une ligne spécifique
`transactionid`	Verrou sur un ID de transaction (pour attendre sa fin)
`virtualxid`	Verrou virtuel sur l'ID de transaction courante
`page`	Verrou sur une page de données (rare)
`advisory`	Verrou applicatif personnalisé (voir Advisory Locks)

Modes de verrous les plus courants

SELECT mode, count(*)  
FROM pg_locks  
GROUP BY mode  
ORDER BY count(*) DESC;

Résultat typique :

        mode         | count
---------------------+-------
 AccessShareLock     | 1245
 RowExclusiveLock    | 87
 ExclusiveLock       | 12
 ShareLock           | 3

Bonnes pratiques pour éviter les blocages

1. Gardez les transactions courtes

-- ❌ MAUVAIS : Transaction qui reste ouverte
BEGIN;  
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  
-- ... longue pause, autre traitement application ...
-- ... requête HTTP externe ...
COMMIT;

-- ✅ BON : Transaction courte et focalisée
BEGIN;  
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  
COMMIT;

2. Évitez les modifications de schéma aux heures de pointe

Les ALTER TABLE acquièrent un verrou ACCESS EXCLUSIVE qui bloque toutes les autres opérations.

-- Préférez les heures creuses pour :
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;  
DROP INDEX idx_old;  
TRUNCATE TABLE logs;

3. Utilisez des index appropriés

Des requêtes lentes retiennent les verrous plus longtemps. Une bonne indexation réduit drastiquement les risques de blocage.

4. Configurez des timeouts appropriés

Dans votre application ou dans PostgreSQL :

-- Timeout de 5 secondes pour une requête
SET statement_timeout = '5s';

-- Timeout pour l'acquisition d'un verrou
SET lock_timeout = '2s';

Surveillance proactive : Créer une vue personnalisée

Pour faciliter le monitoring régulier, vous pouvez créer une vue qui encapsule votre requête de diagnostic :

CREATE OR REPLACE VIEW v_blocked_queries AS  
SELECT  
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    age(now(), blocked.query_start) AS wait_duration,
    blocked.query AS blocked_query,
    blocker.pid AS blocking_pid,
    blocker.usename AS blocking_user,
    blocker.query AS blocking_query,
    blocker.state AS blocking_state
FROM pg_stat_activity blocked  
JOIN pg_stat_activity blocker  
    ON blocker.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

Ensuite, il suffit de faire :

SELECT * FROM v_blocked_queries;

Outils graphiques pour visualiser les verrous

Si vous préférez une interface graphique, plusieurs outils peuvent vous aider :

pgAdmin 4

Onglet "Dashboard" → Section "Server Activity"
Menu "Tools" → "Server Activity" (affiche pg_stat_activity et permet de terminer des processus)

DBeaver

Outils → "Database Monitor" → Vue "Locks"

Monitoring avancé avec Grafana

Des dashboards Grafana pré-configurés existent pour PostgreSQL, avec des panneaux dédiés aux verrous et blocages.

Cas d'étude : Diagnostiquer un problème réel

Imaginons ce scénario : Votre application web est soudainement très lente. Voici la démarche de diagnostic :

Étape 1 : Identifier s'il y a des blocages

SELECT count(*)  
FROM pg_stat_activity  
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Résultat : 45 → Il y a 45 processus bloqués !

Étape 2 : Identifier les coupables

SELECT
    blocker.pid,
    blocker.usename,
    blocker.query,
    count(*) AS blocked_count
FROM pg_stat_activity blocker  
JOIN pg_stat_activity blocked  
    ON blocker.pid = ANY(pg_blocking_pids(blocked.pid))
GROUP BY blocker.pid, blocker.usename, blocker.query  
ORDER BY blocked_count DESC;

Résultat : Le processus 8765 bloque 42 autres processus avec la requête :

UPDATE products SET stock = stock - 1 WHERE id = 123;

Étape 3 : Comprendre pourquoi

SELECT
    state,
    age(now(), state_change) AS time_in_state,
    age(now(), xact_start) AS transaction_age
FROM pg_stat_activity  
WHERE pid = 8765;

Résultat :

 state  | time_in_state | transaction_age
--------+---------------+-----------------
 idle in transaction | 00:15:32 | 00:15:32

Diagnostic : La transaction est restée ouverte depuis 15 minutes en état "idle in transaction" ! L'application a probablement oublié de faire un COMMIT ou ROLLBACK.

Étape 4 : Résoudre

-- Terminer cette connexion problématique
SELECT pg_terminate_backend(8765);

Étape 5 : Prévention

Ajoutez dans postgresql.conf :

idle_in_transaction_session_timeout = 60000  # 60 secondes

Cela fermera automatiquement les transactions inactives après 60 secondes.

Commandes rapides à mémoriser

Voici un récapitulatif des commandes essentielles pour le diagnostic de verrous :

-- 1. Voir tous les processus bloqués
SELECT pid, usename, pg_blocking_pids(pid), query  
FROM pg_stat_activity  
WHERE cardinality(pg_blocking_pids(pid)) > 0;  

-- 2. Voir tous les verrous actifs
SELECT * FROM pg_locks WHERE NOT granted;

-- 3. Voir qui bloque qui (version simple)
SELECT
    blocked.pid AS blocked,
    blocker.pid AS blocker,
    blocked.query AS blocked_query
FROM pg_stat_activity blocked  
JOIN pg_stat_activity blocker  
    ON blocker.pid = ANY(pg_blocking_pids(blocked.pid));

-- 4. Terminer un processus bloquant
SELECT pg_terminate_backend(PID);

-- 5. Annuler une requête sans tuer la connexion
SELECT pg_cancel_backend(PID);

Ressources pour aller plus loin

Documentation officielle PostgreSQL : Chapitre "Explicit Locking"
pg_stat_activity : Documentation de la vue
pg_locks : Documentation de la vue
Wiki PostgreSQL : "Lock Monitoring" (contient des requêtes avancées communautaires)

Résumé des points clés

✅ Les verrous sont normaux : Ils garantissent l'intégrité des données

✅ pg_locks : Vue système pour voir tous les verrous actifs

✅ pg_blocking_pids() : Fonction pour identifier facilement qui bloque qui

✅ granted = false : Signal d'alarme, un processus attend un verrou

✅ Terminer avec précaution : pg_terminate_backend() est une solution de dernier recours

✅ Prévention : Transactions courtes, bons index, timeouts configurés

✅ Monitoring proactif : Créez des vues et des alertes pour détecter les problèmes tôt

Conclusion

Le diagnostic des verrous est une compétence essentielle pour tout développeur ou DevOps travaillant avec PostgreSQL. Avec les outils présentés (pg_locks, pg_blocking_pids(), et pg_stat_activity), vous êtes maintenant équipé pour :

Identifier rapidement les situations de blocage
Comprendre qui bloque qui et pourquoi
Résoudre les situations d'urgence
Prévenir les problèmes futurs

N'oubliez pas : la meilleure résolution de problème est la prévention. Des transactions courtes, une bonne indexation et une surveillance proactive vous éviteront la majorité des cauchemars liés aux verrous.

Prochaine étape : 19.4.2 - Saturation des ressources (CPU, RAM, I/O)

⏭️ Saturation des ressources (CPU, RAM, I/O)

FilesExpand file tree

04.1-diagnostic-verrous.md

Latest commit

History

04.1-diagnostic-verrous.md

File metadata and controls

19.4.1. Diagnostic des verrous (pg_locks, pg_blocking_pids)

Introduction : Qu'est-ce qu'un verrou ?

Pourquoi les verrous sont-ils nécessaires ?

Les symptômes d'un problème de verrous

Comprendre les types de verrous

Verrous au niveau des lignes (Row-Level Locks)

Verrous au niveau des tables (Table-Level Locks)

La vue pg_locks : Votre outil de diagnostic principal

Structure de base de pg_locks

Interpréter la colonne granted

Première requête de diagnostic : Identifier les verrous en attente

Exemple de résultat

La fonction pg_blocking_pids() : Votre meilleure amie

Syntaxe

Exemple d'utilisation

Construire une vue complète du blocage

Requête avancée : Vue détaillée avec informations de verrous

Exemple de résultat

Diagnostic en temps réel : Trouver la chaîne de blocage

Identifier les deadlocks (interblocages)

Comment voir les deadlocks dans les logs

Résoudre un blocage : Les actions possibles

1. Attendre (Solution la plus simple)

2. Terminer la transaction bloquante (Solution radicale)

3. Optimiser les requêtes pour réduire les blocages

Requête pratique : Vue de surveillance complète

Comprendre la vue pg_locks en détail

Types de verrous (locktype)

Modes de verrous les plus courants

Bonnes pratiques pour éviter les blocages

1. Gardez les transactions courtes

2. Évitez les modifications de schéma aux heures de pointe

3. Utilisez des index appropriés

4. Configurez des timeouts appropriés

Surveillance proactive : Créer une vue personnalisée

Outils graphiques pour visualiser les verrous

pgAdmin 4

DBeaver

Monitoring avancé avec Grafana

Cas d'étude : Diagnostiquer un problème réel

Étape 1 : Identifier s'il y a des blocages

Étape 2 : Identifier les coupables

Étape 3 : Comprendre pourquoi

Étape 4 : Résoudre

Étape 5 : Prévention

Commandes rapides à mémoriser

Ressources pour aller plus loin

Résumé des points clés

Conclusion

La vue `pg_locks` : Votre outil de diagnostic principal

Interpréter la colonne `granted`

La fonction `pg_blocking_pids()` : Votre meilleure amie

Comprendre la vue `pg_locks` en détail

Types de verrous (`locktype`)