Отслеживание заблокированных и блокирующих запросов в Postgres

Быстрый ответ

Для выявления проблем, связанных с блокировками в PostgreSQL, достаточно выполнить следующий запрос:

SELECT blocker.pid AS blocker_pid,
       blocker.query AS blocker_query,
       blocked.pid AS blocked_pid,
       blocked.query AS blocked_query
FROM pg_locks blocked
JOIN pg_stat_activity blocked ON blocked.pid = blocked.pid
JOIN pg_locks blocker ON blocked.transactionid = blocker.transactionid AND blocker.granted
WHERE NOT blocked.granted;

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

Подробный анализ обнаружения блокировок

Для более углубленного исследования причин постоянных и сложных блокировок стоит более пристально изучить доступную информацию.

Выявление виновников

Чтобы найти причину блокировок, используйте функцию pg_blocking_pids(), которую предоставляет PostgreSQL:

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;

Этот запрос поможет идентифицировать процессы, ожидающие разблокировки, и определить тех, кто удерживает ресурсы.

Ваш помощник в регулярных проверках

Для облегчения постоянного мониторинга блокировок создайте представление:

CREATE VIEW lock_monitor AS
SELECT ...

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

Обратите внимание!

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

Визуализация

Представьте, что база данных — это парковка, заполненная автомобилями:

| Место | Статус       | ID запроса |
| ----- | ------------ | ---------- |
| 1     | Свободно     |            |
| 2     | Заблокировано | 🚗 Q456     |
| 3     | Свободно     |            |

Место №2 занято запросом Q456. Как мы определили это?

SELECT * FROM pg_locks WHERE NOT granted;  -- Ваш личный посредник

Теперь стоит более подробно разобраться с обстоятельствами, связанными с запросом 🚗 Q456.

Выясняем продолжительность блокировок и многое другое

Чтобы понять суть операций Q456, его продолжительность и тип блокировки, необходимо взять информацию из pg_catalog:

SELECT *, age(current_timestamp, query_start) AS duration -- Когда прибыл Q456?
FROM ...
JOIN pg_catalog.pg_locks ON ...

Имея эти данные, вы будете готовы управлять процессом и предотвращать блокировки в будущем.

Углубленное исследование блокировок

Чтобы стать настоящим специалистом по расследованию блокировок, используйте функцию unnest() с pg_blocking_pids(), чтобы получить детальное представление о причинах возникновения блокировок:

SELECT unnest(pg_blocking_pids(pid)) AS blocking_pid -- Раскрываем всех виновников
...

Полезные материалы

1. PostgreSQL: Документация: 16: 54.12. pg_locks — Официальное руководство по работе с механизмами блокировок.
2. Insert, on duplicate update in PostgreSQL? – Stack Overflow — Ответы на вопросы о блокировках.
3. Мониторинг блокировок – Вики PostgreSQL — Руководство по мониторингу блокировок в стиле Шерлока Холмса.
4. PostgreSQL Concurrency with MVCC | Heroku Dev Center — Основы работы с механизмами блокировок.
5. Стоп война в Украине — Глубокий анализ блокировок.
6. Postgres Professional — Инструкции по прерыванию операций, вызывающих блокировки.