我正在尝试运行ALTER TABLE在MySQL中的200行表上添加一列。它可以在几个盒子上本地正常工作,但是在生产中会无限期挂起(最多可以运行5分钟)。

进行SHOW processlist;我可以一直看到它是Waiting for table metadata lock。进程列表显示它是最古老的查询,因此我不知道它在等待什么,或者为什么它在等待什么都不会结束或被MySQL杀死。有任何想法吗?

SHOW ENGINE INNODB STATUS\G的事务部分如下:

------------
TRANSACTIONS
------------
Trx id counter 4148381
Purge done for trx's n:o < 4148307 undo n:o < 0 state: running but idle
History list length 699
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 4148291, not started
MySQL thread id 123643, OS thread handle 0x7f392b31d700, query id 467771426 some_ip root cleaning up
---TRANSACTION 4148286, not started
MySQL thread id 123642, OS thread handle 0x7f392b538700, query id 467771334 some_ip root cleaning up
---TRANSACTION 4148280, not started
MySQL thread id 123641, OS thread handle 0x7f39304ee700, query id 467771083 some_ip root cleaning up
---TRANSACTION 4148274, not started
MySQL thread id 123640, OS thread handle 0x7f392b569700, query id 467771047 some_ip root cleaning up
---TRANSACTION 4148268, not started
MySQL thread id 123639, OS thread handle 0x7f392b102700, query id 467770952 some_ip root cleaning up
---TRANSACTION 4148263, not started
MySQL thread id 123638, OS thread handle 0x7f39304bd700, query id 467770703 some_ip root cleaning up
---TRANSACTION 0, not started
MySQL thread id 123635, OS thread handle 0x7f392b507700, query id 467770298 some_ip root cleaning up
---TRANSACTION 4148245, not started
MySQL thread id 123634, OS thread handle 0x7f392b443700, query id 467770261 some_ip root cleaning up
---TRANSACTION 4148230, not started
MySQL thread id 123633, OS thread handle 0x7f392b753700, query id 467770267 some_ip root cleaning up
---TRANSACTION 4148303, not started
MySQL thread id 123632, OS thread handle 0x7f392b2ec700, query id 467771749 some_ip root Waiting for table metadata lock
ALTER TABLE some_table
---TRANSACTION 4148379, not started
MySQL thread id 123568, OS thread handle 0x7f393048c700, query id 467775687 some_ip root cleaning up
---TRANSACTION 4148380, not started
MySQL thread id 123563, OS thread handle 0x7f392b68f700, query id 467775899 some_ip root cleaning up
---TRANSACTION 4148373, not started
MySQL thread id 123562, OS thread handle 0x7f392b228700, query id 467774889 some_ip root cleaning up
---TRANSACTION 0, not started
MySQL thread id 123453, OS thread handle 0x7f392b474700, query id 467775900 some_ip root init
SHOW ENGINE INNODB STATUS
---TRANSACTION 4148302, ACTIVE 143 sec
MySQL thread id 123645, OS thread handle 0x7f392b6c0700, query id 467771503 some_ip root cleaning up
Trx read view will not see trx with id >= 4148303, sees < 4148303

最佳答案

我们的生产中也有类似的问题。问题是另一个服务正在访问数据库,并且在尝试运行Alter Command时将进入不确定循环。

就我们而言


我们停止了应用程序服务器-Weblogic
冉ALTER命令
重新启动数据库实例
重新启动Weblogic实例。

07-24 09:50
查看更多