MySQL 表鎖定機制全面解析
MySQL 提供兩層鎖定機制：內部鎖（服務器層，邏輯鎖）與 外部鎖（文件系統層，物理鎖）。理解兩者差異、優先級及配合 myisamchk 的協議，是避免併發衝突、安全維護表的關鍵。
一、內部鎖（邏輯鎖）
語法與基本規則
LOCK TABLES tbl_name {READ | WRITE} [, tbl_name {READ | WRITE} ...]
UNLOCK TABLES
LOCK TABLES 為**當前線程**獲取鎖；UNLOCK TABLES 釋放當前線程所有鎖。- 新的
LOCK TABLES 或連接關閉會自動釋放舊鎖。
- READ 鎖：持鎖線程及其他線程可讀，**不可寫**。
- WRITE 鎖：僅持鎖線程可讀寫，其他線程完全阻塞。
- 無超時機制，線程會一直等待直到獲得所有請求的鎖。
- 優先級：WRITE 鎖 > READ 鎖。寫請求插隊會阻塞後續讀請求，保障寫入及時性。
檢查與修復的鎖協議
| 操作 | 所需鎖 | 步驟 |
|---|
| 檢查表 | READ |
LOCK TABLE tbl_name READ;FLUSH TABLES;（關閉文件、刷新緩存）- 運行
myisamchk tbl_name
UNLOCK TABLES;
|
| 修復/優化表 | WRITE |
LOCK TABLE tbl_name WRITE;FLUSH TABLES;- 備份文件：
cp tbl_name.* /backup/dir
- 運行
myisamchk --recover tbl_name
FLUSH TABLES;（使服務器感知新索引）UNLOCK TABLES;
|
二、外部鎖（文件級鎖）
防止**其他進程**（如另一個 mysqld 實例、myisamchk）在服務器使用表時修改文件。
- 啟用狀態：運行
mysqladmin variables 檢查 skip_locking（舊版為 skip_external_locking）。
- OFF：外部鎖啟用，服務器與工具協作訪問。運行工具前需
mysqladmin flush-tables。
- ON：外部鎖禁用（Linux 默認）。此時必須關閉服務器或使用內部鎖協議確保獨佔訪問。
三、關鍵注意事項
- 死鎖風險：多表鎖定時，所有線程必須按**相同順序**獲取鎖（MySQL 內部已自動按名排序）。
- 鎖粒度：表鎖並發度低，InnoDB 行鎖更適合高併發寫入場景。
- 維護窗口：大表修復耗時長，規劃維護窗口，避免業務高峰期。
- 備份優先：任何修復操作前，務必備份
.MYD、.MYI、.frm。
總結
內部鎖是應用層併發控制的核心；外部鎖是多進程文件保護的補充。現代部署多採用 skip_external_locking=ON，配合內部鎖協議或停機維護，簡單可靠。
