Waiting for table metadata lock

问题：

开发同学反馈一张小表加字段很长时间不能完成，processlist中STATE为Waiting for table metadata lock 请求支援。

问题分析：

想了一下有两种情况：

1.加字段的这个表上有查询长时间未能结束。

2.对这个表有事务操作未完成。

验证：

mysql> select * from information_schema.processlist;
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------+
| ID | USER | HOST      | DB   | COMMAND | TIME | STATE                           | INFO                                         |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------+
|  9 | root | localhost | test | Query   |  690 | Waiting for table metadata lock | alter table t add column c1 int              |
| 10 | root | localhost | NULL | Query   |    0 | executing                       | select * from information_schema.processlist |
|  8 | root | localhost | test | Sleep   |  716 |                                 | NULL                                         |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------------------+
3 rows in set (0.00 sec)

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 421534325778256
                 trx_state: RUNNING
               trx_started: 2018-11-02 19:19:51
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 8
                 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
1 row in set (0.00 sec)

  从information_schema.processlist 上看，并没有对更新表的查询操作。

  从nformation_schema.innodb_trx 上发现有一个记录，但并没有具体的SQL.

解决：

通过PERFORMANCE_SCHEMA.threads， PERFORMANCE_SCHEMA.events_statements_current 关联查询找到未提交事务的thread_id, 并kill 掉。加字段的操作很快完成。

mysql> SELECT concat('kill  ',t.PROCESSLIST_ID ,';') processlist_id, t.thread_id, PROCESSLIST_INFO, e.SQL_TEXT FROM PERFORMANCE_SCHEMA.threads t, PERFORMANCE_SCHEMA.events_statements_current e WHERE e.THREAD_ID = t.THREAD_ID AND t.PROCESSLIST_ID != 'null' AND t.PROCESSLIST_ID IS NOT NULL  AND PROCESSLIST_INFO IS NULL;
+----------------+-----------+------------------+--------------------+
| processlist_id | thread_id | PROCESSLIST_INFO | SQL_TEXT           |
+----------------+-----------+------------------+--------------------+
| kill  8;       |        33 | NULL             | select name from t |
+----------------+-----------+------------------+--------------------+
1 row in set, 1 warning (0.00 sec)

mysql> kill 8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.processlist;
+----+------+-----------+------+---------+------+-----------+----------------------------------------------+
| ID | USER | HOST      | DB   | COMMAND | TIME | STATE     | INFO                                         |
+----+------+-----------+------+---------+------+-----------+----------------------------------------------+
|  9 | root | localhost | test | Sleep   |  714 |           | NULL                                         |
| 10 | root | localhost | NULL | Query   |    0 | executing | select * from information_schema.processlist |
+----+------+-----------+------+---------+------+-----------+----------------------------------------------+
2 rows in set (0.00 sec)