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
URL分享：
http://csdahua.cn/article/poeihg.html
