鎖類型總覽
| 儲存引擎 | 預設鎖粒度 | 支援行鎖 | 備註 |
|---|
| MyISAM / MEMORY / CSV | 表鎖 | 否 | 讀鎖共享、寫鎖排他 |
| InnoDB | 行鎖 | 是 | 支援 MVCC、間隙鎖、Next-Key Lock |
| BDB (BerkeleyDB) | 頁鎖 | 否 | 已淘汰 |
表鎖機制（MyISAM 等）
- 讀鎖（共享鎖）：多執行緒可同時持有，互不阻塞。
- 寫鎖（排他鎖）：獨佔，阻塞所有其他讀/寫請求。
- 優先級：寫鎖 > 讀鎖（避免寫入飢餓）。長查詢會阻塞後續寫入，寫入又阻塞後續讀取 → 連鎖阻塞。
InnoDB 行鎖與 MVCC
行鎖模式
- 記錄鎖：鎖定單一索引記錄。
- 間隙鎖：鎖定索引記錄之間的間隙，防止幻讀。
- Next-Key Lock：記錄鎖 + 間隙鎖，預設 REPEATABLE READ 隔離級別下的鎖策略。
MVCC（多版本並發控制）
- 讀取不加鎖（快照讀），寫入加行鎖。
- 每行儲存
DB_TRX_ID（建立事務 ID）、DB_ROLL_PTR（回滾指標），構建版本鏈。
- 一致性讀：依據事務啟動時的 Read View 判斷可見版本。
應用層鎖：GET_LOCK / RELEASE_LOCK
SELECT GET_LOCK('my_lock', 10); -- 取得名為 my_lock 的鎖，等待 10 秒
-- 業務邏輯
SELECT RELEASE_LOCK('my_lock'); -- 釋放
- 建議性鎖，僅協調良好應用程式，不強制阻擋 SQL 操作。
- 常用於分散式鎖、單一執行緒執行排程任務。
表鎖競爭優化策略
| 策略 | 指令/配置 | 效果 |
|---|
| 優化 SELECT 速度 | 建立摘要表、覆蓋索引 | 縮短讀鎖持有時間 |
| 降低寫入優先級 | --low-priority-updates 啟動參數 | 讀請求優先於寫入 |
| 連線級低優先級 | SET LOW_PRIORITY_UPDATES=1 | 該連線所有寫入低優先級 |
| 單句低優先級 | INSERT LOW_PRIORITY ... / UPDATE LOW_PRIORITY ... | 該句寫入低優先級 |
| 單句高優先級查詢 | SELECT HIGH_PRIORITY ... | 該查詢插隊寫入前執行 |
| 限制寫鎖數量後提升讀優先級 | max_write_lock_count 設較小值 | 每 N 個寫鎖讓讀請求插隊 |
| 改用 InnoDB | ALTER TABLE t ENGINE=InnoDB; | 行鎖+MVCC 根本解決 |
| 並發 INSERT | MyISAM 末尾追加時支援並發讀 | 無空洞表可邊讀邊寫 |
| INSERT DELAYED | INSERT DELAYED ... | 寫入排隊，客戶端立即返回（MyISAM/InnoDB 8.0 棄用） |
| DELETE LIMIT | DELETE ... LIMIT 1000 | 分批刪減少鎖持有時間 |
| SQL_BUFFER_RESULT | SELECT SQL_BUFFER_RESULT ... | 結果存入臨時表，釋放表鎖 |
實務建議
- 核心業務表**強制使用 InnoDB**，享受行鎖 + MVCC。
- 長報表查詢 → 從庫讀 / 建立物化視圖 / 用
SQL_BUFFER_RESULT。
- 批次寫入 → 分批
LIMIT、低優先級、避開高峰期。
- 監控鎖等待：
SHOW ENGINE INNODB STATUS\G、performance_schema.data_locks（8.0+）。
