MySQL 鎖機制全解析：行鎖、表鎖與頁鎖的優劣與應用

MySQL 鎖的三大層級

MySQL 的鎖機制根據鎖定粒度可分為三個層級：頁級鎖、表級鎖與行級鎖。不同儲存引擎支援的鎖層級各不相同：

• 頁級鎖：典型代表為 BDB 引擎，鎖定粒度介於表與行之間，較少見。
• 表級鎖：MyISAM、MEMORY 及早期的 ISAM 採用此機制，鎖定整張表，實作簡單但並發性能較低。
• 行級鎖：InnoDB 為典型代表，鎖定單筆資料，並發性能最佳，是現代應用最常用的選擇。

行級鎖的優缺點權衡

優點

1. 減少鎖衝突：多連線執行不同查詢時，鎖定範圍最小，大幅降低 LOCK 等待狀態。
2. 異常恢復損失小：發生錯誤時僅需回滾少數資料列，資料損失風險極低。

缺點

1. 記憶體開銷大：需為每一行維護鎖資訊，記憶體佔用遠高於表鎖與頁鎖。
2. I/O 較多：查詢時需讀取更多索引頁面，通常建議用於寫入操作而非大量讀取。
3. 易產生死鎖：多事務交叉持有行鎖時，極易形成循環等待導致死鎖。

讀寫隊列機制

MySQL 透過寫入隊列與讀取隊列協調並發存取：

• 寫鎖邏輯：若表未加鎖 → 直接加寫鎖；否則 → 請求進入寫鎖隊列等待。
• 讀鎖邏輯：若表無寫鎖 → 加讀鎖；否則 → 請求進入讀鎖隊列等待。

可透過 LOW_PRIORITY 與 HIGH_PRIORITY 調整隊列優先順序。

實戰範例：長查詢阻塞更新

-- 客戶端 1：執行耗時查詢
SELECT COUNT(*) FROM content GROUP BY content;

-- 客戶端 2：嘗試更新（被阻塞 30 秒）
UPDATE content SET content = 'I love you' WHERE id = 444;
-- Query OK, 1 row affected (30.68 sec)

-- 終止客戶端 1 後，再次更新（僅 20 毫秒）
UPDATE content SET content = 'I hate you' WHERE id = 444;
-- Query OK, 1 row affected (0.02 sec)

解析：客戶端 1 持有讀鎖，導致客戶端 2 的更新請求進入寫鎖隊列；讀鎖釋放（SHOW PROCESSLIST 顯示 COPY TO TMP TABLE 結束）後，更新始得執行。

主從複寫中的鎖策略優化

在 Replication 架構下，可為 Master 與 Slave 配置不同鎖策略（前提是 SQL 已經過優化），以達到最佳效能平衡。