ブロッキングとロックの危機

症状

クエリがハングする、'lock wait timeout'、多数のセッションが待機しているが CPU は低い。

1. ブロッキングチェーンの先頭 (根本ブロッカー) を見つける

誰が誰をブロックしているかを把握する。真の原因は、自身は何も待たずに他を待たせているセッションである。チェーンの最上位のセッションに集中する。

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. 根本ブロッカーが何をしているかを把握する

開いたまま未コミットのトランザクションか。アプリケーションが '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. 判断: 待つか、アプリケーション側で解決するか、強制終了するか

もうすぐ終わるなら待つ。アプリケーションがコミット/ロールバックできるなら、それを実行させる。そうでなく業務が停止しているなら、根本ブロッカーを終了する。強制終了の前に影響 (ロールバック時間、データ) を考慮する。

4. 必要に応じて根本ブロッカーを終了する

チェーンの先頭のセッションのみを強制終了する。その下のセッションは自動的に解放される。

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' のタイムアウトを設定し、適切な分離レベルを選ぶ。