create table if not exists Waiting for table metadata lock[ mysql数据库 ]
mysql数据库
时间：2024-12-24 19:11:02
作者：文/会员上传
简介：
create table if not existsWaiting for table metadata lock
版本mysql 5.5
现象：
mysql> show full processlist;
+----+-----------------+-----------+------+---------+
以下为本文的正文内容，内容仅供参考！本站为公益性网站，复制本文以及下载DOC文档全部免费。
create table if not existsWaiting 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 相互交流
展开阅读全文 ∨
