萬盛學電腦網

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

MySQL 鎖機制深度原理:表鎖/行鎖/間隙鎖/Next-Key Lock、死鎖成因與優化策略

鎖類型全景圖

鎖類型粒度引擎加鎖語句兼容性
表鎖全表MyISAM/MEMORY/CSVLOCK TABLES / 隱式 DDLS-S 兼容,S-X/X-S/X-X 互斥
行鎖單行InnoDBSELECT ... FOR UPDATE / LOCK IN SHARE MODE / DMLX-X 互斥,S-S 兼容
間隙鎖區間InnoDB (RR隔離級)範圍查詢 + FOR UPDATE/SHARE僅阻塞插入,不阻塞間隙鎖本身
Next-Key Lock行+間隙InnoDB (RR隔離級)等值查詢 + FOR UPDATE/SHARE行鎖+間隙鎖組合,防止幻讀
意向鎖表級InnoDB隱式(加行鎖前先加)IS-IX 兼容,IX-IX 兼容,用於快速判斷表級是否有行鎖

表鎖無死鎖機制

MyISAM/MEMORY 使用表鎖時,查詢開始前一次性申請所有需要的鎖,並按固定順序(库名/表名字典序)加鎖,因此不會發生死鎖。但併發寫入性能極差。

InnoDB 行鎖死鎖成因與示例


-- Session A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- X鎖 id=1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 等待 X鎖 id=2

-- Session B
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- X鎖 id=2
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- 等待 X鎖 id=1
-- DEADLOCK!

InnoDB 自動檢測死鎖並回滾代價較小的事務(通常是修改行數少的)。

避免死鎖的工程化策略

  1. 統一訪問順序:所有事務按相同順序訪問表/行(如按 ID 升序)
  2. 縮短事務長度:只在必要時開啟事務,提前提交
  3. 避免長事務:大批量操作分批提交
  4. 合理索引:減少掃描行數,降低鎖範圍
  5. 低隔離級別:READ COMMITTED 間隙鎖較少,但可能出現不可重複讀
  6. 應用層重試:捕獲 Error 1213 (HY000): Deadlock found 後指數退避重試

鎖監控與診斷


-- 當前事務與鎖等待
SELECT * FROM information_schema.INNODB_TRX\G
SELECT * FROM information_schema.INNODB_LOCKS\G
SELECT * FROM information_schema.INNODB_LOCK_WAITS\G

-- 8.0+ 統一到 performance_schema
SELECT * FROM performance_schema.data_locks\G
SELECT * FROM performance_schema.data_lock_waits\G

表鎖優化殘留場景

  • LOCK TABLES t1 WRITE, t2 READ; 批量導入/導出時減少鎖開銷
  • FLUSH TABLES WITH READ LOCK 邏輯備份前全局只讀
  • MyISAM 大表 ALTER TABLE 會持有長時間表鎖,改用 pt-online-schema-change

總結:選對引擎、用好索引、統一順序、縮短事務

InnoDB 行鎖 + RR 隔離級別 是高併發 OLTP 的最佳組合;MyISAM 表鎖僅適合只讀/少寫的報表/日志表。

copyright © 萬盛學電腦網 all rights reserved