MySQL ERROR 1205: Lock wait timeout exceeded; try restarting transaction を修正する

エラーが発生する状況

トランザクション内で UPDATE、INSERT、または DELETE を実行し、約 50 秒待つと次のエラーが発生します：

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

InnoDB はテーブルではなく行をロックします。トランザクションが行を変更すると、コミットまたはロールバックするまでその行に排他ロックを保持し続けます。その間、他の処理はその行にアクセスできません。別のトランザクションが同じ行を先にロックし、タイムアウト時間を超えて保持し続けたため、クエリがタイムアウトしました。このタイムアウト時間は innodb_lock_wait_timeout で設定され、デフォルトは 50 秒です。

ブロッキングの原因を特定する

やみくもにリトライしないでください。まずどのトランザクションがロックを保持しているかを確認しましょう。

ロック待ちとブロッキングクエリを確認する

SELECT
  r.trx_id          AS waiting_trx_id,
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query       AS waiting_query,
  b.trx_id          AS blocking_trx_id,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query       AS blocking_query,
  b.trx_started     AS blocking_started
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;

実行中のプロセスをすべて確認する

SHOW FULL PROCESSLIST;

Time の値が大きい（200 秒以上など）Sleep 状態の接続を探してください。これらはロックを保持したままアイドル状態になっているオープントランザクションです。

応急処置：ブロッキングトランザクションを強制終了する

上記のクエリから blocking_thread の値を取得し、強制終了します：

KILL 42;  -- 42 を実際のブロッキングスレッド ID に置き換えてください

すぐに元のトランザクションをリトライしてください。ブロッキングスレッドがなくなれば、ロックが解放されてクエリが実行できます。ただし、スレッドの強制終了は応急処置に過ぎません。再発する原因については以下のセクションを参照してください。

根本原因

長時間実行されるオープントランザクション — トランザクションが開始され、遅い処理を行う（またはそのまま放置される）ままコミットされない。ロックはその間ずっと保持され続けます。
WHERE 句のカラムにインデックスがない — インデックスがないと、InnoDB は意図した数よりはるかに多くの行をロックします。500 万行のテーブルでは、実際に更新が必要なわずかな行の代わりに 500 万行のロックが発生する可能性があります。
トランザクション内で I/O を行うアプリケーションコード — HTTP リクエスト、ファイル読み取り、または SQL 文の間の 500 ms のスリープでさえ、必要以上に長くロックを保持し続けます。
循環的なロック順序 — トランザクション A はトランザクション B が保持するロックを待ち、B は A が保持するロックを待っています。どちらも進めません。これはデッドロックの典型的な状況であり、ERROR 1205 としても現れることがあります。

恒久的な解決策 1：トランザクションを短く保つ

ルールはシンプルです：開始、書き込み、コミット — できる限り速く。遅い処理はすべてトランザクションブロックの外に移動してください。

-- 悪い例：トランザクション内で遅い処理を行う
START TRANSACTION;
-- ... 外部 API 呼び出しに 3 秒かかる ...
UPDATE orders SET status = 'processed' WHERE id = 123;
COMMIT;

-- 良い例：事前に準備を済ませ、高速なトランザクションを開始する
-- （API 呼び出しはここで行い、トランザクション開始前に済ませる）
START TRANSACTION;
UPDATE orders SET status = 'processed' WHERE id = 123;
COMMIT;

恒久的な解決策 2：フィルタリングするカラムにインデックスを追加する

インデックスがないと、テーブルの全スキャンが強制されます。3 行だけをロックする代わりに、InnoDB は数千行、場合によってはテーブル全体をロックすることになります。現在のインデックスを確認してください：

SHOW INDEX FROM orders;

WHERE 句のカラムにインデックスがない場合は追加してください：

ALTER TABLE orders ADD INDEX idx_status (status);
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

インデックスを追加することで、同じ UPDATE がテーブルの全行をスキャンする代わりに、一致する行のみをロックするようになります。

恒久的な解決策 3：ロック待ちタイムアウトを調整する

タイムアウトを短くしてもロック競合はなくなりませんが、失敗するトランザクションをより速く失敗させることができます。50 秒待つ代わりに 10 秒でリソースが解放されます。

現在のセッションのみ

SET innodb_lock_wait_timeout = 10;

グローバル設定（再起動まで）

SET GLOBAL innodb_lock_wait_timeout = 10;

永続的な設定（my.cnf / my.ini に追加）

[mysqld]
innodb_lock_wait_timeout = 10

次に MySQL を再起動します：

sudo systemctl restart mysql

恒久的な解決策 4：一貫したロック順序を使用する

複数のトランザクションが同じ行にアクセスする場合、常に同じ順序でロックを取得する必要があります。順序が混在すると循環待ちが発生します。安全なパターンは次のとおりです：

-- トランザクション A と B はどちらも行 2 の前に行 1 をロックする必要がある
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- まず行 1 をロック
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;  -- 次に行 2 をロック
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

例えば、トランザクション A が行 1 をロックしてから行 2 を取得しようとし、同時にトランザクション B が行 2 をロックしてから行 1 を取得しようとしているとします。どちらも進めません。一貫した順序を強制することで、この循環を完全に断ち切ることができます。

恒久的な解決策 5：デッドロックログを有効にする

一度有効にしたら、そのまま実行し続けてください。MySQL は検出したすべてのデッドロックをエラーログに記録するため、将来のロック問題の診断がはるかに簡単になります：

SET GLOBAL innodb_print_all_deadlocks = ON;

最新のデッドロックを今すぐ確認するには：

SHOW ENGINE INNODB STATUS\G

LATEST DETECTED DEADLOCK セクションまでスクロールしてください。関連する正確なクエリ、テーブル、ロックタイプが表示されます。問題のあるコードを特定するのに十分な情報が得られます。

確認手順

1. 長時間実行されるオープントランザクションがないことを確認する

SELECT trx_id, trx_started, trx_query,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds_running
FROM information_schema.innodb_trx
ORDER BY seconds_running DESC;

正常な出力：行が表示されないか、すべてのトランザクションが数秒以内に完了している状態です。

2. アクティブなロック待ちがないことを確認する

SELECT COUNT(*) FROM information_schema.innodb_lock_waits;

0 が返されるはずです。

3. 失敗していたクエリをリトライする

エラーが発生していたトランザクションを実行してください。ERROR 1205 が発生せず、正常に完了するはずです。

4. タイムアウト値が正しく設定されていることを確認する

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

クイックリファレンス

ブロッカーの特定: innodb_trx と結合した information_schema.innodb_lock_waits をクエリする
ブロッカーの強制終了: KILL <thread_id>
トランザクションを短く保つ: 素早くコミットし、トランザクションブロック内での遅い I/O をゼロにする
インデックスの追加: 不必要な行範囲ロックやテーブル全体のロックを防ぐ
一貫したロック順序: 同時実行トランザクション間の循環待ちを防ぐ
デッドロックのログ記録: SET GLOBAL innodb_print_all_deadlocks = ON