MySQL 鎖機制深度解析：行級鎖、表級鎖與頁級鎖全面比較

MySQL 鎖機制概述

MySQL 的鎖機制是其並發控制的核心，不同存儲引擎支援不同的鎖粒度，這直接影響資料庫的效能與並發能力。理解鎖的類型、特性及適用場景，對於優化資料庫效能、避免死鎖至關重要。

三大鎖類型詳細比較

1. 表級鎖

支援引擎：MyISAM、MEMORY

特性：鎖定整張資料表。讀鎖（共享鎖）允許並發讀取，但阻塞寫入；寫鎖（排他鎖）阻塞所有其他讀寫操作。開銷小、加鎖快、不會產生死鎖，但鎖定粒度大，衝突機率最高，並發度最低。

適用場景：以查詢為主、少量按索引條件更新資料的應用（如 Web 應用）。

2. 行級鎖

支援引擎：InnoDB

特性：僅鎖定指定記錄，其他進程仍可操作同表中其他記錄。開銷大、加鎖慢、可能出現死鎖，但鎖定粒度最小，衝突機率最低，並發度最高。

優點：

• 多線程訪問不同行時鎖衝突極少
• 回滾時僅有少量更改
• 可長時間鎖定單一行

缺點：

• 佔用更多記憶體
• 大部分資料操作時比表級鎖慢
• 頻繁 GROUP BY 或全表掃描時效能明顯下降

適用場景：大量按索引條件並發更新少量不同資料，同時有並發查詢的 OLTP 系統。

3. 頁級鎖

支援引擎：BDB

特性：一次鎖定相鄰的一組記錄，開銷與加鎖時間介於表鎖與行鎖之間，會出現死鎖，並發度一般。屬於折衷方案。

鎖的實現原理與死鎖問題

InnoDB 與 BDB 在 SQL 處理期間自動獲得鎖（行鎖/頁鎖），而非事務啟動時獲得，因此可能發生死鎖。MyISAM 表級鎖透過鎖隊列管理：寫鎖請求優先於讀鎖請求，可能導致大量寫操作造成查詢永久阻塞，可透過 low_priority_updates=1 調整優先級。

InnoDB 行鎖具體實現與注意事項

InnoDB 透過索引項加鎖實現行鎖，只有透過索引條件檢索資料才使用行級鎖，否則退化為表鎖。兩種行鎖模式：

• 共享鎖 (S Lock)： SELECT ... LOCK IN SHARE MODE，允許讀取，阻塞其他事務獲取排他鎖
• 排他鎖 (X Lock)： SELECT ... FOR UPDATE，允許更新，阻塞其他事務獲取共享鎖與排他鎖

為支援多粒度鎖，InnoDB 還引入意向共享鎖 (IS) 與意向排他鎖 (IX)，均為表級鎖。

實戰範例：InnoDB 行鎖與表鎖的觸發條件

-- 例1：明確指定主鍵且存在記錄 → Row Lock
SELECT * FROM products WHERE id='3' FOR UPDATE;

-- 例2：明確指定主鍵但查無記錄 → 無鎖
SELECT * FROM products WHERE id='-1' FOR UPDATE;

-- 例3：無主鍵條件 → Table Lock
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

-- 例4：主鍵不明確（不等於） → Table Lock
SELECT * FROM products WHERE id<>'3' FOR UPDATE;

-- 例5：主鍵不明確（LIKE） → Table Lock
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注意： FOR UPDATE 僅適用於 InnoDB，且必須在事務區塊 (BEGIN/COMMIT) 中生效。

效能優化相關參數

總結與選型建議

從鎖的角度選擇存儲引擎：

• 表級鎖 (MyISAM)： 適合以查詢為主、少量索引條件更新的應用
• 行級鎖 (InnoDB)： 適合高並發更新、事務處理、需外鍵支援的 OLTP 系統
• 頁級鎖 (BDB)： 折衷方案，較少使用

正確理解鎖機制，結合索引設計與 SQL 優化，才能充分發揮 MySQL 的並發效能。