MySQL 效能調優實戰（五）：深入剖析表鎖機制與實戰演示

MySQL 鎖機制概覽

MySQL 的鎖機制主要分為三大類，各有優劣，適用場景截然不同：

• 表級鎖：開銷極低、加鎖迅速、絕無死鎖風險，但鎖定粒度最粗，衝突概率最高，並發度最低。典型代表為 MyISAM 儲存引擎。
• 行級鎖：開銷較大、加鎖較慢、可能發生死鎖，但鎖定粒度最細，衝突概率最低，並發度最高。InnoDB 引擎即採用此機制。
• 頁面鎖：開銷與加鎖時間介於表鎖與行鎖之間，會出現死鎖，粒度與並發度亦居中。NDB Cluster 採用此類鎖。

一、表鎖實戰演示：MyISAM 引擎的鎖行為

MyISAM 僅支援表鎖，其讀寫鎖的互斥規則如下：

1. 讀操作（共享鎖）：多個會話可同時讀取同一張表，互不阻塞；但會阻塞其他會話對該表的寫請求，寫操作需等待所有讀鎖釋放後方能執行。
2. 寫操作（排他鎖）：會阻塞其他會話的所有讀寫請求，只有當寫鎖釋放後，其他操作才能繼續。

演示場景

會話 1 執行 LOCK TABLES t READ 後，會話 2 的 SELECT 可立即返回，但 INSERT/UPDATE/DELETE 會處於「Waiting for table metadata lock」狀態。釋放會話 1 的鎖後，會話 2 的寫操作方能繼續。

二、行鎖實戰演示：InnoDB 引擎的鎖機制

InnoDB 透過索引項實現行鎖，核心原則：只有透過索引條件檢索資料，才會使用行級鎖；否則退化為表鎖。

1. 行鎖基本行為

兩個會話同時更新同一條記錄（走主鍵索引），第二個會話會進入鎖等待，直到第一個會話提交或回滾。

2. 未命中索引導致的表鎖退化

若 WHERE 條件未使用索引（如全表掃描），InnoDB 會對整張表加鎖，嚴重降低並發度。

3. 死鎖示例與自動檢測

經典死鎖場景：會話 1 鎖住記錄 A 再請求記錄 B，會話 2 鎖住記錄 B 再請求記錄 A。

InnoDB 內建死鎖檢測器，會自動選擇一個受害者事務回滾（通常是修改較少的事務），另一事務繼續執行。死鎖無法完全避免，但可透過統一資源訪問順序、縮短事務長度、適當降低隔離級別等手段降低發生率。