MySQL 鎖機制深度解析：頁鎖、表鎖、行鎖特性與死鎖防範

三大鎖級別對照

行鎖優缺點詳析

優點

• 減少鎖衝突，提升高並發寫入吞吐
• 異常回滾範圍小，資料損失最小化

缺點

• 記憶體開銷大（每行一個鎖結構）
• I/O 次數多，查詢效能較表鎖低
• 易發生死鎖，需應用層處理重試邏輯

MySQL 讀寫隊列機制

• 寫鎖請求：無鎖 → 加寫鎖；有鎖 → 進寫鎖隊列等待
• 讀鎖請求：無寫鎖 → 加讀鎖；有寫鎖 → 進讀鎖隊列等待
• 可用 LOW_PRIORITY / HIGH_PRIORITY 調整優先序

實戰案例：長查詢阻塞更新

-- 客戶端 1：長時間查詢（持有讀鎖）
SELECT COUNT(*) FROM content GROUP BY content;

-- 客戶端 2：更新被阻塞 30 秒
UPDATE content SET content='I love you' WHERE id=444;

-- 客戶端 1 結束後，客戶端 2 瞬間完成（0.02 秒）
UPDATE content SET content='I hate you' WHERE id=444;

說明：讀鎖釋放前，寫請求排隊；讀鎖釋放後，寫請求立即獲得鎖執行。

複寫架構鎖策略

主庫採用行鎖（InnoDB）保障寫入並發；從庫可考慮表鎖（MyISAM）加速報表查詢，前提是 SQL 已優化且無寫入衝突。

死鎖防範清單

• 固定存取順序（如按主鍵排序更新多表）
• 縮短事務持有時間，避免長事務
• 使用 SELECT ... FOR UPDATE 明確加鎖
• 設定 innodb_lock_wait_timeout 避免無限等待
• 監控 SHOW ENGINE INNODB STATUS 分析死鎖日誌