create table if not exists Waiting for table metadata lock

create table if not exists    Waiting for table metadata lock
版本mysql 5.5
現象：
mysql> show full processlist;
+----+-----------------+-----------+------+---------+---------+---------------------------------+-------------------------------------+
| Id | User            | Host      | db   | Command | Time    | State                           | Info                                |
+----+-----------------+-----------+------+---------+---------+---------------------------------+-------------------------------------+
|  1 | event_scheduler | localhost | NULL | Daemon  | 1661254 | Waiting on empty queue          | NULL                                |
|  4 | root            | localhost | test | Query   |      13 | Waiting for table metadata lock | create table if not exists a like t |
|  5 | root            | localhost | test | Sleep   |      32 |                                 | NULL                                |
|  7 | root            | localhost | NULL | Query   |       0 | NULL                            | show full processlist               |
+----+-----------------+-----------+------+---------+---------+---------------------------------+-------------------------------------+


mysql> select * from information_schema.innodb_trx \G
Empty set (0.00 sec)


查不到鎖。


重現：
session  1：
create table a (id int) engine=myisam;
begin;
select * from a;


session2 :
create table if not exists a like t;
....等待.....




session 3 :
mysql> show full processlist;
+----+-----------------+-----------+------+---------+---------+---------------------------------+-------------------------------------+
| Id | User            | Host      | db   | Command | Time    | State                           | Info                                |
+----+-----------------+-----------+------+---------+---------+---------------------------------+-------------------------------------+
|  1 | event_scheduler | localhost | NULL | Daemon  | 1661796 | Waiting on empty queue          | NULL                                |
|  5 | root            | localhost | test | Sleep   |      51 |                                 | NULL                                |
|  7 | root            | localhost | NULL | Query   |       0 | NULL                            | show full processlist               |
| 10 | root            | localhost | test | Query   |      40 | Waiting for table metadata lock | create table if not exists a like t |
+----+-----------------+-----------+------+---------+---------+---------------------------------+-------------------------------------+
4 rows in set (0.00 sec)


mysql> select * from information_schema.innodb_trx \G
Empty set (0.00 sec)


如果a表的定義是innodb，就可以通過  select * from information_schema.innodb_trx \G 查詢到記錄


另：
5.6版本沒有這個問題。




轉載請註明源出處
QQ 273002188 歡迎一起學習
QQ 群 236941212
oracle,mysql,mongo 相互交流

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25099483/viewspace-1985342/，如需轉載，請註明出處，否則將追究法律責任。