MYSQL - 间隙锁（为什么我想锁单个数据确锁了表？）

是什么

gap lock间隙锁是指针对索引的范围进行加锁（前开后闭区间）
如下图所示，针对WHERE条件的b=5，在b字段的索引树上加间隙锁：（0-5），（5-10）

image.png

为什么需要间隙锁

• 为了解决当前读的幻读问题

当前读和快照读

快照读是指在rr或rc级别下使用mvcc进行select，参考上一篇mvcc的文章。
当前读是直接度数据库记录的值，不管历史版本（undolog）

• 快照读：普通查询语句（不加锁）
• 当前读：update，insert，delete，select for update，（select for sharemode待确认），ru级别的普通查询

当前读下幻读问题

在rr级别下，要解决幻读问题。假如没有间隙锁，以下流程会出现幻读问题。
假如有表user，字段：id（primary key），num（普通索引），score。
数据：（1,1,50）

• 两个事务按照以下顺序执行：

1. 事务1: 执行select * from user where num>0 for update 结果：查询出id=1得数据
2. 事务2: 执行insert into user values (2,2,80)
3. 事务2: 再执行步骤1的语句。结果，返回id=1,2 的两条数据。
   结果，步骤1和3的结果不一致，产生了幻读。

• 原因: 步骤1在查出来的数据上加了写锁，步骤2新增时不会受这个写锁的影响。

解决

使用间隙锁

• 如果使用间隙锁。步骤1会同时在num的索引树上加写锁和间隙锁
• 加锁位置如下：
  写锁：查出来的id=1的数据
  间隙锁：左边：小于num=1的最大值。右边：大于num=1的最小值。比如如果数据库的数据有num的值（-10，-5,1,10），就会锁住(-5,1]和[1,10)这两个区间

索引类型是否会对间隙加锁（不一定加的就是GAP锁）

image.png
image.png

UPDATE

单筛选语句（WHERE条件后面只有一个比较语句）

• =：完全匹配

  索引类型	是否使用	备注
  主键	不一定用	能匹配到数据，只对匹配到的数据加X不加GAP；匹配不到数据：加 GAP(比{id}大的最小值)
  唯一索引	不一定用	能匹配到数据，只对匹配到的数据加X不加GAP；匹配不到数据：加 GAP(比{id}大的最小值)
  普通索引	用	锁住此字段的索引树上的左右两个间隙 匹配不到数据：加 GAP[比{id}大的最小值]
  无索引	用其他锁	锁住全表

• > <：范围筛选不带等号

  索引类型	语句	是否使用	锁	备注
  主键	UPDATE user SET score = 1 WHERE id > 3 AND id < 7	用	(MAX(小于3), MIN(大于7)]	对筛选条件的范围加间隙锁（如果条件里的值数据库不存在，则向各自方向寻找，直到找到第一个符合条件的）
  唯一索引	UPDATE user SET score = 1 WHERE num > 3 AND num < 7	用	(MAX(小于3), MIN(大于7)]	对筛选条件的范围加间隙锁（如果条件里的值数据库不存在，则向各自方向寻找，直到找到第一个符合条件的）
  普通索引	UPDATE user SET score = 1 WHERE name > 3 AND name < 7	用	[MAX(小于3), MIN(大于7)]	对筛选条件的范围加间隙锁（如果条件里的值数据库不存在，则向各自方向寻找，直到找到第一个符合条件的）
  无索引	用其他锁	用		锁住全表

---- 未完待续 需要补充上一个章实际的范围值

• >= <=：范围筛选带等号

  索引类型	是否使用	备注
  主键	用	两部分加锁：1. 对筛选条件的范围加间隙锁（如果条件里的值数据库不存在，则向各自方向寻找，直到找到第一个符合条件的） 2. <=符号进行匹配的值右侧加间隙锁（如果条件里的值数据库不存在，则向右寻找，直到找到第一个符合条件的）
  唯一索引	用
  普通索引	用	1. 对筛选条件的范围加间隙锁（如果条件里的值数据库不存在，则向各自方向寻找，直到找到第一个符合条件的） 2. <=符号进行匹配的值右侧加间隙锁（如果条件里的值数据库不存在，则向右寻找，直到找到第一个符合条件的）3. >=符号进行匹配的值左侧加间隙锁（如果条件里的值数据库不存在，则向左寻找，直到找到第一个符合条件的）
  无索引	用其他锁	锁住全表

• like： 待补充

结论：

• UPDATE里的WHERE，建议使用主键/唯一索引的字段，（绝不能使用无索引字段）。
  比如: 针对user表使用以下语句：就会导致全表锁定。
  image.png
  UPDATE user SET score = score + 1 WHERE name = '张三'
• 使用写锁（for update）进行分布式控制时，需要考虑会触发间隙锁的问题（WHERE条件里的普通索引字段、无索引字段）；
  比如：还是针对user表，使用以下语句：
  SELECT * FROM user WHERE name = '张三' FOR UPDATE;,这里可能只想把用户张三的数据锁定，但其实也会触发锁定全表。

多筛选语句（WHERE条件里有多个字段）

MySQL会自动选择条件里的一个索引字段进行加锁，优先使用主键、唯一索引，

待补充场景。。。

疑问

疑问一

用户表有num和name两个字段为普通索引字段，更新语句的where条件里有这两个时，另一个事务insert的数据与where条件一致时，会等待name的x,gap锁，但insert数据只有num一样name不一样时，就不等待了，insert数据只有name一样num不一样时又等待了

• 猜测：gap锁只会加载其中一个索引树上，上述语句加在了name上，而不是两个索引树都加，因为：如果num一样，name不一样肯定不会被更新到。
  name一样，num不一样：锁等待
  name不一样，num一样：直接写入；
  name一样，num一样：锁等待；
  虽然name一样，num不一样时，锁等待了，减低了效率（因为num不一样肯定不会被update语句查出来），但这貌似是最简单粗暴的防止幻读的方式

疑问二

image.png
image.png

针对用户表，进行以下操作：

1. 事务一：SELECT * FROM user WHERE num = 10 for update;
2. 事务二：select * from sys.innodb_lock_waits
3. 查询锁等待情况：select * from sys.innodb_lock_waits
   结果：等待锁的类型是X而不是X,GAP
   image.png

• 猜测
  当where条件里，使用锁的字段的比较值为最大值（10）时，会认定大于10的部分为写锁。
  然而，锁得字段比较值为最小值时，小于最小值的部分却是X,GAP锁，这部分内容需要后续研究。
  然而，使用非索引字段进行筛选条件时，会锁住全表，这时进行insert，等待锁类型也是X。