【MySQL】InnoDB 如何防止幻读

前言

innodb下事务隔离等级：SERIALIZABLE 能解决幻读
REAPETABLE-READ 使用 WHERE 子句能避免幻读（重点）

名词解释

• 加 next - key 锁 （组合锁）
  next - key 锁 = record lock（行锁 ） + gap锁

• 加 record lock 行锁
  只有当查询走索引的时候，才会加record lock，以下统一称为行锁，即使一个表并没有设置任何索引，这种时候 innoDB 会创建一个隐式的聚集索引（primary Key）,然后在这个聚集索引上进行加锁行为的维护。

  注意区分 record lock 和 排它锁 共享锁的区别
  MySQL下 innodb 和 myisam 锁的区别（该博文的所有行锁都不为record lock）

• 加 gap锁
  gap锁，又叫间隙锁，如果 id = 1,3,4,5,7
  若对区间(1,5)加gap锁，可以明确7未被锁住。1和5这个边际需要参考主键顺序

• InnoDB 的主键策略
  该引擎的记录强调顺序性，若建表不使用主键或者唯一键，则自动生成一个隐藏的主键保证数据的顺序性。并建立索引

• lnnoDB 主键 和 聚集索引的关系
  有主键就 根据主键建立聚集索引，没有就找唯一键建立聚集索引
  没有唯一键就先生成隐藏主键，再在隐藏主键上面建立聚集索引
  一个表当且仅当只有一个聚集索引，以下把这个聚集索引都称为主键索引
  
  结论就是，InnoDB的数据有严格的物理结构（顺序性）；

---

操作时机解释

• 加 gap锁 和 行锁 的时机 —— RR隔离级别下 delete 和 update的操作

  1. 走 主键（显式或隐式） 的情况下 如果 where 语句下的条件全部命中，则不会加gap锁，只会行锁。反之，要加行锁，也加gap锁。

  2. 走 辅助键（key） 会在两个空间（通过辅助键找主键、通过主键找内容）加行锁，若where 语句下的条件全部命中，则不会加gap锁。反之，至少需要在两个空间加行锁+辅助键上加gap锁，原因如下：
     下表 normal_key 字段 定义为 辅助键（key）

     2.1 以下是InnoDB走索引的过程
     辅助键（如unique key） 指向 主键索引的B+树，是由于InnoDB索引的数据结构决定 的。InnoDB通过辅助键查询，最终还是要找到主键索引的B+数。隐式的主键无法通过 where key = x 显示得走索引。
     换言之，就是先根据辅助键查询出主键的id，再用主键的id去主键索引查询行记录。
     
     2.2 以下是加锁结果
     最终会在（normal_key -> primary_key）和 （primary_key -> data）这两个空间加行锁，在（normal_key -> primary_key）上加gap锁
     

     normal_key	2	6	9	9	11	15
     primary_key	h	c	b	d	f	a

  3. 若 不走索引 (表定义不使用任何key的字段。隐藏的索引无法被用户放在where条件语句中，也排除)，所有间隙都上gap锁，相当于加了表锁。
     

---

Gap锁具体操作

插入的字段申明values([normal_key], [primary key])

• 条件全部命中

  Session A :
  // 若数据库存在normal_key= 9 的这一条数据
  delete from table where normal_key= 9;
  
  Session B：
  // 由于 A where条件全部命中，normal_key= 10 的记录并没有给锁住
  	insert into table values(10, abc);
  

• 条件全不命中 – 每行都上锁，相当于加表锁

  Session A :
  // 若 数据库存不存在这条数据
  delete from table where normal_key= 7;
  
  Session B：
  // 由于 A where条件全部未命中，整个表给锁住
  insert into table values(10, abc) // 这条sql会给block住
  

• 条件部分命中1 – 部分加 gap锁
  对应的辅助键和主键的关系

  normal_key	2	6	9	9	11	15
  primary_key	h	c	b	d	f	a

  Session A
  // 以下sql会 加gap锁在 normal_key (6-11]
  delete* from table where normal_key= 9;
  
  Session B
  // 因为gap锁的存在，以下sql会给bloack住
  insert into table values(8,c);
  
  Session C
  // 在gap锁外插入数据，成功
  insert into table values(12,c);
  

  gap锁的边际测试

  // gap锁边际：插入【6】
  
  // 成功 因为 normal_key = 6 对应 primary_key = c; bb 首字母排序 < c 可以在(6,c)的左边插入
  insert into table values(6,bb); 
  // 失败 因为 normal_key = 6 对应 primary_key = c; dd 首字母排序 > c 只能在(6,c)的右边插入
  insert into table values(6,dd); 
  
  // 详细原因如下：
  

  gap锁的边际，由主键的大小决定，同样要满足区间的限制。
  当(6,bb)尝试插入时，能预想到：不会破坏原来gap锁区间的主键索引物理（顺序）结构

  normal_key	2	6	6	9	9	11	15
  primary_key	h	bb	c	b	d	f	a

  当(6,dd)尝试插入时，能预想到： 破坏了原来gap锁区间的主键索引物理（顺序）结构

  normal_key	2	6	6	9	9	11	15
  primary_key	h	c	dd	b	d	f	a

---

4. innodb防止幻读的结论

4.1 积极做法： REPEATABLE-READ 隔离级别下使用where子句触发 next-key 锁

• RR 级别下，delete update 等更新语句是默认加锁的没有欢读的隐患，查询是读快照，存在隐患。

4.1.1 解决隐患 —— 加锁

• 读操作时用 select for update （排他锁）或 select lock in share mode（共享锁）

4.1.2 优化操作 —— 避免表锁

• delete 和 update 在RR下是加锁的，如果加的锁都加在行锁上，那么并发性能会提高。思路就是使用where 子句的条件命中索引，不仅是增加了操作速度，还提高了并发度。该技巧同样适用于加锁的读操作

  换言之，delete 和 update如果不走索引，会锁住整个表。

4.2 消极做法： 采用 SERIALIZABLE 事务隔离级别

严格的让所有事务排队执行，相当于所有事务都上了排他锁并且依次执行，自然不会出现任何并发访问的隐患，所以消极但有效。

后记

InnoDB在RR级别下防止幻读，相当于补上了所有并发访问的隐患。RR级别下能够消除脏读和不可重复读，又在规范sql语句上做两点努力：
1.借索引的特性在update delete 操作的时候避免加表锁。——提高性能 。
2. 查询内容的时候用for update 或 select lock in share mode 加锁避免幻读 —— 消除隐患。