mysql间隙锁，next-key lock,row锁加锁范围分析

介绍

mysql可重复读隔离级别的实现主要依赖mvcc(多版本并发控制)和间隙锁，行锁，多种锁的组合使用来解决可重复读和幻读的问题。
mvcc:主要是给保存每行数据的多个版本，每个版本多了2个字段，一个为最后更新事务的id,一个是删除事务的id。在可重复读隔离级别下，每个事务都只能查询出小于等于自己事务id的版本记录。但这只针对于普通的，不加锁的select语句。对于update,delete语句如果想避免幻读，则需要间歇锁。
mysql innodb的锁主要依赖索引实现，如下我们来分析下，不同的sql语句，不同的索引类型加锁的范围的异同。

1.准备测试数据

CREATE TABLE `lock_test`  (
  `id` int(11) NOT NULL,
  `a` int(11) NOT NULL,
  `b` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `a`(`a`) USING BTREE,
  INDEX `b`(`b`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of lock_test
-- ----------------------------
INSERT INTO `lock_test` VALUES (0, 0, 0);
INSERT INTO `lock_test` VALUES (10, 10, 10);
INSERT INTO `lock_test` VALUES (15, 15, 15);
INSERT INTO `lock_test` VALUES (20, 20, 20);
INSERT INTO `lock_test` VALUES (25, 25, 25);

2.唯一索引等值查询

innodb默认会加next key lock，即间歇锁gap+row锁，组成的前开后闭区间。
给非主键索引加锁时除非查询语句只用到了覆盖索引否则也会锁住对应的主键索引记录。

• 2.1 值存在

    select * from lock_test where a = 10 for update;

  如上由于是唯一索引，锁区间由（10,15],退化成行级锁 lock row 15

• 2.2 值不存在

    select * from lock_test where a = 9 for update;

  值不存在如上由于是唯一索引，锁区间由（10,15],退化成间隙锁（5,10）

• 3.唯一索引范围查询

    select * from lock_test where a>=5 and a<=10 FOR UPDATE ;

  范围闭区间查询都是加向前找到第一个不等于比较值的索引行，加上next-key lock锁,
  如上例，锁区间为(0,15]

• 4.非唯一索引等值查询

    select * from lock_test where b=10 FOR UPDATE ;

  非唯一索引由于值不是唯一的。会在前后区间加上间隙锁和行锁
  [5,15]

• 5.非唯一索引范围查询

    select * from lock_test where b>=5 and b<=10 FOR UPDATE ;

  非唯一索引由于值不是唯一的。会在前后区间加上间隙锁和行锁
  b>=5 锁区间=> [0,+∞）
  b<=10 锁区间 => (-∞,15]
  最终区间为[0,15]

• 6.mysql 当前锁的占用情况查询

    – 查询线程执行情况
    SELECT * FROM performance_schema.threads WHERE processlist_id=
    – 查看当前所有事务
    select * from information_schema.innodb_trx;
    – 查看正在锁的事务
    select * from information_schema.innodb_locks;
    – 查看等待锁的事务
    select * from information_schema.innodb_lock_waits;
    – 查看表锁
    show open tables where In_use>0;
    –查看死锁
    show engine innodb status；

  以上测试基于mysql 5.7.33, mysql 8.0对加锁范围有所优化