mysql上下边界原则,MySQL Next-Key Lock边界值问题-CSDN博客

Next-Key Lock

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

next-key lock 就是gap lock加record lock

mysql官网的例子

官网

假设索引有10，11，13，20，next-key lock的范围是

(negative infinity, 10]

(10, 11]

(11, 13]

(13, 20]

(20, positive infinity)

重点是要考虑的边界为什么是左开右闭区间

例子

前提，next-key lock 只有在rr级别，且非唯一索引的情况下才会生效，以下操作的mysql 版本是5.7.21

例子1

非唯一索引id，没有自定义主键

CREATE TABLE `lock1` (

`id` int(11) unsigned NOT NULL,

KEY `idx_union1` (`id`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into lock1 VALUES (1),(2),(3),(4),(5);

// 开始事务，当前读id 为2 的记录，锁定范围应该是(1，3]

session1

start TRANSACTION;

SELECT * from lock1 WHERE id = 2 for UPDATE;

session2

insert into lock1 VALUES (1); // Lock wait timeout exceeded; try restarting transaction 事务超时

insert into lock1 VALUES (3); // 执行成功

第一个例子不符合官网描述,3没有被锁定，1被锁定

例子2

锁定范围应该是(1，5]

非唯一索引id,自定义唯一索引pid

CREATE TABLE `lock3` (

`id` int(11) unsigned NOT NULL,

`pid` int(11) unsigned NOT NULL,

KEY `idx_union1` (`id`) USING BTREE,

PRIMARY key (`pid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into lock3 VALUES (1,10),(1,11),(3,12),(3,19),(5,22),(5,30);

//session1

start TRANSACTION;

SELECT * from lock3 WHERE id = 3 for UPDATE;

//session2

insert into lock3 VALUES (1,3); // 成功

insert into lock3 VALUES (5,21); // 失败

第二个例子看起来像是(1,5]这个范围

原因

最根本的原因是gaplock 的范围是跟主键索引相关的

第一个例子lock1表没有设置主键，但mysql实际会生成自增默认聚簇索引

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order

我们假设它叫pid，假设自增从1开始那lock1的实际数据应该是(1，1),(2，2),(3，3),(4，4),(5，5)，这样只是为了便于理解。因为InnoDB索引结构都是B+树，索引记录都是按顺序排序的，要再插入一条id=2的记录，其必定只能是在(pid=1,id=1)-(pid=3，id=3)记录之间插入，为了防止幻读，就会在这段加锁，假如让1新增成功，则必定在(pid=1,id=1)-(pid=3，id=3)之间，所以新增失败。对于新增3这条记录，由于主键自增，新增的记录应该是(3，6)这样，并不在(pid=1,id=1)-(pid=3，id=3)之间

第二个例子

锁定的范围为(1,11)~(5,22)，(1,3)不在这个范围之内,(5,21)在这个范围内。