MySQL 表鎖定完整指南：內部鎖定與外部鎖定的操作協議

為什麼需要鎖表？

防止 mysqld 服務進程與維護工具（myisamchk）或多客戶端之間對同一張表的並發讀寫衝突，避免資料不一致或索引損壞。

兩大鎖定機制對照

內部鎖定標準協議（MyISAM 表維護必讀）

檢查表（READ 鎖）

mysql -e "LOCK TABLES tbl_name READ; FLUSH TABLES;"
myisamchk tbl_name
mysql -e "UNLOCK TABLES;"

修復/優化表（WRITE 鎖）

mysql -e "LOCK TABLES tbl_name WRITE; FLUSH TABLES;"
cp tbl_name.* /backup/dir/
myisamchk --recover tbl_name   # 或 OPTIMIZE TABLE tbl_name
mysql -e "FLUSH TABLES; UNLOCK TABLES;"

關鍵點：修復後必須再次 FLUSH TABLES，讓服務重新載入新索引檔（.MYI），否則服務繼續使用舊快取導致查詢錯誤。

外部鎖定啟用與排查

# 啟用外部鎖定啟動
mysqld --external-locking

# 查看狀態
mysqladmin variables | grep skip_locking
# skip_locking = OFF 表示外部鎖定生效

• skip_locking=OFF：可直接運行 myisamchk，服務與工具協作鎖定。前置需 mysqladmin flush-tables。
• skip_locking=ON：外部鎖定失效，必須停服務 或改用內部鎖定協議。

InnoDB 時代的鎖定策略

• 行級鎖 + MVCC，極少需手動 LOCK TABLES。
• 線上 DDL（ALGORITHM=INPLACE）無需鎖表。
• 備份用 mysqldump --single-transaction 或 XtraBackup（物理熱備）。
• 若必須鎖表：LOCK TABLES ... WRITE 會阻塞所有寫入，謹慎使用。

避坑清單

1. 切勿在應用程式常規邏輯中使用 LOCK TABLES，極易造成死鎖與併發崩潰。
2. 長事務 ≠ 鎖表：InnoDB 長事務持有行鎖與 Undo Log，需監控 innodb_trx。
3. MDL（Metadata Lock）：DDL 自動獲取，會阻塞後續 DML，MySQL 5.5+ 引入。
4. 備份前 FLUSH TABLES WITH READ LOCK：獲取全局讀鎖 + 關閉所有表，配合快照備份（LVM/ZFS）。