MySQL 鎖機制全景解析：表鎖、行鎖、頁鎖原理、死鎖成因與優化策略

鎖類型與存儲引擎對應

表級鎖機制（MyISAM）

加鎖邏輯

• 寫鎖：無鎖→加寫鎖；有鎖→入寫鎖隊列。
• 讀鎖：無寫鎖→加讀鎖；有寫鎖→入讀鎖隊列。
• 釋放優先級：寫鎖隊列 > 讀鎖隊列（寫優先）。

並發插入特性

MyISAM 支持無衝突並發插入（表尾追加），SELECT 與 INSERT 可並行無需加鎖。

行級鎖機制

優點

• 鎖衝突少（不同行並發）。
• 回滾開銷小。
• 可長時間鎖定單行。

缺點

• 內存開銷大。
• 大範圍掃描比表鎖慢（需獲取大量鎖）。
• GROUP BY/全表掃描場景明顯劣勢。

何時選擇表鎖優於行鎖

• 絕大多數為讀操作。
• 按主鍵精確更新/刪除單行。
• SELECT + 並發 INSERT，極少 UPDATE/DELETE。
• 大量全表掃描/GROUP BY 無寫入。

避免表鎖爭用的實用技巧

1. 優化 SELECT 速度（建摘要表）。
2. --low-priority-updates 啟動或 SET LOW_PRIORITY_UPDATES=1 降低寫優先級。
3. 單條語句用 LOW_PRIORITY/HIGH_PRIORITY。
4. 設置 max_write_lock_count 強制定期提升讀優先級。
5. 用 MyISAM 新版支持並發 INSERT/SELECT。
6. INSERT DELAYED 緩解插入刪除混合爭用。
7. DELETE LIMIT 控制掃描範圍。
8. SQL_BUFFER_RESULT 縮短表鎖持有時間。
9. 修改源碼鎖隊列為單隊列（讀寫同優先級）。

InnoDB 死鎖成因與應對

• 行鎖在 SQL 執行過程中逐步獲取，非事務開始時一次性獲取 → 易死鎖。
• 常見場景：不同索引掃描順序導致交叉等待（參考文章 90324 案例）。
• 應對：統一訪問順序、拆分大事務、適當鎖升級、應用層邏輯規避。

總結建議

大表高並發寫入選 InnoDB 行鎖；讀多寫少、全表掃描選 MyISAM 表鎖；善用 LOCK TABLES 批量操作提速；遇鎖爭用優先優化 SQL 與索引，再考慮引擎切換。