mysql8 Record Locks ，Gap Locks， Next-Key Locks 实验1

xuejianxinokok
347 阅读6分钟

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

mysql8 Record Locks ，Gap Locks， Next-Key Locks

由于 Record Locks和Gap Locks 只是 Next-Key Locks 的特殊形式，我们先考虑 Next-Key Locks

1. Next-Key Locks 定义

先看一下官方文档

dev.mysql.com/doc/refman/…

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.

对于这句话需要说明一下：

  1. next-key lock 是一个 锁组合，也就是说它有多个锁组合而成

  2. 首先 next-key lock 包含 一个 index record 上的 record lock

  3. 另外 next-key lock 还包含一个 a gap lock on the gap before the index record，这句话的意思是 在 第二点 锁定的 record lock 之前的间隙 和 第2点 上的记录锁 组合成一个 next-key lock

  4. 那么怎么理解 record 这个名称，直接翻译过来就是 记录 也就是一个数据条目。对于MySQL innodb 的表来说，表就是聚簇索引，聚簇索引就是表，而二级索引（非聚簇索引）是由 index record 索引条目构成，一个索引条目就是 (key,value)。 对于聚簇索引来说 索引条目(index record)就是(主键,行数据)，对于二级非聚簇索引来说 索引条目(index record)就是(key,主键)。所有对于mysql innodb存储引擎来说 锁都是加在 索引条目(index record)上的。 所以此处的 record 不单单指 由行数据组成的主键索引记录，而且包含二级索引记录。

Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where a round bracket denotes exclusion of the interval endpoint and a square bracket denotes inclusion of the endpoint:

假如一个索引中的key 包含 10, 11, 13, and 20.那么可能的next-key locks 会覆盖以下的区间，这个区间是前开后闭的区间。

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows。

默认情况下 InnoDB 默认的事务隔离级别是 REPEATABLE READ，InnoDB 在查询和索引扫描的时候使用 next-key locks 来防止幻读（phantom rows）。

2 影响加锁的主要因素

2.1 事务的隔离级别

在RR 和RC 的隔离级别下mysql的加锁机制是有区别的，并不是说隔离界别决定了加锁，而是说不同的加锁行为导致了不同的隔离级别。

2.2 索引的类型

在执行sql的时候 主键索引，二级唯一索引，二级非唯一索引，无索引 都会影响加锁行为。

2.3 查询的条件：范围查询 还是 等值查询

下边我们开始设计实验来验证这些加锁情形。

3. 实验的前提条件

3.1 实验环境

数据库版本 8.0.27

操作系统 debian linux

3.2 初始化SQL

-- 建表sql
CREATE TABLE `locktest` (
    `id` INT(10) NOT NULL COMMENT '主键，唯一索引',
    `f1` INT(10) NOT NULL COMMENT '二级唯一索引',
    `f2` INT(10) NOT NULL COMMENT '二级非唯一索引',
    `f3` INT(10) NOT NULL COMMENT '无索引',
    PRIMARY KEY (`id`) ,
    UNIQUE INDEX `ui_f1` (`f1`) ,
    INDEX `i_f2` (`f2`) 
)
COMMENT='锁测试'
ENGINE=InnoDB
;


-- 初始化数据

INSERT INTO locktest (id,f1,f2,f3) VALUES (10,10,10,10);
INSERT INTO locktest (id,f1,f2,f3) VALUES (11,11,11,11);
INSERT INTO locktest (id,f1,f2,f3) VALUES (13,13,13,13);
INSERT INTO locktest (id,f1,f2,f3) VALUES (20,20,20,20);

20220430150817-image.png

4. RR 隔离级别下加锁行为

4.1 主键索引等值查询

我们启动一个新的连接 执行以下sql

# connection1

START TRANSACTION;
SELECT * FROM locktest t WHERE t.id=13 for update;

20220430152901-image.png

可以执行以下sql查询加锁情况

SELECT
 row_number() OVER() 行号
,t.THREAD_ID
,t.OBJECT_NAME
,t.INDEX_NAME
,t.LOCK_TYPE
,t.LOCK_MODE
,t.LOCK_STATUS
,t.LOCK_DATA 
FROM performance_schema.data_locks t

查询结果如下:

20220430153534-image.png

我们对上图解释一下

我们的查询条件 为 WHERE t.id=13 for update 是一个等值查询而且用了 for update 表明了我们要修改数据

mysql 在执行每一个ddl sql之前都会对表加一个意向锁，如果只是查询则加 IS 意向锁，由于我们是 for update

所以在 行号1 上的加锁信息 表示 OBJECT_NAME=locktest表上 的 LOCK_TYPE=TABLE 且 LOCK_MODE=IX ,LOCK_STATUS=GRANTED 已经获得了锁

通俗点说就是在 locktest表上加了一个 表级的 IX 锁.

行号2 上的加锁信息 表示在 表locktest(OBJECT_NAME 字段) 的 PRIMARY(INDEX_NAME 字段), LOCK_TYPE=RECORD 记录锁

LOCK_MODE= X,REC_NO_GAP 这是一个排它锁，且没有间隙，因为这是等值查询，而且是主键 所以只锁定了一条记录

LOCK_DATA=13 表示这条数据是 id=13

为了便于后边描述我们修改查询语句为:

SELECT
 row_number() OVER() 行号
,t.THREAD_ID     '锁线程(THREAD_ID)'
,t.OBJECT_NAME   '锁的表(OBJECT_NAME)'
,t.INDEX_NAME    '锁的索引(INDEX_NAME)'
,t.LOCK_TYPE     '锁粒度(LOCK_TYPE)'
,t.LOCK_MODE     '锁类型(LOCK_MODE)'
,t.LOCK_STATUS   '锁状态(LOCK_STATUS)'
,t.LOCK_DATA     '锁的数据(LOCK_DATA)'
FROM performance_schema.data_locks t

查询结果为：

20220430155141-image.png

这样结果就很清晰了

我们再打开一个新的连接还执行以上sql,

20220430155318-image.png

发现此时 sql被阻塞了，执行查询锁信息的sql 会看到 行号2 中THREAD_ID=46 的一个记录锁正在等待

20220430155417-image.png

此时我们回滚第一个连接

20220430155615-image.png

再次查询锁信息 已经获取了锁

20220430155722-image.png

注意 第二个连接 可能会超时，需要重新执行以下 for update sql

20220430155822-image.png

对于2个连接同时锁定不同的数据 比如第一个连接锁定11，第二个连接锁定 13，是不会发生阻塞的

20220430160201-image.png

哪锁定不存在记录会怎么样呢？

START TRANSACTION;
-- 15这条记录不存在
SELECT * FROM locktest t WHERE t.id=15 FOR update;

20220430160633-image.png

此时的锁信息为

20220430160725-image.png

发现在 id=20 上加了一个 X排他锁还有一个 GAP 锁 这什么意思？这个GAP 保护的范围是什么？

我们在另一个窗口上测试一下

START TRANSACTION;
-- 插入14, 13<14<15<20，发现无法插入
INSERT INTO locktest (id,f1,f2,f3) VALUES (14,14,14,14);

-- 插入16, 20>16>15>13，发现无法插入
START TRANSACTION;
INSERT INTO locktest (id,f1,f2,f3) VALUES (16,16,16,16);

-- 插入12,12<13<15<20，发现可以插入
START TRANSACTION;
INSERT INTO locktest (id,f1,f2,f3) VALUES (12,12,12,12);

-- 插入21, 21>20>15>13，发现可以插入
START TRANSACTION;
INSERT INTO locktest (id,f1,f2,f3) VALUES (21,21,21,21);

综合上边情况 可以看到这个GAP 就是 (13,20) 中间的间隙

20220430150817-image.png

4.1 主键索引范围查询

4.1.1 单范围查询

对于以下范围查询，也是封锁的范围

START TRANSACTION;
SELECT * FROM locktest t WHERE t.id>11 FOR UPDATE;

20220430162627-image.png

START TRANSACTION;
-- 12 不可以插入
INSERT INTO locktest (id,f1,f2,f3) VALUES (12,12,12,12);

START TRANSACTION;
-- 21 也不可以插入，因为锁定了 supremum pseudo-record
INSERT INTO locktest (id,f1,f2,f3) VALUES (21,21,21,21);
-- 9 可以插入
START TRANSACTION;
INSERT INTO locktest (id,f1,f2,f3) VALUES (9,9,9,9);

4.1.2 双范围查询

查询sql如下

START TRANSACTION;
SELECT * FROM locktest t WHERE t.id>11 AND t.id<20 FOR UPDATE;

锁信息如下

20220430163639-image.png

我们看到在 id=20 上有一个 GAP 锁，这个GAP 一定是(11,20)

查询sql如下

START TRANSACTION;
SELECT * FROM locktest t WHERE t.id>=11 AND t.id<=20 FOR UPDATE;

20220430164037-image.png

-- 不可以插入21 难道锁住了同一个页上的最大伪记录 supremum pseudo-record????  why?
INSERT INTO locktest (id,f1,f2,f3) VALUES (21,21,21,21);

-- 可以插入9
INSERT INTO locktest (id,f1,f2,f3) VALUES (9,9,9,9);

这里碰到一个问题 查询范围为 [11,20] 但是此时连 21也不能插入，这个我也有点不解。

4.1.2 不存在的双范围查询

START TRANSACTION;
-- 记录 12 和21 都不存在
SELECT * FROM locktest t WHERE t.id>=12 AND t.id<=21 FOR UPDATE;

20220430164739-image.png

文章不完善的地方后边会慢慢补充。

mysql8 Record Locks ，Gap Locks， Next-Key Locks 实验2

mysql8 Record Locks ，Gap Locks， Next-Key Locks 实验3

avatar
文章
阅读
粉丝