MySQL：逃不掉的锁事，间隙锁( 四 ) _MySQL

根据优化2 ，因为是等值判断，最后一个值不满足c=5，因此退化为间隙锁（5,10）；

根据原则2，只有被访问到的对象才会加锁，这个查询使用覆盖索引，并不需要主键索引，所以主键索引没有加任何索，sessionB的update语句可以执行完成；sessionC的语句被sessionA的间隙锁锁住。

同时需要注意的是：

for update：系统认为接下来会更新数据，因此会将主键索引满足条件的行加行锁；
in share mode：如果有覆盖索引优化，没有访问到主键索引，那么主键索引不会加锁；

因此，这里也就存在说，如果要使用lock in share mode给行家读锁防止数据行被更新，就必须绕过覆盖索引的优化
3.3 主键索引范围锁对于表t，如下两条语句的加锁范围完全不同，语句1只会加行锁，那么语句2呢?
mysql> select * from t where id=10 for update;mysql> select * from t where id>=10 and id<11 for update;

文章插图

开始执行时，要找到第一个id=10的行，由于是主键，所以是唯一索引，由next-key lock(5,10]退化为行锁id=10；
范围查找继续往后查找，找到id=15停止，因此需要加next-key lock(10,15] ，从8.0.18版本，间隙锁退化为(10,15)；

此时sessionA锁的范围为id=10的行锁和(10,15]的间隙锁，因此sessionB和sessionC被阻塞；
可以使用语句“select * from performance_schema.data_locks”表获取加锁的数据。
3.4 非唯一索引范围锁使用索引c进行范围查询：

文章插图
由于c不是唯一索引，因此需要加(5,10]和(10,15]两个next-key lock，因此后两个会话的操作全部被阻塞。
3.5 唯一索引范围锁bug注意，这个bug在8.0.18版本及之后的版本已经优化，不再存在。

文章插图
session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15]这个 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。
但是实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20 。而且由于这是个范围扫描，因此索引 id 上的 (15,20]这个 next-key lock 也会被锁上。
3.6 非唯一索引上存在“等值”的问题执行插入语句：
mysql> insert into t values(30,10,30);

文章插图
虽然有两个c=10的索引，但是主键不同，因此，c=10记录存在间隙。

文章插图
sessionA在遍历的时候，先访问到第一个c=10的记录，根据原则1，加锁为：(c=5,id=5)到(c=10,id=10)这个next-key lock ，即c的索引为(5,10] 。
然后sessionA向右查找，直至(c=15,id=15)，循环结束。根据优化2，等值查询，退化为(c=10,id=10)到(c=15,id=15)的间隙锁，即c的索引为(10,15)；
主键索引上，增加了行锁id=10和id=30；
因此，索引c上的加锁范围为下图蓝色区域：

文章插图
蓝色两边是虚线，表示开区间，即 (c=5,id=5) 和 (c=15,id=15) 这两行上都没有锁。
这里再次举例：如果session b插入(4,5,50)，不会被锁，如果插入（6,5,50）会被锁?。?蛭??端饕?囊蹲咏诘愦娲⒌氖侵骷?? ，二级索引的叶子节点也是有序的，这样6,5,50根据二级索引来排的话是在5,5,10后面的。
3.7 limit语句加锁

文章插图
sessionA的delete语句加了limit 2，表内只有两条数据，删除效果一样，但是加锁效果不同。
delete语句加了limit 2的限制，遍历到(c=10,id=30)这一行之后，满足条件的语句已经有两条，循环结束。因此，索引c的加锁范围变成了（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间。