Blocking & lock crisis

Symptom

Queries hang, 'lock wait timeout', many sessions waiting but CPU is low.

1. Find the head of the blocking chain (root blocker)

Work out who is blocking whom; the real culprit is the session that waits on nothing itself but makes others wait. Focus on the session at the top of the chain.

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. Understand what the root blocker is doing

Is it an open but uncommitted transaction? Did the application leave it 'idle in transaction'? Is it a long batch? Understand what it is before killing it blindly — it may be about to finish.

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. Decide: wait, resolve from the application, or kill

If it is about to finish, wait. If the application can commit/roll back, trigger that. Otherwise, if business is stalled, terminate the root blocker. Before killing, consider the impact (rollback time, data).

4. Terminate the root blocker if necessary

Kill only the session at the head of the chain; the sessions below it are released automatically.

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. Fix the root cause

A missing index may cause wide locks; the application may hold transactions open too long; the isolation level may be wrong. Add indexes, shorten transactions, set an 'idle in transaction' timeout and choose an appropriate isolation level.