MySQL Flush 與 Metadata 鎖定問題分析處理

在高負載的 MySQL 環境中，鎖定爭用是影響效能的常見因素。本文針對 Flush Lock 和 Metadata Lock 兩種鎖定問題，提供實務上的分析與處理技巧。Flush Lock 常發生於執行 FLUSH TABLES WITH READ LOCK 時，若有長查詢未完成，則會造成阻塞。Metadata Lock 則與資料表結構變更相關，當事務持有 Metadata Lock 時，其他事務的 DDL 操作會被阻塞。診斷鎖定問題，可運用 sys.session 查詢執行中查詢的狀態，並搭配 information_schema.INNODB_TRX 分析事務資訊。此外，performance_schema 提供更詳細的鎖定監控資訊，例如 metadata_locks 和 events_statements_history 等，有助於深入瞭解鎖定行為。解決方案包含終止阻塞查詢、最佳化長查詢 SQL、調整資料函式庫引數、使用 MySQL Workbench 的圖形化介面分析連線狀態等。預防措施則包含設定查詢超時、排程最佳化、以及採用 MySQL 8 的備份鎖機制。理解鎖定機制並善用診斷工具，才能有效提升 MySQL 資料函式庫的效能和穩定性。

MySQL Flush Lock 問題分析與處理

在 MySQL 資料函式倉管理中，Flush Lock 是一種常見的鎖定問題，可能會導致查詢效能下降甚至系統停滯。本文將探討 Flush Lock 的成因、診斷方法以及解決策略。

Flush Lock 的成因

Flush Lock 通常發生在執行 FLUSH TABLES WITH READ LOCK 陳述式時，該陳述式會對所有表施加讀鎖，防止資料寫入。正常情況下，這個操作應該很快完成，但如果有長查詢正在執行，FLUSH TABLES WITH READ LOCK 就會被阻塞，進而導致其他查詢也受到阻塞，形成 Flush Lock 問題。

診斷 Flush Lock 問題

要診斷 Flush Lock 問題，可以使用 sys.session 檢視查詢當前正在執行的查詢及其狀態。以下是一個典型的查詢範例：

SELECT thd_id, conn_id, state, current_statement, statement_latency
FROM sys.session
WHERE command = 'Query';

內容解密：

thd_id：執行緒 ID，用於識別每個查詢。
conn_id：連線 ID，對應到客戶端連線。
state：查詢的當前狀態，例如 “Waiting for table flush” 表示正在等待表重新整理。
current_statement：當前正在執行的 SQL 陳述式。
statement_latency：查詢已執行的時間。

透過分析這些資訊，可以找出導致 Flush Lock 的根源查詢。

案例分析

假設查詢結果如下：

*************************** 1. row ***************************
thd_id: 1107
conn_id: 669
state: Waiting for table flush
current_statement: FLUSH TABLES WITH READ LOCK
statement_latency: 2.11 min
*************************** 2. row ***************************
thd_id: 1106
conn_id: 668
state: User sleep
current_statement: SELECT city.*, SLEEP(3600) FROM world.city WHERE ID = 130
statement_latency: 4.21 min
*************************** 3. row ***************************
thd_id: 1108
conn_id: 670
state: Waiting for table flush
current_statement: SELECT * FROM world.city WHERE ID = 3805
statement_latency: 1.41 min

內容解密：

thd_id = 1107 的查詢正在執行 FLUSH TABLES WITH READ LOCK，且狀態為 “Waiting for table flush”，表示它被阻塞。
thd_id = 1106 的查詢是一個長查詢（執行了超過 4 分鐘），它阻塞了 FLUSH TABLES WITH READ LOCK 的執行。
thd_id = 1108 的查詢也被阻塞，等待表重新整理。

處理 Flush Lock 問題

一旦識別出阻塞的查詢，需要決定如何處理。常見的方法包括：

終止阻塞查詢：如果長查詢不是關鍵任務，可以使用 KILL 命令終止它，以釋放鎖定。

KILL 668;

   #### 內容解密：
   - 這裡的 `668` 是 `conn_id`，對應到 `thd_id = 1106` 的查詢。

2. **最佳化長查詢**：分析長查詢的執行計劃，最佳化 SQL 陳述式或新增索引，以減少其執行時間。

3. **調整資料函式庫引數**：根據實際情況調整相關引數，例如增加鎖定超時時間等。

### 使用 MySQL Workbench 分析

除了使用 SQL 查詢外，MySQL Workbench 也提供了圖形化的客戶端連線報表，可以用來監控和分析當前連線和查詢狀態。

```plantuml
@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title MySQL Flush 與 Metadata 鎖定問題分析處理

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

此圖示說明瞭如何使用 MySQL Workbench 檢視客戶端連線和查詢狀態。

重新整理鎖爭用案例分析與解決方案

在資料函式倉管理中，重新整理鎖（Flush Lock）爭用是一個常見且棘手的問題。本章節將探討一個真實案例，分析如何解決因長執行查詢（Long-Running Query）導致的重新整理鎖爭用問題，以及如何預防此類別問題的發生。

問題的本質

重新整理鎖爭用通常發生在執行 FLUSH TABLES WITH READ LOCK 陳述式時，此陳述式需要取得重新整理鎖，但如果有長執行查詢正在進行，則會阻塞該陳述式的執行。進而導致後續查詢無法執行，形成連鎖反應。

立即解決方案

面對重新整理鎖爭用問題，有兩種主要的立即解決方案：

等待長執行查詢完成：如果可以將應用程式重新導向到其他資料函式庫例項，可以嘗試讓長執行查詢完成。但需評估這些查詢是否會對資料一致性造成影響。
內容解密：
- 這種方法的優點是避免了直接干預正在執行的查詢。
- 需要使用 EXPLAIN FOR CONNECTION <processlist id> 命令來分析長執行查詢的執行計劃，以估計其完成時間。
終止相關查詢：如果長執行查詢被視為失控查詢，可以考慮直接終止它們。
內容解密：
- 終止查詢前，需評估該查詢已更改的資料量，使用 information_schema.INNODB_TRX 檢視中的 trx_rows_modified 列來估計。
- 若資料變更量大，建議讓查詢完成，因為回復操作可能耗時更長。

SELECT trx_rows_modified 
FROM information_schema.INNODB_TRX 
WHERE trx_mysql_thread_id = <thread_id>;

內容解密：

此查詢用於檢查指定事務已修改的行數，幫助評估終止查詢的影響。
<thread_id> 需替換為實際的執行緒 ID。

預防措施

預防重新整理鎖爭用的關鍵在於避免長執行查詢和 FLUSH TABLES 陳述式同時發生。具體措施包括：

設定查詢超時：對於 SELECT 陳述式，可以使用 max_execution_time 系統變數或 MAX_EXECUTION_TIME(N) 最佳化器提示來設定超時，避免查詢無限期執行。
```
SET SESSION max_execution_time = 1000;  -- 設定超時為1秒
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM large_table;
```
內容解密：
- 這種方法可以有效防止查詢無限制執行。
- 超時值需根據實際業務需求調整。
排程最佳化：將長執行查詢和需要重新整理表的任務安排在不同時間執行，或在不同資料函式庫例項上執行。
備份策略最佳化：在 MySQL 8 中，可以使用備份鎖（Backup Lock）和日誌鎖（Log Lock）來避免重新整理 InnoDB 表。例如，MySQL Enterprise Backup (MEB) 在 8.0.16 及以後版本中採用了這種方法。

MySQL 的 Metadata Lock 爭議與診斷

MySQL 在 5.7 及更早版本中，Metadata Lock 經常是管理員的困擾來源。問題在於很難判斷誰持有 Metadata Lock。在 MySQL 5.7 中，Performance Schema 加入了對 Metadata Lock 的監控，而在 MySQL 8.0 中，這項功能預設啟用。有了這項監控，很容易就能找出阻塞其他連線取得鎖的來源。本章將透過一個實際案例來探討 Metadata Lock 的問題。

Metadata Lock 爭議的症狀

Metadata Lock 爭議的症狀與 Flush Lock 爭議相似。通常會出現一個長時間執行的查詢或事務，一個等待 Metadata Lock 的 DDL 陳述式，以及可能累積的其他查詢。要關注的症狀包括：

DDL 陳述式和其他查詢卡在「Waiting for table metadata lock」狀態。
查詢可能累積，所有等待的查詢都使用同一個表格。
當 DDL 陳述式等待 lock_wait_timeout 時間後，會發生 ER_LOCK_WAIT_TIMEOUT 錯誤。
可能存在一個長時間執行的查詢或事務，即使事務是空閒的或是執行與 DDL 陳述式無關的查詢。

Metadata Lock 爭議的原因

Metadata Lock 的存在是為了保護資料表的結構定義，只要事務是活躍的，Metadata Lock 就會持續。因此，即使沒有長時間執行的查詢，也可能因為事務持有 Metadata Lock 而導致爭議。

簡而言之，Metadata Lock 的存在是因為一個或多個連線依賴於特定表格的結構不變，或者它們已經明確鎖定了表格。

模擬 Metadata Lock 爭議

本案例使用三個連線來模擬 Metadata Lock 爭議。第一個連線開啟一個事務，第二個連線嘗試對同一個表格新增索引，第三個連線嘗試對同一個表格執行查詢。

-- 連線 1
START TRANSACTION;
SELECT * FROM world.city WHERE ID = 3805\G
SELECT Code, Name FROM world.country WHERE Code = 'USA'\G

-- 連線 2
ALTER TABLE world.city ADD INDEX (Name);

-- 連線 3
SELECT * FROM world.city WHERE ID = 130;

內容解密：

開啟事務：在連線 1 中，我們開啟了一個事務，並對 world.city 表格執行查詢。這個事務會持有 world.city 的 Metadata Lock，直到事務結束。
新增索引：在連線 2 中，我們嘗試對 world.city 表格新增索引，但因為連線 1 的事務仍未結束，Metadata Lock 導致這個操作被阻塞。
執行查詢：在連線 3 中，我們嘗試對 world.city 表格執行查詢，同樣因為 Metadata Lock 而被阻塞。

調查 Metadata Lock 爭議

如果啟用了 wait/lock/metadata/sql/mdl 的 Performance Schema 監控（MySQL 8.0 預設啟用），就可以輕鬆調查 Metadata Lock 爭議。可以使用 metadata_locks 表格來列出已取得和待處理的鎖，或者使用 sys.schema_table_lock_waits 檢視來獲得鎖狀況的摘要。

-- 調查 #1
-- 連線 4
SELECT thd_id, conn_id, state, current_statement, statement_latency 
FROM sys.session 
WHERE command = 'Query' OR trx_state = 'ACTIVE'\G

內容解密：

查詢活躍連線：我們使用 sys.session 檢視來查詢目前活躍的連線和它們的狀態。
檢查鎖狀態：透過檢查 state 和 current_statement，我們可以瞭解哪些連線正在等待 Metadata Lock，以及它們正在執行的陳述式。
診斷鎖爭議：透過分析這些資訊，可以診斷出鎖爭議的原因，並採取相應的措施，例如終止阻塞的事務或調整鎖逾時設定。

MySQL 中繼資料鎖定爭用分析與解決

在 MySQL 資料函式庫的運作過程中，中繼資料鎖定（Metadata Lock）是一種重要的鎖定機制，用於確保資料定義語言（DDL）操作與資料操縱語言（DML）操作之間的協調。然而，當多個連線競爭同一資源時，可能會發生鎖定爭用（lock contention），進而導致效能問題甚至系統停滯。

發生中繼資料鎖定爭用的情況

當一個連線正在執行 DDL 操作（如 ALTER TABLE），而其他連線正在存取同一個表格時，就可能發生中繼資料鎖定爭用。舉例來說，若一個連線正在對 world.city 表格執行 ALTER TABLE 操作以新增索引，而另一個連線正在對同一個表格執行 SELECT 操作，則 SELECT 操作可能會被阻塞，直到 ALTER TABLE 操作完成。

使用 `sys.schema_table_lock_waits` 檢視鎖定爭用

MySQL 提供了 sys.schema_table_lock_waits 檢視，可以用來檢視當前正在等待的鎖定請求以及阻塞這些請求的連線。以下是一個查詢範例：

SELECT *
FROM sys.schema_table_lock_waits\G

內容解密：

此查詢使用 sys.schema_table_lock_waits 檢視來檢視鎖定爭用的詳細資訊。輸出的欄位包括：

object_schema 和 object_name：被鎖定的表格所屬的資料函式庫和表格名稱。
waiting_thread_id 和 waiting_pid：正在等待鎖定的執行緒 ID 和程式 ID。
waiting_account：正在等待鎖定的使用者帳號。
waiting_lock_type：等待的鎖定型別（如 EXCLUSIVE 或 SHARED_READ）。
waiting_query：正在等待鎖定的查詢。
blocking_thread_id 和 blocking_pid：阻塞等待鎖定請求的執行緒 ID 和程式 ID。
sql_kill_blocking_query 和 sql_kill_blocking_connection：用於終止阻塞查詢或連線的 SQL 陳述式。

分析鎖定爭用的輸出結果

從 sys.schema_table_lock_waits 的輸出結果中，可以看到有多個連線正在等待鎖定，而這些等待是由特定的阻塞連線引起的。例如，程式 ID 714 正在等待對 world.city 表格的獨佔鎖定，但被程式 ID 713 阻塞。進一步分析後發現，程式 ID 713 是主要阻塞來源，因此終止該連線或查詢可以解決鎖定爭用問題。

尋找導致鎖定爭用的主要阻塞連線

若要找出導致鎖定爭用的主要阻塞連線，可以使用以下查詢：

SELECT *
FROM sys.schema_table_lock_waits
WHERE waiting_lock_type = 'EXCLUSIVE'
AND waiting_pid <> blocking_pid\G

內容解密：

此查詢篩選出等待獨佔鎖定且等待程式 ID 不等於阻塞程式 ID 的記錄，從而找出主要的阻塞連線。在這個例子中，輸出結果顯示程式 ID 714 正在等待獨佔鎖定，但被程式 ID 713 阻塞。因此，終止程式 ID 713 的查詢或連線可以解決問題。

探討 MySQL 的 Metadata Lock 問題

Metadata Lock（MDL）是用於保護資料函式庫物件（如表、檢視等）結構不被平行操作破壞的重要機制。在 MySQL 中，當一個事務對某個表持有 MDL 鎖時，其他事務若需對該表進行結構變更或某些特定操作（如 SELECT 以外的操作），就必須等待該 MDL 鎖釋放。這種鎖機制在某些情況下可能導致效能問題或死鎖。

分析 MDL 鎖爭用

要分析 MDL 鎖爭用，首先需要了解哪些事務正在持有 MDL 鎖，以及哪些事務正在等待這些鎖。MySQL 提供了多種工具和系統表來幫助我們進行這類別分析。

查詢鎖等待情況

首先，可以使用 sys.schema_table_lock_waits 表來找出哪些事務正在等待 MDL 鎖，以及哪些事務持有導致等待的 MDL 鎖。

SELECT 
    waiting_pid, 
    waiting_query, 
    blocking_pid, 
    blocking_query
FROM 
    sys.schema_table_lock_waits;

這個查詢能夠直接指出哪些查詢正在等待 MDL 鎖，以及是被哪些程式（或查詢）阻塞。

進一步分析阻塞事務

一旦找到了導致 MDL 鎖爭用的事務，我們就需要進一步分析該事務的行為。可以使用 information_schema.INNODB_TRX 表來檢視事務的狀態和詳細資訊。

SELECT 
    *
FROM 
    information_schema.INNODB_TRX
WHERE 
    trx_mysql_thread_id = <blocking_pid>;

#### 內容解密：

trx_id: 事務ID。
trx_state: 事務的當前狀態。
trx_started: 事務開始的時間。
trx_mysql_thread_id: 事務對應的 MySQL 執行緒 ID。
其他欄位提供了關於事務的額外資訊，如隔離級別、已修改的行數等。

查詢事務歷史

使用 performance_schema.events_statements_history 表，可以檢視一個事務中執行的歷史查詢。

SELECT 
    event_id, 
    current_schema, 
    sql_text
FROM 
    performance_schema.events_statements_history
WHERE 
    thread_id = <thread_id>
AND 
    nesting_event_id = <nesting_event_id>
AND 
    nesting_event_type = 'TRANSACTION';

#### 內容解密：

event_id: 事件（查詢）的ID。
current_schema: 執行查詢時使用的當前schema。
sql_text: 執行的 SQL 查詢陳述式。
這個查詢幫助我們瞭解在一個事務中執行的所有查詢，從而找到可能導致 MDL 鎖爭用的查詢。

連線屬性分析

最後，可以透過 performance_schema.session_connect_attrs 表來檢視連線的屬性，從而可能找出問題連線的來源。

SELECT 
    attr_name, 
    attr_value
FROM 
    performance_schema.session_connect_attrs
WHERE 
    processlist_id = <processlist_id>;

#### 內容解密：

attr_name: 連線屬性的名稱。
attr_value: 對應屬性的值。
例如，可以透過這些屬性瞭解到客戶端的名稱、版本、作業系統等資訊。

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。

MySQL Flush 與 Metadata 鎖定問題分析處理

MySQL Flush Lock 問題分析與處理

Flush Lock 的成因

診斷 Flush Lock 問題

內容解密：

案例分析

內容解密：

處理 Flush Lock 問題

此圖示說明瞭如何使用 MySQL Workbench 檢視客戶端連線和查詢狀態。

重新整理鎖爭用案例分析與解決方案

問題的本質

立即解決方案

內容解密：

內容解密：

內容解密：

預防措施

內容解密：

MySQL 的 Metadata Lock 爭議與診斷

Metadata Lock 爭議的症狀

Metadata Lock 爭議的原因

模擬 Metadata Lock 爭議

內容解密：

調查 Metadata Lock 爭議

內容解密：

MySQL 中繼資料鎖定爭用分析與解決

發生中繼資料鎖定爭用的情況

使用 sys.schema_table_lock_waits 檢視鎖定爭用

內容解密：

分析鎖定爭用的輸出結果

尋找導致鎖定爭用的主要阻塞連線

內容解密：

探討 MySQL 的 Metadata Lock 問題

分析 MDL 鎖爭用

查詢鎖等待情況

進一步分析阻塞事務

#### 內容解密：

查詢事務歷史

#### 內容解密：

連線屬性分析

#### 內容解密：

玄貓 BlackCat

使用 `sys.schema_table_lock_waits` 檢視鎖定爭用