Мониторинг блокировок и конфликтов

Представьте, что вы работаете в офисе, где все двери блокируются из-за того, что один человек забыл ключи внутри комнаты. Вот так же работают блокировки в PostgreSQL. Если запрос или транзакция заблокировали ресурс, то другие операции, пытающиеся получить доступ к этому же ресурсу, зависят от их завершения. Такая ситуация может вызвать задержки, конфликтные сценарии и в крайнем случае привести к остановке системы.

Когда возникают блокировки?

Блокировки (Locks) в PostgreSQL используются для управления конкурентным доступом к данным. Они возникают:

1. При выполнении операций записи: UPDATE, DELETE, INSERT.
2. При использовании транзакций, которые удерживают ресурс дольше, чем это необходимо.
3. При конфликтах между разными транзакциями, претендующими на один и тот же ресурс.

Реальная база данных — это "поле боя" за ресурсы, и даже если вы думаете, что ваша система работает идеально, одна неосторожная транзакция может всё "запрячь", как неудачный merge в Git.

Инструменты анализа блокировок: pg_locks

pg_locks — это системное представление PostgreSQL, показывающее текущие блокировки, удерживаемые и ожидаемые транзакциями. Оно отвечает на вопрос: "Кто удерживает блокировку и кто ждёт?"

Основные поля pg_locks:

• locktype: тип блокировки (например, relation, transaction, page, tuple).
• database: идентификатор базы данных.
• relation: идентификатор таблицы (если блокировка связана с таблицей).
• mode: режим блокировки (например, RowExclusiveLock, AccessShareLock).
• granted: флаг, показывающий, предоставлена ли блокировка (true) или транзакция всё ещё ждёт её (false).

Примечание: PostgreSQL применяет так называемый "иерархический режим блокировок". Это означает, что разные операции могут накладывать менее строгие блокировки (например, AccessShareLock для чтения данных) или более строгие (ExclusiveLock для модификации структуры таблицы).

Пример: просмотр всех текущих блокировок

SELECT *
FROM pg_locks;

Но если просто вывести всё содержимое pg_locks, будет слишком много шума. Давайте попробуем что-то более осмысленное!

Пример: блокировки, которые ещё не предоставлены (то есть транзакции ждут)

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

Что здесь происходит?

• Мы фильтруем записи, у которых granted = false, т.е. блокировка ещё не предоставлена.
• relation::regclass преобразует идентификатор таблицы в её имя для удобочитаемости.

Вывод может выглядеть примерно так:

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

Анализ конфликтов: pg_blocking_pids()

Блокировки — это ещё полбеды, но что делать, если одна транзакция блокирует другую? PostgreSQL предлагает удобный способ определения "виновника" с помощью функции pg_blocking_pids().

Функция pg_blocking_pids() возвращает список идентификаторов процессов (pid), которые блокируют выполнение текущей транзакции.

Пример: поиск транзакций, которые блокируют другие

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Что здесь происходит?

• Мы используем представление pg_stat_activity, чтобы извлечь активные процессы в системе.
• Функция pg_blocking_pids(pid) возвращает список блокирующих процессов для каждого pid. Если список не пустой (длина больше 0), значит, процесс заблокирован.

Пример результата:

Транзакция с pid = 4567 заблокирована процессами 1234 и 5678. Мы нашли наших "виновников".

Завершение блокирующих процессов

Когда вы идентифицировали блокирующие процессы, вы можете их остановить с помощью функции pg_terminate_backend():

SELECT pg_terminate_backend(1234); -- "Убиваем" процесс 1234

Но будьте осторожны! Принудительная остановка процесса может привести к откату данных в текущей транзакции. Используйте этот инструмент как "ядерную кнопку" только в крайнем случае.

Практическое применение: сценарий анализа блокировок

Представим, что у нас есть база данных для университета с таблицами students и enrollments. Несколько транзакций пытаются одновременно записывать данные в таблицу enrollments, и мы сталкиваемся с блокировками.

1. Идентификация блокировок:

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

2. Определение блокирующих процессов:

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

3. Устранение блокировок:

Принудительно завершаем один из конфликтующих процессов:

SELECT pg_terminate_backend(1234); -- Завершаем процесс 1234

Примечание: Прежде чем "убивать" процесс, попробуйте разобраться, почему возникла блокировка. Возможно, стоит пересмотреть логику транзакций.

Типичные ошибки и их предотвращение

Блокировки часто возникают из-за неправильного управления транзакциями. Например:

Ошибка: одна транзакция удерживает блокировку слишком долго, не выполняя никаких операций (состояние "idle in transaction").

Решение: внимательно следите за состоянием транзакций с помощью pg_stat_activity и завершайте "зависшие" транзакции.

SELECT pid, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Ошибка: забыли использовать индексы в запросах, что привело к блокировкам на уровне целых таблиц.

Решение: оптимизируйте запросы, добавляя индексы для часто используемых условий.

Вывод таблицы "кто кого ждет"

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

WITH RECURSIVE blocking_tree AS (
  SELECT pid, pg_blocking_pids(pid) AS blocked_by
  FROM pg_stat_activity
  WHERE cardinality(pg_blocking_pids(pid)) > 0
  UNION ALL
  SELECT a.pid, pg_blocking_pids(a.pid)
  FROM pg_stat_activity a
  JOIN blocking_tree b ON a.pid = ANY(b.blocked_by)
)
SELECT pid, blocked_by FROM blocking_tree;

Результат:

Здесь видно, что процесс 5678 блокирует процесс 1234, а тот, в свою очередь, блокирует 4567.