MySQL 的鎖層級與類型

在 MySQL 中，鎖的層級和實現與存儲引擎密切相關。

---

1. 表級鎖（Table-Level Locks）

（1）存儲引擎層的表級鎖

• 實現層級：存儲引擎層（如 MyISAM、InnoDB）。
• 特點：
  • 粒度粗：鎖定整張表，并發性能較低。
  • 適用場景：適合讀多寫少或全表操作（如 MyISAM 引擎的讀/寫鎖）。
• 示例：
  • MyISAM 的表鎖：
    MyISAM 默認使用表級鎖，分為 共享讀鎖（S鎖） 和 排他寫鎖（X鎖）。
    例如，執行 SELECT 時加讀鎖，INSERT/UPDATE 時加寫鎖。
  • InnoDB 的表鎖：
    InnoDB 在特定場景（如未命中索引的全表掃描）可能升級行鎖為表級鎖。

（2）Server 層的表級鎖（元數據鎖，MDL）

• 實現層級：Server 層。
• 特點：
  • 隱式管理：由 MySQL Server 自動加鎖，用于保護表結構（DDL 操作）。
  • 鎖沖突：DML（如 SELECT/INSERT）與 DDL（如 ALTER TABLE）會觸發 MDL 鎖沖突。
• 示例：
  執行 ALTER TABLE 時，MySQL Server 會加 MDL 寫鎖，阻塞其他會話的 DML 操作。

---

2. 行級鎖（Row-Level Locks）

實現層級：存儲引擎層（僅 InnoDB 支持）。

• 特點：
  • 粒度細：僅鎖定需要操作的行，并發性能高。
  • 支持事務：通過 MVCC（多版本并發控制）實現非鎖定讀。
• 鎖類型：
  • 共享鎖（S 鎖）：允許其他事務讀，但禁止寫。
  • 排他鎖（X 鎖）：禁止其他事務讀或寫。
  • 間隙鎖（Gap Lock）：鎖定索引記錄間的間隙，防止幻讀。
• 示例：
  執行 SELECT ... FOR UPDATE 時，InnoDB 會對符合條件的行加排他鎖。

---

3. 對比表級鎖與行級鎖

特性	表級鎖	行級鎖
粒度	鎖定整張表	鎖定單行或多行
并發性能	低（鎖沖突概率高）	高（鎖沖突概率低）
存儲引擎	MyISAM（默認）、InnoDB（特殊情況）	InnoDB（默認支持）
適用場景	全表操作、讀多寫少	高并發寫、事務隔離（如 RC/RR 級別）
實現層級	存儲引擎層（MyISAM/InnoDB） + Server 層（MDL）	存儲引擎層（僅 InnoDB）

---

4. 關鍵注意事項

1. InnoDB 的鎖升級：

   • 當行鎖數量過多或未命中索引時，InnoDB 可能將行鎖升級為表級鎖。
   • 示例：UPDATE table SET col=1 WHERE unindexed_col=10（全表掃描觸發表鎖）。

2. 死鎖風險：

   • 行級鎖可能引發死鎖（如事務 A 鎖行 1，事務 B 鎖行 2，互相等待）。
   • InnoDB 會自動檢測死鎖并回滾代價較小的事務。

3. 顯式鎖與隱式鎖：

   • 顯式鎖：通過 LOCK TABLES 或 SELECT ... FOR UPDATE 手動加鎖。
   • 隱式鎖：由存儲引擎自動管理（如 DML 語句觸發的行鎖）。

---

5. 總結

• 表級鎖：
  • 存儲引擎層：MyISAM 默認使用，InnoDB 在特殊場景下觸發。
  • Server 層：元數據鎖（MDL）保護表結構。
• 行級鎖：
  • 僅 InnoDB 支持，通過 MVCC 和鎖機制實現高并發事務。
  • 默認細粒度鎖，避免全表鎖定。