阻塞与锁危机

症状

查询挂起，出现 '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. 决定：等待、由应用解决，还是终止

若即将完成，就等待。若应用可以提交/回滚，就触发它。否则，若业务已停滞，就终止 root blocker。终止之前要考虑影响（回滚时间、数据）。

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' 超时，并选择合适的隔离级别。