三種鎖粒度核心差異
| 鎖類型 | 代表引擎 | 鎖定範圍 | 併發度 | 記憶體開銷 | 死鎖風險 |
|---|
| 表級鎖 | MyISAM, MEMORY, CSV | 整張表 | 低 | 極小 | 無 |
| 頁級鎖 | BDB (已淘汰) | 資料頁（通常 8-16KB） | 中 | 中 | 可能 |
| 行級鎖 | InnoDB | 單行記錄 | 高 | 大 | 可能 |
行級鎖優缺點深度剖析
優點
- 高併發：不同線程操作不同行幾乎無衝突。
- 回滾開銷小：僅需撤銷該行變更。
- 支援長時間單行鎖定（如 `SELECT ... FOR UPDATE`）。
缺點
- 記憶體消耗大：每行鎖需維護鎖結構。
- 大量表全掃描/GROUP BY 時，鎖升級開銷超過表級鎖。
- 頻繁大範圍操作效能劣於較粗粒度鎖。
表級鎖適用的五大場景
- 以讀取為主（SELECT 佔多數）。
- 嚴格索引條件下的單行更新/刪除（`WHERE unique_key = val`）。
- SELECT 與 INSERT 併發，極少 UPDATE/DELETE。
- 大量全表掃描與 GROUP BY，無寫入操作。
- 需支援「版本化」並發存取（如時間旅行、寫時複製）。
實務優化建議
- 高併發寫入場景：優先選 InnoDB（行級鎖 + MVCC）。
- 唯讀/以讀為主報表庫：MyISAM 表級鎖開銷極低。
- 避免長事務持有大量行鎖，導致鎖等待與死鎖。
- InnoDB 參數
innodb_lock_wait_timeout（預設 50s）依業務調整。
避免死鎖的常見模式
-- 固定順序存取多表
LOCK TABLES real_table WRITE, insert_table WRITE;
INSERT INTO real_table SELECT * FROM insert_table;
TRUNCATE TABLE insert_table;
UNLOCK TABLES;
以固定順序鎖定資源、縮短事務範圍、適當索引，可大幅降低死鎖機率。
