Skip to main content

QA InnoDB 锁机制

从加锁规律 → 触发 SQL → 分析命令 → 案例验证四个维度梳理 MySQL InnoDB 锁，便于面试与线上排查。

一、加锁核心规律​

1. 加锁对象 = 索引​

查询条件加锁位置说明
主键等值聚簇索引（主键）只锁主键索引那一行
唯一索引等值唯一索引 + 聚簇索引先锁唯一索引，再回表锁主键
普通索引等值普通索引 + 聚簇索引RR 下还可能出现间隙锁 / 临键锁
无索引 / 索引失效全表所有行 + 间隙相当于表锁，性能极差

2. 隔离级别决定锁范围​

隔离级别中文锁范围防幻读
READ UNCOMMITTED读未提交几乎不加锁❌
READ COMMITTED (RC)读已提交只锁行，不锁间隙❌（有幻读）
REPEATABLE READ (RR)可重复读行锁 + 间隙锁 + 临键锁✅
SERIALIZABLE串行化所有 SELECT 隐式加读锁✅

RC vs RR：

  • RC：只锁命中行，不锁范围 → 并发高，但两次查询结果可能不同（幻读）。
  • RR：锁命中行 + 锁范围（间隙）→ 并发略低，但可重复读。

3. 不同 SQL 触发的锁类型​

SQL锁类型
SELECT ...（普通查询）不加锁（快照读 / MVCC）
SELECT ... LOCK IN SHARE MODE行级共享锁（S 锁）
SELECT ... FOR UPDATE行级排他锁（X 锁）
INSERT排他锁 + 意向排他锁 + AUTO-INC 锁
UPDATE / DELETE先查后写 → 行级排他锁
ALTER TABLE元数据锁（MDL）排他锁

二、如何触发不同类型的锁​

1. 行锁（排他锁 X）​

-- 方式 1：显式加锁
BEGIN;
SELECT * FROM user WHERE id = 10 FOR UPDATE;  -- X 锁
COMMIT;

-- 方式 2：DML 隐式加锁
UPDATE user SET name = 'new' WHERE id = 10;   -- X 锁
DELETE FROM user WHERE id = 10;               -- X 锁

2. 行锁（共享锁 S）​

SELECT * FROM user WHERE id = 10 LOCK IN SHARE MODE;  -- S 锁
-- 其他事务可读，不可写（写操作会被阻塞）

3. 间隙锁（仅 RR）​

-- 假设 user 表 id 有记录：1, 5, 10, 20

-- 会话 1（RR）
BEGIN;
SELECT * FROM user WHERE id BETWEEN 5 AND 10 FOR UPDATE;

-- 会话 2（被阻塞）
INSERT INTO user(id) VALUES (7);  -- 7 在 (5,10) 间隙内

4. 元数据锁（MDL）​

-- 会话 1：长查询（持有 MDL_SHARED_READ）
SELECT SLEEP(60) FROM user;

-- 会话 2：DDL（等待 MDL_EXCLUSIVE）
ALTER TABLE user ADD COLUMN age INT;  -- 被阻塞

5. 意向锁（自动，不可见）​

UPDATE user SET name = 'x' WHERE id = 1;
-- 自动附加：行锁(X) + 意向排他锁(IX)

三、分析锁的命令（实战排查）​

1. 查看当前锁与等待（最常用）​

-- 运行中的事务
SELECT * FROM information_schema.INNODB_TRX;

-- MySQL 5.7
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- MySQL 8.0（推荐）
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

2. 查看连接状态​

SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

重点看 State 列：

State含义
Updating正在更新
Locked等待锁
Waiting for table metadata lock等待 MDL

3. InnoDB 详细锁信息​

SHOW ENGINE INNODB STATUS\G

关注 TRANSACTIONS 段落，例如：

---TRANSACTION xxxxx, ACTIVE 10 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 456 n bits 80 index PRIMARY

4. 死锁信息​

SHOW ENGINE INNODB STATUS\G
-- 搜索 "LATEST DETECTED DEADLOCK" 段落

my.cnf 可开启全部死锁日志：

innodb_print_all_deadlocks = ON

四、实战案例：一条 UPDATE 如何加锁​

场景​

CREATE TABLE user (
    id INT PRIMARY KEY,
    age INT,
    name VARCHAR(50),
    INDEX idx_age(age)
) ENGINE=InnoDB;

-- 数据：id=1,10,20,30 对应 age=5,15,25,35
-- 隔离级别：RR
UPDATE user SET name = 'updated' WHERE age BETWEEN 10 AND 25;

加锁拆解​

序号对象锁类型
1表 user意向排他锁（IX）
2索引 idx_age，age=15临键锁（行级 X）
3索引 idx_age，age=25临键锁（行级 X）
4间隙 (10,15)间隙锁（Gap）
5间隙 (15,25)间隙锁（Gap）
6聚簇索引 id=10行级 X（回表锁主键）
7聚簇索引 id=20行级 X（回表锁主键）
8表结构MDL_SHARED_WRITE

命令验证​

-- 会话 1
BEGIN;
UPDATE user SET name = 'updated' WHERE age BETWEEN 10 AND 25;
-- 暂不提交

-- 会话 2（MySQL 8.0）
SELECT * FROM performance_schema.data_locks\G

输出中应能看到与上表对应的多条锁记录。

五、规律速查表​

场景加锁行为
锁的载体加在索引上，不是「行」本身
主键等值只锁主键那一行
唯一索引等值锁唯一索引 + 回表锁主键
普通索引等值（RC）锁命中行
普通索引等值（RR）锁命中行 + 左右间隙
无索引 / 索引失效全表行 + 全间隙 ≈ 表锁
普通 SELECT不加锁（快照读）
DML自动加排他锁 + 意向锁
DDLMDL 排他锁

六、面试高频追问​

Q：怎么判断 UPDATE 是否走索引？​

EXPLAIN UPDATE user SET name = 'x' WHERE age = 20;
-- 看 key 列：有值 = 走索引；NULL = 全表扫描 → 可能锁全表

Q：如何避免索引失效导致「锁全表」？​

  • WHERE 条件字段要有索引
  • 避免对列做函数运算（如 WHERE age + 1 = 20）
  • 避免隐式类型转换（如 id 为 INT 却写 WHERE id = '10'）
  • 避免前导通配符（如 LIKE '%abc'）

Q：生产死锁如何快速定位？​

-- 1. 锁等待关系
SELECT * FROM performance_schema.data_lock_waits;

-- 2. 最早启动的事务（常为阻塞源）
SELECT * FROM information_schema.INNODB_TRX
WHERE trx_state = 'RUNNING'
ORDER BY trx_started ASC;

-- 3. 终止阻塞事务
KILL <thread_id>;

-- 4. 死锁详情
SHOW ENGINE INNODB STATUS\G

七、记忆口诀​

索引定范围，隔离定间隙；快照读不加，DML 自动 X；DDL 看 MDL，排查用 8.0 视图。