MySQL 行鎖與表鎖深度解析：InnoDB 與 MyISAM 鎖機制對比
鎖是資料庫並發控制的核心。MySQL 不同儲存引擎支援的鎖粒度不同：InnoDB 支援行鎖與表鎖，MyISAM 僅支援表鎖。本文深度解析兩種鎖模式、InnoDB 行鎖實現原理、鎖升級條件與效能調優。
一、鎖粒度對比
| 特性 | 表鎖 | 行鎖 |
|---|
| 儲存引擎 | MyISAM、MEMORY、CSV | InnoDB、NDB Cluster |
|---|
| 鎖定對象 | 整張表 | 單行記錄（索引項） |
|---|
| 並發度 | 低（寫鎖阻塞所有讀寫） | 高（僅阻塞同一行） |
|---|
| 鎖衝突概率 | 高 | 低 |
|---|
| 開銷 | 小（鎖較少） | 大（鎖結構多、記憶體佔用高） |
|---|
| 死鎖風險 | 無 | 有 |
|---|
二、InnoDB 行鎖實現原理
核心結論： InnoDB 行鎖是通過**索引項**實現的，無索引時退化為表鎖。
1. 三種行鎖模式
- Record Lock（記錄鎖）： 鎖定單個索引記錄
- Gap Lock（間隙鎖）： 鎖定索引記錄間的間隙，防止幻讀（RR 隔離級別）
- Next-Key Lock（臨鍵鎖）： Record Lock + Gap Lock，鎖定區間，左開右閉
2. 索引與鎖的關係實驗
CREATE TABLE t (
id INT PRIMARY KEY,
name VARCHAR(20),
age INT,
INDEX idx_age (age)
) ENGINE=InnoDB;
INSERT INTO t VALUES (1, 'Alice', 20), (5, 'Bob', 25), (10, 'Cindy', 30);
| SQL | 使用索引 | 鎖類型 | 鎖定範圍 |
|---|
SELECT * FROM t WHERE id = 5 FOR UPDATE | 主鍵 | Record Lock | id=5 該行 |
SELECT * FROM t WHERE id > 5 AND id < 10 FOR UPDATE | 主鍵 | Next-Key Lock | (5, 10] 區間 |
SELECT * FROM t WHERE age = 25 FOR UPDATE | 二級索引 idx_age | Next-Key Lock | age=25 的記錄 + 間隙 |
SELECT * FROM t WHERE name = 'Bob' FOR UPDATE | 無索引（全表掃描） | **表鎖** | 全表所有行 |
三、MyISAM 表鎖機制
- 共享讀鎖（S 鎖）：
LOCK TABLES t READ，可並發讀，阻塞寫
- 排他寫鎖（X 鎖）：
LOCK TABLES t WRITE，阻塞所有讀寫
- 鎖釋放：
UNLOCK TABLES 或連線斷開
- 並發插入：
concurrent_insert 參數控制（尾部插入可並發）
四、意向鎖
InnoDB 為支援多粒度鎖，引入表級意向鎖：
- IS（意向共享鎖）： 事務準備給某些行加 S 鎖，先獲取表 IS 鎖
- IX（意向排他鎖）： 事務準備給某些行加 X 鎖，先獲取表 IX 鎖
兼容性矩陣：
| X | IX | S | IS |
|---|
| X | ✗ | ✗ | ✗ | ✗ |
| IX | ✗ | ✓ | ✗ | ✓ |
| S | ✗ | ✗ | ✓ | ✓ |
| IS | ✗ | ✓ | ✓ | ✓ |
五、鎖升級與退化場景
| 場景 | 結果 | 原因 |
|---|
| 無索引欄位 WHERE | 表鎖 | 全表掃描，無法定位行鎖 |
| 索引失效（函數/隱含轉換） | 表鎖 | 同左 |
| LIKE '%keyword%' | 表鎖 | 無法使用索引 |
| 範圍查詢匹配大量行 | 可能升級 | 優化器判斷行鎖成本過高 |
| 明確 LOCK TABLES | 表鎖 | 用戶顯式請求 |
六、效能調優建議
- 為所有 WHERE/JOIN/ORDER BY 欄位建立索引，確保行鎖生效
- 避免長事務：縮短持鎖時間，減少鎖等待
- 合理設置隔離級別：RC 級別無 Gap Lock，減少鎖衝突（需評估幻讀風險）
- 大批量操作分批提交：單批 500-1000 行
- 監控鎖指標：
SHOW ENGINE INNODB STATUS\G
-- 關注：Lock wait secs, Deadlocks, Row lock waits, Row lock time avg
SELECT * FROM performance_schema.table_lock_waits_summary_by_table;
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/lock%';
七、悲觀鎖 vs 樂觀鎖
| 維度 | 悲觀鎖 | 樂觀鎖 |
|---|
| 核心思想 | 假設衝突常見，先鎖後改 | 假設衝突稀少，改時檢查版本 |
| 實現方式 | SELECT ... FOR UPDATE | 版本號/時間戳 + CAS 更新 |
| 適用場景 | 高衝突、寫多讀少 | 低衝突、讀多寫少、分佈式 |
| 缺點 | 並發度低、易死鎖 | 重試開銷、ABA 問題 |
-- 樂觀鎖範例
UPDATE account
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 5; -- 版本不匹配則更新 0 行，應用層重試
