MySQLのロック解消手順

2023年6月30日

先日、INSERT INTO SELECT FROMで読み込み側のテーブルにロックをかけてしまった。
（恥ずかしながらSELECTだからロックされないと勝手に思ってた。Oracleだとかからなかったような…。）

Qiita

MySQLのINSERT SELECTでロックを回避する方法 - Qiita

https://qiita.com/wakuwaku7/items/923acbb171da1eeca7a4

INSERT INTO SELECT FROM構文とは？任意の条件でSELECTした結果を別のテーブルにINSERTする構文です。複数レコードを一括で挿入するため、1レコードずつ処理するより高速に動作しSQLで完結するためアプ...

その際テンパっちゃってロックの解除がなかなか進まず冷や汗をかいたので手順をまとめておく。

まずはこちらのクエリでロックを行っているプロセスを探す。

SELECT trx_id, trx_rows_locked, trx_mysql_thread_id FROM information_schema.INNODB_TRX;

+-----------------+-----------------+---------------------+
| trx_id          | trx_rows_locked | trx_mysql_thread_id |
+-----------------+-----------------+---------------------+
|        38716601 |               0 |            82690603 |
|        38716592 |               1 |            82690301 |
|        38716387 |               2 |            82690086 |
|        38715612 |        10939083 |            66276449 |
+-----------------+-----------------+---------------------+

「trx_rows_locked」がロック数なので、大量にロックを行ってるやつがいたらまずそれが怪しい。次にその怪しいプロセスがなにをやってるか調べる。

SHOW PROCESSLIST;

+----------+-----------------+--------------------+---------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+-----------------+--------------------+---------------+
| 82690603 | hogeuser | 192.168.2.10:45150 | hogedb | Sleep | 83 | | NULL |
| 82690301 | hogeuser | 192.168.2.10:45152 | NULL | Sleep | 200 | | NULL |
| 82690086 | hogeuser | 192.168.2.10:45188 | hogedb | Query | 0 | init | SHOW PROCESSLIST |
| 66276449 | hogeuser | 192.168.2.40:51360 | hogedb | Execute | 11 | updating | delete from `users` where `foo` = 1000 |
+----------+-----------------+--------------------+---------------+

trx_mysql_thread_id と Id が対応するので実行内容が特定できる。
これで終了させたいプロセスが特定出来たら。

trx_mysql_thread_id を指定してKILLする。