MySQL 鎖機制深度解析：表鎖、行鎖、頁鎖、死鎖與優化策略

儲存引擎鎖粒度對照

表鎖獲取邏輯（MyISAM）

WRITE LOCK:
  IF 無鎖 → 加寫鎖
  ELSE → 寫鎖隊列等待

READ LOCK:
  IF 無寫鎖 → 加讀鎖
  ELSE → 讀鎖隊列等待

釋放時：優先喚醒寫鎖隊列，再喚醒讀鎖隊列
→ 大量 UPDATE 會阻塞 SELECT

鎖爭用監控

SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |  -- 立即獲得
| Table_locks_waited    | 15324   |  -- 等待次數
+-----------------------+---------+
-- 等待比例高 → 考慮拆表、換引擎、優化索引

MyISAM 並行插入優化

• 資料檔無空洞（無刪除/更新產生空洞）→ 尾部追加，SELECT 不阻塞 INSERT
• 有空洞 → 並行插入暫停，空洞填滿後自動恢復
• 不可並行時：寫入臨時表 → LOCK TABLES real WRITE, tmp WRITE; INSERT ... SELECT; TRUNCATE tmp; UNLOCK TABLES

行級鎖優缺點（InnoDB）

表鎖優於行鎖的場景

• 絕大多數 SELECT，極少 UPDATE/DELETE
• 主鍵精確 UPDATE/DELETE（單行）
• 全表掃描/GROUP BY 無寫入

替代方案：MVCC（多版本並發控制）

InnoDB 實際採用 MVCC + 行鎖：讀不阻寫、寫不阻讀，透過版本鏈實現一致性讀。又稱「時間旅行」「寫時複製」。極端情況下記憶體佔用可能超過傳統鎖。

應用層顧問鎖

SELECT GET_LOCK('my_lock', 10);   -- 獲取，超時 10 秒
SELECT RELEASE_LOCK('my_lock');   -- 釋放
-- 僅適用於良性協作應用，非強制