MySQL 鎖粒度機制深度解析：表鎖與行級鎖的並發控制、優先級與存儲引擎層實現差異

核心概念：鎖粒度

鎖粒度決定了併發控制的精細程度。粒度越細，並發度越高，但鎖管理開銷越大。MySQL 採用插件式存儲引擎，不同引擎支援不同粒度。

一、表鎖 — MyISAM、MEMORY、CSV 預設

機制特點

• 鎖定整張表：單次操作鎖住所有行。
• 開銷極低：加鎖/解鎖快，無死鎖。
• 並發極差：寫鎖獨佔，讀鎖共享但阻塞寫。

讀寫鎖優先級與隊列行為

1. 寫鎖優先級高：寫請求插隊到讀鎖隊列前；讀請求不插隊到寫鎖前。
2. READ LOCAL 允許尾部並發插入 (MyISAM 特有)。
3. 長時間查詢會阻塞後續寫入，導致「寫飢餓」。

Server 層強制表鎖場景

無論引擎，ALTER TABLE、LOCK TABLES、FLUSH TABLES WITH READ LOCK 由 Server 層實施表鎖，忽略引擎鎖。

二、行級鎖 — InnoDB、XtraDB 核心優勢

實現層級

僅在存儲引擎層實現。MySQL Server 層不知行鎖存在，只見引擎返回的鎖等待狀態。

InnoDB 行鎖三大模式

鎖模式	鎖定範圍	目的	兼容性
記錄鎖	單個索引記錄	防止修改衝突	X 鎖互斥，S 鎖共享
間隙鎖	索引記錄間隙	防止幻讀	互不阻塞，但阻塞插入
臨鍵鎖	記錄 + 間隙	RR 級防幻讀	X 鎖互斥

特殊鎖類型

• 插入意向鎖：INSERT 前獲取的 Gap Lock 一種，允許多事務同一間隙插入不同值。
• 自增鎖：AUTO_INCREMENT 計數器鎖，語句級釋放 (非事務級)。

三、並發控制對比

維度	表鎖	行級鎖
鎖衝突概率	高 (全表)	低 (單行/間隙)
死鎖可能	無	有 (需檢測回滾)
內存開銷	極小	較大 (鎖結構數組)
適用負載	讀多寫少、小表、OLAP	高併發 OLTP、事務密集

四、最佳實踐與避坑

1. 核心業務必用 InnoDB：行級鎖 + MVCC 解決讀寫衝突。
2. 避免無索引 UPDATE/DELETE：全表掃描會鎖定所有記錄+間隙，實際退化為表鎖效果。
3. 縮小事務範圍：長事務持有鎖時間長，劇增衝突與死鎖概率。
4. 監控指標：Innodb_row_lock_waits、Innodb_row_lock_time_avg、Innodb_deadlocks。
5. 死鎖排查：SHOW ENGINE INNODB STATUS 查看 LATEST DETECTED DEADLOCK 區塊，分析鎖獲取順序，調整業務 SQL 順序統一化。