MySQL Metadata Lock Deep Dive
MySQL metadata lock deep dive 的核心責任是說明 DDL、transaction 與 table metadata 之間的阻塞關係。MySQL 在查詢 table 時會取得 metadata lock；DDL 需要等待既有 metadata lock 釋放，等待中的 DDL 又會阻塞後續查詢，形成 production 常見雪崩。
本文涵蓋 MDL 的 lock lifecycle、找出誰持鎖與誰等待的偵測查詢、DDL 變更前的風險審查、事故 runbook、與 online schema change 工具的互動，以及事前預防。
Lock Lifecycle
Lock lifecycle 的核心責任是建立 MDL 心智模型。
| 行為 | MDL 影響 |
|---|---|
SELECT / DML | 取得 table metadata lock，交易結束釋放 |
| Long transaction | 延長 metadata lock 持有時間 |
ALTER TABLE | 先持有 SHARED_UPGRADABLE，升級成 EXCLUSIVE 時等既有的鎖釋放；等待期間後續 query 排在它後面 |
| Online schema change | 仍需 metadata lock 進行切換 / rename |
| Idle transaction | 看似無操作，仍可能持有 metadata lock |
MDL 的風險在於排隊。當 ALTER TABLE 等待 long transaction 時，後續新的 query 可能排在 DDL 後面，讓原本小變更變成服務不可用。
Detection
Detection 的核心責任是快速找出誰持鎖、誰等待。
1SELECT *
2FROM performance_schema.metadata_locks
3WHERE OBJECT_SCHEMA = 'appdb'
4ORDER BY OBJECT_NAME, LOCK_STATUS;一個交易 SELECT 過 t 之後沒有 COMMIT、另一個 session 對 t 下 ALTER TABLE、再來一個 session SELECT * FROM t 時，上面這段查詢在 MySQL 8.4 回的是下面幾列（只留 t 本身的鎖；ALTER 的暫存表 #sql-… 與 schema 層的鎖省略）：
1OBJECT_NAME LOCK_TYPE LOCK_STATUS OWNER_THREAD_ID
2t SHARED_READ GRANTED 50 -- 長交易：SELECT 之後沒 COMMIT，鎖持有到交易結束
3t SHARED_UPGRADABLE GRANTED 51 -- ALTER TABLE 先拿到可升級的共享鎖
4t EXCLUSIVE PENDING 51 -- ALTER TABLE 要升級成 EXCLUSIVE，等 thread 50 釋放
5t SHARED_READ PENDING 52 -- 後來的 SELECT 排在 pending 的 EXCLUSIVE 後面LOCK_STATUS = PENDING 的列就是被擋住的 session；它們的 OWNER_THREAD_ID 與持有鎖的那一列（GRANTED 而不屬於 DDL 的 thread）放在一起讀，就是誰擋誰。同一時間 processlist 裡 ALTER 與後來的 SELECT 兩條的 State 都是 Waiting for table metadata lock。
搭配 processlist：
1SHOW FULL PROCESSLIST;Production dashboard 應監控 running DDL、metadata lock wait、long transaction age、threads running、blocked query count 與 replication lag。
DDL Risk Review
DDL risk review 的核心責任是在變更前預測 MDL 風險。
| DDL 類型 | 風險 | 控制方式 |
|---|---|---|
| Add nullable column | 依版本 / algorithm 可能較低 | staging dry run、algorithm check |
| Add index | 可能長時間操作與切換 lock | online DDL / OSC、低峰窗口 |
| Change column type | table rebuild 風險高 | ghost table / phased migration |
| Rename / swap table | 短暫但關鍵 MDL | kill blocker、短窗口 |
| Drop column / table | destructive 且需鎖 | backup、approval、blocked query watch |
DDL review 要列出 algorithm、lock mode、預估時間、rollback、kill blocker policy 與 replication impact。
Incident Runbook
Incident runbook 的核心責任是把 MDL 事故分流。
| Step | 操作 |
|---|---|
| Identify blocker | 查 long transaction / metadata_locks |
| Stop new DDL | 暫停 migration pipeline |
| Decide kill | 依 owner / transaction age / impact |
| Protect app | 降低 traffic、停 heavy endpoint |
| Validate | 查 query 恢復、replication lag |
| Retrospective | 補 DDL gate、long transaction alert |
Kill session 是高風險操作。決策要記錄 transaction owner、已執行時間、可能 rollback 成本與業務影響。
OSC Interaction
OSC interaction 的核心責任是說明 gh-ost / pt-online-schema-change 仍需要 MDL 管理。Ghost table 工具把大部分 copy 與 backfill 移到旁路，但最後 cutover / rename 仍需要短暫 metadata lock。
| 工具階段 | MDL 風險 |
|---|---|
| Create ghost table | 低 |
| Copy / backfill | 主要是 load / replication lag |
| Trigger / binlog | 依工具模式不同 |
| Cutover / rename | 關鍵 MDL window |
OSC runbook 要在 cutover 前檢查 long transaction。若 blocker 存在，先延後 cutover，而非硬切。
Prevention
Prevention 的核心責任是讓 MDL 事故在 release 前被擋下。
- Long transaction alert。
- DDL dry run 與 algorithm / lock mode 記錄。
- Migration window 與 kill blocker policy。
- OSC cutover pre-check。
- Application transaction timeout。
- Read-only replica 上先測 schema change。
MDL 是 MySQL schema governance 的核心議題。每個 production DDL 都要有 metadata lock plan。
下一步路由
Metadata lock deep dive 完成後，schema change 工具讀 Online Schema Change Tools；lock 行為讀 Lock Contention；操作演練讀 Online Schema Change Lab。