MySQLでロックを特定し、強制終了する



MySQLでロックが発生しているスレッドを特定（＋強制終了）する方法です。 
よく忘れるのでまとめてみました。

間違っているところはマサカリお待ちしています。

# ロック状態の確認方法

## 1.件数だけ

```mysql
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 5.6 SHOW ENGINE 構文](https://dev.mysql.com/doc/refman/5.6/ja/show-engine.html)

```mysql
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 5.6 SHOW PROCESSLIST 構文](https://dev.mysql.com/doc/refman/5.6/ja/show-processlist.html)

```
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)` の特定ができたので、強制終了します。  

[MySQL 5.6 KILL 構文](https://dev.mysql.com/doc/refman/5.6/ja/kill.html)

```
KILL 5;
```

# 実際にやってみる

まず、データを作ります。

```mysql
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

```mysql
# 最初に、ロックがないことを確認します。
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

```mysql
# こっちからもロック件数が１なことを確認します。
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

```mysql
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  

```mysql
KILL 5;
```

セッション Bで 止まっていたUPDATE文が実行されたのが確認できました。