為了確保資料的一致性與完整性，任何資料庫系統都必須實作鎖定機制。鎖定機制的優劣直接決定了資料庫的並發處理能力與整體效能，因此也是各類資料庫核心技術的關鍵所在。本文將深入分析 MySQL 中最常用的兩大儲存引擎——MyISAM 與 InnoDB——各自的鎖定機制原理、特性與優化策略。
資料庫鎖定機制本質上是為了保證資料一致性，在並發存取共享資源時強制執行有序存取的一套規則。MySQL 因其獨特的插件式儲存引擎架構，不同引擎針對不同應用場景優化了專屬的鎖定機制，因此各引擎的鎖定策略差異極大。
整體而言，MySQL 各儲存引擎採用三種鎖定粒度：行級鎖、頁級鎖 與 表級鎖。以下逐一分析其特點與優劣。
行級鎖的鎖定粒度最細，也是目前主流資料庫實作的最小鎖定單位。由於粒度極小，鎖定資源爭用機率最低，能最大程度發揮並發處理能力，適合高並發系統。但缺點也明顯：獲取與釋放鎖的開銷最大，且最容易發生死鎖。
表級鎖粒度最大，實作邏輯最簡單，系統負面影響最小，獲取與釋放速度極快。因一次鎖定整張表，自然避免了死鎖問題。但鎖定粒度大導致資源爭用機率最高，嚴重制約並發度。
頁級鎖介於行級與表級之間，MySQL 中較為獨特（主要由 BerkeleyDB 引擎使用）。其資源開銷與並發能力均介於兩者之間，同樣可能發生死鎖。
隨著鎖定粒度縮小，鎖定相同資料量所需記憶體增加、演算法複雜度提高，但應用請求遇到鎖等待的機率降低，系統整體並發度提升。
在 MySQL 中：MyISAM、Memory、CSV 等非事務引擎採用表級鎖；InnoDB、NDB Cluster 採用行級鎖；BerkeleyDB 則採用頁級鎖。這一演進歷程反映了 MySQL 早期試圖實作統一鎖管理器，後因新引擎引入（如 BerkeleyDB 的頁級鎖、InnoDB 的行級鎖）不得不開放鎖定決策權給儲存引擎自行實作。
MySQL 表級鎖主要分為讀鎖與寫鎖，內部透過四個佇列管理：
雖使用者僅見讀/寫兩類，但 MySQL 內部實際定義了 11 種鎖類型（由 thr_lock_type 列舉），包含 READ、WRITE、READ_WITH_SHARED_LOCKS、WRITE_ALLOW_WRITE、WRITE_CONCURRENT_INSERT 等特殊類型，支援 Concurrent Insert、DDL 線上操作、延遲插入等進階場景。
滿足即授予，否則進入等待佇列。
檢查當前寫鎖佇列 → 檢查等待寫鎖佇列 → 檢查當前讀鎖佇列。特殊情況（WRITE_DELAYED、WRITE_CONCURRENT_INSERT 等）可直接獲取。優先級規則：寫鎖預設高於讀鎖，READ_HIGH_PRIORITY 可阻塞寫鎖，其他寫鎖類型優先級低於讀鎖。
MyISAM 完全依賴 MySQL 內建表級鎖，為支援 Concurrent Insert，MySQL 在原有機制基礎上擴充實作。其他事務型引擎（InnoDB、NDB Cluster、BerkeleyDB）則接管鎖管理，MySQL 僅持有 WRITE_ALLOW_WRITE 類型鎖。
行級鎖由儲存引擎自行實作，InnoDB 作為最廣泛使用的事務型引擎，其鎖機制與 Oracle 頗為相似。
相容性矩陣：
| S | X | IS | IX | |
|---|---|---|---|---|
| S | 兼容 | 衝突 | 兼容 | 衝突 |
| X | 衝突 | 衝突 | 衝突 | 衝突 |
| IS | 兼容 | 衝突 | 兼容 | 兼容 |
| IX | 衝突 | 衝突 | 兼容 | 兼容 |
InnoDB 透過索引實作鎖定：在索引鍵值前後的間隙標記鎖資訊，稱為「Next-Key Locking」。範圍查詢會鎖定整個範圍內所有索引鍵值（含不存在的），以防止幻讀。但此機制有副作用：鎖定不存在的鍵值會阻塞範圍內的插入操作，特定場景下嚴重影響效能。
InnoDB 實作 SQL-92 標準四種隔離級別，並透過 MVCC 保證一致性。內建死鎖偵測機制，偵測到死鎖時會回滾「較小」事務（以修改行數判斷），保留較大事務。跨引擎死鎖無法偵測，僅能靠鎖超時解決。
以下案例展示典型鎖行為（環境：test_innodb_lock(a int, b varchar(16), index(a), index(b))）：
Session A 更新 a=1 未提交 → Session B 更新同行阻塞 → A 提交 → B 獲得鎖繼續。
Session A 更新 b=2000（有索引） → Session B 更新 b=3000 阻塞 → 原因：InnoDB 無法精確鎖定行，升級為表鎖。
Session A 更新 a < 4 and a > 1 → Session B 插入 a=2 阻塞 → 範圍鎖鎖定了不存在的鍵值 2。
Session A 更新 a=1 and b='b2' → Session B 更新 a=1 and b='b1' 阻塞 → 同一索引鍵 a=1 下的間隙鎖衝突。
Session A 更新 t1.id=11 → Session B 更新 t2.id=21 → A 更新 t2.id=21 等待 B → B 更新 t1.id=11 等待 A → 循環等待觸發死鎖，InnoDB 回滾較小事務。
OPTIMIZE TABLE 整理碎片參數 concurrent_insert：
2：允許尾部並發插入（忽略中間空洞）1（預設）：無空洞時允許尾部插入0：禁用建議設為 1，刪除量大時避免設為 2 造成空間浪費與掃描開銷。
讀密集型系統可設 low_priority_updates=1 降低寫優先級；寫密集型保持預設。
SHOW STATUS LIKE 'table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 100 |
| Table_locks_waited | 0 |
+-----------------------+-------+
Table_locks_immediate：立即獲取鎖次數Table_locks_waited：等待鎖次數（偏高需優化）SHOW STATUS LIKE 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 490578 |
| Innodb_row_lock_time_avg | 37736 |
| Innodb_row_lock_time_max | 121411 |
| Innodb_row_lock_waits | 13 |
+-------------------------------+--------+
重點關注：Innodb_row_lock_waits（總等待次數）、Innodb_row_lock_time_avg（平均等待）、Innodb_row_lock_time（總等待時間）。
CREATE TABLE innodb_monitor(a int) ENGINE=InnoDB;
SHOW ENGINE INNODB STATUS;
建立 innodb_monitor 表可啟用詳細監控，資訊寫入 error log 供分析。
本文系統梳理了 MySQL 鎖定機制的演進脈絡，深度剖析表級鎖與行級鎖的實作原理、InnoDB 間隙鎖特性與死鎖處理，並給出針對性優化策略與監控手段。理解鎖機制是應對高並發、解決效能瓶頸的基石，期望能助你在實戰中遊刃有餘。
