トランザクションを張り続けているプロセスを特定してkillする

忘備録として残しておきます。

# 環境
- MySQL 5.7

# やり方

```mysql
# まずはプロセスを確認する。
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)
 
```