| 鎖類型 | 粒度 | 引擎 | 加鎖語句 | 兼容性 |
|---|---|---|---|---|
| 表鎖 | 全表 | MyISAM/MEMORY/CSV | LOCK TABLES / 隱式 DDL | S-S 兼容,S-X/X-S/X-X 互斥 |
| 行鎖 | 單行 | InnoDB | SELECT ... FOR UPDATE / LOCK IN SHARE MODE / DML | X-X 互斥,S-S 兼容 |
| 間隙鎖 | 區間 | InnoDB (RR隔離級) | 範圍查詢 + FOR UPDATE/SHARE | 僅阻塞插入,不阻塞間隙鎖本身 |
| Next-Key Lock | 行+間隙 | InnoDB (RR隔離級) | 等值查詢 + FOR UPDATE/SHARE | 行鎖+間隙鎖組合,防止幻讀 |
| 意向鎖 | 表級 | InnoDB | 隱式(加行鎖前先加) | IS-IX 兼容,IX-IX 兼容,用於快速判斷表級是否有行鎖 |
MyISAM/MEMORY 使用表鎖時,查詢開始前一次性申請所有需要的鎖,並按固定順序(库名/表名字典序)加鎖,因此不會發生死鎖。但併發寫入性能極差。
-- 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 自動檢測死鎖並回滾代價較小的事務(通常是修改行數少的)。
READ COMMITTED 間隙鎖較少,但可能出現不可重複讀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 邏輯備份前全局只讀ALTER TABLE 會持有長時間表鎖,改用 pt-online-schema-changeInnoDB 行鎖 + RR 隔離級別 是高併發 OLTP 的最佳組合;MyISAM 表鎖僅適合只讀/少寫的報表/日志表。