MySQL——InnoDB引擎下可重复读事务下的next_key lock

1. 规则

1. 加锁的基本单位是next_key lock（左开右闭的区间 (] ），有的时候会退化成行锁或间隙锁
          这个区间是动态的，比如原本的范围是(10,15)，事务二把10记录删除了，事务1的区间就变为(5,15) ----基于下面的栗子。
2. 二级索引没有回表时，不会锁住聚簇索引的数据(lock in share mode模式下，for update 则会锁住)。扫描聚簇索引时会锁住对应的二级索引的相应数据。
3. 增删改过程会涉及对应的二级索引树和聚簇索引树，如果有一个被锁，操作就会被阻塞。
    
4. 四种情况：ps满足limit条件后，就不会接着扫描了
   1. 唯一索引等值查询
      • 当查询的记录是存在的，next-key lock 会退化成「行锁」。
      • 当查询的记录是不存在的，next-key lock 会退化成「间隙锁」。
   2. 唯一索引范围查询
      • 转化为[范围的边界值，第一个可以停止的数据(边界值或比边界值大(小)的下一条数据)}区间内数值(稠密的)的唯一索引等值查询。(左右是根据扫描方向定的)
        • 第一个可以停止的数据，如果>范围边界值，则上述区间变为右开的
        • 第一个可以停止的数据，如果=范围边界值，则上述区间变为右闭的
        • 倒序的话，会直接变为右闭，且不会退化。
   3. 非唯一索引等值查询
      • 当查询的记录是存在的，next-key lock 会变成「next_key锁 + 下一段间隙锁」。
      • 当查询的记录是不存在的，next-key lock 会退化成「间隙锁」。
   4. 非唯一索引范围查询
      • 转化为4.2中的区间内数值(稠密的)的非唯一索引等值查询。
        • 倒序的话，第一个可以停止的数据=边界值，就额外向左读取一条数据，加锁且不会退化。

2. 栗子

| 主键 | 索引| 无 |
—————————
|  id  | a  | b  |
—————————
|  0   | 0  | 0  |
|  5   | 5  | 5  |
|  10  | 10 | 10 |
|  15  | 15 | 15 |
|  20  | 20 | 20 |

// 无索引
1. select * from t where b = 5 for update
    ·无索引列加锁，锁住所有区间 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20,∞]

// 唯一索引
2. select * from t where id = 5 lock in share mode
    ·锁住(0,5]区间
    ·命中，退化成为行锁,锁住id=5这条记录
    ·结果，锁住区间[5,5]

3. select * from t where id = 7 lock in share mode
    ·锁住(5,10]区间
    ·没有命中，退化成间隙锁(5,10)
    ·结果：锁住(5,10)

4. select * from t where id>=10 AND id<11 for update
    ·转换为[10,15)区间内id的等值查询
    ·id=10，命中数据，退化为行锁[10,10]
    ·id=(10,15)，没有命中数据，退化为间隙锁(10,15)
    ·所以最终锁住 [10,15)

5. select * from t where id>9 AND id<12 for update
    ·转换为(9,15)区间内id的等值查询
    ·id=(9,10)，没有命中数据，退化为间隙锁(5,10)
    ·id=10，命中数据，退化为行锁[10,10]
    ·id=(10,15)，没有命中数据，退化为间隙锁(10,15)
    ·所以最终锁住 (5,15)

5*. select * from t where id>9 AND id<12 order by id desc for update
    // 倒排序 因为是倒序，所以区间要向左转换。
    ·转换为[5,12)区间内id的等值查询
    ·id=(10,12)，没有命中数据，退化为间隙锁(10,15)
    ·id=10，命中数据，退化为行锁[10,10]
    ·id=(5,10)，没有命中数据，退化为间隙锁(5,10)
    ·id=5，加锁为 (0,5]，不会退化
    ·所以最终锁住 (0,15)

6. select * from t where id>10 AND id<=15 for update
    ·转换为(10,15]区间内id的等值查询
    ·id=(10,15)，没有命中数据，退化为间隙锁(10,15)
    ·id=15，命中数据，退化为行锁[15,15]
    ·最终锁住(10,15]区间

// 普通索引
7. select * from t where a = 5 for update
    ·锁住(0,5]区间，命中数据，变化成next_key + gap = (0,10)
    ·最终锁住(0,10)

8. select * from t where a = 7 for update
    ·锁住(5,10]区间，没有命中，退化为间隙锁(5,10)
    ·结果：锁住(5,10)

9. select * from t where a>=10 AND a<11 for update
    ·转换为[10,11)区间内a的等值查询
    ·a=10，命中数据，锁住(5,15)
    ·id=(10,11)，没有命中数据，锁住(10,15)
    ·所以最终锁住 (5,15）

10. select id from t where a>9 AND a<12 order by a desc for update
    // 倒排序 因为是倒序，所以区间要向左转换。
    ·转换为[5,12)区间内a的等值查询
    ·id=(10,12)，没有命中，锁住(10,15)
    ·id=10，命中数据，锁住(5,15)
    ·id=(5,10)，没有命中数据，锁住(5,10)
    ·id=5，加锁为 (0,5]，不会退化
    ·所以最终锁住 (0,15)

3. 更多栗子

使用本篇的规则去解释哦