Моніторинг блокувань і конфліктів

Уяви, що ти працюєш в офісі, де всі двері заблоковані через те, що хтось забув ключі в кімнаті. Ось так само працюють блокування в 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.