MySQL：加锁规则

加锁规则

以下指的都是可重复读下的锁定读的情况。即select .. for update 和 select .. lock in share mode

唯一索引等值查询

• 记录存在
  • 在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。避免记录被删除或修改，防止幻读和不可重复读。
  • 之所以可以从临键锁退化为记录锁，是因为等值查询且索引唯一的时候，不可能插入，只需要加记录锁防止修改和删除就行了
• 记录不存在
  • 在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会退化成「间隙锁」。
  • 锁定范围，防止插入新记录造成幻读。

唯一索引范围查询

当唯一索引进行范围查询时，会对每一个扫描到的索引加 next-key 锁。

• 大于（>）：找到一条记录就对一条记录加next-key锁，直到supremum pseudo-record。

然后针对以下情况会发生退化：

• 大于等于 (>=)
  • 因为存在等值查询的条件，那么如果等值查询的记录存在于表中，该记录的索引中的 next-key 锁会退化成记录锁。（因为不需要加第一个符合条件的记录前面的间隙锁）
  • 简而言之，加第一个符合条件的记录的记录锁，其余加 next-key 锁。
• 小于或小于等于 (< 或 <=)
  • 当条件值的记录不在表中，那么不管是「小于」还是「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁（因为当前记录不属于查询范围，当前记录本身的记录锁可以去掉），其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。
  • 当条件值的记录在表中，
    • 如果是「小于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁（因为查询到终止范围的时候，这一条记录本身也不可能符合范围的条件），其他扫描到的记录，都是在这些记录的索引上加 next-key 锁；
    • 如果「小于等于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引 next-key 锁不会退化成间隙锁（因为是唯一索引，所以扫描到第一个终止范围的时候，就不用往下扫描了，但是它本身依然需要加next-key锁）。其他扫描到的记录，都是在这些记录的索引上加 next-key 锁。

 

非唯一索引等值查询

• 记录存在：
  • 扫描过程中的每个符合条件的记录的二级索引加 next-key 锁。第一个不符合条件的记录的二级索引的 next-key 锁退化成间隙锁。对应的主键索引加记录锁。
  • 注意，非唯一索引中，即使边缘条件二级索引有间隙锁的情况下，依然有可能插入记录。因为非唯一索引下，同一个二级索引值下，可能有多个主键值，只有二级索引值+主键值才能唯一确定插入位置，确定了插入位置后，就要看插入的位置的下一条记录是否有间隙锁，如果有间隙锁，就会发生阻塞，如果没有间隙锁，则可以插入成功。
• 记录不存在：
  • 第一个不符合条件的记录的二级索引加间隙锁（next-key 锁退化来的）。
  • 同理，只要主键索引在加锁的范围外，依然是可以插入的。

 

非唯一索引范围查询

二级索引的 next-key 锁不会退化为间隙锁或记录锁，每个扫描到的记录的二级索引都加 next-key 锁。对应的主键索引加记录锁。

 

没有索引的查询

全表扫描。每条记录加 next-key 锁，相当于锁住整个表。