MySQL 資料表鎖定完整指南：內部鎖、外部鎖、檢查修復協議實戰

前言：為何需要鎖表？

當執行表檢查、修復、備份或大量資料變更時，必須防止 MySQL 服務線程與維護工具（myisamchk/isamchk）或其他客戶端同時存取同一張表，避免資料損壞或結果不一致。MySQL 提供兩層鎖定機制：內部鎖（服務層邏輯鎖）與 外部鎖（檔案系統層鎖）。

一、內部鎖定—— 服務層邏輯鎖

語法

-- 鎖定
LOCK TABLES tbl_name {READ | WRITE} [, tbl_name {READ | WRITE}] ...

-- 解鎖
UNLOCK TABLES

特性規則

• READ 鎖（共享鎖）： 持鎖線程可讀，所有線程可讀，寫請求阻塞。
• WRITE 鎖（排他鎖）： 僅持鎖線程可讀寫，其他線程全阻塞。
• 優先級： WRITE > READ，確保變更儘快處理；READ 請求在 WRITE 釋放前等待。
• 自動釋放： 新的 LOCK TABLES、連線關閉、線程結束均自動釋放。
• 無死鎖： MySQL 內部鎖機制保證無死鎖發生。

適用場景

• READ：myisamchk 檢查表、mysqldump 一致性備份。
• WRITE：myisamchk -r 修復表、OPTIMIZE TABLE、大量批次更新。

二、外部鎖定—— 檔案系統層鎖

• 又稱 File-level Lock，透過作業系統檔案鎖（fcntl/flock）防止多進程同時修改同一資料檔。
• 預設啟用，但 Linux 預設關閉（--skip-locking / skip_external_locking=ON），因部分檔案系統（NFS、SMB）鎖不可靠。
• 查詢狀態：SHOW VARIABLES LIKE 'skip_external_locking'; → OFF 表啟用，ON 表禁用。

外部鎖啟用時的協作流程

1. 執行 mysqladmin flush-tables 刷新並關閉表檔。
2. 運行 myisamchk 檢查/修復，服務與工具透過檔案鎖協作。
3. 修復後再次 flush-tables 讓服務感知新索引檔。

外部鎖禁用時的替代方案

• 停止 MySQL 服務（最安全）。
• 或使用內部鎖協議（下文詳述）確保服務不干擾維護操作。

三、表檢查鎖定協議（內部鎖版）

-- 1. 客戶端加 READ 鎖 + 刷新
mysql -u root -p db_name
LOCK TABLE tbl_name READ;
FLUSH TABLES;

-- 2. 執行檢查
myisamchk tbl_name
# 或 isamchk tbl_name

-- 3. 釋放鎖
UNLOCK TABLES;

原理： READ 鎖防寫不防讀，FLUSH TABLES 強制服務關閉檔案描述符、刷新緩衝區，讓 myisamchk 看到最新磁碟狀態。

四、表修復鎖定協議（內部鎖版）

-- 1. 客戶端加 WRITE 鎖 + 刷新
mysql -u root -p db_name
LOCK TABLE tbl_name WRITE;
FLUSH TABLES;

-- 2. 備份原檔 + 執行修復
cp tbl_name.* /backup/dir/
myisamchk --recover tbl_name
# 或 isamchk --recover tbl_name

-- 3. 再次刷新 + 釋放鎖
FLUSH TABLES;
UNLOCK TABLES;

關鍵差異

1. WRITE 鎖：獨佔存取，連讀請求也阻塞，確保修復期間零干擾。
2. 雙重 FLUSH TABLES：修復產生新索引檔，第二次 FLUSH 強制服務重新開啟檔案、載入新索引元資料。
3. 備份原檔：修復前 cp tbl_name.* 留存回滾點。

五、實戰建議

• 生產環境優先停機維護，避免鎖爭用影響業務。
• 若必須線上操作，使用 LOCK TABLES ... WRITE 搭配 FLUSH TABLES，並在低峰期執行。
• InnoDB 表不適用此協議，請用 innodb_force_recovery 或線上備份工具（xtrabackup、mysqlbackup）。
• MySQL 5.6+ OPTIMIZE TABLE、ANALYZE TABLE 已支援線上 DDL，大幅降低鎖表需求。

結語

理解內/外部鎖差異、掌握檢查修復標準協議，是 DBA 守護資料完整性的基本功。隨著 InnoDB 成為預設引擎、線上 DDL 普及，傳統鎖表操作逐漸退出歷史舞台，但對 MyISAM 存量系統、緊急搶修場景，本文協議仍是「救命稻草」。