Как обнаружить запрос, который содержит блокировку в Postgres?

Я хочу постоянно отслеживать взаимные блокировки в postgres.

Я наткнулся на Заблокировать мониторинг" и попытался выполнить следующий запрос:

SELECT bl.pid     AS blocked_pid,
     a.usename  AS blocked_user,
     kl.pid     AS blocking_pid,
     ka.usename AS blocking_user,
     a.query    AS blocked_statement
FROM  pg_catalog.pg_locks         bl
 JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
 JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
 JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;

К сожалению, он никогда не возвращает непустой набор результатов. Если я упростил данный запрос в следующем виде:

SELECT bl.pid     AS blocked_pid,
     a.usename  AS blocked_user,
     a.query    AS blocked_statement
FROM  pg_catalog.pg_locks         bl
 JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
WHERE NOT bl.granted;

то он возвращает запросы, ожидающие получения блокировки. Но я не могу изменить его, чтобы он мог возвращать как заблокированные, так и блокирующие запросы.

Любые идеи?

Ответ 1

С 9.6 это намного проще, поскольку он ввел функцию pg_blocking_pids(), чтобы найти сеансы, которые блокируют другой сеанс.

Итак, вы можете использовать что-то вроде этого:

select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

Ответ 2

Из этой превосходной статьи о блокировках запросов в Postgres можно получить заблокированный запрос и запрос блокировщика и их информацию из следующего запроса.

CREATE VIEW lock_monitor AS(
SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
  blockeda.query as blocked_query, blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
  (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
  AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database()
);

SELECT * from lock_monitor;

Поскольку запрос длинный, но полезный, автор статьи создал для него представление, чтобы упростить его использование.

Ответ 3

Одна вещь, которую я нахожу, часто отсутствует в них, - это способность искать блокировки строк. По крайней мере, в больших базах данных, над которыми я работал, строковые блокировки не отображаются в pg_locks (если они были, pg_locks были бы намного, намного больше и не было бы реального типа данных, чтобы показывать заблокированную строку в этом представлении должным образом).

Я не знаю, что есть простое решение для этого, но обычно я делаю это, смотрю на таблицу, где блокировка ждет и ищет строки, где xmax меньше, чем идентификатор транзакции. Это обычно дает мне возможность начать, но это немного практический и не автоматизированный подход.

Обратите внимание, что вы видите незафиксированные записи в строках этих таблиц. После фиксации строки не отображаются в текущем снимке. Но для больших таблиц это боль.

Ответ 4

Эта модификация ответа a_horse_with_no_name даст вам блокирующие запросы в дополнение только к заблокированным сеансам:

SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));