Кризис блокировок

Симптом

Запросы зависают, 'lock wait timeout', много сессий ожидают, но загрузка CPU низкая.

1. Найдите вершину цепочки блокировок (root blocker)

Выясните, кто кого блокирует; настоящий виновник — сессия, которая сама ничего не ждёт, но заставляет ждать других. Сосредоточьтесь на сессии на вершине цепочки.

Oracle

SELECT blocking_session, sid, serial#, username, seconds_in_wait, sql_id FROM v$session WHERE blocking_session IS NOT NULL ORDER BY seconds_in_wait DESC;

PostgreSQL

SELECT pid, usename, pg_blocking_pids(pid) blocked_by, state, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid))>0;

MySQL

SELECT waiting_pid, waiting_query, blocking_pid, blocking_query FROM sys.innodb_lock_waits;

SQL Server

SELECT session_id, blocking_session_id, wait_type, wait_resource FROM sys.dm_exec_requests WHERE blocking_session_id<>0;

MongoDB

db.currentOp({ waitingForLock: true })

2. Выясните, что делает корневая блокирующая сессия (root blocker)

Это открытая, но не зафиксированная транзакция? Приложение оставило её в состоянии 'idle in transaction'? Это длинный пакетный процесс? Разберитесь, прежде чем завершать вслепую — возможно, она вот-вот закончится.

Oracle

SELECT s.sid, s.prev_sql_id, t.start_time, t.used_ublk FROM v$transaction t JOIN v$session s ON t.ses_addr=s.saddr;

PostgreSQL

SELECT pid, state, xact_start, now()-xact_start age, query FROM pg_stat_activity WHERE pid=<root_pid>;

3. Решите: ждать, решить через приложение или завершить

Если она вот-вот завершится — подождите. Если приложение может выполнить commit/rollback — инициируйте это. Иначе, если работа бизнеса стоит, завершите корневую блокирующую сессию. Перед завершением оцените последствия (время отката, данные).

4. При необходимости завершите корневую блокирующую сессию (root blocker)

Завершайте только сессию в голове цепочки; сессии под ней освобождаются автоматически.

Oracle

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

PostgreSQL

SELECT pg_terminate_backend(<root_pid>);

MySQL

KILL <root_id>;

SQL Server

KILL <root_session_id>;

MongoDB

db.killOp(<opid>)

5. Устраните первопричину

Отсутствующий индекс может вызывать широкие блокировки; приложение может слишком долго держать транзакции открытыми; уровень изоляции может быть неподходящим. Добавьте индексы, сократите транзакции, задайте тайм-аут 'idle in transaction' и выберите подходящий уровень изоляции.