MySQL行锁 - 行锁原理实例分析

1、简单SQL的加锁分析

1.1、数据准备

---

# 建表
CREATE TABLE student (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(10),
	age INT,
	score INT
);
# 创建索引
ALTER TABLE student ADD UNIQUE INDEX uk_student_name(name);
ALTER TABLE student ADD INDEX idx_student_age(age);
# 添加测试数据
INSERT INTO student VALUES (1, 'zhangsan', 18, 80);
INSERT INTO student VALUES (2, 'lisi', 18, 91);
INSERT INTO student VALUES (3, 'wangwu', 19, 97);
INSERT INTO student VALUES (4, 'zhaoliu', 19, 91);
INSERT INTO student VALUES (5, 'tianqi', 20, 100);

1.2、RC隔离级别下DML条件

1.2.1、主键

---

DELETE FROM student WHERE id=3;

该语句查询到的结果为：

由图可见：在RC隔离级别下，若DML语句中条件包含主键时，会为该主键对应记录添加X锁。

1.2.2、非主键唯一索引

---

DELETE FROM student WHERE name='wangwu';

该语句的查询结果为：

由图可见，此组合中，name为非主键唯一索引列，因此delete语句会选择走name列的索引进行where条件的过滤，在找到name='wangwu'的记录后，首先会将unique索引上的name='wangwu'索引记录加上X锁。同时，会根据读取到的id列，回主键索引(聚簇索引)，然后将聚簇索引上的id=3对应的主键索引项加X锁。

• 为什么聚簇索引上的记录也要加锁？
  试想一下，如果并发的一个SQL，是通过主键索引来更新：

  UPDATE student SET name='sunba' WHERE id=3;
  

  此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新 / 删除需要串行执行的约束。

综上所述：在RC隔离级别下，若DML语句中条件包含非主键唯一索引时，那么SQL需要加两个X锁：一个是唯一索引条件对应记录，另一把锁对应于聚簇索引上主键索引中的记录。

1.2.3、非唯一索引

---

DELETE FROM student WHERE age=19;

该语句查询结果如下：

由图可见：age 列索引上，满足age=19查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。它与非主键唯一索引唯一的区别在于，非主键唯一索引最多只有一个满足等值查询的记录，而非唯一索引会将所有满足查询条件的记录都加锁。

综上所述：在RC隔离级别下，若DML语句中条件包含非唯一索引时，那么对应的所有满足SQL查询条件的索引，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

1.2.4、无索引

---

DELETE FROM student WHERE score=91;

该语句查询结果如下：

由于 score 列上没有索引，因此只能走聚簇索引，进行全表扫描。
由图可见：满足删除条件的记录有两条，但是，聚簇索引上所有的记录，都被加上了X锁。无论记录是否满足条件，全部被加上X锁。且既不是加表锁，也不是在满足条件的记录上加行锁。

该现象这是由于MySQL的实现决定的：如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。因此也就把所有的记录都锁上了。在实际的实现中，MySQL有一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录对应的锁释放（但违背了2PL的约束）。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。

综上所述：在RC隔离级别下，若DML语句中条件没有包含索引时，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的是满足条件的记录上的锁，但是不满足条件的记录上的加锁 / 放锁动作不会省略。同时，优化也违背了2PL的约束。

1.3、RR隔离级别下DML条件

1.3.1、主键

---

结论：与RC级别下主键加锁方式一致：在RR隔离级别下，若DML语句中条件包含主键时，会为该主键对应记录添加X锁。

1.3.2、非主键唯一索引

---

结论：与RC级别下非主键唯一索引加锁方式一致：在RR隔离级别下，若DML语句中条件包含非主键唯一索引时，那么SQL需要加两个X锁：一个是唯一索引条件对应记录，另一把锁对应于聚簇索引上主键索引中的记录。

1.3.3、非唯一索引

---

DELETE FROM student WHERE age=19;

MySQL的RR隔离级别在RC隔离级别的基础上解决了“幻读”的问题，我们回顾一下幻读的定义：

幻读就是在一个事务内，因读取到另一个事务已提交的insert数据或者delete数据，导致在该事务内，对同一张表读取两次以上的结果不一致。

那么RR隔离级别下，如何防止幻读呢？如下图：

如图所示：RR级别下非唯一索引与RC级别下非唯一索引最大的区别就在于，在非唯一索引树（Secondary Key）中，多了GAP锁，即间隙锁。RR级别正是通过间隙锁的存在，解决了幻读的问题。那么间隙锁是如何解决幻读的问题呢？

由幻读定义我们可以得出结论：在一个事务内连续做多次当前读，那么这多次当前读返回的记录应该是完全相同的。也就是说不会比之前返回更多或更少的记录。

如何保证两次当前读返回一致的记录呢？我们可以想象，在一个事务内多次进行当前读（假设当前读的条件为A）的过程中，不允许其他任何满足条件A的DML语句执行，这样就可以有效地解决幻读的问题。为了实现这个功能，GAP锁应运而生。

基于B+树索引的有序性，我们从图中次要索引（Secondary Key）中出发：（此处不要较真，只考虑可能性）
（1）记录(18, 2)之前，不会插入 age = 19（即满足条件）条件之内的值。
（2）记录(18, 2)与(19, 3)之间，可以插入 (19, 1)、(19, 2)等记录。
（3）记录(19, 3)与(19, 4)之间，可以插入(19, 3.1)、(19, 3.2)等记录。
（4）记录(19, 4)与(20, 5)之间，可以插入(19, 5)、(19, 6)等记录。
（5）记录(20, 5)之后，不会插入 age = 19（即满足条件）条件之内的值。
MySQL选择使用GAP锁，可以插入满足数据的三个间隙，避免后续DML操作可以操作到范围之内的间隙。

那么为什么RR隔离级别下，主键索引与非主键唯一索引不需要GAP锁呢？这就要从GAP存在的目的说起：GAP锁的目的是为了防止一个事务内的多次当前读，出现幻读的情况。而主键索引、非主键唯一索引都能够保证数据的唯一性。在一个等值查询中，最多只能返回一条指定记录。所以只需要添加行锁来避免不可重复读即可。

综上所述：在RR隔离级别下，若DML语句中条件包含非唯一索引时，首先通过索引在次要索引上（Secondary Key）定位到第一条满足查询条件的索引记录，并在索引记录上添加X锁，GAP上添加GAP锁，主键索引上添加X锁。然后返回读取下一条…重复进行直至进行到第一条不满足条件的记录，此时该记录上不需要添加X锁，但仍需要添加GAP锁。最后返回结束。

1.3.4、无索引

---

DELETE FROM student WHERE score=91;

查询结果如下：

如图所示：这是一个很恐怖的现象！首先，聚簇索引上的所有记录，都被加上了X锁。其次，聚簇索引每条记录间的间隙也同时被加上了GAP锁。这个示例表，只有5条记录，一共需要5个记录锁，6个GAP锁。试想，如果表上有1000万条记录呢？

在这种情况下，这个表上，除了不加锁的快照读，其他任何加锁的并发SQL，均不能执行，不能更新，不能删除，不能插入，全表被锁死。

当然，对于此，MySQL做了一些优化：就是所谓的semi-consistent read。semi-consistent read开启的情况下，对于不满足查询条件的记录，MySQL会提前放锁。针对上面的这个用例，就是除了记录(2, lisi, 18, 91)、(4, zhaoliu, 19, 91)之外，所有的记录锁都会被释放，同时不加GAP锁。在RC和RR级别下，可以通过设置参数innodb_locks_unsafe_for_binlog来控制是否开启semi-consistent read。

综上所述：在RR隔离级别下，若DML语句中条件不包含索引时，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发更新 / 删除 / 插入操作。MySQL提供了semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。

1.4、Serializable隔离级别下DML条件

对于DML语句而言，Serializable隔离级别与RR隔离级别一样，此处不再赘述。

SELECT * FROM student WHERE id=3;

在 RC 与 RR 隔离级别下，简单SELECT语句都是通过快照读的方式得到结果，不需要加锁。然而在Serializable隔离级别下，以上SELECT语句会加读锁，也就是说不可以用快照读的方式。MVCC并发控制降级为 Lock-Based CC。
故综上所述：在MySQL（InnoDB）中，所谓的“读不加锁”，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别中“读不加锁”就不再成立，所有的读操作，都是当前读。

2、复杂SQL的加锁分析

2.1、数据准备

---

# 建表
CREATE TABLE score (
	std_id INT PRIMARY KEY, 
	chinese INT,
	math INT,
	remark VARCHAR(10)
);
# 创建索引
ALTER TABLE score ADD INDEX idx_subject_score(chinese, math);
# 添加测试数据
INSERT INTO score VALUES (1, 40, 40, 'BAD');
INSERT INTO score VALUES (2, 42, 49, NULL);
INSERT INTO score VALUES (3, 48, 49, 'WELL');
INSERT INTO score VALUES (4, 47, 44, NULL);
INSERT INTO score VALUES (5, 50, 50, 'GREAT');

2.2、RR隔离级别

DELETE FROM score WHERE chinese>40 AND chinese<50 AND math=49 AND remark IS NOT NULL;

在RR隔离级别下，SQL走的是idx_subject_score索引，在索引执行计划的Extra参数中我们提到Index Key、Index Filter、Table Filter的概念，那么对于以上的SQL语句，我们可以得到分析结果：

1. Index Key：chinese>40 AND chinese<50，该条件用于确定SQL在idx_subject_score索引上的查询范围。

   

2. Index Filter：math=49，该条件可以在idx_subject_score索引上进行过滤，但不属于Index Key。

   

3. Table Filter：remark IS NOT NULL，该条件在idx_subject_score索引上无法继续过滤，只能在聚簇索引上进行Table Filter级别的过滤。

   

如图所示，在RR隔离级别下：
（1）由Index Key所确定的范围，被加上了GAP锁；
（2）Index Filter锁给定的条件何时过滤，视MySQL的版本而定，在MySQL 5.6版本之前，不支持ICP（即索引下推），因此Index Filter在MySQL Server层过滤，在5.6后支持了ICP，则在index上过滤。若不支持ICP，则不满足Index Filter的记录，也需要加上记录X锁；若支持ICP，则不满足Index Filter的记录，无需加记录X锁。（图中红色箭头说明）
（3）Table Filter对应的过滤条件，则在聚簇索引中读取后，在MySQL Server层面过滤，因此聚簇索引上也需要X锁。最后，选取出了一条满足条件的记录(3, 48, 49, 'WELL')，但是加锁的数量，要远远大于满足条件的记录数量。

综上所述：在RR隔离级别下，针对一个复杂的SQL，首先需要提取其where条件。Index Key确定的范围，需要加上GAP锁；Index Filter过滤条件，视MySQL版本是否支持ICP，若支持ICP，则不满足Index Filter的记录，不加X锁，否则需要X锁；Table Filter过滤条件，无论是否满足，都需要加X锁，加锁的数量，要远远大于满足条件的记录数量。