本文小结：介绍了Inodb加行锁的原则。

加锁规则总结

1、三种行锁的规则为：

（1）Next-Key是默认锁。左开右的闭理解：右闭是行锁（一行记录），左开-右开是间隙锁，所Next-KEY=行锁+间隙锁

（2）行锁场景

• 主键上&&等值查询，对主键B+树上元素。
• 唯一索引&&等值查询，对于主键B+树/唯一索引B+树上元素。
• 普通索引需要回表（等值&范围）， 主键B+树上满足条件的元素都是行锁。

（2）间隙锁场景。也可以理解为出现幻读的场景

• 主键等值查询，只有在未找到元素时候，需要对向右第一个元素加间隙锁。
• 主键范围查询时，无论是否有结果，需要向右第一个元素加Next-Key。
• 二级索引等值查询时，无论是否命中结果，需要对向右第一个元素加间隙锁。

2、以select * from t where … for update为例加锁规则
（1）where等值查询

• 主键等值：select * from t where id =2 for update
  • 找到
    • 主键B+树, 加这个元素的行锁。
  • 找不到
    • 主键B+树, 加右边界的间隙锁
• 唯一键等值：select * from t where uiq_key =2 for update
  • 找到
    • 二级索引uiq_key的B+树：加这个元素的行锁
    • 主键B+树： 加这个元素的行锁
  • 找不到
    • 二级索引uiq_key的B+树：的右边界的 间隙锁。
    • 找不到元素，就不会去回表，扫描主键元素，所以主键B+树不需要加任何锁
• 普通索引等值：select * from t where idx_key =2 for update
  • 找到
    • 二级索引 idx_key的B+树：所有满足元素 都加NEXT-KEY；右边界第一个元素加间隙锁
    • 主键B+树 ：加行锁
  • 找不到
    • 二级索引idx_key 的B+树上：右边界第一个元素加间隙锁
    • 找不到元素，就不会去回表，扫描主键元素，所以主键B+树不需要加任何锁

（2）where 范围查询

• 主键：select * from t where id >2 for update
  • 找到
    • 主键B+树，满足条件的每个元素加Next-Key， 右边界第一个元素 加间隙锁。
  • 找不到
    • 主键B+树，向右第一个元素加Next-Key。
• 唯一键：select * from t where uiq_key >2 for update
  • 找到
    • 二级索引uiq_key的B+树：满足条件的每个元素加Next-Key， 右边界第一个元素 加间隙锁。
    • 主键B+：满足条件元素的行锁
  • 找不到
    • 二级索引uiq_key的B+树 向右第一个元素加Next-Key
    • 找不到元素，就不会去回表，扫描主键元素，所以主键B+树不需要加任何锁
• 普通索：select * from t where idx_key >2 for update
  • 找到
    • 二级索引idx_key的B+树：上所有满足元素&向右第一个元素 都加NEXT-KEY。
    • 主键B+：满足条件元素的行锁
  • 找不到
    • 二级索引uiq_key的B+树 向右第一个元素加Next-Key
    • 找不到元素，就不会去回表，扫描主键元素，所以主键B+树不需要加任何锁

2、扫描元素
扫描元素包括满足条件和不满足条件的。以等值查询为例，主键和唯一索引找到元素之后，就不会再扫描，但是对于普通索引还需要继续扫描直到第一个不满足条件的元素。

准备数据

1、数据源

数据

3、如何查看是哪种行锁

MySQL 8.0 开始information_schema.innodb_locks 改为 performance_schema.data_locks。

通过LOCK_MODE表示行锁、间隙锁、selectkey：

• 行锁：S,REC_NOT_GAP或X,REC_NOT_GAP。
• 间隙锁：S,GAP或X,GAP。
• Next-Key：S或X。

1 主键

1.1 等值

1、存在元素

（1）SQL举例

或者

（2）加锁：

• 在5加上行锁（X,REC_NOT_GAP）

2、不存元素，则是在向右最后一个元素加selectkey

（1）SQL:

（2）加锁

• 在10是间隙锁（X,GAP）

 

1.2 范围查询

1、存在元素

（1）SQL:

（2）加锁：

主键在10、15加NEXT-KEY (X)；20是间隙锁(X,GAP)

 

2 普通索引

通过普通索引需要回表查询主键时，主键B+树上满足条件的元素都是行锁

2.1 等值

1、存在元素

(1) SQL

(2)加锁

• 二级索引，5 加 NEXT-KEY( X )，c=10 加间隙锁(X,GAP)。c=10 为向右最后一个元素
• 主键上， c=5加行锁(X,REC_NOT_GAP)

2、不满足元素

（1）SQL

UPDATE t SET d = 4 WHERE c =7;

(2)加锁：

• 二级索引：c=10，加间隙锁(X,GAP)。 10为向右最后一个元素

 

2.2 范围查询

1、存在元素

(1)SQL:

（2）加锁：

• 二级索引B+树：10加 NEXT-KYE(X )。 15（右边界）NEXT-KYE(X )
• 主键B+树：10 加行锁 ( X,REC_NOT_GAP)

 

3 全表扫描

没有命中任何索引，全表扫描主键B+树。

1、存在元素

（1）SQL:

select * from t WHERE d =5 for update;

（2）加锁：

所有元素都加了NEXT-KEY(X) ,还有一个supremum pseudo-record也加了NEXT-KEY

 

参考

1、mysql45讲 ：https://time.geekbang.org/column/article/75659