删除表会使MySQL挂起 [英] Dropping table makes MySQL hang

问题描述

当我尝试删除表时，MySQL挂起.我没有其他公开课.如何解决呢?我已经等待了10个小时，但该过程尚未终止.

When I try to drop a table, MySQL hangs. I don't have any other open sessions. How to resolve this? I have waited for 10 hours and the process has not terminated.

推荐答案

Waiting for table metadata lock
drop table tableA name

SELECT l1.lat, l1.lon, l2.zipcode FROM tableA l1, tableBl2 where l1.lat = l2.latitude and l1.lon = l2.longitude limit 10

如果这是您的表，请参见此链接

If this is your table, see this link

您有一个隐式死锁.终止其他事务以释放该分支，或终止该分支以释放其他事务.

you have an implicit deadlock. Kill the other transactions to release the drop, or kill the drop to release the other transactions.

您可以在sql_plus中使用 KILL thread_id.

You can use KILL thread_id, in sql_plus.

自从我提出另一种有趣的经历以来，我正在添加更多信息.

I'm adding further information since I came up with another interesting experience.

Metadata在给定表(drop，alter ...)上的ddl操作与该表上的 select 查询之间可能同样会发生死锁.

Metadata Dead locks may equally happen between a ddl operation on a given table (drop, alter...) and a select query on that table.

是，select.

因此，如果您在mysql(或php，例如，使用pdo::fetch)中循环游标，并且在同一表上运行ddl语句，则会出现死锁.

So, if you loop over a cursor in mysql (or php, for example with pdo::fetch), and you run a ddl statement on the same table(s), you will get a deadlock.

这种非典型情况的一种解决方案是在完全提取任何select语句之后，用commit语句系统地释放隐式锁.

One solution to this atypical scenario is to release the implicit locks with a commit statement systematically after any select statement is completely fetched.

这篇关于删除表会使MySQL挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！