Mysql的RR隔离级别下加锁规则

1.案例环境

mysql版本8.0.32,隔离级别RR

CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE = InnoDB;

insert into t values (0, 0, 0),(5, 5, 5), (10, 10, 10), (15, 15, 15), (20, 20, 20), (25, 25, 25);

2.加锁规则

等值查询规则如下：

原则1：加锁的基本单位是 next-key lock,next-key lock 是前开后闭区间。

原则2：查找过程中访问到的对象才会加锁。

优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化成行锁。

优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化成间隙锁。

范围查询规则如下：

• 等值和范围分开判断
• 索引在范围查询的时候，都会访问到所在区间不满足条件的第一个值为止
• 非唯一索引一律加next-key lock，如果命中的数据有主键并且是for update,会回表把主键加一个行锁。lock in share mode使用覆盖索引则不会回表锁主键。
• 唯一索引根据边界条件判断是否退化成间隙锁

如果使用了倒序排序(order by xx desc)

唯一索引和非唯一索引判断规则都一样，先把数据按照倒序排序，检索范围的右边多加一个next-key lock区间，哪个范围方向还有命中的等值判断，再向同方向拓展一个同方向开反方向闭合的区间。

锁查看：

select * from performance_schema.data_locks;

LOCK_MODE:锁模式(IX：排他锁，X：Next-key lock, x,REC_NOT_GAP：行锁，X,GAP:间隙锁，LOCK_INSERT_INTENTION 插入意向锁)

3.案例一：唯一索引等值查询

加锁规则分析：

• 根据原则1，加锁的规则时next-key lock,session A的加锁范围时(5,10]
• 根据优化2，id=7是一个等值查询，而id=10不满足等值条件，next-key lock 退化成间隙锁，最终范围为(5,10).

4.案例二：非唯一索引等值查询

加锁规则分析：

session A

• 根据原则1，加锁的单位是next-key lock，因此会给普通索引c的(0,5]范围上锁。
• 因为c是普通索引，所以仅访问到c=5是不能停下来的，还需要继续向右遍历一直到c=10才停止，根据原则2，访问到的对象都要加锁，因此要给普通索引c加上范围为(5,10]的next-key lock，
• 根据优化2，等值判断，向右遍历，最后一个值不符合等值条件，退化成间隙锁(5,10).
• 根据原则2，只有访问到的对象才会加锁。这个查询使用了覆盖索引（只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快），并不需要回表去存放主键的b+树去找数据。所以主键索引上没有加任何的锁，这也就是为什么session B的update语句可以执行，而Session C会被session A 的间隙锁锁住。（锁是加在索引上的）

lock in share mode: 覆盖索引查询时，如果查找的结果不需要回表，那只锁覆盖索引，不会锁住主键。例如：如果session A执行的语句是select * from t where c = 5 lock in share mode;就会回表，因此把主键id=5锁住。

for update: 系统认为接下来要更新数据，所以不管查询列是否用到覆盖索引，会给普通索引加next-key lock,主键索引满足条件的行加锁。例如：如果session A执行的语句是select id from t where c = 5 for update;主键id=5会被锁住。

5.案例三：唯一索引范围查询

加锁分析：

• 等值跟范围分开判断，先判断id=10，按照原则1跟优化1，会加id=10的行锁，然后判断10<id<11,在这个区间第一个不满足条件的范围锁是(10,15].然后因为id=15不满足id<11这个条件，索引最终的上锁范围是(10,15)和id=10.

所以session B的insert(8,8,8)插入成功而(13,13,13)会被阻塞，然后session C的更新也会成功。

如果上面session A的语句换成 select * from t where id > 10 and id <= 15 for update;同理分析家的锁为(10,15].

6.案例四：非唯一索引范围查找

加锁分析：

• 首先先判断等值c=10，根据原则1，会给c加上范围为(5,10]的next-key lock,然后根据原则2，会把id = 10这一行用行锁锁起来，然后接着向后判断，一直到c=15停止，根据原则2,会给c的范围为(10,15]加上next-key lock.。
• 然后判断范围10<c<11,不满足该范围的第一个条件范围是(10,15]

最终一共加了3把锁，id=10,c的(5,10],(10,15].

如果把session A的语句改为select id from t where c >= 10 and c < 11 lock in share mode;那样就不会锁住id=10。

如果把session A的语句改为select * from t where c >= 10 and c <= 15 for update;那就是在上面判断的基础上多加一个(15,20]的锁。

7.案例五：倒叙范围查找

select * from t where id>10 and id<=15 order by id desc for update;

 加锁分析：

        1.首先先把数据倒序排序：20,15,10,5,0.

        2.先判断查询范围10<id<15,所以加锁的范围为(10,15)

        3.按照倒叙排序后，检索范围的右边多加一个Next-key lock区间，所以加一个(5,10]

        4.哪边还有命中的等值判断，在相同方向拓展同方向的开反方向的闭区间。15在左边且有等值命中(<=).所以加一个[15,20).

所以最终锁住的id范围是(5,20),即一共3把锁，(5,10],(10,15],(15,20).

 

select * from t where id>=10 and id<15 order by id desc for update;

加锁分析：

        1.首先先把数据倒序排序：20,15,10,5,0.

        2.先判断查询范围10<id<15,所以加锁的范围为(10,15)

        3.按照倒叙排序后，检索范围的右边多加一个Next-key lock区间，所以加一个(5,10]

        4.哪边还有命中的等值判断，在相同方向拓展同方向的开反方向的闭区间。10在右边且有等值命中(>=).因为加过一个(5,10],所以需要在往下一个范围加(0,5].

所以最终锁住的id范围是(0,15),即一共3把锁，(0,5],(5,10],(10,15).

上面的两个例子换成非唯一索引也一样适用，只不过如果需要回表的话，命中的主键会被加一个行锁。例如：select * from t where c >= 10 and c < 15 order by c desc for update;

8.案例六：limit语句

insert into t values(30,10,30);

这里的session A是加了limit，所以等值查询的时候只需要在范围内找到两条符合条件的数据 就会停止往后加锁。

没加limit，最终的锁如下：

非唯一索引c: (5,10],(10,15);

主键id：行锁id=10,id=15.

加了limit 2,因为在判断范围(5,10]的时候已经有(c = 10,id = 10),(c = 10,id = 30)这两条符合条件的数据，所以就不需要再加(10,15)这个范围的锁。

9.案例七：一个死锁的例子

这个案例的目的是说明：next-key lock 实际上是间隙锁和行锁加起来的结果。

死锁分析：

1.session A 启动事务查询时加了 lock inshare mode,在索引c上加了next-key lock (5,10],(10,15),id=10加了行锁。

2.session B 的update语句也要在c上加next-key lock(5,10],进入锁等待

3.然后session A 要再插入(8,8,8)这一行，被session B的间隙锁锁住。由于出现了死锁，Innodb让session B回滚。