MySQL 鎖機制全解析：表鎖、行鎖、頁鎖與效能調優實戰

MySQL 鎖機制全解析：表鎖、行鎖、頁鎖與效能調優實戰

鎖是資料庫實現並發控制的核心機制。MySQL 採用插件式儲存引擎架構，不同引擎支援不同粒度的鎖：

• MyISAM / MEMORY：表級鎖
• BDB：頁級鎖
• InnoDB：行級鎖（支援表鎖、意向鎖、間隙鎖、Next-Key Lock）

鎖粒度對比

手動鎖操作（MyISAM 為例）

-- 加讀鎖（共享鎖）
LOCK TABLES t9 READ;

-- 加寫鎖（排他鎖）
LOCK TABLES t9 WRITE;

-- 查看鎖狀態
SHOW GLOBAL STATUS LIKE 'table_locks%';
-- Table_locks_immediate: 立即獲取鎖次數
-- Table_locks_waited: 等待鎖次數（越高爭用越嚴重）

-- 解鎖
UNLOCK TABLES;

MyISAM 表鎖深度解析

1. 並發插入控制

系統變數 concurrent_insert：

• 0：禁止並發插入
• 1（預設）：無空洞時允許表尾並發插入
• 2：無論有無空洞均允許表尾並發插入

LOCK TABLES t9 READ LOCAL 可在滿足並發插入條件時允許其他 Session 表尾插入。

2. 鎖調度優先級

MyISAM 寫鎖優先於讀鎖：即使讀請求先到達等待隊列，後到的寫請求會插隊到讀請求之前。大量更新會導致查詢「餓死」。

3. 緩解寫鎖飢餓的策略

• 啟動參數 low-priority-updates=1：降低更新優先級
• Session 級：SET LOW_PRIORITY_UPDATES=1
• 語句級：INSERT/UPDATE/DELETE LOW_PRIORITY ...
• 系統參數 max_write_lock_count：讀鎖達到閾值時暫時降低寫優先級
• 避免長時間運行的查詢（會阻塞寫入）

InnoDB 行鎖核心機制

1. 鎖模式

• 共享鎖（S Lock / 讀鎖）：SELECT ... LOCK IN SHARE MODE
• 排他鎖（X Lock / 寫鎖）：SELECT ... FOR UPDATE、UPDATE、DELETE、INSERT
• 意向共享鎖（IS）、意向排他鎖（IX）：表級意向鎖，協調行鎖與表鎖

2. 鎖演算法（關鍵）

• Record Lock：鎖定單條索引記錄
• Gap Lock：鎖定索引間隙，防止幻讀（RR 隔離級別）
• Next-Key Lock：Record Lock + Gap Lock，鎖定區間（前開後閉）

3. 索引與鎖的關係

• 透過主鍵索引查詢 → 鎖定主鍵索引記錄
• 透過二級索引查詢 → 鎖定二級索引記錄 + 回表鎖定主鍵索引記錄
• 無索引/全表掃描 → 升級為表鎖（鎖定所有記錄 + 間隙）

死鎖排查與預防

-- 查看最近死鎖資訊
SHOW ENGINE INNODB STATUS\G

-- 關鍵配置
innodb_lock_wait_timeout = 50  -- 鎖等待超時（秒）
innodb_deadlock_detect = ON    -- 死鎖檢測（預設開啟）

預防原則

1. 固定順序存取多張表/多行記錄
2. 拆分大事務，縮短持鎖時間
3. 合理設計索引，避免全表掃描升級表鎖
4. 使用 SELECT ... FOR UPDATE NOWAIT/SKIP LOCKED（8.0+）減少等待
5. 低衝突業務考慮樂觀鎖（版本號/時間戳）

監控指標

• Innodb_row_lock_waits：等待次數
• Innodb_row_lock_time_avg：平均等待時間
• Innodb_row_lock_time_max：最大等待時間
• Table_locks_waited（MyISAM）：表鎖等待次數