MySQL 鎖機制深度解析：表鎖、行鎖、頁鎖、死鎖與效能調優全景圖

鎖是資料庫並發控制的核心機制。MySQL 不同儲存引擎支援不同粒度的鎖：MyISAM/MEMORY 表鎖、BDB 頁鎖、InnoDB 行鎖。本文系統梳理鎖類型、相容矩陣、死鎖成因與處理、鎖升級與調優策略，構建完整的 MySQL 鎖知識體系。

鎖粒度與引擎對照

表鎖機制詳解

鎖相容矩陣

鎖佇列調度策略

1. **寫鎖優先** ：寫鎖佇列優先於讀鎖佇列獲取鎖
2. **釋放時** ：先喚醒寫鎖佇列，再喚醒讀鎖佇列
3. **含義** ：大量 UPDATE 會導致 SELECT 饑餓等待

表鎖優化參數

[mysqld]
# 降低寫鎖優先級，讓讀請求插隊
low_priority_updates = 1

# 或會話級
SET LOW_PRIORITY_UPDATES = 1;

# 單條語句指定
INSERT LOW_PRIORITY INTO ...
UPDATE LOW_PRIORITY ... 
SELECT HIGH_PRIORITY ...

# 閾值觸發讀請求提權
max_write_lock_count = 100  # 連續 100 次寫鎖後暫時提升讀優先級

InnoDB 行鎖核心機制

行鎖類型

• Record Lock：鎖定單條記錄（索引項）
• Gap Lock：鎖定索引間隙，防止幻讀 (RR 隔離級別)
• Next-Key Lock：Record Lock + Gap Lock 組合，鎖定區間
• Insert Intention Lock：插入前獲取的 Gap Lock 變體，允許並發插入不同值

行鎖加鎖規則（關鍵）

1. **唯一索引等值查詢** → Record Lock（精確鎖定該行）
2. **非唯一索引等值查詢** → Next-Key Lock（鎖定區間防幻讀）
3. **主鍵/唯一索引範圍查詢** → Next-Key Lock
4. **無索引/全表掃描** → 表鎖（實際為所有行加 Next-Key Lock，極度殺並發）
5. **FOR UPDATE / LOCK IN SHARE MODE** → 顯式加行鎖

死鎖經典場景

-- Session A                          -- Session B
BEGIN;                                 BEGIN;
UPDATE account SET bal=bal-100 WHERE id=1;  -- 鎖 id=1
                                        UPDATE account SET bal=bal+100 WHERE id=2;  -- 鎖 id=2
UPDATE account SET bal=bal+100 WHERE id=2;  -- 等待 id=2
                                        UPDATE account SET bal=bal-100 WHERE id=1;  -- 等待 id=1 → 死鎖

死鎖檢測與處理

• InnoDB 自動檢測死鎖，**回滾代價較小的事務** (權重：已修改行數、鎖數、事務啟動時間)
• SHOW ENGINE INNODB STATUS\G 查看 LATEST DETECTED DEADLOCK 詳細資訊
• 應用層捕獲 ERROR 1213 (HY000): Deadlock found 重試邏輯（指數退避 + 隨機抖動）

鎖效能調優策略

1. 索引是行鎖的前提

**無索引 = 表鎖**。所有 WHERE/JOIN/ORDER BY 條件欄位必須有索引，EXPLAIN 驗證 type != ALL。

2. 縮短事務長度

• 業務邏輯移出事務（計算、外部 API 呼叫、檔案 I/O）
• 單一事務內 SQL 條數最小化
• 避免長事務阻塞 Purge 線程、導致 Undo Log 膨脹

3. 固定加鎖順序

多表操作**統一按表名/主鍵順序**加鎖，應用層封裝統一存取路徑。

4. 選擇適當隔離級別

高並發場景建議 READ COMMITTED + binlog_format=ROW，大幅減少 Gap Lock 衝突。

5. 熱點行優化

• **計數器表拆分**：單行計數 → 多行分段計數 + 彙總
• **樂觀鎖**：UPDATE ... SET val=val+1, version=version+1 WHERE id=? AND version=?
• **佇列化**：高寫入熱點寫入 Redis/消息佇列，異步批次落庫

6. 監控指標

-- 當前鎖等待
SELECT * FROM performance_schema.data_locks;
SELECT * FROM sys.schema_table_lock_waits;
SELECT * FROM sys.innodb_lock_waits;

-- 歷史死鎖
SHOW ENGINE INNODB STATUS\G

-- 表鎖統計
SHOW STATUS LIKE 'Table_locks%';
-- Table_locks_immediate: 立即獲得
-- Table_locks_waited: 等待過的

應用層顯式鎖：GET_LOCK / RELEASE_LOCK

-- 獲取命名鎖 (阻塞至取得或超時)
SELECT GET_LOCK('order_process_123', 10);  -- 返回 1 成功

-- 業務邏輯
...

-- 釋放
SELECT RELEASE_LOCK('order_process_123');

用於**分散式業務邏輯互斥**（如訂單處理、排程單例），不依賴資料表記錄。

總結

MySQL 鎖機制的核心矛盾：**一致性 vs 並發度**。InnoDB 行鎖配合 MVCC 實現了高並發下的 ACID，但也帶來了死鎖、Gap Lock 衝突、熱點行競爭等複雜現象。掌握**加鎖規則、索引設計、事務縮短、隔離級別選擇、監控體系**五大支柱，才能駕馭鎖而非被鎖困住。