Crise de bloqueios e locks

Sintoma

Consultas travam, 'lock wait timeout', muitas sessões esperando, mas a CPU está baixa.

1. Encontre o início da cadeia de bloqueio (root blocker)

Descubra quem bloqueia quem; o verdadeiro culpado é a sessão que não espera por nada, mas faz as outras esperarem. Concentre-se na sessão no topo da cadeia.

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. Entenda o que o root blocker está fazendo

É uma transação aberta sem commit? A aplicação a deixou 'idle in transaction'? É um batch longo? Entenda o que é antes de matá-la às cegas — ela pode estar quase terminando.

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. Decida: esperar, resolver pela aplicação ou matar

Se estiver quase terminando, espere. Se a aplicação puder fazer commit/rollback, acione isso. Caso contrário, se o negócio estiver parado, encerre o root blocker. Antes de matar, considere o impacto (tempo de rollback, dados).

4. Encerre o root blocker se necessário

Mate apenas a sessão no início da cadeia; as sessões abaixo dela são liberadas automaticamente.

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. Corrija a causa raiz

Um índice ausente pode causar locks amplos; a aplicação pode manter transações abertas por tempo demais; o nível de isolamento pode estar errado. Crie índices, encurte as transações, defina um timeout para 'idle in transaction' e escolha um nível de isolamento adequado.