[MySQL] next-key lock 实践

背景

探索next-key lock在唯一索引等值查询和普通索引等值查询的锁定情况

表结构

CREATE TABLE `test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `c` int(11) NOT NULL DEFAULT '0' COMMENT '索引',
  `d` int(11) NOT NULL DEFAULT '0' COMMENT '非索引',
  PRIMARY KEY (`id`),
  KEY `idx_c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='测试表'

INSERT INTO test
VALUES(0,0,0),
(5,5,5),
(10,10,10),
(15,15,15),
(20,20,20),
(25,25,25);

主键/唯一索引等值查询

（1）SQL1

BEGIN;
SELECT * FROM test WHERE id = '5' LOCK IN SHARE MODE;

（2）SQL2 ✅

 INSERT INTO test VALUES(6,6,6);

（3）SQL3 ❎

 UPDATE test SET d = 5 WHERE id = 5;

在SQL1事务COMMIT之前，SQL2执行成功；SQL3一直处于阻塞状态，无法完成更新操作。此时，SQL1持有以下锁：

• SQL1持有了(0,5]的next-key lock，由于是唯一索引，会退化为行锁，锁定范围是id=5这一条行记录

普通索引等值查询

（1）SQL1

BEGIN;
SELECT * FROM test WHERE c = '5' LOCK IN SHARE MODE;

（2）SQL2 ❎

 INSERT INTO test VALUES(6,6,6);

（2）SQL3 ❎

 UPDATE test SET d = 5 WHERE id = 5;

在SQL1事务COMMIT之前，SQL2、SQL3一直处于阻塞状态，无法完成更新操作。此时，SQL1持有以下锁：

• SQL1持有了(0,5]的next-key lock
• SQL1持有了(5,10]的next-key lock，由于10不等于5，因此退化为(5,10)的间隙锁
  最终，SQL1持有锁定范围是(0,10)
  同时，证明了MySQL锁定的是索引范围内的行记录，而非索引？

总结

• MySQL锁定的是索引范围内的行记录
• next-key lock会在特定情况下退化为行锁或者间隙锁