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()) 以達到良好的效果。 
我不知道查詢現有結果集資源以釋放它們的方法；唯一的方法是將這些資源保存到私有數組。