MySQL加锁规则 _MySQL加锁

1.加锁规则
原则 1：加锁的基本单位是 next-key lock 。希望你还记得，next-key lock 是前开后闭区间。原则 2：查找过程中访问到的对象才会加锁。优化 1：索引上（唯一索引）的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。（也不会向右遍历了,因此不会增加右侧的间隙锁）（必须是记录匹配的情况下）优化 2：索引上（唯一与非唯一索引）的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。（记录匹配或不匹配都可，匹配上了，如果是唯一索引，就加行锁，如果是非唯一索引，就加next-key lock；匹配不上，无论是唯一索引还是非唯一索引，都加间隙锁）一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止（针对范围锁，无论是唯一索引还是非唯一索引，都要访问到不满足条件的第一个值为止）。

已知数据库中执行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，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；（这里为啥是(5,10]其实没有说出规则，只能猜测只有当前这个next-key lock能锁住id=7）同时根据优化 2，这是一个等值查询 (id=7)，由于不存在id=7，只能向右遍历，而 id=10又不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10) 。
案例二（非唯一索引等值查询，加锁情况）

文章插图

根据原则 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 。
案例三（主键（唯一）索引范围锁）

文章插图

现在我们就用前面提到的加锁规则，来分析一下 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 的时候，用的是范围查询判断。
案例四（非唯一索引范围锁）

文章插图

这次 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，才知道不需要继续往后找了。