MySQL ALTER TABLE 必知的 Metadata Lock 陷阱與避坑指南

MySQL ALTER TABLE 必知的 Metadata Lock 陷阱與避坑指南

核心現象

執行 ALTER TABLE 時長時間卡住，SHOW PROCESSLIST 顯示 Waiting for table metadata lock。根因：目標表上存在未提交的事務或長查詢，持有 Metadata Lock（MDL），DDL 必須等待其釋放。

Metadata Lock (MDL) 機制詳解

MySQL 5.5.3+ 引入 MDL，保護表結構定義不被並發 DDL 破壞，確保主從同步邏輯正確。

• 範圍：表、Schema、存儲過程/函數/觸發器/事件
• 獲取時機：事務首次訪問表（含 SELECT）即獲取 MDL
• 釋放時機：事務提交/回滾結束時（非語句結束）
• 衝突：持有 MDL 的事務未結束時，其他 Session 的 DDL/FLUSH TABLES/LOCK TABLE WRITE 會阻塞

官方文檔關鍵點摘錄

為確保事務可序列化，Server 不允許一個 Session 對另一個 Session 未完成事務中使用的表執行 DDL。Server 在事務中獲取該表的 MDL，並延遲釋放至事務結束。MDL 阻止表結構變更，意味著被事務使用的表無法被其他 Session 的 DDL 修改，直到事務結束。

此原則同時適用於事務表與非事務表。即使 autocommit=ON，每條語句隱式事務結束才釋放 MDL，但長查詢仍會長時間持有。

實戰避坑清單

1. 執行 DDL 前必查：SHOW PROCESSLIST 確認目標表無 Sleep、長運行 Query 或未提交事務。
2. 業務低峰期操作：減少並發衝突機率。
3. 拆分大表 DDL：使用 pt-online-schema-change 或 gh-ost 線上變更工具，避免長鎖表。
4. 設置鎖等待超時：SET SESSION lock_wait_timeout=30; 避免無限等待阻塞其他查詢。
5. 監控 MDL 等待：performance_schema.metadata_locks 表可查看持有/等待詳情（5.7+）。

典型踩坑場景

-- Session A（長事務未提交）
START TRANSACTION;
SELECT * FROM t WHERE id=1 FOR UPDATE;
-- 忘記 COMMIT/ROLLBACK，去喝咖啡了...

-- Session B（運維執行 DDL）
ALTER TABLE t ADD COLUMN c INT;  -- 卡住：Waiting for table metadata lock

-- Session C（正常業務查詢）
SELECT * FROM t;  -- 也被阻塞，因 MDL 佇列 FIFO，DDL 在前排隊，後續讀寫全卡死

後果：DDL 堵在中間，導致所有後續訪問該表的查詢排隊，業務瞬間雪崩。

緊急處理

1. 找到持有 MDL 的 Session：SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_NAME='t'\G
2. Kill 該 Session（謹慎，可能導致業務數據不一致）
3. 或等待其自然結束

總結

MDL 是 MySQL 為了數據一致性與主從正確性引入的保護機制。理解其獲取/釋放時機、衝突規則，配合線上變更工具與監控，才能在生產環境安全執行 DDL，避免「改個字段類型，庫掛了」的慘劇。