MySQL 全局鎖與表鎖深度解析：FLUSH TABLES WITH READ LOCK 與 LOCK TABLES 實戰

MySQL 鎖機制實戰：FLUSH TABLES WITH READ LOCK 與 LOCK TABLES 深度解析

在 MySQL 備份、主從搭建、DDL 變更等場景中，正確使用全局鎖與表鎖是保證數據一致性的關鍵。本文深度解析 FLUSH TABLES WITH READ LOCK（FTWRL）與 LOCK TABLES 的工作原理、阻塞行為、隱式提交陷阱及生產級最佳實踐。

一、FLUSH TABLES WITH READ LOCK（全局讀鎖）

1. 命令語法與作用

FLUSH TABLES WITH READ LOCK;  -- 獲取全局讀鎖
UNLOCK TABLES;                  -- 釋放所有鎖

• 範圍：全庫所有表，所有線程
• 效果：阻塞所有寫操作（DML/DDL），允許讀操作繼續
• 核心動作：將所有髒頁刷新到磁盤（確保數據文件一致性），然後在所有表上加讀鎖
• 典型場景：邏輯備份、文件系統快照、搭建從庫初始同步

2. 關鍵行為：為什麼 FTWRL 會等待長時間運行的 SELECT？

許多 DBA 困惑：SELECT 是讀操作，為什麼 FTWRL 要等它結束？

-- 會話 A：長時間掃描大表
SELECT COUNT(*) FROM huge_table;  -- 耗時 30 分鐘

-- 會話 B：執行 FTWRL
FLUSH TABLES WITH READ LOCK;  -- 狀態顯示 "Waiting for table flush"

根因：

1. FTWRL 需 Flush 所有髒頁到磁盤，保證文件級一致性。
2. 長查詢會在內存中訪問/修改頁（如臨時表、排序緩衝），這些頁可能尚未刷盤。
3. 查詢具有 原子性，不能中途被打斷刷盤，否則破壞查詢結果正確性。
4. 因此 FTWRL 必須等待長查詢結束，才能安全完成「Flush + 加鎖」。

對策：備份窗口選在業務低峰；使用 pt-online-schema-change 類工具避免長鎖；或改用 Percona XtraBackup 物理熱備（無需 FTWRL）。

3. 隱式提交陷阱：會話斷開自動 UNLOCK

-- 會話 A
FLUSH TABLES WITH READ LOCK;
-- 此時保持會話開啟（建議 screen/tmux/nohup）

-- 若 Ctrl+C 斷開、網絡中斷、kill 會話
-- MySQL 會隱式執行 UNLOCK TABLES，鎖瞬間失效！

鐵律：執行 FTWRL 的會話必須全程保持存活，直到備份/快照完成並手動 UNLOCK TABLES。

二、LOCK TABLES（表級鎖）

1. 語法與模式

LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE};
UNLOCK TABLES;

• READ [LOCAL]：允許並發讀，LOCAL 允許非衝突 INSERT（MyISAM 尾部插入）
• [LOW_PRIORITY] WRITE：獨佔寫鎖，阻塞所有其他讀寫
• 範圍：僅鎖定顯式指定的表，不影響其他表

2. 典型場景

• MyISAM 表定期 LOCK TABLES t READ; mysqldump ...; UNLOCK TABLES; 邏輯備份
• 多表關聯業務需原子讀取一致性快照（如報表系統）
• 手動觸發 ANALYZE TABLE / OPTIMIZE TABLE 前鎖定防寫入

3. 同樣受隱式提交影響

會話斷開、執行 START TRANSACTION、執行 DDL 等均會隱式 UNLOCK TABLES。

三、生產級最佳實踐清單

四、總結

• FLUSH TABLES WITH READ LOCK = 全局 Flush 髒頁 + 全局讀鎖，阻塞寫、等待長查詢、會話斷開即失效。
• LOCK TABLES = 顯式表級鎖，粒度細、不影響無關表、同樣怕隱式提交。
• 現代最佳實踐：能用事務隔離級別解決就不用顯式鎖；能用 XtraBackup/gh-ost 就不用 FTWRL/LOCK TABLES。顯式鎖僅作為兜底方案，且必須在受控會話（screen/tmux）中執行，嚴禁交互式終端直接操作。