Crisis de bloqueos

Síntoma

Las consultas se cuelgan, 'lock wait timeout', muchas sesiones en espera pero la CPU está baja.

1. Encuentre la cabeza de la cadena de bloqueo (root blocker)

Determine quién bloquea a quién; el verdadero culpable es la sesión que no espera nada pero hace esperar a las demás. Concéntrese en la sesión que está en la cima de la cadena.

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. Entienda qué está haciendo el root blocker

¿Es una transacción abierta pero sin commit? ¿La aplicación la dejó 'idle in transaction'? ¿Es un batch largo? Entienda qué es antes de terminarla a ciegas: puede estar a punto de finalizar.

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 desde la aplicación o terminar

Si está a punto de finalizar, espere. Si la aplicación puede hacer commit/rollback, provóquelo. De lo contrario, si el negocio está detenido, termine el root blocker. Antes de terminarlo, considere el impacto (tiempo de rollback, datos).

4. Termine el root blocker si es necesario

Termine solo la sesión en la cabeza de la cadena; las sesiones debajo de ella se liberan automáticamente.

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 la causa raíz

Un índice faltante puede provocar bloqueos amplios; la aplicación puede mantener transacciones abiertas demasiado tiempo; el nivel de aislamiento puede ser incorrecto. Agregue índices, acorte las transacciones, configure un timeout para 'idle in transaction' y elija un nivel de aislamiento adecuado.