MySQL InnoDB 加锁规则与案例分析
InnoDB中处理并发写问题是采用对索引进行加锁，可重复读隔离级别默认是Next-Key Lock（Record Lock + Gap Lock，左开右闭）
关于加锁的理解和应用，《MySQL 45讲》这里总结的实在太棒了（墙裂推荐看一看）！我们看下加锁的规则以及一些典型案例分析（因为间隙锁在可重复读隔离级别下才有效，所以若没有特殊说明，默认是可重复读隔离级别）。
加锁的规则
加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。
- 原则1：加锁的基本单位是next-key lock。next-key lock是前开后闭区间。
- 原则2：查找过程中访问到的对象才会加锁。
- 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
- 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
- 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
举例：
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);
不等号条件里的等值查询
等值查询和“遍历”有什么区别？为什么我们文章的例子里面，where条件是不等号，这个过程里也有等值查询？
我们一起来看下这个例子，分析一下这条查询语句的加锁范围：
begin;
select * from t where id>9 and id<12 order by id desc for update;
利用上面的加锁规则，这个语句的加锁范围是主键索引上的 (0,5]、(5,10]和(10, 15)。
值得说的有两点
- id=15 这一行，并没有被加上行锁。为什么呢？
- (0,5] 为什么也加锁进去了？
我们按照总结的加锁原则往下看：
加锁单位是next-key lock，都是前开后闭区间，但是这里用到了优化2，即索引上的等值查询，向右遍历的时候id=15不满足条件，所以next-key lock退化为了间隙锁 (10, 15)。
但是，我们的查询语句中where条件是大于号和小于号，这里的“等值查询”又是从哪里来的呢？
要知道，加锁动作是发生在语句执行过程中的，所以你在分析加锁行为的时候，要从索引上的数据结构开始。这里，我再把这个过程拆解一下。
这个表的索引id的示意如下:
(0,0,0) - (5,5,5) - (10,10,10) - (15,15,15) - (20,20,20) - (25,25,25)
- 首先这个查询语句的语义是order by id desc，要拿到满足条件的所有行，优化器必须先找到“第一个id<12的值”。
- 这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到id=12的这个值，只是最终没找到，但找到了(10,15)这个间隙。
- 然后倒序向左遍历，在遍历过程中，就不是等值查询了，会扫描到id=5这一行，所以会加一个next-key lock (0,5]。
也就是说，在执行过程中，通过树搜索的方式定位记录的时候，用的是“等值查询”的方法。
等值查询的过程
begin;
select id from t where c in(5,20,10) lock in share mode;
在查找c=5的时候，先锁住了(0,5]。但是因为c不是唯一索引，为了确认还有没有别的记录c=5，就要向右遍历，找到c=10才确认没有了，这个过程满足优化2，所以加了间隙锁(5,10)。
同样的，执行c=10这个逻辑的时候，加锁的范围是(5,10] 和 (10,15)；执行c=20这个逻辑的时候，加锁的范围是(15,20] 和 (20,25)。
通过这个分析，我们可以知道，这条语句在索引c上加的三个记录锁的顺序是：先加c=5的记录锁，再加c=10的记录锁，最后加c=20的记录锁。(按索引c的从小到大加锁)
你可能会说，这个加锁范围，不就是从(5,25)中去掉c=15的行锁吗？为什么这么麻烦地分段说呢？
因为我要跟你强调这个过程：这些锁是“在执行过程中一个一个加的”，而不是一次性加上去的。
理解了这个加锁过程之后，我们就可以来分析下面例子中的死锁问题了。
如果同时有另外一个语句，是这么写的：
begin;
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。
也就是说，这两条语句要加锁相同的资源，但是加锁顺序相反。当这两条语句并发执行的时候，就可能出现死锁。
死锁问题查看
| Session A | Session B |
|---|---|
| begin; select id from t where c in(5,20,10) lock in share mode; | |
| begin; select id from t where c in(5,20,10) order by c desc for update; |
在出现死锁后，执行show engine innodb status命令得到的部分输出。这个命令会输出很多信息，有一节LATESTDETECTED DEADLOCK，就是记录的最后一次死锁信息。
我们来看看这图中的几个关键信息。
这个结果分成三部分：
- TRANSACTION，是第一个事务的信息；
- TRANSACTION，是第二个事务的信息；
- WE ROLL BACK TRANSACTION (1)，是最终的处理结果，表示回滚了第一个事务。
第一个事务的信息中：
- WAITING FOR THIS LOCK TO BE GRANTED，表示的是这个事务在等待的锁信息；
- index c of table
test.t，说明在等的是表t的索引c上面的锁； - lock mode S waiting 表示这个语句要自己加一个读锁，当前的状态是等待中；
- Record lock说明这是一个记录锁；
- n_fields 2表示这个记录是两列，也就是字段c和主键字段id；
- 0: len 4; hex 0000000a; asc ;;是第一个字段，也就是c。值是十六进制a，也就是10；
- 1: len 4; hex 0000000a; asc ;;是第二个字段，也就是主键id，值也是10；
- 这两行里面的asc表示的是，接下来要打印出值里面的“可打印字符”，但10不是可打印字符，因此就显示空格。
- 第一个事务信息就只显示出了等锁的状态，在等待(c=10,id=10)这一行的锁。
- 当然你是知道的，既然出现死锁了，就表示这个事务也占有别的锁，但是没有显示出来。别着急，我们从第二个事务的信息中推导出来。
第二个事务显示的信息要多一些：
- “ HOLDS THE LOCK(S)”用来显示这个事务持有哪些锁；
- index c of table
test.t表示锁是在表t的索引c上； - hex 0000000a和hex 00000014表示这个事务持有c=10和c=20这两个记录锁；
- WAITING FOR THIS LOCK TO BE GRANTED，表示在等(c=5,id=5)这个记录锁。
从上面这些信息中，我们就知道：
- “lock in share mode”的这条语句，持有c=5的记录锁，在等c=10的锁；
- “for update”这个语句，持有c=20和c=10的记录锁，在等c=5的记录锁。
因此导致了死锁。这里，我们可以得到两个结论：
- 由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问；
- 在发生死锁的时刻，for update 这条语句占有的资源更多，回滚成本更大，所以InnoDB选择了回滚成本更小的lock in share mode语句，来回滚。
锁等待问题查看
| Session A | Session B |
|---|---|
| begin; select * from t where id>10 and id<=15 for update; | |
| delete from t where id=10; (Query OK) insert into t values (10,10,10);(Blocked) |
可以看到，由于session A并没有锁住c=10这个记录，所以session B删除id=10这一行是可以的。但是之后，session B再想insert id=10这一行回去就不行了。
现在我们一起看一下此时show engine innodb status的结果，看看能不能给我们一些提示。锁信息是在这个命令输出结果的TRANSACTIONS这一节。
我们来看几个关键信息。
-
index PRIMARY of table
test.t，表示这个语句被锁住是因为表t主键上的某个锁。 -
lock_mode X locks gap before rec insert intention waiting 这里有几个信息：
- insert intention表示当前线程准备插入一个记录，这是一个插入意向锁。为了便于理解，你可以认为它就是这个插入动作本身。
- gap before rec 表示这是一个间隙锁，而不是记录锁。
-
那么这个gap是在哪个记录之前的呢？接下来的0~4这5行的内容就是这个记录的信息。
-
n_fields 5也表示了，这一个记录有5列：
- 0: len 4; hex 0000000f; asc ;;第一列是主键id字段，十六进制f就是id=15。所以，这时我们就知道了，这个间隙就是id=15之前的，因为id=10已经不存在了，它表示的就是(5,15)。
- 1: len 6; hex 000000000513; asc ;;第二列是长度为6字节的事务id，表示最后修改这一行的是trx id为1299的事务。
- 2: len 7; hex b0000001250134; asc % 4;; 第三列长度为7字节的回滚段信息。可以看到，这里的acs后面有显示内容(%和4)，这是因为刚好这个字节是可打印字符。
- 后面两列是c和d的值，都是15。
因此，我们就知道了，由于delete操作把id=10这一行删掉了，原来的两个间隙(5,10)、(10,15）变成了一个(5,15)。
说到这里，你可以联合起来再思考一下这两个现象之间的关联：
- session A执行完select语句后，什么都没做，但它加锁的范围突然“变大”了；
- 当我们执行select * from t where c>=15 and c<=20 order by c desc lock in share mode; 向左扫描到c=10的时候，要把(5, 10]锁起来。
也就是说，所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的。
再看一个update的例子
| Session A | Session B |
|---|---|
| begin; select * from t where c>5 lock in share mode; | |
| update t set c=1 where c=5; (Query OK) update t set c=5 where c=1;(Blocked) |
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，因此原来锁的(5,10]，变成了 (1,10]。这样后边再把c从1改成5就被间隙锁(1,10)堵住了。
加锁例子分析
案例一：等值查询间隙锁
| Session A | Session B | Session C |
|---|---|---|
| begin; update t set d=d+1 where id=7; | ||
| insert into t values (8,8,8);(Blocked) | ||
| update t set d=d+1 where id=10;(Query OK) |
由于表t中没有id=7的记录，所以用我们上面提到的加锁规则判断一下的话：
- 根据原则1，加锁单位是next-key lock，session A加锁范围就是(5,10]；
- 同时根据优化2，这是一个等值查询(id=7)，而id=10不满足查询条件，next-key lock退化成间隙锁，因此最终加锁的范围是(5,10)。
所以，session B要往这个间隙里面插入id=8的记录会被锁住，但是session C修改id=10这行是可以的。
案例二：非唯一索引等值锁
| Session A | Session B | Session C |
|---|---|---|
| begin; select id from t where c=5 lock in share mode; | ||
| update t set d=d+1 where id=5;(Query OK) | ||
| insert into t values (7,7,7);(Blocked) |
这里session A要给索引c上c=5的这一行加上读锁。
- 根据原则1，加锁单位是next-key lock，因此会给(0,5]加上next-key lock。
- 要注意c是普通索引，因此仅访问c=5这一条记录是不能马上停下来的，需要向右遍历，查到c=10才放弃。根据原则2，访问到的都要加锁，因此要给(5,10]加next-key lock。
- 但是同时这个符合优化2：等值判断，向右遍历，最后一个值不满足c=5这个等值条件，因此退化成间隙锁(5,10)。
- 根据原则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，下面这两条查询语句，加锁范围相同吗？
mysql> select * from t where id=10 for update; mysql> select * from t where id>=10 and id<11 for update; 你可能会想，id定义为int类型，这两个语句就是等价的吧？其实，它们并不完全等价。
在逻辑上，这两条查语句肯定是等价的，但是它们的加锁规则不太一样。现在，我们就让session A执行第二个查询语句，来看看加锁效果。
| Session A | Session B | Session C |
|---|---|---|
| begin; select * from t where id >=10 and id < 11 for update; | ||
| insert into t values (8,8,8);(Query OK)insert into t values (13,13,13)(Blocked) | ||
| update t set d=d+1 where id=15;(Blocked) |
现在我们就用前面提到的加锁规则，来分析一下session A 会加什么锁呢？
开始执行的时候，要找到第一个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 B和session C的结果你就能理解了。
这里你需要注意一点，首次session A定位查找id=10的行的时候，是当做等值查询来判断的，而向右扫描到id=15的时候，用的是范围查询判断。
案例四：非唯一索引范围锁
接下来，我们再看两个范围查询加锁的例子，你可以对照着案例三来看。
需要注意的是，与案例三不同的是，案例四中查询语句的where部分用的是字段c。
| Session A | Session B | Session C |
|---|---|---|
| begin; select * from t where c >=10 and c < 11 for update; | ||
| insert into t values (8,8,8);(Blocked) | ||
| update t set d=d+1 where id=15;(Blocked) |
这次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
| Session A | Session B | Session C |
|---|---|---|
| begin; select * from t where id >10 and c <=15 for update; | ||
| insert into t values (16,16,16);(Blocked) | ||
| update t set d=d+1 where id=20;(Blocked) |
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的一行，也会被锁住。
案例六：非唯一索引上存在"等值"的例子
接下来的例子，是为了更好地说明“间隙”这个概念。这里，我给表t插入一条新记录。
mysql> insert into t values(30,10,30); 新插入的这一行c=10，也就是说现在表里有两个c=10的行。那么，这时候索引c上的间隙是什么状态了呢？你要知道，由于非唯一索引上包含主键的值，所以是不可能存在“相同”的两行的。
| Session A | Session B | Session C |
|---|---|---|
| begin; delete from t where c=10; | ||
| insert into t values (12,12,12);(Blocked) | ||
| update t set d=d+1 where c=15;(Query OK) |
session A在遍历的时候，先访问第一个c=10的记录。同样地，根据原则1，这里加的是(c=5,id=5)到(c=10,id=10)这个next-key lock。
然后，session A向右查找，直到碰到(c=15,id=15)这一行，循环才结束。根据优化2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成(c=10,id=10) 到 (c=15,id=15)的间隙锁。
案例七：limit 语句加锁
| Session A | Session B |
|---|---|
| begin; delete from t where c=10 limit 2; | |
| insert into t values (12,12,12);(Query OK) |
session A的delete语句加了 limit 2。你知道表t里c=10的记录其实只有两条，因此加不加limit 2，删除的效果都是一样的，但是加锁的效果却不同。可以看到，session B的insert语句执行通过了，跟案例六的结果不同。
这是因为，案例七里的delete语句明确加了limit 2的限制，因此在遍历到(c=10, id=30)这一行之后，满足条件的语句已经有两条，循环就结束了。
因此，索引c上的加锁范围就变成了从（c=5,id=5)到（c=10,id=30)这个前开后闭区间。
这个例子对我们实践的指导意义就是，在删除数据的时候尽量加limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。
案例八：一个死锁的例子
最后我们再看一个案例，目的是说明：next-key lock实际上是间隙锁和行锁加起来的结果。
| Session A | Session B |
|---|---|
| begin; select id from t where c=10 lock in share mode; | |
| update t set d=d+1 where c=10;(Blocked) | |
| insert into t values (8,8,8); | |
| Error 1213 deadlock found |
现在，我们按时间顺序来分析一下为什么是这样的结果。
session A 启动事务后执行查询语句加lock in share mode，在索引c上加了next-key lock(5,10] 和间隙锁(10,15)；
session B 的update语句也要在索引c上加next-key lock(5,10] ，进入锁等待；
然后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来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。
总结
- 锁是加在索引上而不是行记录上
- 锁采用Next-Key Lock ，记录锁+间隙锁 区间是左开右闭
- 加锁的规则：
- 原则1：加锁的基本单位是next-key lock。next-key lock是前开后闭区间。
- 原则2：查找过程中访问到的对象才会加锁。
- 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
- 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
- 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
参考
- 《MySQL45讲》
- 《MySQL技术内幕 : InnoDB存储引擎》