MySQLでロックが発生しているスレッドを特定（＋強制終了）する方法です。
よく忘れるのでまとめてみました。
間違っているところはマサカリお待ちしています。
ロック状態の確認方法
1.件数だけ
mysql> SELECT trx_rows_locked FROM information_schema.INNODB_TRX;
+-----------------+
| trx_rows_locked |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)
出ている数字(1)は、ロックがかかっている行数です。
2.件数＋スレッドID
mysql> SHOW ENGINE INNODB STATUS\G
...
------------
TRANSACTIONS
------------
Trx id counter 709
Purge done for trx's n:o < 708 undo n:o < 0
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 55, OS thread handle 0x7f6560413700, query id 202 localhost 127.0.0.1 rkojima
SHOW ENGINE INNODB STATUS
---TRANSACTION 705, ACTIVE 254 sec
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 0x7f6560475700, query id 190 localhost 127.0.0.1 rkojima
...
この行で確認できます。
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
この部分 => 1 row lock(s),
ロックが発生しているスレッドを特定する。
実際にロックが発生しているスレッドのIDは、その下に出ています。
MySQL thread id 5, OS thread handle 0x7f6560475700, query id 190 localhost 127.0.0.1 rkojima
この部分 => MySQL thread id 5
このthread id は show processlist;のIdと同じです。
mysql> show processlist;
+----+---------+-----------------+-------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+---------+-----------------+-------------+---------+------+-------+------------------+
| 5 | rkojima | localhost:55238 | lock_sample | Sleep | 170 | | NULL |
| 55 | rkojima | localhost:42366 | lock_sample | Query | 0 | NULL | show processlist |
+----+---------+-----------------+-------------+---------+------+-------+------------------+
2 rows in set (0.00 sec)
スレッドを強制終了させる
Id (processlist_id) の特定ができたので、強制終了します。
KILL 5;
実際にやってみる
まず、データを作ります。
CREATE DATABASE `lock_sample` DEFAULT CHARACTER SET utf8;
use lock_sample;
CREATE TABLE `User` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL COMMENT '名前',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
INSERT INTO `User` (`name`) VALUES ("sato"), ("suzuki"), ("takahashi");
SELECT * FROM `User`;
+----+-----------+
| id | name |
+----+-----------+
| 1 | sato |
| 2 | suzuki |
| 3 | takahashi |
+----+-----------+
3 rows in set (0.00 sec)
スレッド A
# 最初に、ロックがないことを確認します。
SELECT trx_rows_locked FROM information_schema.INNODB_TRX;
Empty set (0.00 sec)
# ロック状態にします。
start transaction;
UPDATE `User` SET `name` = "saito" WHERE `id` = 1;
# もう一度ロック件数を表示し、１件になっていることを確認します。
SELECT trx_rows_locked FROM information_schema.INNODB_TRX;
+-----------------+
| trx_rows_locked |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)
もう一つコンソールを開いて
スレッド B
# こっちからもロック件数が１なことを確認します。
SELECT trx_rows_locked FROM information_schema.INNODB_TRX;
+-----------------+
| trx_rows_locked |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)
# どのスレッドでロックがかかっているか
SHOW ENGINE INNODB STATUS\G
...
------------
TRANSACTIONS
------------
...
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 0x7f6560475700, query id 218 localhost 127.0.0.1 rkojima
--------
...
# どんなプロセスなのか見てみる。
show processlist;
+----+---------+-----------------+-------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+---------+-----------------+-------------+---------+------+-------+------------------+
| 5 | rkojima | localhost:55238 | lock_sample | Sleep | 436 | | NULL |
| 55 | rkojima | localhost:42366 | lock_sample | Query | 0 | NULL | show processlist |
+----+---------+-----------------+-------------+---------+------+-------+------------------+
2 rows in set (0.00 sec)
# トランザクション中に何もしていない時、Infoの表示はNULLのようですね。
# 更新かけてみる
# 1) ロックがかかっていない行は更新できる。
UPDATE `User` SET `name` = "susuki" WHERE `id` = 2;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
# 2) id=1は行ロックかかっているので待ちが発生
UPDATE `User` SET `name` = "saito" WHERE `id` = 1;
# 待ち状態なので、応答なし
もう一つコンソールを開いて確認してみます。
スレッド C
SELECT trx_rows_locked FROM information_schema.INNODB_TRX;
+-----------------+
| trx_rows_locked |
+-----------------+
| 1 |
| 1 |
+-----------------+
2 rows in set (0.00 sec)
SHOW ENGINE INNODB STATUS\G
...
------------
TRANSACTIONS
------------
Trx id counter 714
Purge done for trx's n:o < 712 undo n:o < 0
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 57, OS thread handle 0x7f6560444700, query id 233 localhost 127.0.0.1 rkojima
SHOW ENGINE INNODB STATUS
---TRANSACTION 713, ACTIVE 18 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 55, OS thread handle 0x7f6560413700, query id 228 localhost 127.0.0.1 rkojima Updating
UPDATE `User` SET `name` = "saito" WHERE `id` = 1
------- TRX HAS BEEN WAITING 18 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 306 n bits 80 index `PRIMARY` of table `lock_sample`.`User` trx id 713 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 00000000070c; asc ;;
2: len 7; hex 0a000001370110; asc 7 ;;
3: len 5; hex 736169746f; asc saito;;
------------------
---TRANSACTION 70C, ACTIVE 969 sec
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 0x7f6560475700, query id 218 localhost 127.0.0.1 rkojima
...
show processlist;
+----+---------+-----------------+-------------+---------+------+----------+---------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+---------+-----------------+-------------+---------+------+----------+---------------------------------------------------+
| 5 | rkojima | localhost:55238 | lock_sample | Sleep | 1055 | | NULL |
| 55 | rkojima | localhost:42366 | lock_sample | Query | 1 | Updating | UPDATE `User` SET `name` = "saito" WHERE `id` = 1 |
| 57 | rkojima | localhost:54252 | NULL | Query | 0 | NULL | show processlist |
+----+---------+-----------------+-------------+---------+------+----------+---------------------------------------------------+
3 rows in set (0.00 sec)
試しにトランザクション張ったままのセッションをKILLしてみましょう。
スレッド C
KILL 5;
セッション Bで 止まっていたUPDATE文が実行されたのが確認できました。