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

為什麼需要表鎖定？

並發寫入導致資料損壞、讀取髒資料、維護工具與服務衝突。鎖定協調存取，確保一致性。

兩種鎖定機制

1. 內部鎖定（SQL 層，Server 管理）

LOCK TABLES tbl_name {READ | WRITE} [, tbl_name {READ | WRITE}] ...
UNLOCK TABLES

• READ：當前線程可讀，其他線程可讀，**不可寫**
• WRITE：僅當前線程可讀寫，**其他線程阻塞**
• 優先級：**WRITE > READ**，寫請求插隊，後續 READ 等待 WRITE 釋放
• 自動釋放：新 LOCK TABLES、連線關閉、UNLOCK TABLES
• 用途：myisamchk 檢查/修復協作、應用層顯式鎖表

2. 外部鎖定（檔案系統層，OS 管理）

• 又稱檔案鎖，myisamchk/isamchk 與 Server 協作用
• --skip-locking（Linux 預設啟用）= **禁用外部鎖**
• 驗證：mysqladmin variables | grep skip_locking

檢查表鎖定協議（唯讀場景）

# 1. 客戶端獲取讀鎖 + 刷新
mysql -u root -p db_name
LOCK TABLE tbl_name READ;
FLUSH TABLES;  -- 關閉檔案句柄，落盤脏頁

# 2. 離線工具檢查
myisamchk tbl_name
isamchk tbl_name

# 3. 釋放鎖
UNLOCK TABLES;

修復/優化表鎖定協議（寫入場景）

# 1. 獲取寫鎖 + 刷新
LOCK TABLE tbl_name WRITE;
FLUSH TABLES;

# 2. 備份原檔 + 修復
cp tbl_name.* /backup/dir/
myisamchk --recover tbl_name
# 或優化
myisamchk --analyze tbl_name

# 3. 二次刷新 + 釋放（關鍵：新索引檔需被 Server 感知）
FLUSH TABLES;
UNLOCK TABLES;

外部鎖定啟用時的協作流程

1. mysqladmin flush-tables 刷新並關閉表
2. 直接執行 myisamchk，工具與 Server 透過檔案鎖自動協調
3. 修復同樣需備份、二次 FLUSH TABLES

外部鎖定禁用時的替代方案

• 最佳：停止服務 離線修復
• 次佳：內部鎖定協議（上文流程）+ **確保無其他客戶端存取**
• 禁忌：服務運行中、無鎖保護、直接跑 myisamchk → 索引損壞風險極高

InnoDB 表的特殊說明

InnoDB 使用行鎖 + MVCC，**不適用** LOCK TABLES / myisamchk。備份用 mysqldump --single-transaction，修復用 innodb_force_recovery 重啟導出重建。

現代替代：線上結構變更工具

• pt-online-schema-change (Percona)：影子表 + 觸發器同步，零鎖定 DDL
• gh-ost (GitHub)：無觸發器，Binlog 同步，更安全
• MySQL 8.0+ 內建 INSTANT DDL、RENAME COLUMN 等無鎖操作