create table if not exists Waiting for table metadata lock

千家信息网最后更新 2025年01月20日create table if not exists Waiting for table metadata lockcreate 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 相互交流