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”的表结构的程序，新创建的进程将再次处于 Metallock 中。

我什至无法删除数据库：

mysql> drop database bookmaker3;

这也会产生金属锁。

如何修复这个问题？

Answer 1

不幸的是，所接受的解决方案是错误的。说的一点都没错

这确实是（几乎肯定；见下文）要做的事情。但随后它表明，

...而1398不是与锁的连接。怎么会这样？ 1398 是正在等待锁定的连接。这意味着它还没有获得锁，因此杀死它没有任何作用。持有锁的进程仍将持有锁，并且下一个线程尝试执行某些操作将因此也停止并按适当的顺序进入“等待元数据锁”。

您无法保证“等待元数据锁定”(WFML) 的进程也不会阻塞，但您可以确定仅终止 WFML 进程将完全无济于事。

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

您可以确定的一件事是，没有标记为“等待元数据锁定”的进程。可以说，这些人是受害者。

SHOW FULL PROCESSLIST WILL 告诉您进程是否正在做某事，是的。通常它会起作用。在这里，持有锁的进程什么都不做，并且隐藏在其他也不做任何事情的线程中并报告为“睡眠”。

如果SHOW FULL PROCESSLIST向您显示一个正在运行DML的进程，或者处于“发送数据”状态，那么，那几乎肯定是罪魁祸首。其他进程正在等待它释放锁（它们可以是隐式锁；进程根本不需要发出 LOCK TABLE，这实际上会以不同的方式锁定）。但是一个进程可以在不执行任何操作时持有锁，并被适当地标记为“睡眠”。

在OP的情况下，罪魁祸首几乎肯定是进程1396，它在进程1398之前启动，现在处于睡眠状态，并且已经已经持续了 46 秒。由于 1396 显然已经完成了它需要做的所有事情（事实证明它现在正在休眠，并且已经这样做了 46 秒，就 MySQL 而言），没有线程进入在它可以持有锁并仍然持有它之前睡眠（否则 1396 也会停止）。

由于MySQL的“无死锁”锁定策略，任何进程都不能持有锁、释放锁并再次恢复锁；因此，锁等待总是由仍然持有锁且之前从未持有过该锁的进程引起。这很有用（我们将在下面利用这个事实），因为它可以保证锁“队列”是顺序的。

重要：如果您以受限用户身份连接到 MySQL，SHOW FULL PROCESSLIST 将不会显示所有进程。因此锁可能由您看不到的进程持有。

所以：如果SHOW FULL 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 状态的进程，并且无论如何它都会按时间降序对它们进行排序，因此更容易找到挂起的进程（由于顺序，通常是 在“等待元数据锁定”之前立即睡眠一个；并且它总是比任何等待的睡眠时间都多的睡眠之一。

重要的事情

保留任何“等待元数据锁定”进程单独。

快速而肮脏的解决方案，不真正推荐，但很快

杀死同一数据库上所有处于“睡眠”状态的进程，这些进程比最旧的线程处于“等待元数据锁定”状态。这就是Arnaud Amaury 会这么做：

• 对于每个在 WaitingForMetadataLock 中至少有一个线程的数据库：
  • 该数据库上 WFML 中最旧的连接已存在 Z 秒
  • 该数据库上且早于 Z 的所有“睡眠”线程都必须消失。从最新鲜的开始，以防万一。
  • 如果该数据库上存在一个旧的非睡眠连接，那么可能就是持有锁的连接，但它正在做某事。您当然可以杀死它，但特别是如果它是更新/插入/删除，则后果自负。
  • 每次KILL后，重新评估情况并相应地重新启动进程。正在等待的进程现在可能正在运行，或者它们可能已经短暂运行并现在正在休眠。 他们现在甚至可能持有新的元数据锁。

一百次中有九十九次，要杀死的线程是处于睡眠状态且比等待元数据锁定的较旧线程更老的线程中最年轻的线程：

TIME     STATUS
319      Sleep
205      Sleep
 19      Sleep                      
(*) TIME 顺序实际上有毫秒，或者我被告知，它只是不显示它们。因此，虽然两个进程的时间值均为 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 事务执行什么操作。它很可能处于“睡眠”状态。所以：一个带有活动锁的活动事务（#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);
实际解决方案，需要时间和工作
问题通常是由这种架构引起的：

当 Web 应用程序终止或 Web 应用程序轻量级线程实例终止时，容器/连接池可能不会。正是容器使连接保持打开状态，因此显然连接不会关闭。不出所料，MySQL 不会认为该操作已完成。
如果网络应用程序没有自行清理（没有事务的ROLLBACK或COMMIT，没有UNLOCK TABLES等），那么该 Web 应用程序开始执行的任何操作仍然存在，并且可能仍然会阻止其他所有人。
那么有两种解决方案。更糟糕的是降低空闲超时。但猜猜如果在两个查询之间等待太久会发生什么（确切地说：“MySQL 服务器已经消失”）。然后，您可以使用 mysql_ping（如果可用）（很快就会被弃用。PDO 有解决方法。或者您可以检查该错误，如果发生则重新打开连接（这是 Python 的方式）。因此 - 只需少量的性能费用 - 这是可行的。
更好、更智能的解决方案实施起来并不那么简单。努力让脚本自行清理，确保检索所有行或释放所有查询资源，捕获所有异常并正确处理它们，或者，如果可能的话，完全跳过持久连接。让每个实例创建自己的连接或使用智能池驱动程序（在 PHP PDO 中，使用 PDO::ATTR_PERSISTENT 显式设置为 false）。
或者（例如在 PHP 中），您可以通过提交或回滚事务（这应该足够了）来让析构和异常处理程序强制清理连接（这应该足够了），甚至可能发出显式表解锁和 RELEASE_ALL_LOCKS()，或者提交连接自杀（ KILL CONNECTION_ID()) 以达到良好的效果。
我不知道查询现有结果集资源以释放它们的方法；唯一的方法是将这些资源保存到私有数组中。