MySQL:永久获取“等待表元数据锁定" [英] MySQL : Permanently getting " Waiting for table metadata lock"
问题描述
我的MySQL数据库将三个Web应用程序用作存储后端.但是，最近我永久遇到错误等待表元数据锁定".它几乎一直都在发生，我不明白为什么.
mysql> show processlist
-> ;
+------+-----------+-----------------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------+-----------------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| 36 | root | localhost:33444 | bookmaker2 | Sleep | 139 | | NULL |
| 37 | root | localhost:33445 | bookmaker2 | Sleep | 139 | | NULL |
| 38 | root | localhost:33446 | bookmaker2 | Sleep | 139 | | NULL |
| 39 | root | localhost:33447 | bookmaker2 | Sleep | 49 | | NULL |
| 40 | root | localhost:33448 | bookmaker2 | Sleep | 139 | | NULL |
| 1315 | bookmaker | localhost:34869 | bookmaker | Sleep | 58 | | NULL |
| 1316 | root | localhost:34874 | bookmaker3 | Sleep | 56 | | NULL |
| 1395 | bookmaker | localhost:34953 | bookmaker | Sleep | 58 | | NULL |
| 1396 | root | localhost:34954 | bookmaker3 | Sleep | 46 | | NULL |
| 1398 | root | localhost:34956 | bookmaker3 | Query | 28 | Waiting for table metadata lock | CREATE TABLE IF NOT EXISTS LogEntries (
lid INT NOT NULL AUTO_INCREMEN |
| 1399 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+------+-----------+-----------------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
当然可以杀死相应的进程.但是，如果我重新启动试图创建数据库"bookmaker3"的表结构的程序，则新创建的过程将再次在metalock中结束.
我什至无法删除数据库:
mysql> drop database bookmaker3;
这也产生了一个metalock.
如何修复?
使用锁定取消连接
Kill 1398
然后检查您是否将自动提交设置为0，
select @@autocommit;
如果是，您可能忘记了提交事务.然后另一个连接要对该表进行操作，这将导致锁定.
在您的情况下:如果您对 LogEntries (存在该查询)进行了一些查询，但没有提交，则您尝试从另一个连接中执行CREATE TABLE if NOT EXISTS-发生元数据锁定. /p>
修改 对我而言，该错误位于您的应用程序中.选中此处，或者如果您未在应用程序中使用事务，则将 autocommit设置为1 .
ps还检查此帖子:
My MySQL database serves three webapps as the storage backend. However I recently encounter permanantly the error "Waiting for table metadata lock". It happen nearly all the time and I do not understand why.
mysql> show processlist
-> ;
+------+-----------+-----------------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------+-----------------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| 36 | root | localhost:33444 | bookmaker2 | Sleep | 139 | | NULL |
| 37 | root | localhost:33445 | bookmaker2 | Sleep | 139 | | NULL |
| 38 | root | localhost:33446 | bookmaker2 | Sleep | 139 | | NULL |
| 39 | root | localhost:33447 | bookmaker2 | Sleep | 49 | | NULL |
| 40 | root | localhost:33448 | bookmaker2 | Sleep | 139 | | NULL |
| 1315 | bookmaker | localhost:34869 | bookmaker | Sleep | 58 | | NULL |
| 1316 | root | localhost:34874 | bookmaker3 | Sleep | 56 | | NULL |
| 1395 | bookmaker | localhost:34953 | bookmaker | Sleep | 58 | | NULL |
| 1396 | root | localhost:34954 | bookmaker3 | Sleep | 46 | | NULL |
| 1398 | root | localhost:34956 | bookmaker3 | Query | 28 | Waiting for table metadata lock | CREATE TABLE IF NOT EXISTS LogEntries (
lid INT NOT NULL AUTO_INCREMEN |
| 1399 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+------+-----------+-----------------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
Of course one can kill the corresponding process. However if I restart my program that tries to create the table structure of my database "bookmaker3" the newly created process ends up again in a metalock.
I even cannot drop the database:
mysql> drop database bookmaker3;
This yields also a metalock.
How can this be repaired?
Kill the connection with lock
Kill 1398
Then check if you have autocommit set to 0 by
select @@autocommit;
If yes, you propably forgot to commit transaction. Then another connection want to do something with this table, which causes the lock.
In your case: If you made some query to LogEntries (whitch exists) and did not commit it, then you try to execute CREATE TABLE IF NOT EXISTS from another connection - metadata lock happens.
edit For me the bug is somewhere at your application. Check there, or set autocommit to 1 if your not using transactions in application.
ps also check this posts:
这篇关于MySQL:永久获取“等待表元数据锁定"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
