Examiner en détail les attentes de verrous par objet et mode de verrou (pg_locks)

Commande

SELECT DISTINCT
       w.pid AS waiting_pid,
       w.locktype,
       coalesce(w.relation::regclass::text, 'xid ' || w.transactionid, w.virtualxid, w.objid::text) AS target,
       w.mode AS wanted_mode,
       b.pid AS blocker_pid,
       b.mode AS blocker_mode,
       b.granted AS blocker_holds,
       now() - wa.state_change AS waiting_for,
       left(wa.query, 60) AS waiting_query,
       left(ba.query, 60) AS blocker_query
FROM pg_locks w
JOIN pg_locks b
  ON b.pid = ANY (pg_blocking_pids(w.pid))
 AND b.locktype = w.locktype
 AND b.database IS NOT DISTINCT FROM w.database
 AND b.relation IS NOT DISTINCT FROM w.relation
 AND b.page IS NOT DISTINCT FROM w.page
 AND b.tuple IS NOT DISTINCT FROM w.tuple
 AND b.virtualxid IS NOT DISTINCT FROM w.virtualxid
 AND b.transactionid IS NOT DISTINCT FROM w.transactionid
 AND b.classid IS NOT DISTINCT FROM w.classid
 AND b.objid IS NOT DISTINCT FROM w.objid
 AND b.objsubid IS NOT DISTINCT FROM w.objsubid
JOIN pg_stat_activity wa ON wa.pid = w.pid
JOIN pg_stat_activity ba ON ba.pid = b.pid
WHERE NOT w.granted
ORDER BY waiting_for DESC, waiting_pid;

Ce que fait la commande

Pour chaque demande de verrou non accordée (granted = false), affiche l'objet cible (table, identifiant de transaction, etc.), le mode demandé, le PID bloquant et son mode, si le bloqueur détient réellement le verrou, et le temps d'attente approximatif.

Quand l'utiliser

Pour comprendre quel conflit de table et de mode de verrou provoque le blocage ; par ex. un DDL attendant AccessExclusiveLock, ou un verrou de ligne apparaissant comme une attente transactionid.

Attention

Comme la correspondance utilise pg_blocking_pids(), seules les demandes réellement en conflit sont listées. blocker_holds = false signifie que le bloqueur ne détient pas encore le verrou mais est placé avant dans la file avec un mode en conflit (lock queue). locktype = 'transactionid' est un conflit au niveau ligne : la session attend la fin de la transaction qui a modifié la ligne ; la ligne elle-même n'est pas visible dans pg_locks. pg_locks peut contenir des milliers de lignes sur les gros systèmes ; ne l'exécutez pas fréquemment. waiting_for est une approximation basée sur state_change.