MySQL で「Lock wait timeout exceeded」が出て UPDATE が通らない

要約

更新系のクエリが数十秒固まったあとに失敗する。

UPDATE orders SET status = 'paid' WHERE id = 42;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

これは 他のトランザクションが同じ行のロックを握ったままで、待ち時間が innodb_lock_wait_timeout を超えたことを意味する。
デッドロック（エラー 1213）とは別で、循環待ちが起きているわけではない。
単に「相手がいつまでも離さない」状態である。

したがって、タイムアウト値を伸ばしても待ち時間が延びるだけで解決しない。離さない側を先に特定する。

よくある原因

1. コミット漏れのセッションが居る: SET autocommit=0 のまま SELECT ... FOR UPDATE や UPDATE を実行し、そのまま放置された接続が典型である。
   対話的な mysql クライアントを開いたまま席を立った、という形でも起きる。
2. 例外時に ROLLBACK していない: アプリケーション側で例外が飛んだあと、トランザクションを閉じずにコネクションをプールへ返している。
   次に同じ接続を借りた処理が、前の処理のロックを引き継いだ状態で動く。
3. 長時間バッチとの競合: 集計や一括更新が広い範囲の行を触っている間、オンライン処理がその行に当たると待たされる。
   夜間バッチが伸びた日にだけ発生する、といった出方をする。
4. インデックスが無くロック範囲が広い: InnoDB の行ロックはインデックスレコードに対して掛かる。InnoDB Locking（新しいタブで開く）↗ が説明するとおり、適切なインデックスが無いと実質的に走査した範囲すべてがロック対象になる。WHERE status = 'pending' に索引が無ければ、1 行の更新のつもりでもテーブル全体を巻き込む。
5. 更新順序が揃っていない: 複数の処理が同じ複数行を別の順序で更新すると、待ち行列が長くなる。
   運が悪ければデッドロックに、運が良ければ（?）このタイムアウトになる。

解決策

1. 居座っているトランザクションを特定する

まず実行中のトランザクションを開始時刻順に見る。

SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

trx_started が極端に古く trx_query が NULL の行があれば、それがクエリを投げ終えたまま閉じられていないトランザクションである。
緊急時はそのスレッドを落とす。

KILL 12345;  -- trx_mysql_thread_id の値

落とすとそのトランザクションはロールバックされる。
書きかけの更新が消える点は理解したうえで実行する。

2. 待っている側と待たせている側を突き合わせる

MySQL 8.0 以降は performance_schema で対応関係を直接引ける。

SELECT r.trx_id AS waiting_trx, r.trx_query AS waiting_query,
       b.trx_id AS blocking_trx, b.trx_query AS blocking_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.innodb_trx b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID;

blocking_query が分かれば、どのコードパスが原因かを追える。

3. ロック範囲をインデックスで絞る

更新条件に索引が効いているかを確認する。

EXPLAIN UPDATE orders SET status = 'paid' WHERE status = 'pending';

type が ALL なら全表走査で、走査した行がすべてロック対象になる。
条件列に索引を張ればロックは該当行に限定される。

CREATE INDEX idx_orders_status ON orders (status);

4. トランザクションを短く保つ

もっとも効くのは設計側の対処である。
外部 API 呼び出しやファイル I/O をトランザクションの内側に入れない、更新はまとめて末尾に寄せる、といった原則でロック保持時間を縮める。

タイムアウト値そのものは innodb_lock_wait_timeout で変えられる。
既定は 50 秒で、セッション単位でも設定できる。

SET SESSION innodb_lock_wait_timeout = 10;

短くする方向は有効である。
滞留を早く検知でき、失敗したトランザクションを素早く再実行に回せる。
逆に長くする設定は、根本原因を隠したまま待ち行列を伸ばすだけになりやすいので、原因を特定したうえで判断する。