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 相互交流

当前文章：createtableifnotexistsWaitingfortablemetadatalock
浏览路径：http://cdweb.net/article/poeihg.html