Waiting for table metadata lock

一個沒提交的事務使用了A表， 另外一個session 對A表進行alter，出現waiting for table metadata lock
解決方法：檢視information_schema.INNODB_TRX擁有事務的trx_mysql_thread_id，kill trx_mysql_thread_id回滾事務
如：
SessionA
mysql> select * from t1;
+---+------+------+------+------+
| x | y    | z    | a    | b    |
+---+------+------+------+------+
| 1 |    1 | NULL | NULL | NULL |
| 2 |    2 | NULL | NULL | NULL |
| 3 |    3 | NULL | NULL | NULL |
+---+------+------+------+------+
3 rows in set (0.00 sec)
mysql> begin；
mysql> update t1 set x=y+3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
mysql> select * from t1;
+---+------+------+------+------+
| x | y    | z    | a    | b    |
+---+------+------+------+------+
| 4 |    1 | NULL | NULL | NULL |
| 5 |    2 | NULL | NULL | NULL |
| 6 |    3 | NULL | NULL | NULL |
+---+------+------+------+------+
3 rows in set (0.02 sec)




session B:
mysql> alter table t1 drop b;
等待




mysql> select trx_id,trx_state,trx_started,trx_requested_lock_id,trx_wait_started,trx_weight,trx_mysql_thread_id,trx_query,trx_operation_state from information_schema.INNODB_TRX\G
*************************** 1. row ***************************
               trx_id: 12101
            trx_state: RUNNING
          trx_started: 2014-11-19 11:18:33
trx_requested_lock_id: NULL
     trx_wait_started: NULL
           trx_weight: 9
  trx_mysql_thread_id: 6
            trx_query: NULL
  trx_operation_state: NULL
1 row in set (0.00 sec)
mysql> show full processlist;
+----+------+-----------+--------------------+---------+------+---------------------------------+-----------------------+
| Id | User | Host      | db                 | Command | Time | State                           | Info                  |
+----+------+-----------+--------------------+---------+------+---------------------------------+-----------------------+
|  3 | root | localhost | test               | Query   |   76 | Waiting for table metadata lock | alter table t1 drop b |
|  4 | root | localhost | information_schema | Query   |    0 | init                            | show full processlist |
|  6 | root | localhost | test               | Sleep   |   64 |                                 | NULL                  |
+----+------+-----------+--------------------+---------+------+---------------------------------+-----------------------+
3 rows in set (0.00 sec)




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




session B:
執行成功
mysql> alter table t1 drop b;
Query OK, 0 rows affected (1 min 52.33 sec)
Records: 0  Duplicates: 0  Warnings: 0


Session A:
之前的操作被回滾
mysql> select * from t1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    7
Current database: test


+---+------+------+------+
| x | y    | z    | a    |
+---+------+------+------+
| 1 |    1 | NULL | NULL |
| 2 |    2 | NULL | NULL |
| 3 |    3 | NULL | NULL |
+---+------+------+------+
3 rows in set (0.00 sec)