MySQL：永久性地出现“等待表元数据锁”的问题。

Question

我的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”的表结构的程序，新创建的进程会再次陷入死锁。

我甚至无法删除数据库。

mysql> drop database bookmaker3;

这也会产生一个金属锁。

这个怎么修复？

- toom

---

你可以在这里查看： https://dev59.com/8GYs5IYBdhLWcg3wGf_F或者在这里： https://dev59.com/Mm035IYBdhLWcg3wYO11 - thibault ketterer

Answer 1

很遗憾，被接受的解决方案是错误的。它在说到“使用锁定断开连接”时是正确的。

这确实（几乎可以确定；见下文）是要做的。但是接着它建议：

Kill 1398

...并且1398不是与锁的连接。为什么呢？1398是连接等待锁。这意味着它还没有锁，因此杀死它是没有用的。持有锁的进程仍将保持锁定状态，下一个尝试执行某些操作的线程因此也会停滞并按顺序进入“等待元数据锁”的状态。

您无法保证“等待元数据锁”（WFML）进程不会阻止，但可以肯定的是，仅杀死WFML进程将完全无效。

真正的原因是另一个进程正在持有该锁，更重要的是，SHOW FULL PROCESSLIST 不会直接告诉您是哪个进程。

您唯一可以确定的是，它不是标记为“等待元数据锁”的任何进程。可以说，它们是受害者。 SHOW FULL PROCESSLIST 可以告诉您进程是否正在执行某些操作。通常它是有效的。在这里，持有锁的进程什么也没做，并且隐藏在其他线程中，这些线程也什么也没做，并被报告为“Sleeping”。

如果SHOW FULL PROCESSLIST显示一个正在运行DML或处于“发送数据”状态的进程，那么很明显，它几乎肯定是罪魁祸首。其他进程正在等待它释放锁定（它们可以是隐式锁定；该进程根本不需要发出LOCK TABLE，这实际上会以不同的方式锁定）。但是，进程在什么也不做时可能保持锁定，并被标记为“Sleep”。

在OP的情况下，罪魁祸首几乎肯定是进程1396，它比进程1398先启动，并且现在处于Sleep状态，已经有46秒了。由于1396显然已经完成了所有必要的工作（正如MySQL所认为的那样，它现在正在睡眠，并且已经这样做了46秒），没有线程在它之前进入睡眠状态，因此它可以保持锁定并仍然保持锁定（否则1396也会停顿）。

由于MySQL的“无死锁”锁定策略，没有进程可以保持锁定，释放它，并再次恢复它；因此，锁定等待始终是由仍然保持锁定且从未保持过该锁定的进程引起的。这是有用的（我们将在下面利用这个事实），因为它保证了锁定“队列”是连续的。

重要提示：如果您以有限的用户身份连接到MySQL，SHOW FULL PROCESSLIST不会显示所有进程，所以锁可能是被您看不到的进程占用。

因此，如果 SHOW FULL PROCESSLIST 显示了所有内容并显示了一个正在运行的进程，则该进程很可能是负责该锁的进程，并且您需要等待它完成其操作（或者您可以杀死该进程，但风险自负）。

本答案的其余部分讨论了令人困惑的情况，即进程没有明显的原因处于等待状态，而似乎也没有人做任何事情。

更好的 SHOW PROCESSLIST

SELECT ID, TIME, USER, HOST, DB, COMMAND, STATE, INFO
    FROM INFORMATION_SCHEMA.PROCESSLIST WHERE DB IS NOT NULL
    AND (`INFO` NOT LIKE '%INFORMATION_SCHEMA%' OR INFO IS NULL)
    ORDER BY `DB`, `TIME` DESC

可以调整以上内容，仅显示处于SLEEP状态的进程，并按时间顺序排序，以便更容易找到挂起的进程(由于先后顺序，通常是紧挨着"等待元数据锁定"的那个Sleep进程; 而且它总是那些已经休眠比任何正在等待的进程要长)。

重要提示

不要干扰任何"等待元数据锁定"的进程。

快速而简单的解决方案，不是真正推荐但是快速可行的

杀掉在同一数据库中处于"SLEEP"状态并且年龄比"等待元数据锁定"状态中最老的线程还要旧的所有进程。这就是Arnaud Amaury将要做的事情：

• 对于每个至少有一个线程在等待元数据锁的数据库：
  • 在该数据库中，最老的WFML连接已经存在Z秒
  • 该数据库中所有睡眠时间比Z长的"Sleep"线程都必须终止。从最新的线程开始，以防万一。
  • 如果该数据库存在一个旧的非睡眠连接，则可能它是持有锁的那个连接，但它正在做某些事情。当然可以杀死它，但特别是如果它是UPDATE/INSERT/DELETE操作，则自行承担风险。
  • 在每次KILL之后，重新评估情况并相应地重新启动该过程。原来等待的进程现在可能正在运行，或者它们可能已经短暂运行并处于睡眠状态。 它们甚至可能是现在持有新元数据锁的那些进程。

99次中有99次，要终止的线程是那些处于睡眠状态且比等待元数据锁的最老线程更老的线程中最年轻的线程：

TIME     STATUS
319      Sleep
205      Sleep
 19      Sleep                      <--- one of these two "19"
 19      Sleep                      <--- and probably this one(*)
 15      Waiting for metadata lock  <--- oldest WFML
 15      Waiting for metadata lock
 14      Waiting for metadata lock

(*) 时间顺序实际上有毫秒，至少我是这么被告知的，只是不显示。因此，虽然两个进程的时间值都为19，但最低的那个应该更年轻。

更专注的修复

运行SHOW ENGINE INNODB STATUS并查看“TRANSACTION”部分。您将找到，其中包括以下内容

TRANSACTION 1701, ACTIVE 58 sec;2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 1396, OS thread handle 0x7fd06d675700, query id 1138 hostname 1.2.3.4 whatever;

现在你可以使用SHOW FULL PROCESSLIST检查线程ID 1396正在进行的#1701事务。很可能它处于“Sleep”状态。因此：一个有活动锁的活动事务（＃1701），它甚至已经做了一些更改，因为它有一个撤消日志条目...但目前处于空闲状态。那个，而不是其他任何线程，是您需要终止的线程。失去这些更改。

请记住，在MySQL中什么也不做并不意味着总体上什么也不做。如果您从MySQL获取一些记录并构建CSV以进行FTP上传，则在FTP上传期间，MySQL连接处于空闲状态。

实际上，如果使用MySQL的进程和MySQL服务器位于同一台机器上，并且该机器运行Linux并且具有root权限，则有一种方法可以找出请求锁的连接的进程。这反过来允许确定（从CPU使用率或最坏的情况下，strace-ff-p pid）该进程是否真的正在做某事，以帮助决定是否安全终止。

为什么会发生这种情况？

我看到这种情况发生在使用“持久”或“池化”MySQL连接的Web应用程序中，这些应用程序现在通常保存很少的时间：Web应用程序实例终止，但是连接没有终止，因此其锁仍然存在...并阻塞其他所有人。

另一个有趣的方法是，在上述假设中，运行返回一些行的查询，并且仅检索其中的一些行。如果查询未设置为“自动清除”（但底层DBA如何处理），它将保持连接打开，并防止完全锁定表。我在一段代码中遇到过这种情况，该代码通过选择该行并验证是否出现错误（不存在）或没有（它必须存在）来验证是否存在该行，但实际上没有检索该行。

PHP和PDO

PDO具有持久连接功能。 这就是我确保PDO不池化连接并关闭每个连接的方式。 它很凌乱。

在打开时，设置选项（new PDO（）的第四个选项）：

PDO::ATTR_PERSISTENT => false

断开连接时：

// We should have no transactions and no locks.
// So we discard them.
try {
    $pdo->exec('ROLLBACK WORK');
    $pdo->exec('UNLOCK TABLES');
} catch (Exception $err) {
    // Send a mail
}
// No cooperative locks. So this will not hurt a bit.
try {
    $pdo->exec('DO RELEASE_ALL_LOCKS()');
} catch (Exception $err) {
    // Send a mail
}
// Ensure the connection withers on the vine, but not too soon.
$pdo->exec('SET wait_timeout = 5');

// $pdo->setAttribute(PDO::ATTR_TIMEOUT, 5);
// If nothing else works!
// try {
//     $pdo->exec('KILL CONNECTION_ID()');
// } catch (Exception $err) {
//     // Exception here is expected: "Query execution was interrupted"
// }
// Invoke the garbage collector
$pdo = NULL;

询问数据库

如果你使用的是较新的 MySQL 数据库，但不是最新版本，有另一种获取罪犯的方法{{但这种方法即将被弃用}}，那就是（你需要再次拥有信息模式的特权）

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS 
     WHERE LOCK_TRX_ID IN 
        (SELECT BLOCKING_TRX_ID FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS);

实际解决方案，需要时间和工作

这个问题通常是由以下架构引起的:

 webapp (jar, php) --> container or app connection pool
   (mysqldb, php_module, fastcgi...) --> 
   --> MySQL

当webapp死亡或webapp轻量级线程实例死亡时，容器/连接池可能不会死亡。而且是容器保持连接打开，所以显然连接不会关闭。MySQL自然不会认为操作完成。

如果webapp没有清理自己（对于事务没有ROLLBACK或COMMIT，没有UNLOCK TABLES等），那么webapp开始的任何工作仍然存在，并且可能仍然阻塞其他人。

然后有两个解决方案。更糟糕的一个是降低空闲超时时间。但是猜猜如果在两个查询之间等待太长时间会发生什么（确切地说：“MySQL服务器已经关闭”）。如果可用，则可以使用mysql_ping（即将被弃用）。PDO有解决方法。或者，如果发生该错误，则可以检查并重新打开连接（这是Python的方式）。因此，需要付出一些小的性能费用，就可以做到这一点。

更好、更智能的解决方案不太直观。请努力让脚本在自身清理方面更加完善，确保检索所有行或释放所有查询资源、捕获并正确处理所有异常，或者如果可能的话，完全跳过持久连接。让每个实例创建自己的连接或使用智能池驱动程序（在PHP PDO中，将PDO::ATTR_PERSISTENT明确设置为false）。

另外（例如，在PHP中），您可以通过提交或回滚事务（这应该足够了），甚至发出显式表解锁和RELEASE_ALL_LOCKS()，或通过执行KILL CONNECTION_ID()来强制清除销毁和异常处理程序连接。

我不知道有一种查询现有结果集资源以释放它们的方法；唯一的方法是将这些资源保存在私有数组中。

- LSerni

---

非常奇怪，我有一个睡眠进程，时间一直在变化，所以它看起来从未挂起，就像它正在重新创建一样。然而，我注意到ID没有改变，所以在杀死那个进程后，问题得到了解决。 - Illegal Operator

@IllegalOperator 如果时间一直增加，那是正常的——线程被锁定了，它一直在睡眠。问题在于，如果线程被锁定并且睡眠时间不断重置为零：这意味着线程正在执行某些操作，而这些操作可能会导致事务出现故障。我仍然建议杀掉线程，但线程正在执行的任何操作都将丢失，并可能处于不稳定状态。 - LSerni

非常感谢 - 那很有帮助 - 而且非常重要 - 解释得非常好。好答案！ - ospf

这应该绝对成为被接受的答案。谢谢 @LSerni - AsafSavich

当我摆脱连接池，或者至少将我的应用程序中的min_pool和max_pool数量（从min=10和max=100）减少到min=1和max=2时，我不再遇到元数据锁问题了。这应该是被接受的答案。 - luthfianto

Answer 2

断开带有锁的连接

Kill 1398

然后检查您是否将自动提交设置为0：

select @@autocommit;

如果是的话，你可能忘记提交事务。然后另一个连接想要对这个表执行某些操作，这会导致锁定。

在您的情况下：如果您对LogEntries（它存在）进行了某些查询并且没有提交，那么您尝试从另一个连接执行CREATE TABLE IF NOT EXISTS - 就会发生元数据锁定。

编辑 对我而言，错误可能出现在您的应用程序中。请检查一下，或者如果您没有在应用程序中使用事务，请将autocommit设置为1。

Ps 还要检查这些帖子：

• 不是在bugs.mysql上的错误
• MyISAM和INNOdb表上的锁复制

- michalczukm

---

没错，我尝试在数据库上创建一个表结构，同时又有另一个连接。关闭另一个连接解决了这个问题。谢谢。 - toom

Answer 3

对我有效的方法如下：

1. 运行此命令并查找持有锁的所有者线程ID：select * from performance_schema.metadata_locks;
2. 从此命令中找到该特定线程的PROCESSLIST_ID x：select * from performance_schema.threads;
3. Kill x;

- Yashvi Porwal

Answer 4

如果您有 HS 插件并尝试 CREATE 或 ALTER 已经通过 HS 尝试访问的表，您将面临类似的问题，并且必须以这种方式重新启动 HS 插件以释放表元数据锁：

UNINSTALL PLUGIN HANDLERSOCKET;
INSTALL PLUGIN HANDLERSOCKET SONAME 'handlersocket.so';

- Alexander V. Ilyin

Answer 5

LSerni已经很好地回答了这个问题，但我想补充一下我用PHP写的东西。

在我的项目中，我们正在构建大量遗留代码。因此，像连接池这样的现代化思想并不存在。我们有很多一次性连接到数据库被闲置。不幸的是，这意味着我的最新更新，即修改一个大表格，一直挂起。

我编写了这个PHP函数；我在myqsli_query()语句之后立即调用它。

function unsleepingCity($link) {
    //to prevent the issue in https://www.abhinavbit.com/2019/07/sleep-thread-causing-waiting-for-table-metadata-lock.html
    //looks for sleep processes locking the DB and kills them
    $query = "SELECT ps.id from information_schema.processlist ps join information_schema.INNODB_TRX itx on itx.trx_mysql_thread_id=ps.id and ps.command='Sleep';";
    $result = mysqli_query($link,$query);
    while ($row=mysqli_fetch_assoc($result)) {
        $proc = $row['id'];
        $query="kill $proc;";
        mysqli_query($link,$query);
    }
}

这可能有点过于“烧地球”，但非常有效。

如果您想在查询中获取有关这些睡眠进程的更多数据，我还有这个查询供您使用：

SELECT ps.id,ps.user,ps.host,ps.db,ps.command from information_schema.processlist ps join information_schema.INNODB_TRX itx on itx.trx_mysql_thread_id=ps.id and ps.command='Sleep';

- CttCJim

Answer 6

在现代MariaDB中，您可以运行以下命令以在清除阻塞查询的同时保留连接： sudo mysql -e "KILL QUERY ID XXXX" 您可以通过运行以下命令来获取QUERY_ID： SELECT QUERY_ID FROM information_schema.PROCESSLIST WHERE ID = XXXX; 如果您杀死进程ID而不是查询ID，则正在运行的任何脚本都会失去连接，并可能引起下游问题。

- TheWitness