MySQL 鎖機制完整解析：三級鎖、行鎖優缺點、讀寫隊列與複制場景應用

三級鎖粒度對照

行鎖優缺點深度分析

優點

1. 減少鎖衝突：多連線操作不同行時幾乎無阻塞
2. 細粒度回滾：異常時僅回滾受影響行，資料損失最小

缺點

1. 記憶體開銷大：每行鎖需記憶體結構
2. I/O 開銷大：掃描索引加鎖比全表鎖慢，讀操作通常不加行鎖（MVCC 快照讀）
3. 易死鎖：循環等待資源，需應用層避免（固定順序訪問表/行）

讀寫隊列機制

MySQL 內部維護兩個佇列：

• 寫鎖佇列：表無鎖 → 加寫鎖；否則入佇列等待
• 讀鎖佇列：表無寫鎖 → 加讀鎖；否則入佇列等待

LOW_PRIORITY / HIGH_PRIORITY 可改變預設行為。

實戰演示：長查詢阻塞寫入

-- 客戶端 1：長時間查詢 (約 30 秒)
SELECT COUNT(*) FROM content GROUP BY content;

-- 客戶端 2：更新同表
UPDATE content SET content='I love you' WHERE id=444;
-- 等待 30.68 秒才執行 (排隊)

-- 客戶端 1 結束後
UPDATE content SET content='I hate you' WHERE id=444;
-- 僅 0.02 秒 (無阻塞)

流程解析

1. 客戶端 1 獲得讀鎖 (表無鎖)
2. 客戶端 2 請求寫鎖 → 表有讀鎖 → 進入寫鎖佇列
3. 客戶端 1 釋放讀鎖 (COPY TO TMP TABLE 階段)
4. 客戶端 2 獲得寫鎖 → 執行更新

複制場景鎖策略

• Master：高併發寫入 → InnoDB 行鎖最佳
• Slave：單執行緒重放 (SQL Thread) → 可用表鎖引擎或調整 slave_parallel_workers (5.6+)
• 前提：SQL 已優化，鎖策略才能發揮最大效能

死鎖預防最佳實踐

1. 固定順序訪問多張表
2. 固定順序訪問同表多行 (如按 ID 排序)
3. 縮短事務範圍，避免長事務
4. 設定 innodb_lock_wait_timeout (預設 50s) 避免無限等待
5. 監控 SHOW ENGINE INNODB STATUS 中的 LATEST DETECTED DEADLOCK