MySQL 表鎖機制詳解：內部鎖與外部鎖、LOCK TABLES 實戰與維護協議

兩種鎖機制

內部鎖：LOCK TABLES 語法

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

-- 解鎖
UNLOCK TABLES

鎖類型行為

優先級規則：WRITE 等待隊列優先於 READ，避免寫入飢餓。

自動釋放場景

• 連線斷開
• 執行新的 LOCK TABLES
• 執行 UNLOCK TABLES

維護操作鎖協議

檢查表（僅讀）

mysql> LOCK TABLE tbl_name READ;
mysql> FLUSH TABLES;  -- 關閉檔案、刷新快取到磁碟
# 另一終端
$ myisamchk tbl_name
# 完成後
mysql> UNLOCK TABLES;

修復/優化表（需寫入）

mysql> LOCK TABLE tbl_name WRITE;
mysql> FLUSH TABLES;

# 備份原檔
$ cp tbl_name.* /backup/

# 修復
$ myisamchk --recover tbl_name
# 或優化
$ myisamchk --analyze tbl_name

# 重新載入索引快取
mysql> FLUSH TABLES;
mysql> UNLOCK TABLES;

外部鎖協議

# 檢查外部鎖狀態
mysqladmin variables | grep skip_locking

# skip_locking = OFF：外部鎖啟用
# 可直接執行 myisamchk，服務與工具協作

# skip_locking = ON：外部鎖禁用
# 必須用內部鎖協議，或停止服務

InnoDB 差異說明

• InnoDB 使用行級鎖，LOCK TABLES 仍會加表級鎖但極少使用
• 維護操作：CHECK TABLE、OPTIMIZE TABLE、ANALYZE TABLE 線上執行，無需手工加鎖
• 備份建議：mysqldump --single-transaction 或 Percona XtraBackup 熱備份

最佳實踐

1. 應用層避免使用 LOCK TABLES，用行級鎖/交易控制並發
2. 維護窗口期執行 DDL/優化，配合 pt-online-schema-change 線上變更
3. MyISAM 表定期 mysqlcheck -A --auto-repair
4. 監控 SHOW OPEN TABLES WHERE In_use > 0 發現長鎖

1. 上一篇文章： 編寫高質量高性能 MySQL 語句核心原則
2. 下一篇文章： MySQL 內建複寫實現高可用：主從配置、雙主互備與 PHP 故障切換

MySQL 綜合教學

MySQL 中文亂碼存儲根因排查全記錄：從 Binlog 到字符集鏈路

MySQL 中文亂碼存儲根因排查全記錄：從 Binlog 到

MySQL Thread Cache 深度解析：原理、監控指標與調優實戰

MySQL 每個連線對應一個執行緒，頻繁建立/銷毀執行緒會消

MySQL 解壓版安裝與配置全紀錄：環境變數、my.ini、密碼修改

MySQL 解壓版安裝與配置全紀錄：環境變數、my.ini、

SQL Server 索引使用情況監控：兩大 DMV 深度解析

核心 DMV sys.dm_db_index_operat