MySQL 鎖機制全景分析：表鎖、行鎖、頁鎖與優化策略

MySQL 三大存儲引擎支援不同鎖級別：MyISAM（表鎖）、BDB（頁鎖）、InnoDB（行鎖/表鎖）。理解鎖特性與調度機制，是高併發優化的基石。

三種鎖級別特性對比

MyISAM 表鎖深度解析

1. 鎖爭用監控

SHOW STATUS LIKE 'table%';
-- Table_locks_immediate: 立即獲取鎖的次數
-- Table_locks_waited: 等待鎖的次數（值高說明爭用嚴重）

2. 鎖模式與兼容性

• 表共享讀鎖（Read Lock）：SELECT 自動加。兼容讀鎖，阻塞寫鎖
• 表獨占寫鎖（Write Lock）：UPDATE/DELETE/INSERT 自動加。阻塞所有讀寫

3. 實驗驗證

場景 A：長時間讀操作期間

• 其他會話 SELECT：立即執行
• 其他會話 UPDATE：等待讀鎖釋放

場景 B：長時間寫操作期間

• 其他會話 SELECT：等待寫鎖釋放
• 其他會話 UPDATE：等待寫鎖釋放

4. 併發插入（Concurrent Insert）

受 concurrent_insert 參數控制：

5. 鎖調度優先級

MySQL 默認 寫優先於讀。可調整：

• 啟動參數 --low-priority-updates：讀優先
• SET LOW_PRIORITY_UPDATES = 1：當前連接更新降級
• 語句級 LOW_PRIORITY / HIGH_PRIORITY

警告：盲目設讀優先可能導致長查詢「餓死」寫入。根治方案是讀寫分離（主從架構）。

InnoDB 行鎖核心

1. 行鎖模式

• 共享鎖（S 鎖）：SELECT ... LOCK IN SHARE MODE，兼容 S 鎖，阻塞 X 鎖
• 排他鎖（X 鎖）：SELECT ... FOR UPDATE、UPDATE/DELETE/INSERT，阻塞 S/X 鎖

2. 索引與鎖的關係

-- 有索引：行鎖
SELECT * FROM t1 WHERE uid = 'xxx' FOR UPDATE;

-- 無索引：退化為表鎖
SELECT * FROM t1 WHERE unindexed_col = 'xxx' FOR UPDATE;

結論：行鎖必須命中索引，否則升級為表鎖，併發度驟降。

3. 間隙鎖與 Next-Key Lock

REPEATABLE READ 隔離級別下，InnoDB 使用 Next-Key Lock（記錄鎖 + 間隙鎖）防止幻讀。

4. 死鎖處理

• InnoDB 自動檢測死鎖，回滾代價較小的事務
• innodb_lock_wait_timeout（默認 50s）控制等待超時
• 應用層捕獲 ERROR 1213 (HY000) 重試

常用優化策略

1. 優化查詢：建立合適索引，減少掃描行數
2. 縮短事務：減少鎖持有時間
3. 避免全表掃描：防止行鎖升級為表鎖
4. 合理隔離級別：RC 級別減少間隙鎖，提升併發
5. 讀寫分離：主庫寫、從庫讀，物理隔離鎖衝突
6. 分表分庫：降低單表併發壓力

問題排查工具箱

-- 查看當前進程與鎖狀態
SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS;

-- 殺死阻塞進程
KILL <thread_id>;

-- 手動鎖表（慎用）
LOCK TABLES t1 WRITE;
INSERT INTO t1 VALUES (2, 'xxx');
UNLOCK TABLES;

總結

鎖是數據庫併發控制的核心。MyISAM 表鎖簡單但併發低；InnoDB 行鎖複雜但支撐高併發。實戰中：選對引擎、建對索引、縮短事務、用好隔離級別、上讀寫分離，是鎖優化的五大法寶。