Mysql加锁规则详解

目     录

加锁规则

前面介绍了间隙锁和 next-key lock 的概念，下面将介绍查询语句的加锁规则。

• 原则：加锁的基本单位是next-key lock（前开后闭）；

• 加锁规则：

• 加锁规则1：查询过程中访问到的对象，都会加一个next-key lock

• 加锁规则2：范围查询，或非唯一索引，或记录不存在，需要向右访问到不满足条件的第一个值为止，加锁范围为这个值的next-key lock

• 等值查询优化规则：

• 优化规则1：如果是唯一索引，且记录存在，next-key lock会退化为记录锁

• 优化规则2：如果最后一个值不满足等值条件的时候，此时next-key lock会退化为间隙锁；

我还是以上篇文章的表 t 为例，和你解释一下这些规则。表 t 的建表语句和初始化语句如下。

代码块SQL:

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

下边将结合实际的案例进行介绍

案例

案例一：等值查询间隙锁

第一个例子是关于等值条件操作间隙：

图1 等值查询的间隙锁

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

1. 根据加锁规则2，记录不存在，需要向后访问到第一个不满足等值条件的值（id=10），session A加锁范围是id=10的next-key lock (5, 10]

2. 根据优化规则2，这是一个等值查询，但最后一个值（id=10）不满足等值条件（id=7），因此退化为间隙锁，最终加锁的范围是（5, 10）

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

第二个例子是关于覆盖索引上的锁：

图2 只加在非唯一索引上的锁

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

1. 根据加锁规则1，查询过程中访问到的对象（c=5），都会加上一个next-key lock (0, 5]

2. 根据加锁规则2，c是非唯一索引，需要向右访问到不满足条件的第一个值为（c=10），并加上next-key lock (5, 10]

3. 根据优化规则2，这是一个等值查询，但最后一个值（c=10）不满足等值条件（c=5），因此退化为间隙锁，最终加锁的范围是（5, 10）

因此session A的加锁范围为索引c上的next-key lock (0, 5]和间隙锁 (5, 10)

这就是session C被阻塞的原因。

但为什么session B并没有被阻塞呢？

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

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

案例三：主键索引范围锁

第三个例子是关于范围查询的。

图3 主键索引上范围查询的锁

现在我们就用前面提到的加锁规则，来分析一下 session A 会加什么锁呢？

1. 根据加锁规则1，查询过程中访问到的行（id>=10 and id<11的范围条件访问到的行是id=10），都会加一个next-key lock (5, 10]

2. 根据优化规则1，等值查询（id>=10可以看作id=10），唯一索引且记录存在，则退化为记录锁（id=10）

3. 根据加锁规则2，范围查询（id<11），需要向右访问到不满足条件的第一个值（id=15）为止，并加上next-key lock (10, 15]

因此sesssion A的锁定范围为记录锁id=10和next-key lock (10, 15]。

案例四：唯一索引范围锁

和案例3一样，都是唯一索引的范围锁

图4 唯一索引范围锁

加锁规则如下：

1. 根据加锁规则1，查询过程中访问到的行（id>10 and id<=15的范围条件访问到的行是id=15），都会加一个next-key lock (10, 15]

2. 根据加锁规则2，范围查询（id<=15），需要向右访问到不满足条件的第一个值（id=20）为止，并加上next-key lock (15, 20]

因此session A的锁定范围为 (10, 15] 和 (15, 20] 两个 next-key lock

id>=10为什么可以当作等值查询，而id<=15却被当作范围查询？

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

接下来，我们再看非唯一索引的范围锁，你可以对照着案例三来看。

需要注意的是，与案例三不同的是，案例五中查询语句的 where 部分用的是字段 c（非唯一索引）。

图 5 非唯一索引范围锁

这次 session A 用字段 c 来判断

1. 根据加锁规则1，查询过程中访问到的行（c>=10 and c<11的范围条件访问到的行是c=10），都会加一个next-key lock (5, 10]，虽然查询条件 c>=10可以当做等值条件c=10，但由于c不是唯一索引，所以不能退化为记录锁，加锁范围应该是next-key lock (5, 10]

2. 根据加锁规则2，范围查询（c<11），需要向右访问到不满足条件的第一个值（c=15）为止，并加上next-key lock (10, 15]

因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock（同时锁定的还有id=10的主键索引）。

案例六：一个死锁的例子

前面的例子中，我们在分析的时候，是按照 next-key lock 的逻辑来分析的，因为这样分析比较方便。最后我们再看一个案例，目的是说明：next-key lock 实际上是间隙锁和行锁加起来的结果。

我们先来看下面这个例子：

图 6 next-key lock引起的死锁问题

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

1. 根据加锁规则1，查询过程中访问到的对象（c=10），都会加上一个next-key lock (5, 10]

2. 根据加锁规则2，c是非唯一索引，需要向右访问到不满足条件的第一个值为（c=15），并加上next-key lock (10, 15]

3. 根据优化规则2，这是一个等值查询，但最后一个值（c=15）不满足等值条件（c=10），因此退化为间隙锁，最终加锁的范围是（10, 15）

因此session A的加锁范围是索引c上的 next-key lock (5, 10] 和间隙锁 (10, 15)

接着再分析为什么会出现死锁：

1. session A的加锁范围是索引c上的 next-key lock (5, 10] 和间隙锁 (10, 15)

2. 同样，session B的update语句的加锁范围也是索引c上的 next-key lock (5, 10] 和间隙锁 (10, 15)，进入锁等待

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 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。

案例七：limit 语句加锁

limit语句会影响加锁的范围

这两个例子中，session A1增加了limit 1，加锁效果就不一样了。可以看到session B1的insert语句执行通过了，但seesion B的insert语句被阻塞。

这是因为，session A1中明确加了limit 1的限制，因此在遍历到c=10时，满足条件的语句已经有一条了，就不需要向后继续遍历了，因此加锁范围就从 (5, 10]和(10, 15]变成了索引c上的next-key lock(5, 10]，因此session B1的inset语句可以执行。

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

补充说明

• 锁是加在索引上的，避免对索引的并发操作：

• 加共享锁：如案例二中的select id from t where c=5 lock in share mode，会在索引c上加next-key lock (0, 5] 和间隙锁 (5, 10)，锁定的是普通索引（c=5, id=5），以及(0, 5)和(5, 10)的间隙，由于是覆盖索引且加共享锁，不会在主键索引（id=5, c=5, d=5）这一行加锁，因此所有不会修改普通索引（c=5, id=5）的操作，或者不需要普通索引（c=5, id=5）排他锁的操作都不会被阻塞。

• 加排他锁：如果用select id from t where c=5 for update，系统会认为你接下来要更新数据，因此会同时锁定主键索引（id=5, c=5, d=5）

• 阻塞的操作：update t set id=6 where id=5（将普通索引（c=5, id=5）修改为（c=5, id=6））；update t set id=6 where c=5（需要普通索引（c=5, id=5）排他锁）；

• 不阻塞的操作：update t set d=d+1 where id=5（不会修改普通索引（c=5, id=5）的操作）;

• 非索引字段加锁：根据加锁规则1，查询过程中访问到的对象，都会加一个next-key lock，因此会对整个表的所有行和间隙加锁，不建议这么使用。

• 覆盖索引：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表（Mysql覆盖索引与回表）




参考文章：


https://www.cnblogs.com/lixuwu/p/14696027.html