1. 加锁规则

在默认的可重复读的隔离级别下，加锁规则可以总结为：两个原则，两个优化和一个bug。

1. 原则1：加锁的基本单位是next-key lock。next-key lock是前开后闭的区间
2. 原则2：查询过程中访问到的对象才会加锁
3. 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁
4. 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁
5. 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

我们创建如下的表t作为案例中的表：

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. 各种案例

案例一：等值查询间隙锁

由于表t中没有id=7的记录，所以具体加锁过程可以拆解为：

1. 根据原则1，加锁单位是next-key lock，session A的加锁范围是(5,10]
2. 根据优化2，因为是等值查询，并且id=10不满足条件，退化为间隙锁，所以session A 的加锁范围是(5,10)

故session B会被阻塞，而session C能够正常查询

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

这个例子是关于覆盖索引的。

我们来分析加锁的过程：

1. 根据原则1，加锁单位是next-key lock，session A的加锁范围是(0，5]
2. 因为c是普通索引，不是唯一索引，所以访问完id=5不会马上停下来，需要继续查询，查询到id=10才会放弃。根据原则2，访问到的都要加锁，因此session A的加锁范围变成(0，10]
3. 根据优化2，最后一个值不满足id=5，退化为间隙锁，所以session A的加锁范围是(0,10)
4. 根据原则2，只有访问到的对象才会加锁。由于session A使用了覆盖索引，并不需要回表查询主键索引，所以只对覆盖索引加了锁(0,10)。

对于session B中的语句，使用的是主键索引，因此并没有被加锁，可以正常执行。

对于session C中的语句，要插入(7,7,7)，就会被锁(0,10)锁住。

特别需要注意的是，lock in share mode只锁覆盖索引。但是for update就不一样了，因为系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

这个例子说明，锁是加在索引上的。

案例三：主键索引范围锁

这个例子是关于范围查询的

我们先来看下面两条SQL语句：

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

虽然上面两条语句的逻辑效果是一样的，但是加锁范围却是不同的。

对于语句1，根据优化1，唯一索引上的next-key lock会退化成行锁，所以只会锁住id=10.

对于语句2，情况就会比较复杂，我们来看下面这张图：

我们来分析下具体的加锁过程：

1. 开始找到id=10这一行，因为是唯一索引，所以next-key lock会退化成行锁，只会对id=10加上行锁。
2. 接着是范围查询往后找，找到id=15这一行停下来，加上间隙锁(10,15]

因此session A的加锁范围是[10,15]。session B和sessionC就很好判断了。

需要注意的是：首次定位查到id=10是用等值查询判断的，而向右扫描则是用的范围查询。

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

这个案例和上一个案例的不同之处在于使用的是非唯一索引。

我们来分析下具体的加锁过程：

1. 首先查询到id=10，会加上next-key lock，由于不是唯一索引，所以使用不了优化1，故加上锁(5.10]。
2. 接下来是范围查询，找到id=15，加上next-key lock，加上锁(10,15]

所以完整的加锁范围是(5,15]。session B和session C就很容易理解了。

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

这个案例是唯一索引范围查询中的一个bug。

session A是一个范围查询，按照原则1，应该是只会加入next-key lock (10,15]。并且因为id是唯一索引，所以循环判断到id=15就结束了。

但是在具体实现上，InnoDB会往前扫描到第一个不满足条件的行为止，也就是id=20.所以也会加上next-key lock (15,20]。

所以完整的加锁范围变成(10,20]。这样session B和session C都会被阻塞。

这是唯一索引在范围查询中的bug，需要额外注意。

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

为了更好地说明“间隙”的概念，我们给t表插入一条新的记录：

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

插入了这一行之后，就有两行的c=10了。由于主键上的唯一索引，所以这两行是不一样的，它们之间也是有间隙的。如下图：

接下来我们来看案例：

这个案例虽然用的是delete语句，但是前面提到的原则和优化都是一样的。

我们来分析具体的加锁过程：

1. 首先访问到c=10这一行，会加上next-key lock，加锁范围是(5.10]。
2. 接着因为c是非唯一索引，所以会继续访问到c=15，加上next-key lock (10,15]。但是因为id=15不满足条件，所以会退化为间隙锁(10,15)。

所以session A的完整加锁范围是(5,15)。如下图所示：

然后session B和session C的情况就显而易见了。

案例七：limit语句加锁

我们再来看个案例六的对照案例：

这个例子中，delete语句中加入了limit 2，虽然我们知道t表里面有且仅有两条c=10的记录，所以结果上加和不加是一样的。但是加锁却不同，这是因为遍历到(c=10,id=30)这一行之后，已经满足两条结果了，就没有往后查询了。加锁范围也变成了(5,10]。如下图所示：

因此，session B就能正常插入了。

这个例子给我们的启示就是，删除数据的时候尽量用limit，这样可以缩小加锁的范围。

案例八：死锁的例子

这个案例是为了说明next-key lock是间隙锁和行锁共同形成的。

我们来分析加锁的过程：

1. 对于session A，由于c是非唯一索引，所以会查询到id=15，并且行锁退化为间隙锁。所以加锁范围是(5,10]和(10,15)。
2. 对于session B，由于c是非唯一索引，所以会查询到id=15，并且行锁退化为间隙锁。所以正常情况下加锁范围是(5,10]和(10,15)。

但是因为session B是在session A之后执行的，所以session A先对(5,10]和(10,15)进行加锁，这里包含两个间隙锁(5,10)和(10,15)，以及一个行锁c=10。

接着，session B想要在(5,10]和(10,15)，因为间隙锁是可以重复叠加的，所以(5,10)和(10,15)加锁成功。又因为行锁相互排斥，所以session N想要加的id=10的行锁会被session A阻塞。

再接着，session A执行insert语句，则会被session B的间隙锁阻塞。这样就形成了死锁，系统只能让session B回滚了。

这个案例的目的，就是想要告诉我们：next-key lock具体执行的时候，是要分成间隙锁和行锁两阶段执行的。而不是说因为行锁被阻塞了，也会造成间隙锁被阻塞，从而整个next-key lock被阻塞。

来源：自己整理的MySQL实战45讲笔记