MySQLのエラー『Lock Wait Timeout Exceeded』の解決方法

『Lock Wait Timeout Exceeded』エラーは、トランザクションがロックを取得できないまま、設定されたタイムアウト時間を超えた場合に発生する。デッドロックや長時間のトランザクションが原因となることが多い。このエラーの発生条件と、解決方法について詳しく説明する。

1. エラーの発生条件

このエラーは以下のような状況で発生する。

• 長時間ロックが解放されないトランザクションが存在する。
• 複数のトランザクションが同じリソースを競合してロックしようとしている。
• デッドロックが発生しており、リソースの奪い合いが続いている。

エラーメッセージの例：

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

2. 実行中のトランザクションを確認する

ロックを保持しているプロセスを確認する。

SHOW PROCESSLIST;

または、ロック情報をより詳細に確認する。

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

3. ロックを解除する

不要なトランザクションを強制終了する。

KILL <プロセスID>;

プロセスIDは SHOW PROCESSLIST の結果から取得できる。

4. トランザクションの適切なコミットを行う

長時間のトランザクションを防ぐため、定期的にコミットを行う。

COMMIT;

または、変更を取り消してロックを解放する。

ROLLBACK;

5. InnoDBのロックタイムアウト設定を変更する

デフォルトでは innodb_lock_wait_timeout は 50 秒に設定されている。必要に応じて変更する。

SET GLOBAL innodb_lock_wait_timeout = 120;

設定を永続化するには、my.cnf に以下を追加する。

[mysqld]
innodb_lock_wait_timeout = 120

6. ロックの影響を受けにくいクエリの実行

テーブルロックを最小限に抑えるため、SELECT クエリに LOCK IN SHARE MODE や FOR UPDATE を適切に使用する。

SELECT * FROM my_table WHERE id = 1 LOCK IN SHARE MODE;

SELECT * FROM my_table WHERE id = 1 FOR UPDATE;

7. インデックスの適用

適切なインデックスを設定することで、テーブルスキャンを防ぎ、ロック競合を軽減できる。

ALTER TABLE my_table ADD INDEX idx_column1 (column1);

8. デッドロックの検出と対策

最近発生したデッドロックを確認する。

SHOW ENGINE INNODB STATUS;

デッドロックが頻発する場合は、クエリの順序を統一したり、短いトランザクションを心がける。

9. 読み取り専用トランザクションの活用

更新を伴わない場合、読み取り専用トランザクションを利用することで、ロックの競合を減らせる。

SET TRANSACTION READ ONLY;
START TRANSACTION;
SELECT * FROM my_table;
COMMIT;

10. テーブルロックを手動で解除する

場合によっては、明示的にロックを解除する必要がある。

UNLOCK TABLES;

11. MySQLの再起動

最終手段として、MySQLを再起動することでロックを強制的に解除できる。

sudo systemctl restart mysql