忘備録として残しておきます。
環境
- MySQL 5.7
やり方
# まずはプロセスを確認する。
mysql> show processlist;
+-------+----------+---------------------+--------------------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+----------+---------------------+--------------------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| 5 | admin | localhost:57175 | mysql | Sleep | 0 | | NULL |
| 7401 | admin | localhost:36446 | hogehoge | Sleep | 56 | | NULL |
| 12001 | admin | localhost:46258 | piyopiyo | Sleep | 4837 | | NULL |
| 12010 | admin | localhost:46268 | piyopiyo | Sleep | 4836 | | NULL |
| 12011 | admin | localhost:46270 | piyopiyo | Sleep | 4837 | | NULL |
| 13599 | admin | localhost:48596 | piyopiyo | Sleep | 4858 | | NULL |
| 18286 | admin | localhost:47872 | NULL | Sleep | 26 | | NULL |
| 34204 | admin | localhost:41936 | NULL | Sleep | 3518 | | NULL |
| 34205 | admin | localhost:41942 | NULL | Sleep | 3518 | | NULL |
| 34265 | admin | localhost:50990 | piyopiyo | Sleep | 2574 | | NULL |
| 34266 | admin | localhost:50992 | fugafuga | Sleep | 2574 | | NULL |
| 34269 | admin | localhost:51236 | piyopiyo | Query | 2574 | Waiting for table metadata lock | DROP TABLE IF EXISTS `foo` |
| 34443 | admin | localhost:42502 | NULL | Sleep | 2029 | | NULL |
| 34447 | admin | localhost:42506 | NULL | Sleep | 1658 | | NULL |
| 36714 | admin | localhost:52570 | fugafuga | Query | 0 | starting | show processlist |
| 36762 | admin | localhost:49470 | hogehoge | Query | 0 | Opening tables | SELECT fk.referenced_table_name AS 'to_table', fk.referenced_column_name AS 'primary_key', |
| 36763 | admin | localhost:37310 | NULL | Sleep | 0 | | NULL |
| 36764 | admin | localhost:41294 | NULL | Sleep | 0 | | NULL |
+-------+----------+---------------------+--------------------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
# id:34269がロック待ちになっていることがわかったので、トランザクションを確認する。
mysql> select * from INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 1158606943
trx_state: RUNNING
trx_started: 2017-11-17 01:27:56
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 0
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: READ UNCOMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 1136726483
trx_state: RUNNING
trx_started: 2017-11-17 00:24:57
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 34204
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
# トランザクションが２つ貼られているのが分かる。
# 次クエリを実行して、「TRANSACTIONS」セクションを確認する。
mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2017-11-17 02:00:27 0x2ae44af91700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 5 seconds
-----------------
（いろいろ表示される）
------------
TRANSACTIONS
------------
Trx id counter 1165555070
Purge done for trx's n:o < 1136726486 undo n:o < 0 state: running but idle
History list length 16202
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 328633969292688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328633969293600, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1136726483, ACTIVE 4819 sec
0 lock struct(s), heap size 1136, 0 row lock(s)
MySQL thread id 34204, OS thread handle 47159570663168, query id 1339855 localhost admin
Trx read view will not see trx with id >= 1136726484, sees < 1136726484
（いろいろ表示される）
----------------------------
END OF INNODB MONITOR OUTPUT
============================
# id:34204がアクティブなトランザクションということがわかったので、こいつをキルする。
mysql> kill 34204
# もう一度プロセスを確認すると、killしたプロセスが消えてロック待ちをしているプロセスがいなくなっている。
mysql> show processlist;
+-------+----------+---------------------+--------------------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+----------+---------------------+--------------------+---------+------+----------+------------------+
| 5 | admin | localhost:57175 | mysql | Sleep | 12 | | NULL |
| 7401 | admin | localhost:36446 | hogehoge | Sleep | 17 | | NULL |
| 12001 | admin | localhost:46258 | piyopiyo | Sleep | 7579 | | NULL |
| 12010 | admin | localhost:46268 | piyopiyo | Sleep | 7578 | | NULL |
| 12011 | admin | localhost:46270 | piyopiyo | Sleep | 7579 | | NULL |
| 13599 | admin | localhost:48596 | piyopiyo | Sleep | 7600 | | NULL |
| 18286 | admin | localhost:47872 | NULL | Sleep | 56 | | NULL |
| 34443 | admin | localhost:42502 | NULL | Sleep | 4771 | | NULL |
| 34447 | admin | localhost:42506 | NULL | Sleep | 4400 | | NULL |
| 36714 | admin | localhost:52570 | information_schema | Query | 0 | starting | show processlist |
| 36855 | admin | localhost:43512 | hogehoge | Sleep | 3 | | NULL |
| 36874 | admin | localhost:43522 | hogehoge | Sleep | 3 | | NULL |
| 36908 | admin | localhost:43544 | hogehoge | Sleep | 4 | | NULL |
| 36947 | admin | localhost:43578 | hogehoge | Sleep | 2521 | | NULL |
| 38242 | admin | localhost:44830 | hogehoge | Sleep | 3 | | NULL |
+-------+----------+---------------------+--------------------+---------+------+----------+------------------+
15 rows in set (0.00 sec)