postgres-list-locks Tool

kind: tools
name: list_locks
type: postgres-list-locks
source: postgres-source
description: "Lists active locks with associated process and query information."

SELECT
    locked.pid,
    locked.usename,
    locked.query,
    string_agg(locked.transactionid::text,':') as trxid,
    string_agg(locked.lockinfo,'||') as locks
FROM
    (SELECT
      a.pid,
      a.usename,
      a.query,
      l.transactionid,
      (l.granted::text||','||coalesce(l.relation::regclass,0)::text||','||l.mode::text)::text as lockinfo
    FROM
      pg_stat_activity a
      JOIN pg_locks l ON l.pid = a.pid  AND a.pid != pg_backend_pid()) as locked
GROUP BY 
    locked.pid, locked.usename, locked.query;

{
  "pid": 23456,
  "usename": "dbuser",
  "query": "INSERT INTO orders (...) VALUES (...);",
  "trxid": "12345:0",
  "locks": "true,public.orders,RowExclusiveLock||false,0,ShareUpdateExclusiveLock"
}