🔝 Retour au Sommaire

14.2.4. pg_locks : Verrous actifs

Introduction

La vue pg_locks est une vue système qui affiche tous les verrous (locks) actuellement actifs ou en attente dans votre instance PostgreSQL. C'est l'outil de diagnostic principal pour comprendre les problèmes de blocage entre transactions, les deadlocks, et les contentions de ressources.

Imaginez pg_locks comme un registre en temps réel de "qui utilise quoi" dans votre base de données. Chaque fois qu'une transaction lit ou modifie des données, PostgreSQL pose des verrous pour garantir la cohérence. Cette vue vous montre tous ces verrous actifs.

Pourquoi les verrous existent-ils ?

Les verrous sont essentiels pour maintenir l'intégrité des données dans un système multi-utilisateurs. Sans verrous, vous pourriez avoir :

Lectures sales (Dirty Reads) : Lire des données non validées d'une autre transaction
Écrasements de données : Deux transactions modifient la même ligne simultanément
Incohérences : Lire des données dans un état transitoire invalide

PostgreSQL utilise un système de verrous sophistiqué pour permettre à plusieurs transactions de travailler en parallèle tout en garantissant l'isolation et la cohérence (principes ACID).

Comprendre les concepts de base

Qu'est-ce qu'un verrou (lock) ?

Un verrou est une marque posée sur une ressource (table, ligne, index, etc.) qui indique qu'une transaction l'utilise. Il existe différents types de verrous avec différents niveaux de restriction :

Verrous partagés (SHARE) : Plusieurs transactions peuvent lire, mais aucune ne peut écrire
Verrous exclusifs (EXCLUSIVE) : Une seule transaction peut accéder à la ressource

Types de ressources verrouillables

PostgreSQL peut poser des verrous sur :

Relations : Tables, index, séquences, vues matérialisées
Lignes (Tuples) : Lignes individuelles dans une table
Pages : Blocs de données
Transactions : Identifiants de transaction
Objets : Autres objets de la base

Conflits de verrous

Un conflit se produit quand une transaction demande un verrou incompatible avec un verrou déjà posé par une autre transaction. Dans ce cas :

La transaction demandeuse attend que le verrou soit libéré
Si toutes les transactions attendent mutuellement → Deadlock (interblocage)

Accéder à pg_locks

SELECT * FROM pg_locks;

Cette requête montre tous les verrous actifs dans l'instance. Vous verrez généralement beaucoup de lignes, même sur un système peu chargé, car PostgreSQL pose des verrous pour presque toutes les opérations.

Vue simplifiée :

SELECT
    locktype,
    database,
    relation::regclass AS table_name,
    mode,
    granted,
    pid
FROM pg_locks  
WHERE NOT granted  -- Verrous en attente uniquement  
ORDER BY pid;

Les colonnes principales de pg_locks

1. locktype

Le type de ressource verrouillée. C'est la colonne la plus importante pour comprendre ce qui est bloqué.

Valeurs principales :

relation : Verrou sur une table, index, séquence, ou vue matérialisée
tuple : Verrou sur une ligne spécifique (rare, généralement lors d'un UPDATE/DELETE)
transactionid : Verrou sur un identifiant de transaction (très courant)
virtualxid : Verrou sur un identifiant de transaction virtuel
object : Verrou sur un objet de base de données (fonction, type personnalisé, etc.)
page : Verrou sur une page spécifique (rare)
advisory : Verrou applicatif personnalisé (Advisory Lock)

Exemple :

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

2. database

L'OID de la base de données où se trouve la ressource verrouillée. Peut être NULL pour les verrous globaux.

Traduire l'OID en nom de base :

SELECT
    l.locktype,
    d.datname AS database_name,
    l.mode,
    l.granted
FROM pg_locks l  
LEFT JOIN pg_database d ON l.database = d.oid  
ORDER BY d.datname;

3. relation

L'OID de la relation (table, index, séquence) verrouillée. NULL si le verrou n'est pas sur une relation.

Traduire l'OID en nom de table :

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

Note : La conversion ::regclass transforme automatiquement l'OID en nom lisible de table.

4. mode

Le mode du verrou demandé. C'est crucial pour comprendre le niveau de restriction.

Modes de verrous sur les tables (du moins au plus restrictif) :

AccessShareLock : Lecture simple (SELECT)
- Compatible avec presque tout sauf AccessExclusiveLock
RowShareLock : SELECT FOR UPDATE/SHARE
- Empêche les modifications de structure (ALTER, DROP)
RowExclusiveLock : INSERT, UPDATE, DELETE
- Empêche LOCK TABLE en mode SHARE ou plus restrictif
ShareUpdateExclusiveLock : VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY
- Empêche les modifications de schéma et autres VACUUM
ShareLock : CREATE INDEX (non concurrent)
- Empêche les écritures (INSERT, UPDATE, DELETE)
ShareRowExclusiveLock : Rare, combinaison de SHARE et ROW EXCLUSIVE
ExclusiveLock : REFRESH MATERIALIZED VIEW CONCURRENTLY
- Empêche tout sauf AccessShareLock
AccessExclusiveLock : ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, VACUUM FULL
- Bloque TOUT accès (lecture et écriture)

Matrice de compatibilité :

Mode demandé	Conflits avec
AccessShareLock	AccessExclusiveLock
RowShareLock	ExclusiveLock, AccessExclusiveLock
RowExclusiveLock	ShareLock, ShareRowExclusiveLock, ExclusiveLock, AccessExclusiveLock
ShareUpdateExclusiveLock	ShareUpdateExclusiveLock, ShareLock, ShareRowExclusiveLock, ExclusiveLock, AccessExclusiveLock
ShareLock	RowExclusiveLock, ShareUpdateExclusiveLock, ShareRowExclusiveLock, ExclusiveLock, AccessExclusiveLock
ShareRowExclusiveLock	Tout sauf AccessShareLock et RowShareLock
ExclusiveLock	Tout sauf AccessShareLock
AccessExclusiveLock	TOUT

Exemple - Voir les verrous par mode :

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

5. granted

Booléen indiquant si le verrou a été obtenu ou s'il est en attente.

TRUE : Le verrou est acquis, la transaction peut continuer
FALSE : Le verrou est en attente d'être accordé (blocage)

C'est la colonne clé pour détecter les problèmes de performance !

Voir les verrous en attente :

SELECT
    locktype,
    relation::regclass AS table_name,
    mode,
    pid,
    page,
    tuple
FROM pg_locks  
WHERE NOT granted  -- granted = FALSE  
ORDER BY pid;

Si cette requête retourne des lignes, vous avez des transactions bloquées !

6. pid

Le Process ID (PID) du backend PostgreSQL qui détient ou attend ce verrou.

Vous pouvez croiser avec pg_stat_activity pour voir quelle requête est associée :

SELECT
    l.pid,
    l.locktype,
    l.mode,
    l.granted,
    a.usename,
    a.query,
    a.state
FROM pg_locks l  
JOIN pg_stat_activity a ON l.pid = a.pid  
WHERE NOT l.granted  
ORDER BY l.pid;

7. transactionid et virtualxid

transactionid : L'identifiant de transaction réel (XID) verrouillé
virtualxid : L'identifiant de transaction virtuel (utilisé avant qu'un XID réel soit assigné)

Pourquoi c'est important ?

Chaque transaction qui modifie des données obtient un verrou exclusif sur son propre transactionid. Si une transaction A essaie de lire une ligne modifiée par une transaction B non encore committée, A devra attendre le verrou sur le transactionid de B.

Exemple :

SELECT
    locktype,
    transactionid,
    virtualxid,
    mode,
    granted,
    pid
FROM pg_locks  
WHERE locktype IN ('transactionid', 'virtualxid')  
ORDER BY pid;

8. page et tuple

Pour les verrous de type tuple (verrous sur des lignes), ces colonnes indiquent :

page : Le numéro de page (bloc) où se trouve la ligne
tuple : L'index de la ligne dans cette page

Exemple de verrou sur une ligne spécifique :

SELECT
    locktype,
    relation::regclass AS table_name,
    page,
    tuple,
    mode,
    granted,
    pid
FROM pg_locks  
WHERE locktype = 'tuple'  
ORDER BY relation, page, tuple;

9. classid, objid, objsubid

Pour les verrous de type object, ces colonnes identifient l'objet spécifique verrouillé (fonction, type, etc.).

Ces colonnes sont moins fréquemment utilisées dans le diagnostic quotidien.

Requêtes pratiques courantes

1. Voir tous les verrous actifs (vue simplifiée)

SELECT
    l.locktype,
    d.datname AS database,
    l.relation::regclass AS table_name,
    l.page,
    l.tuple,
    l.virtualxid,
    l.transactionid,
    l.mode,
    l.granted,
    a.usename AS user,
    a.query,
    a.state
FROM pg_locks l  
LEFT JOIN pg_database d ON l.database = d.oid  
LEFT JOIN pg_stat_activity a ON l.pid = a.pid  
ORDER BY l.granted, l.pid;

2. Identifier les transactions bloquées

SELECT
    l.pid AS blocked_pid,
    a.usename AS blocked_user,
    a.query AS blocked_query,
    l.locktype,
    l.mode,
    l.relation::regclass AS blocked_table
FROM pg_locks l  
JOIN pg_stat_activity a ON l.pid = a.pid  
WHERE NOT l.granted  
ORDER BY l.pid;

Si cette requête retourne des lignes, vous avez un problème de blocage actif !

3. Identifier qui bloque qui

C'est LA requête la plus importante pour diagnostiquer les blocages :

SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS blocking_statement,
    blocked_locks.mode AS blocked_mode,
    blocking_locks.mode AS blocking_mode
FROM pg_locks blocked_locks  
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid  
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype  
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid  
WHERE NOT blocked_locks.granted;

Note : Cette requête est complexe mais essentielle. Elle croise pg_locks avec lui-même et pg_stat_activity pour trouver les relations bloqueuse/bloquée.

4. Version simplifiée avec pg_blocking_pids (PostgreSQL 9.6+)

PostgreSQL fournit une fonction auxiliaire qui simplifie grandement le diagnostic :

SELECT
    a.pid AS blocked_pid,
    a.usename AS blocked_user,
    a.query AS blocked_query,
    a.wait_event_type,
    a.wait_event,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    blocking.state AS blocking_state
FROM pg_stat_activity a  
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(a.pid))  
WHERE a.wait_event_type = 'Lock'  
ORDER BY a.pid;

Beaucoup plus simple ! Cette requête utilise pg_blocking_pids(pid) qui retourne directement les PIDs bloquants.

5. Compter les verrous par type et mode

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

6. Voir les verrous sur une table spécifique

SELECT
    l.pid,
    l.mode,
    l.granted,
    a.usename,
    a.query,
    a.state
FROM pg_locks l  
JOIN pg_stat_activity a ON l.pid = a.pid  
WHERE l.relation = 'ma_table'::regclass  
ORDER BY l.granted DESC, l.pid;

Remplacer 'ma_table' par le nom de votre table.

7. Détecter les AccessExclusiveLock (dangereux en production)

Les AccessExclusiveLock bloquent TOUT accès (lecture et écriture) à une table. C'est typiquement causé par :

ALTER TABLE
DROP TABLE
TRUNCATE
VACUUM FULL
REINDEX

SELECT
    l.pid,
    l.relation::regclass AS table_name,
    l.mode,
    l.granted,
    a.usename,
    a.query,
    now() - a.query_start AS duree
FROM pg_locks l  
JOIN pg_stat_activity a ON l.pid = a.pid  
WHERE l.mode = 'AccessExclusiveLock'  
  AND l.granted
ORDER BY (now() - a.query_start) DESC;

En production, des AccessExclusiveLock de longue durée peuvent paralyser l'application !

8. Voir les verrous par base de données

SELECT
    d.datname AS database,
    l.locktype,
    count(*) AS nombre_verrous
FROM pg_locks l  
LEFT JOIN pg_database d ON l.database = d.oid  
GROUP BY d.datname, l.locktype  
ORDER BY count(*) DESC;

9. Trouver les transactions en attente avec durée

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

Si une transaction attend depuis plus de quelques secondes, c'est anormal.

10. Identifier les tables les plus contendues

SELECT
    relation::regclass AS table_name,
    count(*) AS nombre_verrous,
    count(*) FILTER (WHERE NOT granted) AS verrous_en_attente
FROM pg_locks  
WHERE relation IS NOT NULL  
GROUP BY relation  
ORDER BY count(*) DESC  
LIMIT 20;

Les tables avec beaucoup de verrous_en_attente ont des problèmes de contention.

Cas d'usage en production

Scénario 1 : "Mon application ne répond plus !"

Étape 1 : Vérifier s'il y a des blocages

SELECT count(*) AS transactions_bloquees  
FROM pg_locks  
WHERE NOT granted;

Si le résultat > 0, il y a des blocages.

Étape 2 : Identifier qui bloque qui

SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.query AS blocked_query,
    blocker.pid AS blocker_pid,
    blocker.usename AS blocker_user,
    blocker.query AS blocker_query,
    blocker.state AS blocker_state
FROM pg_stat_activity blocked  
JOIN LATERAL (  
    SELECT *
    FROM pg_stat_activity
    WHERE pid = ANY(pg_blocking_pids(blocked.pid))
) blocker ON true
WHERE blocked.wait_event_type = 'Lock';

Étape 3 : Décider de l'action

Options :

Attendre que la transaction bloquante se termine
Annuler la transaction bloquante avec pg_cancel_backend(pid)
Tuer la transaction bloquante avec pg_terminate_backend(pid) (plus brutal)

-- Annuler une requête (propre)
SELECT pg_cancel_backend(12345);  -- Remplacer par le PID blocker

-- Tuer une connexion (brutal)
SELECT pg_terminate_backend(12345);

Scénario 2 : "Deadlock détecté"

PostgreSQL détecte automatiquement les deadlocks et annule une des transactions. Vous verrez dans les logs :

ERROR:  deadlock detected  
DETAIL:  Process 12345 waits for ShareLock on transaction 67890; blocked by process 54321.  
Process 54321 waits for ShareLock on transaction 23456; blocked by process 12345.

Analyser un deadlock :

Consulter les logs PostgreSQL (souvent dans /var/log/postgresql/ ou via journald)
Identifier les tables/lignes impliquées
Revoir l'ordre des opérations dans le code applicatif

Prévention :

Toujours accéder aux tables dans le même ordre
Garder les transactions courtes
Utiliser des niveaux d'isolation appropriés

Scénario 3 : "ALTER TABLE bloque tout"

Un développeur lance un ALTER TABLE en production sans s'en rendre compte :

ALTER TABLE commandes ADD COLUMN statut VARCHAR(50);

Cette commande prend un AccessExclusiveLock qui bloque :

Toutes les lectures (SELECT)
Toutes les écritures (INSERT, UPDATE, DELETE)

Diagnostic :

SELECT
    l.pid,
    l.relation::regclass AS table_name,
    l.mode,
    a.query,
    now() - a.query_start AS duree
FROM pg_locks l  
JOIN pg_stat_activity a ON l.pid = a.pid  
WHERE l.mode = 'AccessExclusiveLock'  
  AND l.locktype = 'relation'
ORDER BY duree DESC;

Solution immédiate :

-- Annuler l'ALTER TABLE
SELECT pg_cancel_backend(pid);

Prévention :

Utiliser LOCK TABLE ... NOWAIT pour échouer immédiatement si la table est utilisée
Planifier les migrations en fenêtre de maintenance
Utiliser des outils comme pg_repack ou migrations "zero-downtime"

Scénario 4 : "Transactions idle in transaction bloquent tout"

Une application mal codée ouvre une transaction, lit une ligne, puis reste en attente de l'utilisateur sans committer :

BEGIN;  
SELECT * FROM produits WHERE id = 123 FOR UPDATE;  
-- L'application attend maintenant l'input utilisateur pendant 10 minutes...

Pendant ce temps, la ligne est verrouillée !

Diagnostic :

SELECT
    a.pid,
    a.state,
    a.query,
    now() - a.xact_start AS duree_transaction,
    l.locktype,
    l.mode,
    l.relation::regclass AS table_name
FROM pg_stat_activity a  
JOIN pg_locks l ON a.pid = l.pid  
WHERE a.state = 'idle in transaction'  
  AND (now() - a.xact_start) > interval '5 minutes'
  AND l.granted
ORDER BY duree_transaction DESC;

Solution :

-- Tuer la transaction zombie
SELECT pg_terminate_backend(pid);

Prévention :

Configurer idle_in_transaction_session_timeout
Revoir le code applicatif pour fermer les transactions rapidement
Utiliser des connection poolers avec timeout

Scénario 5 : "Contention sur une séquence"

Les séquences (utilisées pour SERIAL ou IDENTITY) peuvent devenir un point de contention si beaucoup d'INSERT concurrents ont lieu.

Diagnostic :

SELECT
    l.relation::regclass AS sequence_name,
    count(*) AS nombre_verrous_actifs,
    count(*) FILTER (WHERE NOT l.granted) AS verrous_en_attente
FROM pg_locks l  
JOIN pg_class c ON l.relation = c.oid  
WHERE c.relkind = 'S'  -- S = Sequence  
GROUP BY l.relation  
ORDER BY count(*) DESC;

Solution :

Augmenter le CACHE de la séquence
Utiliser UUID au lieu de SERIAL
Partitionner la table si possible

Advisory Locks : Verrous applicatifs

PostgreSQL permet de créer des verrous personnalisés au niveau applicatif avec les Advisory Locks.

Utilisation des Advisory Locks

Obtenir un verrou advisory :

-- Verrou exclusif sur l'entier 12345
SELECT pg_advisory_lock(12345);

-- Verrou partagé
SELECT pg_advisory_lock_shared(12345);

Libérer le verrou :

SELECT pg_advisory_unlock(12345);

Essayer d'obtenir sans attendre (non-bloquant) :

SELECT pg_try_advisory_lock(12345);  -- Retourne TRUE si acquis, FALSE sinon

Voir les advisory locks actifs :

SELECT
    l.pid,
    l.objid,
    l.classid,
    l.mode,
    l.granted,
    a.query
FROM pg_locks l  
JOIN pg_stat_activity a ON l.pid = a.pid  
WHERE l.locktype = 'advisory'  
ORDER BY l.pid;

Cas d'usage :

Éviter que deux workers traitent le même job
Implémenter des mutex/sémaphores au niveau base
Coordination distribuée

Exemple : Job queue avec advisory lock

-- Worker essaie d'obtenir le lock sur un job
SELECT pg_try_advisory_lock(job_id) FROM jobs WHERE status = 'pending' LIMIT 1;

-- Si TRUE, le worker traite le job
-- Si FALSE, un autre worker l'a déjà pris

Monitoring et alertes

Alertes critiques à configurer

Transactions bloquées depuis > 30 secondes

SELECT count(*)  
FROM pg_locks l  
JOIN pg_stat_activity a ON l.pid = a.pid  
WHERE NOT l.granted  
  AND (now() - a.query_start) > interval '30 seconds';

AccessExclusiveLock actifs

SELECT count(*)  
FROM pg_locks  
WHERE mode = 'AccessExclusiveLock'  
  AND locktype = 'relation'
  AND granted;

Transactions idle in transaction avec verrous

SELECT count(*)  
FROM pg_stat_activity a  
JOIN pg_locks l ON a.pid = l.pid  
WHERE a.state LIKE 'idle in transaction%'  
  AND (now() - a.xact_start) > interval '5 minutes'
  AND l.granted;

Dashboard de monitoring

-- Vue synthétique des verrous
CREATE OR REPLACE VIEW v_lock_monitoring AS  
SELECT  
    CASE
        WHEN NOT l.granted THEN '🔴 Bloqué'
        WHEN l.mode = 'AccessExclusiveLock' THEN '🟠 Access Exclusive'
        WHEN a.state = 'idle in transaction' AND (now() - a.xact_start) > interval '1 minute' THEN '🟡 Idle in TX'
        ELSE '🟢 Normal'
    END AS statut,
    l.pid,
    a.usename,
    l.locktype,
    l.relation::regclass AS table_name,
    l.mode,
    l.granted,
    a.state,
    a.query,
    now() - a.xact_start AS duree_transaction,
    ARRAY(SELECT pg_blocking_pids(l.pid)) AS blocked_by
FROM pg_locks l  
JOIN pg_stat_activity a ON l.pid = a.pid  
WHERE l.locktype IN ('relation', 'transactionid', 'tuple')  
ORDER BY  
    CASE statut
        WHEN '🔴 Bloqué' THEN 1
        WHEN '🟠 Access Exclusive' THEN 2
        WHEN '🟡 Idle in TX' THEN 3
        ELSE 4
    END,
    duree_transaction DESC;

-- Utilisation
SELECT * FROM v_lock_monitoring WHERE statut != '🟢 Normal';

Outils de diagnostic avancés

1. pg_blocking_pids() - PostgreSQL 9.6+

-- Trouver qui bloque un PID spécifique
SELECT pg_blocking_pids(12345);  -- Retourne un array de PIDs

-- Utilisation dans une requête
SELECT
    pid,
    query,
    pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity  
WHERE cardinality(pg_blocking_pids(pid)) > 0;

2. pg_lock_status - Extension tierce

L'extension pg_lock_status fournit des vues simplifiées :

-- Installation
CREATE EXTENSION pg_lock_status;

-- Voir les blocages
SELECT * FROM pg_lock_status();

3. Logs PostgreSQL

Configurez PostgreSQL pour logger les verrous longs :

# postgresql.conf
log_lock_waits = on  
deadlock_timeout = 1s  # Log après 1 seconde d'attente

Après un redémarrage ou reload, les attentes de verrous apparaîtront dans les logs.

Bonnes pratiques

Gardez les transactions courtes
- Ne jamais attendre l'input utilisateur dans une transaction
- Commit ou Rollback le plus tôt possible

Utilisez NOWAIT quand approprié

SELECT * FROM produits WHERE id = 123 FOR UPDATE NOWAIT;
-- Échoue immédiatement si la ligne est verrouillée

Configurez des timeouts

SET lock_timeout = '5s';  -- Échoue après 5 secondes d'attente
SET idle_in_transaction_session_timeout = '10min';

Évitez les deadlocks
- Accédez toujours aux tables dans le même ordre
- Utilisez des niveaux d'isolation appropriés
- Gardez les transactions simples
Surveillez en continu
- Alertes sur blocages > 30 secondes
- Dashboards avec pg_locks
- Analyse régulière des logs
Évitez les DDL en production
- Planifiez les ALTER TABLE en fenêtre de maintenance
- Utilisez des outils comme pg_repack pour les réorgs
- Testez les migrations sur un clone de prod

Utilisez FOR UPDATE judicieusement

-- Limite le scope du verrouillage
SELECT * FROM commandes WHERE id = 123 FOR UPDATE;
-- Mieux que de verrouiller toute la table

Exploitez les Advisory Locks pour la coordination
- Plutôt que de faire du polling applicatif
- Pour les jobs queues distribuées
- Pour les tâches périodiques exclusives

Différences entre types de verrous

Table-level locks vs Row-level locks

Table-level (relation locks) :

Posés par les DDL (ALTER, DROP, TRUNCATE)
Impactent toute la table
Visibles avec locktype = 'relation'

Row-level (tuple locks) :

Posés par UPDATE, DELETE, SELECT FOR UPDATE
N'impactent que les lignes concernées
Visibles avec locktype = 'tuple' (rare dans pg_locks)
Principalement gérés via MVCC (versions de lignes)

Transaction locks

Les verrous sur transactionid sont fondamentaux à MVCC :

Chaque transaction modifiant des données obtient un XID
Les autres transactions doivent attendre si elles veulent lire des lignes non commitées

Limitations et considérations

1. pg_locks montre l'état instantané

pg_locks est une vue dynamique. Entre deux lectures, les verrous peuvent changer. Pour un diagnostic fiable, combinez avec pg_stat_activity.

2. Performance

Sur des systèmes très chargés (milliers de connexions), pg_locks peut devenir lent à interroger. Utilisez des filtres (WHERE NOT granted) pour limiter les résultats.

3. Verrous non visibles

Certains verrous légers (lightweight locks, spinlocks) utilisés en interne ne sont pas visibles dans pg_locks.

4. Interprétation complexe

La matrice de compatibilité des modes de verrous est complexe. En cas de doute, référez-vous à la documentation officielle.

Résumé des colonnes clés

Colonne	Description	Usage principal
locktype	Type de ressource (relation, tuple, transactionid)	Identifier ce qui est verrouillé
mode	Mode du verrou (AccessShareLock, RowExclusiveLock, etc.)	Comprendre le niveau de restriction
granted	Verrou obtenu ou en attente	Détecter les blocages
pid	Process ID	Croiser avec pg_stat_activity
relation	OID de la table/index	Identifier la table bloquée
transactionid	ID de transaction	Blocages liés à MVCC

Conclusion

pg_locks est l'outil indispensable pour :

✅ Diagnostiquer les blocages : Qui attend qui ?
✅ Identifier les goulots d'étranglement : Contention sur tables/séquences
✅ Détecter les transactions problématiques : Idle in transaction avec verrous
✅ Comprendre les deadlocks : Analyser les conflits circulaires
✅ Optimiser la concurrence : Réduire les contentions

Points critiques à surveiller :

Verrous en attente (granted = FALSE)
AccessExclusiveLock sur tables fréquemment utilisées
Transactions longues avec verrous
Deadlocks récurrents

Requête essentielle à retenir :

-- Qui bloque qui ?
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocker.pid AS blocker_pid,
    blocker.query AS blocker_query
FROM pg_stat_activity blocked  
JOIN LATERAL (  
    SELECT *
    FROM pg_stat_activity
    WHERE pid = ANY(pg_blocking_pids(blocked.pid))
) blocker ON true
WHERE blocked.wait_event_type = 'Lock';

Combinez pg_locks avec pg_stat_activity pour une vision complète de ce qui se passe dans votre base de données.

Prochaines étapes : Apprenez à optimiser les niveaux d'isolation des transactions, et explorez les stratégies de réduction des contentions (partitionnement, réplication en lecture, pooling).

⏭️ pg_catalog : Métadonnées système

FilesExpand file tree

02.4-pg-locks.md

Latest commit

History

02.4-pg-locks.md

File metadata and controls

14.2.4. pg_locks : Verrous actifs

Introduction

Pourquoi les verrous existent-ils ?

Comprendre les concepts de base

Qu'est-ce qu'un verrou (lock) ?

Types de ressources verrouillables

Conflits de verrous

Accéder à pg_locks

Les colonnes principales de pg_locks

1. locktype

2. database

3. relation

4. mode

5. granted

6. pid

7. transactionid et virtualxid

8. page et tuple

9. classid, objid, objsubid

Requêtes pratiques courantes

1. Voir tous les verrous actifs (vue simplifiée)

2. Identifier les transactions bloquées

3. Identifier qui bloque qui

4. Version simplifiée avec pg_blocking_pids (PostgreSQL 9.6+)

5. Compter les verrous par type et mode

6. Voir les verrous sur une table spécifique

7. Détecter les AccessExclusiveLock (dangereux en production)

8. Voir les verrous par base de données

9. Trouver les transactions en attente avec durée

10. Identifier les tables les plus contendues

Cas d'usage en production

Scénario 1 : "Mon application ne répond plus !"

Scénario 2 : "Deadlock détecté"

Scénario 3 : "ALTER TABLE bloque tout"

Scénario 4 : "Transactions idle in transaction bloquent tout"

Scénario 5 : "Contention sur une séquence"

Advisory Locks : Verrous applicatifs

Utilisation des Advisory Locks

Monitoring et alertes

Alertes critiques à configurer

Dashboard de monitoring

Outils de diagnostic avancés

1. pg_blocking_pids() - PostgreSQL 9.6+

2. pg_lock_status - Extension tierce

3. Logs PostgreSQL

Bonnes pratiques

Différences entre types de verrous

Table-level locks vs Row-level locks

Transaction locks

Limitations et considérations

1. pg_locks montre l'état instantané

2. Performance

3. Verrous non visibles

4. Interprétation complexe

Résumé des colonnes clés

Conclusion