萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 鎖機制全景解析:表鎖、頁鎖、行鎖與死鎖應對

鎖粒度與存儲引擎對應

鎖類型粒度支持引擎死鎖風險典型場景
表鎖整張表MyISAM, MEMORY, CSV無只讀多、寫入少、全表掃描
頁鎖數據頁(16KB)BDB有歷史遺留,已少用
行鎖單行記錄InnoDB有高併發寫入、事務型業務

表鎖機制詳解

加鎖邏輯

  • 寫鎖(WRITE/EXCLUSIVE):無鎖→加鎖;有鎖→入寫鎖隊列(優先級高)
  • 讀鎖(READ/SHARED):無寫鎖→加鎖;有寫鎖→入讀鎖隊列
  • 釋放順序:寫鎖隊列優先 → 讀鎖隊列

後果:大量寫入導致讀請求饑餓;長查詢阻塞後續寫入,寫入又阻塞新讀取 → 連鎖阻塞。

表鎖優化參數

[mysqld]
low_priority_updates=1           -- 寫入降優,讀優先
max_write_lock_count=100         -- 達閾值暫時提升讀優先級
  • LOW_PRIORITY 修飾單條 INSERT/UPDATE/DELETE
  • HIGH_PRIORITY 修飾關鍵 SELECT
  • INSERT DELAYED 緩衝寫入(MyISAM 專用)
  • SQL_BUFFER_RESULT 將結果集存入臨時表,提前釋放表鎖

行鎖(InnoDB)核心機制

三種行鎖模式

  • Record Lock:鎖定單條記錄(WHERE id = 1)
  • Gap Lock:鎖定間隙,防止幻讀(WHERE id > 10)
  • Next-Key Lock:Record + Gap,RR 隔離級默認

加鎖條件

  1. 索引列 + 精確匹配 → Record Lock
  2. 索引列 + 範圍查詢 → Next-Key Lock
  3. 非索引列 → 全表掃描 + 每行 Record Lock + 間隙 Gap Lock(退化表鎖效果)
  4. 無索引 + 更新 → 鎖全表所有記錄及間隙

死鎖成因與排查

經典死鎖場景

-- 會話 A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 等待 B 持有的鎖

-- 會話 B
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;  -- 等待 A 持有的鎖
-- Deadlock found when trying to get lock; try restarting transaction

排查工具

-- 即時查看
SHOW ENGINE INNODB STATUS\G  -- LATEST DETECTED DEADLOCK 章節

-- 持久化記錄(MySQL 5.7+)
[mysqld]
innodb_print_all_deadlocks=ON
log_error=/var/log/mysql/error.log

預防原則

  1. 固定加鎖順序:所有事務按相同順序訪問表/行(如按 ID 升序)
  2. 縮短事務:少做非 DB 操作(HTTP、計算),拆分大事務
  3. 合理索引:避免無索引更新導致全表掃描鎖
  4. 低隔離級:READ COMMITTED 可減少 Gap Lock(需評估業務可接受性)
  5. 應用級鎖:GET_LOCK('key', 10) 串行化熱點邏輯

鎖監控指標

SHOW STATUS LIKE 'Table_locks%';
-- Table_locks_immediate: 立即獲取
-- Table_locks_waited: 等待次數(高則關注)

SHOW ENGINE INNODB STATUS\G
-- ROW OPERATIONS: lock struct(s), heap size, row lock(s)
-- TRANSACTIONS: LOCK WAIT, lock_mode, lock_type

選型決策:何時用表鎖?何時用行鎖?

讀多寫少、全表掃描多、無事務需求 → MyISAM 表鎖

高併發寫入、需事務、行級併發 → InnoDB 行鎖

大表批量導入/重建索引時,可臨時 LOCK TABLES WRITE 加速,結束後 UNLOCK TABLES。

copyright © 萬盛學電腦網 all rights reserved