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

More than 1 year has passed since last update.

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 5.6 SHOW ENGINE 構文

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 idshow processlist;Idと同じです。

MySQL 5.6 SHOW PROCESSLIST 構文

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 構文

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;

# もう一度ロック件数を表示し、1件になっていることを確認します。
SELECT trx_rows_locked FROM information_schema.INNODB_TRX;
+-----------------+
| trx_rows_locked |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)

もう一つコンソールを開いて


スレッド B

# こっちからもロック件数が1なことを確認します。

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文が実行されたのが確認できました。