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