只改一行语句，为什么锁那么多

注1：MySQL后面的版本可能会改变加锁策略， 所以这个规则只限于截止到现在的最新版本， 即5.x系列

注2：因为间隙锁在可重复读隔离级别下才有效， 所以本篇文章接下来的描述， 若没有特殊说明， 默认是可重复读隔离级别。

加锁规则

加锁规则里面， 包含了两个“原则”、 两个“优化”和一个“bug”（牢牢记住）：

原则1： 加锁的基本单位是next-key lock。 希望你还记得， next-key lock是前开后闭区间。

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

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

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

一个bug： 唯一索引上的范围查询会访问到不满足条件的第一个值为止。

注：上述说的等值查询是指SQL语句where条件中有等值条件，且该语句会加锁。

示例：此处体现了加锁规则1中左开右闭的本质，那就是，如果是范围查询且范围区间为左开右闭，如（5，10]，那么加锁范围就是（5，10]。如果是范围查询且范围区间为左闭，如 [5，10] 或 [5， 10），则加锁区间为（0，5] 和（5，10]。（此处主要是针对原则1的总结，不涉及加锁规则中的优化）

假设有如下表结构：

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);

案例一：等值查询间隙锁

等值条件操作间隙：

由于表t中没有id=7的记录， 所以用我们上面提到的加锁规则判断一下的话：

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

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

所以， session B要往这个间隙里面插入id=8的记录会被锁住， 但是session C修改id=10这行是可以的。

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

覆盖索引上的锁：

你是不是有一种“该锁的不锁， 不该锁的乱锁”的感觉？ 我们来分析一下吧。这里session A要给索引c上c=5的这一行加上读锁。

1）根据原则1， 加锁单位是next-key lock， 因此会给(0,5]加上next-key lock。

2）要注意c是普通索引， 因此仅访问c=5这一条记录是不能马上停下来的， 需要向右遍历， 查到c=10才放弃。 根据原则2， 访问到的都要加锁， 因此要给(5,10]加next-key lock。

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

4）根据原则2 ， 只有访问到的对象才会加锁， 这个查询使用覆盖索引， 并不需要访问主键索引， 所以主键索引上没有加任何锁， 这就是为什么session B的update语句可以执行完成。

但session C要插入一个(7,7,7)的记录， 就会被session A的间隙锁(5,10)锁住。

注：在这个例子中， lock in share mode只锁覆盖索引， 但是如果是for update就不一样了。 执行 for update时， 系统会认为你接下来要更新数据， 因此会顺便给主键索引上满足条件的行加上行锁。

这个例子说明， 锁是加在索引上的； 同时， 它给我们的指导是， 如果你要用lock in share mode来给行加读锁避免数据被更新的话， 就必须得绕过覆盖索引的优化， 在查询字段中加入索引中不存在的字段。 比如， 将session A的查询语句改成select d from t where c=5 lock in share mode。你可以自己验证一下效果。

案例三： 主键索引范围锁

思考：对于我们这个表t， 下面这两条查询语句， 加锁范围相同吗？

select * from t where id=10 for update;
select * from t where id>=10 and id<11 for update;

在逻辑上， 这两条查语句肯定是等价的， 但是它们的加锁规则不太一样。 现在， 我们就让session A执行第二个查询语句， 来看看加锁效果。

下面我们来分析一下session A的加锁规则：

1）开始执行的时候， 要找到第一个id=10的行， 因此本该是next-key lock(5,10]。 根据优化1，主键id上的等值条件， 退化成行锁， 只加了id=10这一行的行锁。

2）范围查找就往后继续找， 找到id=15这一行停下来， 因此需要加next-key lock(10,15]。

所以， session A这时候锁的范围就是主键索引上， 行锁id=10和next-key lock(10,15]。 这样， session B和session C的结果你就能理解了。

注：首次session A定位查找id=10的行的时候， 是当做等值查询来判断的， 而向右扫描到id=15的时候， 用的是范围查询判断。

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

接下来， 我们再看两个范围查询加锁的例子：

这次session A用字段c来判断， 加锁规则跟案例三唯一的不同是： 在第一次用c=10定位记录的时候， 索引c上加了(5,10]这个next-key lock后， 由于索引c是非唯一索引， 没有优化规则， 也就是说不会蜕变为行锁， 因此最终sesion A加的锁是， 索引c上的(5,10] 和(10,15] 这两个next-key lock。

所以从结果上来看， sesson B要插入（8,8,8)的这个insert语句时就被堵住了。

这里需要扫描到c=15才停止扫描， 是合理的， 因为InnoDB要扫到c=15， 才知道不需要继续往后找了

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

前面四个案例，主要用到加锁规则中的两个原则和两个优化，下面来看一下加锁规则中的bug案例：

session A是一个范围查询， 按照原则1的话， 应该是索引id上只加(10,15]这个next-key lock， 并且因为id是唯一键， 所以循环判断到id=15这一行就应该停止了。

但是实现上， InnoDB会往前扫描到第一个不满足条件的行为止， 也就是id=20。 而且由于这是个范围扫描， 因此索引id上的(15,20]这个next-key lock也会被锁上。

所以你看到了， session B要更新id=20这一行， 是会被锁住的。 同样地， session C要插入id=16的一行， 也会被锁住。

照理说， 这里锁住id=20这一行的行为， 其实是没有必要的。 因为扫描到id=15， 就可以确定不用往后再找了。 但实现上还是这么做了， 因此我认为这是个bug。（MySQL官方也将其标记为bug，但未修正）

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

给表t插入一条新记录：

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

新插入的这一行c=10， 也就是说现在表里有两个c=10的行。 那么， 这时候索引c上的间隙是什么状态了呢？ 你要知道， 由于非唯一索引上包含主键的值， 所以是不可能存在“相同”的两行的。

虽然有两个c=10， 但是它们的主键值id是不同的（分别是10和30） ， 因此这两个c=10的记录之间， 也是有间隙的。

这次我们用delete语句来验证。 注意， delete语句加锁的逻辑， 其实跟select ... for update 是类似的， 也就是我在文章开始总结的两个“原则”、 两个“优化”和一个“bug”。

 这时， session A在遍历的时候， 先访问第一个c=10的记录。 同样地， 根据原则1， 这里加的是（5，10] 这个next-key lock。

然后， session A向右查找， 直到碰到c=15这一行， 循环才结束。 根据优化2， 这是一个等值查询， 向右查找到了不满足条件的行， 所以会退化成（10，15）的间隙锁。

也就是说， 这个delete语句在索引c上的加锁范围， 就是下图中蓝色区域覆盖的部分。

 这个蓝色区域左右两边都是虚线， 表示开区间， 即（5，15）这两行上都没有锁。

案例七： limit 语句加锁

案例六对照案例，场景如下所示：

这个例子里， session A的delete语句加了 limit 2。 你知道表t里c=10的记录其实只有两条， 因此加不加limit 2， 删除的效果都是一样的， 但是加锁的效果却不同。 可以看到， session B的insert语句执行通过了， 跟案例六的结果不同。

这是因为， 案例七里的delete语句明确加了limit 2的限制， 因此在遍历到(c=10, id=30)这一行之后， 满足条件的语句已经有两条， 循环就结束了。

因此， 索引c上的加锁范围就变成了从（5，10] 这个前开后闭区间， 如下图所示：

可以看到， (c=10,id=30） 之后的这个间隙并没有在加锁范围里， 因此insert语句插入c=12是可以执行成功的。

结论：在删除数据的时候尽量加limit。 这样不仅可以控制删除数据的条数， 让操作更安全， 还可以减小加锁的范围。

案例八： 一个死锁的例子

我们再看一个案例， 目的是说明： next-key lock实际上是间隙锁和行锁加起来的结果。示例如下：

现在， 我们按时间顺序来分析一下为什么是这样的结果：

1）session A 启动事务后执行查询语句加lock in share mode， 在索引c上加了next-key lock(5,10] 和间隙锁(10,15)。

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

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

问：session B的next-key lock不是还没申请成功吗？

答：session B的“加next-key lock(5,10] ”操作， 实际上分成了两步， 先是加(5,10)的间隙锁， 加锁成功； 然后加c=10的行锁， 这时候才被锁住的。

也就是说， 我们在分析加锁规则的时候可以用next-key lock来分析。 但是要知道， 具体执行的时候， 是要分成间隙锁和行锁两段来执行的。

注：

小结：思考题

还使用上面的表结构：

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);

-- 在session A中执行该语句时，session B会阻塞
select * from t where c>=15 and c<=20 lock in share mode;

-- 在session A中执行该语句时，session B不会阻塞 
select * from t where c>=15 and c<20 lock in share mode;

begin;
select * from t where id>9 and id<12 order by id desc for update;

等值查询过程

与上面这个例子对应的， 下面这个语句的加锁范围是什么？

begin; select id from t where c in(5,20,10) lock in share mode;

这条查询语句里用的是in， 我们先来看这条语句的explain结果。

可以看到， 这条in语句使用了索引c并且rows=3， 说明这三个值都是通过B+树搜索定位的。

1）在查找c=5的时候， 先锁住了(0,5]。 但是因为c不是唯一索引， 为了确认还有没有别的记录c=5，就要向右遍历， 找到c=10才确认没有了， 这个过程满足优化2， 所以加了间隙锁(5,10)。

2）执行c=10这个逻辑的时候， 加锁的范围是(5,10] 和 (10,15)。

3） 执行c=20这个逻辑的时候， 加锁的范围是(15,20] 和 (20,25)。

通过这个分析， 我们可以知道， 这条语句在索引c上加的三个记录锁的顺序是： 先加c=5的记录锁， 再加c=10的记录锁， 最后加c=20的记录锁，最终加锁范围为（0，25）。

针对上述示例，需要注意的是其加锁过程：这些锁是“在执行过程中一个一个加的”， 而不是一次性加上去的。

理解了上述加锁过程，再来看一下下面例子中的死锁问题。如果同时有另一个语句，是这么写的：

select id from t where c in(5,20,10) order by c desc for update;

那么此时的加锁范围，又是什么呢？

虽然间隙锁是不互锁的，但是这两条语句都会在索引c上的c=5、 10、 20这三行记录上加记录锁。

同时由于语句里面是order by c desc， 这三个记录锁的加锁顺序， 是先锁c=20， 然后c=10， 最后是c=5。

也就是说， 这两条语句要加锁相同的资源， 但是加锁顺序相反。 当这两条语句并发执行的时候，就可能出现死锁。

怎么看死锁

下图是在出现死锁后， 执行show engine innodb status命令得到的部分输出。这个命令会输出很多信息， 有一节LATESTDETECTED DEADLOCK， 就是记录的最后一次死锁信息。

下面来看一下图中的几个关键信息：

从上面这些信息中， 我们就知道：

1）“lock in share mode”的这条语句， 持有c=5的记录锁， 在等c=10的锁。

2）“for update”这个语句， 持有c=20和c=10的记录锁， 在等c=5的记录锁。

因此导致了死锁。 这里， 我们可以得到两个结论：

1）由于锁是一个个加的， 要避免死锁， 对同一组资源， 要按照尽量相同的顺序访问。

2）在发生死锁的时刻， for update 这条语句占有的资源更多， 回滚成本更大， 所以InnoDB选择了回滚成本更小的lock in share mode语句， 来回滚。

怎么看待等待

看完死锁， 我们再来看一个锁等待的例子。

由于session A并没有锁住c=10这个记录， 所以session B删除id=10这一行是可以的。 但是之后， session B再想insert id=10这一行回去就不行了。

此时show engine innodb status的结果，如下：

下面来看一下图中的几个关键信息：

1）index PRIMARY of table `test`.`t` ， 表示这个语句被锁住是因为表t主键上的某个锁。

2）lock_mode X locks gap before rec insert intention waiting 这里有几个信息：

insert intention表示当前线程准备插入一个记录， 这是一个插入意向锁。 为了便于理解， 你可以认为它就是这个插入动作本身。

gap before rec表示这是一个间隙锁， 而不是记录锁。

3）那么这个gap是在哪个记录之前的呢？ 接下来的0~4这5行的内容就是这个记录的信息。

4）n_fields 5也表示了， 这一个记录有5列：

0: len 4; hex0000000f; asc ;;第一列是主键id字段， 十六进制f就是id=15。 所以， 这时我们就知道了， 这个间隙就是id=15之前的， 因为id=10已经不存在了， 它表示的就是(5,15)。

1: len 6; hex000000000513; asc ;;第二列是长度为6字节的事务id， 表示最后修改这一行的是trxid为1299的事务。

2: len 7; hexb0000001250134; asc % 4;; 第三列长度为7字节的回滚段信息。 可以看到， 这里的acs后面有显示内容(%和4)， 这是因为刚好这个字节是可打印字符。

后面两列是c和d的值， 都是15。

因此， 我们就知道了， 由于delete操作把id=10这一行删掉了， 原来的两个间隙(5,10)、 (10,15）变成了一个(5,15)。

也就是说， 所谓“间隙”， 其实根本就是由“这个间隙右边的那个记录”定义的。

注：session B中执行delete from t where id = 10;语句时，会首先找到id = 10这一行，在找的过程中，同样满足优化1，即next-key-lock退化为行锁。

update的例子

update语句案例：

session A的加锁范围是索引c上的 (5,10]、 (10,15]、 (15,20]、 (20,25]和(25,supremum]。

重点：根据c>5查到的第一个记录是c=10， 因此不会加(0,5]这个next-key lock。

之后session B的第一个update语句， 要把c=5改成c=1， 你可以理解为两步：

1）插入(c=1, id=5)这个记录。

2）删除(c=5, id=5)这个记录。

索引c上(5,10)间隙是由这个间隙右边的记录， 也就是c=10定义的。 所以通过这个操作， session A的加锁范围变成了图7所示的样子：

接下来session B要执行 update t set c = 5 where c = 1这个语句了， 一样地可以拆成两步：

1）插入(c=5, id=5)这个记录。

2）删除(c=1, id=5)这个记录。

第一步试图在已经加了间隙锁的(1,10)中插入数据， 所以就被堵住了。

小结：思考题

所谓“间隙”， 其实根本就是由“这个间隙右边的那个记录”定义的。

那么， 一个空表有间隙吗？ 这个间隙是由谁定义的？ 你怎么验证这个结论呢？

答：一个空表就只有一个间隙。比如，在空表上执行如下语句，加锁范围就是next-key lock (-∞, supremum]。

begin;
select* from t where id>1 for update;

验证场景：

此时show engine innodb status的结果，如下：

insert语句的锁为什么这么多

先说结论：insert……select语句，在可重复读隔离级别下，会给select的表中扫描到的记录和间隙加读锁。

insert……select语句

假设有如下表结构：

-- 创建表t
CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
)ENGINE=InnoDB;

-- 插入4条数据
insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

-- 创建表t2
create table t2 like t

-- binlog_format=statement时执行
insert into t2(c,d) select c,d from t;

思考：为什么在可重复读隔离级别下，binlog_format=statement时，执行如下语句，需要对表t的所有行和间隙加锁？

insert into t2(c,d) select c,d from t;

答：需要考虑日志和数据的一致性。

执行如下序列：

如果session B先执行， 由于这个语句对表t主键索引加了(-∞,1]这个next-key lock， 会在语句执行完成后， 才允许session A的insert语句执行。

如果没有锁的话，可能出现session B的insert语句先执行，但是后写入binlog的情况。

于是，在binlog_format=statement的情况下， binlog里面就记录了这样的语句序列：

insert into t values(-1,-1,-1); 
insert into t2(c,d) select c,d from t;

这个语句到了备库执行， 就会把id=-1这一行也写到表t2中， 出现主备不一致。

insert循环写入

执行insert …select 的时候， 对目标表也不是锁全表， 而是只锁住需要访问的资源。

假设现在有一个需求：要往表t2中插入一行数据， 这一行的c值是表t中c值的最大值加1。

对应SQL语句如下：

insert into t2(c,d) (select c+1, d from t force index(c) order by c desc limit 1);

这个语句的加锁范围， 就是表t索引c上的(3,4]和(4,supremum]这两个next-key lock， 以及主键索引上id=4这一行。

该语句慢查询日志：

通过这个慢查询日志， 我们看到Rows_examined=1， 即执行这条语句的扫描行数为1。

问1：如果把这样一行数据插入到表t中，语句的执行流程是怎样的？扫描行数又是多少？

insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);

该语句慢查询日志：

这时候的Rows_examined的值是5。

该语句执行计划：

从Extra字段可以看到“Using temporary”字样， 表示这个语句用到了临时表。

InnoDB扫描行数：

这个语句执行前后， Innodb_rows_read的值增加了4。 因为默认临时表是使用Memory引擎的， 所以这4行查的都是表t， 也就是说对表t做了全表扫描。

该语句整个执行过程：

创建临时表， 表里有两个字段c和d。

按照索引c扫描表t， 依次取c=4、 3、 2、 1， 然后回表， 读到c和d的值写入临时表。 这时， Rows_examined=4。

由于语义里面有limit 1， 所以只取了临时表的第一行， 再插入到表t中。 这时， Rows_examined的值加1， 变成了5。

也就是说， 这个语句会导致在表t上做全表扫描， 并且会给索引c上的所有间隙都加上共享的next-key lock。 所以， 这个语句执行期间， 其他事务不能在这个表上插入数据。

问2：这个语句的执行为什么需要临时表？

答：该语句是一边遍历数据，一边更新数据，如果读出来的数据直接写回原表，则有可能在遍历过程中，读到刚刚插入的记录。而新插入的记录如果参与计算逻辑，就跟语义不符了。

问3：这个语句的执行为什么走全表扫描？

答：因为实现上这个语句没有在子查询中直接使用limit 1。

可以使用如下方法对其进行优化（优化后可以避免全表扫描）：

由于这个语句涉及的数据量很小， 你可以考虑使用内存临时表来做这个优化。 使用内存临时表优化时， 语句序列的写法如下：

create temporary table temp_t(c int,d int) engine=memory; 
insert into temp_t (select c+1, d from t force index(c) order by c desc limit 1); 
insert into t select * from temp_t; 
drop table temp_t;

insert唯一键冲突

唯一键冲突序列：

这个例子也是在可重复读（repeatable read） 隔离级别下执行的。 可以看到， session B要执行的insert语句进入了锁等待状态。

也就是说， session A执行的insert语句， 发生唯一键冲突的时候， 并不只是简单地报错返回， 还在冲突的索引上加了锁。 我们前面说过， 一个next-key lock就是由它右边界的值定义的。 这时候， session A持有索引c上的(5,10]共享next-key lock。

注：官方文档有一个描述错误， 认为如果冲突的是主键索引， 就加记录锁， 唯一索引才加next-key lock。 但实际上， 这两类索引冲突加的都是next-key lock。（官方已修正）

问：为什么要加这个next-key lock？

答：防止这一行被别的事务干掉。

经典死锁场景：

死锁逻辑如下：

流程状态变化：

insert into … on duplicate key update

在插入数据时，如果出现主键冲突，则直接报错。若把语句改写为如下形式，会给索引c上(5,10]加一个排他的next-key lock（写锁） 。

insert into t values(5,4,4) on duplicate key update d=100;

insert into …on duplicate key update 这个语义的逻辑是， 插入一行数据， 如果碰到唯一键约束， 就执行后面的更新语句。

语句执行效果1：

语句执行效果2：假设现在表t里面只有(1,1,1)和(2,2,2)这两行。

可以看到， 主键id是先判断的， MySQL认为这个语句跟id=2这一行冲突， 所以修改的是id=2的行。

注：执行这条语句的affected rows返回的是2， 很容易造成误解。 实际上， 真正更新的只有一行， 只是在代码实现上， insert和update都认为自己成功了， update计数加了1， insert计数也加了1。

小结：思考题

小结如下：