MySQL 如何找到导致“等待表元数据锁定”状态的事务？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13148630/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I find which transaction is causing a "Waiting for table metadata lock" state?
提问by Drew
I am trying to perform some DDL on a table and SHOW PROCESSLISTresults in a " Waiting for table metadata lock " message.
我正在尝试对表执行一些 DDL 并SHOW PROCESSLIST导致“等待表元数据锁定”消息。
How can I find out which transaction is not yet closed?
我怎样才能找出哪些交易尚未关闭？
I'm using MySQL v5.5.24.
我正在使用 MySQL v5.5.24。
回答by Joddy
SHOW ENGINE INNODB STATUS \G
Look for the Section -
寻找部分 -
TRANSACTIONS
We can use INFORMATION_SCHEMATables.
我们可以使用INFORMATION_SCHEMA表。
Useful Queries
有用的查询
To check about all the locks transactions are waiting for:
要检查所有的锁事务正在等待：
USE INFORMATION_SCHEMA;
SELECT * FROM INNODB_LOCK_WAITS;
A list of blocking transactions:
阻塞事务列表：
SELECT *
FROM INNODB_LOCKS
WHERE LOCK_TRX_ID IN (SELECT BLOCKING_TRX_ID FROM INNODB_LOCK_WAITS);
OR
或者
SELECT INNODB_LOCKS.*
FROM INNODB_LOCKS
JOIN INNODB_LOCK_WAITS
ON (INNODB_LOCKS.LOCK_TRX_ID = INNODB_LOCK_WAITS.BLOCKING_TRX_ID);
A List of locks on particular table:
特定表上的锁列表：
SELECT * FROM INNODB_LOCKS
WHERE LOCK_TABLE = db_name.table_name;
A list of transactions waiting for locks:
等待锁定的事务列表：
SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY
FROM INNODB_TRX
WHERE TRX_STATE = 'LOCK WAIT';
Reference- MySQL Troubleshooting: What To Do When Queries Don't Work, Chapter 6 - Page 96.
参考- MySQL 故障排除：当查询不起作用时该怎么办，第 6 章 - 第 96 页。
回答by thibault ketterer
If you cannot find the process locking the table (cause it is alreay dead), it may be a thread still cleaning up like this
如果你找不到锁定表的进程（因为它已经死了），它可能是一个线程仍然像这样清理
section TRANSACTION of
部分交易
show engine innodb status;
at the end
在末尾
---TRANSACTION 1135701157, ACTIVE 6768 sec
MySQL thread id 5208136, OS thread handle 0x7f2982e91700, query id 882213399 xxxIPxxx 82.235.36.49 my_user cleaning up
as mentionned in a comment in Clear transaction deadlock?
you can try killing the transaction thread directly, here with
你可以尝试直接杀死事务线程，这里用
KILL 5208136;
worked for me.
为我工作。
回答by Hln
回答by sonance207
I had a similar issue with Datagrip and none of these solutions worked.
我在 Datagrip 上遇到了类似的问题，但这些解决方案都没有奏效。
Once I restarted the Datagrip Client it was no longer an issue and I could drop tables again.
一旦我重新启动 Datagrip 客户端，它就不再是问题，我可以再次删除表。
