記事の一覧

ロック待ちの調べ方、MySQL・PostgreSQLで待たせている相手を探す

Lock wait timeout exceeded や終わらない UPDATE の原因を、sys.innodb_lock_waits や pg_blocking_pids で突き止め、止めるか待つかを判断する手順を解説します。

更新の処理がいつまでも終わらない、Lock wait timeout exceeded; try restarting transaction が出る、ALTER TABLE を流したらサービス全体が止まった。こうした症状の多くは、ロック待ちが原因です。この記事では MySQL (InnoDB) と PostgreSQL で、待たされている処理と待たせている相手を見つけ、どう手を打つかを順に説明します。

ロック待ちとは

データベースは、同じ行を 2 つのトランザクションが同時に書き換えないように、行やテーブルにロックをかけます。ロックがかかっているところを別のトランザクションが書き換えようとすると、先のトランザクションがコミットかロールバックで終わるまで待たされます。これがロック待ちです。

ロック待ちそのものはデータの整合性を守るための普通の動きです。問題になるのは、ロックを持ったトランザクションがなかなか終わらないときです。長いバッチ、コミットを忘れたまま放置された接続、トランザクションの中での外部 API の呼び出しなどが、典型的な原因です。待っている処理はさらに後ろの処理を待たせるので、1 つの長いトランザクションが多くの処理を止めることがあります。

似た言葉にデッドロックがあります。デッドロックはお互いに待ち合って輪になった状態で、データベースがすぐに検知して一方を取り消します。一方的に待っているだけのロック待ちは、相手が終わるかタイムアウトになるまで続きます。デッドロックの調べ方は データベースのデッドロックとは で説明しています。

調べる順番は MySQL でも PostgreSQL でも同じです。

  1. 待たされている処理を見つける
  2. それを待たせている相手 (ブロッカー) を見つける
  3. ブロッカーが何をしているか、止めてよいかを確かめる

MySQL でロック待ちを調べる

ここでは MySQL 8.0 以降を前提にします。MySQL 5.7 までの information_schema.INNODB_LOCKS と INNODB_LOCK_WAITS は 8.0 でなくなり、performance_schema.data_locks と data_lock_waits に置き換わりました。古い記事の SQL はそのままでは動きません。

これらのテーブルと、後で使う sys スキーマのビューは Performance Schema の機能です。SHOW GLOBAL VARIABLES LIKE 'performance_schema'; が OFF だと使えません。Amazon RDS では無効になっていることがあり、有効にするにはパラメータグループを変えて再起動する必要があります。information_schema.INNODB_TRX は Performance Schema がなくても使えるので、後で説明する「長く開いたままのトランザクションを探す」の SQL はその場合でも動きます。

待っている処理と待たせている相手を一度に見る

いちばん手早いのは sys スキーマの innodb_lock_waits ビューです。待っている側と待たせている側を 1 行にまとめて見せてくれます。

SELECT
  wait_age_secs,
  locked_table_name,
  locked_index,
  waiting_pid,
  waiting_query,
  blocking_pid,
  blocking_query,
  blocking_trx_age,
  blocking_trx_rows_modified,
  sql_kill_blocking_connection
FROM sys.innodb_lock_waits
ORDER BY wait_age_secs DESC;

読み方は次のとおりです。

列 意味
wait_age_secs 何秒待っているか
locked_table_name、locked_index どのテーブルのどのインデックスで待っているか
waiting_pid、waiting_query 待っている接続の ID と、その SQL
blocking_pid、blocking_query 待たせている接続の ID と、いま実行中の SQL
blocking_trx_age 待たせている側のトランザクションが始まってからの時間
blocking_trx_rows_modified 待たせている側がこれまでに変更した行数
sql_kill_blocking_connection 待たせている接続を切るための KILL 文

結果が空なら、いまは InnoDB の行ロックで待っている処理はありません。それでも処理が止まっているなら、後で説明するメタデータロックを疑います。

同じ情報は performance_schema.data_lock_waits と information_schema.INNODB_TRX を結合しても取れます。sys スキーマを使えない環境では、こちらを使います。

SELECT
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query           AS waiting_query,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query           AS blocking_query,
  b.trx_started         AS blocking_trx_started
FROM performance_schema.data_lock_waits w
JOIN information_schema.INNODB_TRX b
  ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
JOIN information_schema.INNODB_TRX r
  ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID;

blocking_query が NULL のとき

調べてみると、blocking_query が NULL になっていることがよくあります。これは、待たせている接続がいま何も実行していないという意味です。行を更新したあと、コミットもロールバックもせずに止まっています。アプリがトランザクションを閉じ忘れた、手作業で BEGIN したまま席を外した、といった状況です。

この接続が直前に何を実行したかは、Performance Schema の履歴から追えます。まず blocking_pid を Performance Schema のスレッド ID に変換します。

SELECT THREAD_ID
FROM performance_schema.threads
WHERE PROCESSLIST_ID = 1234;   -- blocking_pid の値

得られた THREAD_ID で、そのスレッドが最近実行した SQL を見ます。

SELECT EVENT_ID, SQL_TEXT
FROM performance_schema.events_statements_history
WHERE THREAD_ID = 5678         -- 上で得た THREAD_ID
ORDER BY EVENT_ID;

ロックを取った UPDATE や SELECT ... FOR UPDATE が見つかれば、アプリのどの処理か見当がつきます。接続元のホストやユーザーは、information_schema.PROCESSLIST の HOST と USER で確かめられます。

長く開いたままのトランザクションを探す

ロック待ちが起きていなくても、長く開いたままのトランザクションはロック待ちの予備軍です。INNODB_TRX で、開始が古い順に並べて見ます。

SELECT
  t.trx_mysql_thread_id AS thread_id,
  p.USER,
  p.HOST,
  p.COMMAND,
  TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_age_sec,
  t.trx_rows_locked,
  t.trx_rows_modified,
  LEFT(t.trx_query, 100) AS current_query
FROM information_schema.INNODB_TRX t
LEFT JOIN information_schema.PROCESSLIST p
  ON p.ID = t.trx_mysql_thread_id
ORDER BY t.trx_started
LIMIT 20;

COMMAND が Sleep で trx_age_sec が大きい行は、トランザクションを開いたまま何もしていない接続です。

innodb_lock_wait_timeout の落とし穴

InnoDB は行ロックを innodb_lock_wait_timeout 秒 (既定 50 秒) 待つと、待っている側に ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction を返します。

ここで注意したいのは、既定ではトランザクション全体ではなく、タイムアウトした SQL 1 つだけが取り消されることです。トランザクションはまだ開いたままで、それより前の変更も残っています。アプリがエラーを受けて何もせずに次へ進むと、半分だけ更新した状態でコミットしてしまうことがあります。1205 を受けたら、アプリ側で明示的にロールバックしてからやり直すようにしてください。タイムアウトでトランザクション全体を戻したい場合は innodb_rollback_on_timeout を有効にしますが、起動時にしか設定できません。

innodb_lock_wait_timeout はセッションごとに変えられます。画面からの操作のように早くエラーを返したい処理では短く、夜間の大きなバッチでは長く、と使い分けられます。

SET SESSION innodb_lock_wait_timeout = 5;

ALTER TABLE が止まるときはメタデータロック

ここまでの方法で何も出てこないのに処理が止まっている場合は、メタデータロックの待ちかもしれません。メタデータロックはテーブルの構造を変える DDL と、そのテーブルを使っているトランザクションのあいだのロックです。InnoDB の行ロックとは仕組みが別なので、sys.innodb_lock_waits には出ません。

典型的なのは次の流れです。あるトランザクションがテーブルを読んだまま終わっていない。そこに ALTER TABLE を流すと、ALTER はそのトランザクションが終わるのを待つ。待っている ALTER の後ろに、同じテーブルへの普通の SELECT や UPDATE が並び、サービス全体が止まったように見える。

SHOW PROCESSLIST の State に Waiting for table metadata lock が並んでいたら、これです。待たせている相手は sys.schema_table_lock_waits で見られます。

SELECT
  object_schema,
  object_name,
  waiting_pid,
  waiting_query,
  waiting_query_secs,
  blocking_pid,
  blocking_lock_type,
  sql_kill_blocking_connection
FROM sys.schema_table_lock_waits;

メタデータロックを待つ時間の上限は lock_wait_timeout で、既定値は 31536000 秒 (1 年) です。実質的には待ち続けます。本番で DDL を流すときは、先に長いトランザクションがないことを確かめ、セッションの lock_wait_timeout を数秒にしておくと、詰まったときに ALTER の側があきらめてくれます。

SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders ADD COLUMN note VARCHAR(255);

PostgreSQL でロック待ちを調べる

待っている処理と待たせている相手を見る

PostgreSQL では、pg_stat_activity の wait_event_type が Lock のセッションがロックを待っています。待たせている相手のプロセス ID は pg_blocking_pids() 関数で取れます。

SELECT
  pid,
  pg_blocking_pids(pid)  AS blocked_by,
  wait_event,
  now() - query_start    AS waiting_for,
  left(query, 100)       AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;

blocked_by は配列です。複数のセッションに待たされていることもあります。wait_event には何を待っているかが入り、行ロックの待ちなら transactionid や tuple、テーブルのロックなら relation になります。

次に、待たせている側が何をしているかを見ます。

SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  now() - xact_start    AS xact_age,
  now() - state_change  AS in_this_state,
  left(query, 100)      AS last_query
FROM pg_stat_activity
WHERE pid IN (
  SELECT unnest(pg_blocking_pids(pid))
  FROM pg_stat_activity
  WHERE wait_event_type = 'Lock'
);

state が idle in transaction なら、トランザクションを開いたまま何もしていません。MySQL の blocking_query が NULL の状態と同じです。PostgreSQL の query 列には、アイドルの接続でも最後に実行した SQL が残るので、どの処理がロックを取ったかの手がかりになります。

pg_blocking_pids() は、ロックを持っている相手 (hard block) だけでなく、同じロックを先に待っている相手 (soft block) も返します。これは ALTER TABLE のときに効いてきます。ALTER TABLE の多くはテーブルに最も強いロックをかけるので、長いトランザクションがあると ALTER が待ちます。その ALTER の後ろに、同じテーブルへの SELECT まで並んでしまいます。MySQL のメタデータロックと同じ形の詰まりです。

pg_locks で細かく見る

ロックの種類まで見たいときは pg_locks を使います。granted が false の行が待っているロックです。

SELECT
  l.pid,
  l.locktype,
  l.relation::regclass AS relation,
  l.mode,
  l.granted,
  l.waitstart
FROM pg_locks l
WHERE NOT l.granted;

行ロックの情報はメモリではなくディスクの行に記録されるので、ふつうは pg_locks に行として出ません。行ロックを待っているセッションは、相手のトランザクション ID (locktype が transactionid) を待つ形で出ます。waitstart は PostgreSQL 14 から追加された列です。

pg_locks を自分自身と結合して待ちの関係をたどることもできますが、PostgreSQL のドキュメントは、正しく書くのが難しいので pg_blocking_pids() を使うよう勧めています。どちらも内部のロック管理の情報を一瞬占有するので、1 秒ごとのように頻繁に実行するのは避けてください。

止めるときは cancel と terminate を使い分ける

待たせている相手を止めるには、2 つの関数があります。

関数 すること
pg_cancel_backend(pid) いま実行中の SQL だけを取り消す。接続は残る
pg_terminate_backend(pid) 接続ごと切る。開いていたトランザクションはロールバックされる

idle in transaction の相手は、実行中の SQL がないので pg_cancel_backend では止まりません。pg_terminate_backend を使います。どちらも、既定ではスーパーユーザーか、同じロールのメンバー、または pg_signal_backend の権限を持つロールが使えます。

lock_timeout で待ちに上限をつける

PostgreSQL は既定ではロックを無期限に待ちます。lock_timeout を設定すると、ロックを指定の時間待ったところでエラーにします (既定は 0 で無効)。

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;

ドキュメントは、lock_timeout を postgresql.conf に書くとすべてのセッションに効くので勧めないとしています。DDL を流すセッションや、特定のロールにだけ設定します。statement_timeout を設定している場合は、lock_timeout をそれ以上の値にしても意味がありません。SQL 全体のタイムアウトが先に効くからです。

待ちが続いたことをログに残すには、log_lock_waits を有効にします。deadlock_timeout (既定 1 秒) より長くロックを待ったセッションがログに書かれます。

待たせている相手を止めるかどうかの判断

ブロッカーを見つけたら、すぐに切りたくなります。ただ、切るとそのトランザクションの変更はすべてロールバックされます。大量の行を更新していたトランザクションは、ロールバックにも同じくらいの時間がかかることがあり、その間はロックが残ります。

判断の目安を書きます。

  • idle in transaction や Sleep のまま長時間たっている接続は、何もしていないので、切っても失われるのはコミットされていない変更だけです。接続元のアプリや人を確かめたうえで、切る候補になります。
  • 実行中のバッチや大きな更新 (blocking_trx_rows_modified が大きい) は、切るとロールバックに時間がかかります。あと少しで終わるなら待つほうが早いこともあります。
  • 誰の処理か分からないときは、HOST、USER、application_name、client_addr から持ち主を探します。

再発を防ぐには、トランザクションを短くすること、トランザクションの中で外部 API を呼んだり利用者の入力を待ったりしないこと、更新の条件に合うインデックスを用意してロックする行を減らすこと、が基本になります。PostgreSQL では idle_in_transaction_session_timeout で、放置されたトランザクションを自動で切ることもできます。

この確認を続けるには

ロック待ちは、起きている最中に調べないと手がかりが消えてしまいます。上の sys.innodb_lock_waits や pg_stat_activity の SQL を cron で 1 分ごとに実行して結果をファイルに残す、長いトランザクションの数や最大の経過時間を監視ツールで取り、一定の時間を超えたら通知する、といった方法で続けられます。PostgreSQL なら log_lock_waits を有効にしておくだけでも、あとから追える範囲が広がります。

AI に任せる方法もあります。Scandock は、接続文字列を登録すると AI がデータベースの構成を読んで監視項目を決め、値の推移や過去の通知の結末を踏まえて異常かどうかを判断するサービスです。異常と判断すると、読み取り専用のクエリで関連するデータを調べ、原因・影響・対処案をつけて通知します。対処の SQL は事前に登録したものだけで、AI が提案し、人が承認してから実行します。

参考

関連する記事