mysql间隙锁加锁11个规则

        间隙锁是在可重复读隔离级别下才会生效的： next-key lock 实际上是由间隙锁加行锁实现的，如果切换
到读提交隔离级别 (read-committed) 的话，就好理解了，过程中去掉间隙锁的部分，也就是只剩下行锁
的部分。而在读提交隔离级别下间隙锁就没有了，为了解决可能出现的数据和日志不一致问题，需要把
binlog 格式设置为 row 。也就是说，许多公司的配置为：读提交隔离级别加 binlog_format=row。业务不
需要可重复读的保证，这样考虑到读提交下操作数据的锁范围更小（没有间隙锁），这个选择是合理
的。

next-key lock的加锁规则

　 总结的加锁规则里面，包含了两个 “ “ 原则 ” ” 、两个 “ “ 优化 ” ” 和一个 “bug” 。
     　　原则 1 ：加锁的基本单位是 next-key lock 。 next-key lock 是前开后闭区间。
     　　原则 2 ：查找过程中访问到的对象才会加锁。任何辅助索引上的锁，或者非索引列上的锁，最终
　 都要回溯到主键上，在主键上也要加一把锁。
     　　优化 1 ：索引上的等值查询，给唯一索引加锁的时候， next-key lock 退化为行锁。也就是说如果
     InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁
     　　优化 2 ：索引上（不一定是唯一索引）的等值查询，向右遍历时且最后一个值不满足等值条件的
     时候， next-keylock 退化为间隙锁。
    　　一个 bug ：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

案例分析

 

我们以表test作为例子，建表语句和初始化语句如下：其中id为主键索引
CREATE TABLE `test` (
id` int(11) NOT NULL,
col1` int(11) DEFAULT NULL,
col2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`col1`)
) ENGINE=InnoDB;
insert into test values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

案例一：唯一索引等值查询间隙锁

 

 

由于表 test 中没有 id=7 的记录

        根据原则 1 ，加锁单位是 next-key lock ， session A 加锁范围就是 (5,10] ；

        同时根据优化 2 ，这是一个等值查询 (id=7) ，而 id=10 不满足查询条件， next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)

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

这里 session A 要给索引 col1 上 col1=5 的这一行加上读锁.

　　        根据原则 1 ，加锁单位是 next-key lock ，左开右闭，5是闭上的，因此会给 (0,5]加上 next-key lock

　　要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的（可能有col1=5的其他记录），

       需要向右遍历，查到c=10 才放弃。根据原则 2 ，访问到的都要加锁，因此要给 (5,10] 加next-key lock 。

　　　　但是同时这个符合优化 2 ：等值判断，向右遍历，最后一个值不满足 col1=5 这个等值条件，因此退化成间隙锁 (5,10) 。

　　　　根据原则 2 ， 只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，

　　所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。

　　　　但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住 这个例子说明，锁是加在索引上的。

　　　　执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

　　　　如果你要用 lock in share mode来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，因为覆盖索引不会访问主键索引，不会给主键索引上加锁

案例三：主键索引范围查询锁

 

　　开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10] 。 根据优化 1 ，主键
id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
　　它是范围查询， 范围查找就往后继续找，找到 id=15 这一行停下来，不满足条件，因此需要加
next-key lock(10,15] 。
　　session A 这时候锁的范围就是主键索引上，行锁 id=10 和 next-key lock(10,15] 。首次 session A 定位查找
id=10 的行的时候，是当做等值查询来判断的，而向右扫描到 id=15 的时候，用的是范围查询判断。

案例四：非唯一索引范围查询锁

 

 

 

 

　　在第一次用 col1=10 定位记录的时候，索引 c 上加了 (5,10] 这个 next-key lock 后，由于索引 col1 是非唯
一索引，没有优化规则，也就是 说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和
(10,15] 这两个 next-keylock 。
　　这里需要扫描到 col1=15 才停止扫描，是合理的，因为 InnoDB 要扫到 col1=15 ，才知道不需要继续往后
找了。

案例五：唯一索引范围查询锁 bug

 

 

 

　　session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15] 这个 next-key lock ，并且因
为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。
　　但是实现上， InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20 。而且由于这是个范围扫
描，因此索引 id 上的 (15,20] 这个 next-key lock 也会被锁上。照理说，这里锁住 id=20 这一行的行为，其
实是没有必要的。因为扫描到 id=15 ，就可以确定不用往后再找了。

 

案例六：非唯一索引上存在 " " 等值 " " 的例子

这里，我给表 t 插入一条新记录：insert into t values(30,10,30);也就是说，现在表里面有两个c=10的行
但是它们的主键值 id 是不同的（分别是 10 和 30 ），因此这两个c=10 的记录之间，也是有间隙的。

 

 

 

 

　　这次我们用 delete 语句来验证。注意， delete 语句加锁的逻辑，其实跟 select ... for update 是类似的，
也就是我在文章开始总结的两个 “ 原则 ” 、两个 “ 优化 ” 和一个 “bug” 。
　　这时， session A 在遍历的时候，先访问第一个 col1=10 的记录。同样地，根据原则 1 ，这里加的是
(col1=5,id=5) 到 (col1=10,id=10) 这个 next-key lock 。
　　由于c是普通索引，所以继续向右查找，直到碰到 (col1=15,id=15) 这一行循环才结束。根据优化 2 ，这是
一个等值查询，向右查找到了不满足条件的行，所以会退化成 (col1=10,id=10) 到 (col1=15,id=15) 的间隙
锁。

 

　　这个 delete 语句在索引 c 上的加锁范围，就是上面图中蓝色区域覆盖的部分。这个蓝色区域左右两边都
是虚线，表示开区间，即 (col1=5,id=5) 和 (col1=15,id=15) 这两行上都没有锁

 案例七： limit 语句加锁

 

 

 

　　session A 的 delete 语句加了 limit 2 。你知道表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2 ，删
除的效果都是一样的。但是加锁效果却不一样
　　这是因为，案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (col1=10, id=30) 这一行之后，
满足条件的语句已经有两条，循环就结束了。因此，索引 col1 上的加锁范围就变成了从（ col1=5,id=5)
到（ col1=10,id=30) 这个前开后闭区间，如下图所示：

 

 

 

　　这个例子对我们实践的指导意义就是， 在删除数据的时候尽量加 limit 。
　　这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。

案例八：一个死锁的例子

 

 

　　session A 启动事务后执行查询语句加 lock in share mode ，在索引 col1 上加了 next-keylock(5,10] 和
间隙锁 (10,15) （索引向右遍历退化为间隙锁）；
　　session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待； 实际上分成了两步，
先是加 (5,10) 的间隙锁，加锁成功；然后加 col1=10 的行锁，因为sessionA上已经给这行加上了读
锁，此时申请死锁时会被阻塞
　　然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁， InnoDB 让
session B 回滚

案例九：order by索引排序的间隙锁1

如下面一条语句
下图为这个表的索引id的示意图。
begin;
select * from test where id>9 and id<12 order by id desc for update;

 

 

 

　　首先这个查询语句的语义是 order by id desc ，要拿到满足条件的所有行，优化器必须先找到 “ 第
一个 id<12 的值 ” 。
　　 这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到 id=12 的这个值，只是最终
没找到，但找到了 (10,15) 这个间隙。（ id=15 不满足条件，所以 next-key lock 退化为了间隙锁 (10,
15) 。）
　　 然后向左遍历，在遍历过程中，就不是等值查询了，会扫描到 id=5 这一行，又因为区间是左开右
闭的，所以会加一个next-key lock (0,5] 。 也就是说，在执行过程中，通过树搜索的方式定位记录
的时候，用的是 “ 等值查询 ” 的方法。

案例十：order by索引排序的间隙锁2

　　由于是 order by col1 desc ，第一个要定位的是索引 col1 上 “ 最右边的 ”col1=20 的行。这是一个非唯一索引的等值查询：

　　左开右闭区间，首先加上 next-key lock (15,20] 。 向右遍历，col1=25不满足条件，退化为间隙锁 所以会加上间隙锁(20,25) 和 next-key lock (15,20] 。

　　在索引 col1 上向左遍历，要扫描到 col1=10 才停下来。同时又因为左开右闭区间，所以 next-keylock 会加到 (5,10]，

这正是阻塞session B 的 insert 语句的原因。 在扫描过程中， col1=20 、 col1=15 、 col1=10 这三行都存在值，由于是 select * ，所以会在主键

id 上加三个行锁。 因此， session A 的 select 语句锁的范围就是：
　　　　索引 col1 上 (5, 25) ；
　　　　主键索引上 id=15 、 20 两个行锁。

 案例十一：update修改数据的例子-先插入后删除

 

 

　　注意：根据 col1>5 查到的第一个记录是 col1=10 ，因此不会加 (0,5] 这个 next-key lock 。
session A 的加锁范围是索引 col1 上的 (5,10] 、 (10,15] 、 (15,20] 、 (20,25] 和(25,supremum] 。
之后 session B 的第一个 update 语句，要把 col1=5 改成 col1=1 ，你可以理解为两步：
　　　　 插入 (col1=1, id=5) 这个记录；
　　　　 删除 (col1=5, id=5) 这个记录。
　　通过这个操作， session A 的加锁范围变成了图 7 所示的样子：

 

 

　　好，接下来 session B 要执行 update t set col1 = 5 where col1 = 1 这个语句了，一样地可以拆成两步：
　　　　插入 (col1=5, id=5) 这个记录；
　　　　删除 (col1=1, id=5) 这个记录。 第一步试图在已经加了间隙锁的 (1,10) 中插入数据，所以就被堵住
　　了。