MySQL InnoDB supremum pseudo-record范围说明

引言

在 MySQL InnoDB 存储引擎中，锁机制是保证数据一致性和事务隔离的重要手段之一。InnoDB 引入了多种锁机制，其中 next-key lock 是实现行锁和间隙锁的关键部分。在 next-key lock 的机制中，supremum pseudo-record 是一个特殊的存在，通常被描述为 “正无穷大” ，用于表述索引记录中最大值之后的间隙。然而，在实际应用中，supremum pseudo-record 的行为并非总是符合这一描述。
本文通过一系列测试案例，深入探讨 supremum pseudo-record 的实际范围和锁定机制，以期帮助开发者更好地理解 InnoDB 的锁行为。

官方介绍：

For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum” pseudo-record having a value higher than any value actually in the index. 
The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.
来源： https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html

案例分析

表数据说明

CREATE TABLE `t_10` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `a` INT(11) DEFAULT NULL,
  `b` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=INNODB;

-- 按序插入10w条数据
DELIMITER ;;
CREATE PROCEDURE idata_10()
BEGIN
  DECLARE i INT;
  SET i=1;
    start transaction;
  WHILE(i<=100000)DO
    INSERT INTO t_10 VALUES(i, i, i);
    SET i=i+1;
  END WHILE;
    commit;
END;;
DELIMITER ;
CALL idata_10();

默认隔离级别（RR）

案例一：supremum pseudo-record 的加锁范围分析

-- session A
START TRANSACTION;
SELECT  id FROM t_10 WHERE a = 1680 LOCK IN SHARE MODE;

加锁信息如下图，(1679、1680]、(1680、1681)，这个符合预期；为什么会有supremum pseduo-record，这个不符合预期；

此时再开启一个会话，执行如下sql：

-- session B
START TRANSACTION;
SELECT  id FROM t_10 WHERE a = 1682 FOR UPDATE;

会话B加锁范围符合预期；也能说明session A加锁范围supremum pseduo-record，表示的不是(1681, +∞)

案例二：更大范围内的锁定分析

session A:
START TRANSACTION;
SELECT  id FROM t_10 WHERE a > 1 AND a < 1000000 FOR UPDATE;

supremum pseudo-record，出现了91次，和预期不符；

案例三：索引页大小占用分析

mysql innodb一个页是16K（默认大小），对于索引a，叶子节点，一个页能存储约1100条记录，而我本地实际验证情况和这个匹配。
a的范围每增加约1100时，会多锁定一个supremum pseudo-record的间隙。
这一结果进一步表明，supremum pseudo-record 的锁定范围可能受到索引页内部结构的限制，而并非单纯地锁定“最大值之后的间隙”。

结论

基于上面的验证情况，得到的结论：supremum pseudo-record 的锁定范围不仅仅是文档中描述的“最大值之后的间隙”，也用于表示索引页内最后一个索引记录之后间隙的特殊伪记录。
表示最大值之后的间隙，通过select … where col >= max col value for update即可验证。

如有错误之处，恳请指正，感谢！