MySQL：意向锁、间隙锁、临键锁-每日运维

什么是锁?

共享资源在高并发环境下的一种保护

InnoDB存储引擎的锁

共享锁

排他锁

说白了就是读写锁

对细颗粒度上下锁, 那么在粗颗粒粒度就需要上意向锁

意向锁就是表锁, 分为 IS 和 IX 意向共享锁和意向排他锁

比如对 r 共享资源上锁, 那么就需要对表, 页上意向锁

也就是说, 在上锁前, 需要对粗颗粒粒度上IX意向锁, 然后在对 r 上 X 排它锁

如果在X锁之前, 粗颗粒上已经上锁其他锁, 比如 IS 意向共享锁, 意向锁之间是相互兼容的, 所以 IS 和 IX 兼容

但是 X 锁和 IS 锁是不兼容的, 所以需要等待

show engine INNODB STATUS;查看当前锁请求信息

show FULL PROCESSLIST;: 查看mysql 当前执行的进程, 可以看到是否发生死锁

一致性非锁定读

以可重复读为例

首先它需要是一致性的, 并且是非锁定的读取

一是正确性, 二是不加锁的

那么怎么实现这种读取呢?

无锁却又能保证一致性, 一般人首先都会想到cas, 但是cas还是算锁, 它底层使用的LOCK执行, 锁住了缓存或者CPU总线

但是cas不合适高并发环境, 在超多并发的情况下, CAS会占用巨量的CPU资源

另一种方法便是多版本控制(乐观锁), 这样即便被上了X锁, 也是在特定的版本上上锁, 不是在最新的版本上, 这样如果另一个版本的事务出现了, 也不会影响到我这个版本的row, 因为另一个事务也将在另一个快照上运行

虽然快照多了浪费空间, 但是以空间换取时间, 一致性和性能是值得的

那么如何实现多版本控制呢?

使用undo log实现呗

undo log 中类似于单向链表, 每个链表都有一个版本id, 同一行记录在 undo log中存在多个节点

这在书本上被叫做多版本并发控制(MVCC, multi version concurrency control)

读已提交: 将会从undo log中读取到最新的版本

可重复读: 将会从undo log中按照版本ID读取

可能有人不懂, 我举个例子:

读已提交: 相当于你直接从github下载最新版本的软件, 下次你还是从github上下载最新的, 这样只要github更新了版本你下载的永远都是最新版本的app

可重复读: 相当于你将软件从github上读取下来, 保存到网盘中, 下次你要下载直接从网盘上下载而不是github, 这样即便github更新了app, 但是你还是从网盘上下载旧版本的app

带入到代码中是这样的:

会话1	会话2
begin
SELECT * FROM `student` WHERE age = 22;

	begin
	UPDATE student SET age=23 WHERE id = 1; // 成功
	SELECT * FROM `student` WHERE age = 22;

SELECT * FROM `student` WHERE age = 22;

	commit;
SELECT * FROM `student` WHERE age = 22;

commit;

会话1 不管会话2 怎么变化, 是否修改并提交了事务, 但是会话1 查询还是存在该row

可以发现在 会话2 事务提交之后, 已经将数据同步到磁盘中了, 但是 会话1 还是旧的值, 说明 会话1 也是在快照上读取的, 因为我们的事务等级是 可重复读

如果在读已提交下, 会话2 commit 事务之后, 会话1 再去查询, 就会发现age被修改了

一致性锁定读

在读取的时候上锁

select * from t for update; -- 上X锁
select * from t lock in share model; -- 上共享锁

锁算法

行锁的三种算法

record lock(记录锁): 单个行记录上的锁
gap lock(间隙锁): 锁定一个范围, 但是不包含记录本身
Next-Key Lock(临键锁): Gap Lock+Record Lock, 锁定一个范围包括记录本身

record lock

针对唯一索引或者主键索引使用的锁

当使用READ COMMITTED（读提交）隔离级别时，在MySQL评估完WHERE条件后，对于不匹配的行，记录锁会被释放。

在READ COMMITTED隔离级别下，事务只能看到已经提交的数据，并且每个语句在执行时都是一个独立的事务。当进行查询或更新操作时，MySQL会在满足WHERE条件的行上获取记录锁，以确保事务的一致性。然而，如果某行在判断完WHERE条件后发现不匹配，即不满足查询或更新条件，MySQL会立即释放该行上的记录锁。

这个特性的意义在于减少了记录锁的持有时间，提高了并发性能。如果记录锁在WHERE条件判断后不再需要，MySQL会尽快释放它们，让其他事务可以访问相同的行或间隙。

gap Lock

重点: gap lock(间隙锁)唯一目的是防止其他事务向间隙中插入数据

防止其他事务插入数据!!!

防止其他事务插入数据!!!

防止其他事务插入数据!!!

重点: 间隙锁可以共存。一个事务获取的间隙锁并不会阻止另一个事务在同一个间隙上获取间隙锁。共享间隙锁和排他间隙锁之间没有区别。它们彼此之间不冲突，执行相同的功能。

不同事务可以在同一个间隙上持有冲突的锁。例如，事务A可以在一个间隙上持有共享间隙锁（gap S-lock），而事务B则在同一个间隙上持有排他间隙锁（gap X-lock）。允许冲突的间隙锁的原因是，如果一个记录从索引中删除，不同事务持有的该记录上的间隙锁必须被合并。

next-key lock

临键锁是一种将记录锁和间隙锁结合在一起的锁定方式。当在数据库中搜索或扫描索引时，临键锁会在遇到的索引记录上设置锁定，并且还会对该索引记录之前的间隙进行锁定。这样可以防止其他会话在已被锁定的索引记录之前的间隙中插入新的记录。

临键锁的作用是确保数据的一致性和避免幻读现象。

next-key lock 是前开后闭区间(a, b]

如果上X锁的row是唯一索引的话, 就不会加上Next-Key Lock, 而是会退化为Record Lock

其他情况默认上的是Next-Key Lock

记住间隙锁是左开右闭区间的

记住间隙锁是左开右闭区间的

记住间隙锁是左开右闭区间的

插入意向锁

插入意向锁是在数据库中的一个机制，它用于管理并发插入操作。当多个事务尝试在同一个索引间隙（两个索引记录之间的空白区域）进行插入时，插入意向锁可以确保它们不会互相冲突。

假设有两个事务A和B，它们都想要在索引间隙中插入新的记录。在开始插入之前，事务A会设置一个插入意向锁来指示它的插入意图。这样，其他事务（如事务B）也可以知道有另一个事务在该间隙上进行插入操作，并且它们可以继续自己的插入操作而不会互相干扰。

插入意向锁仅仅是一种指示，它并不阻止其他事务对同一个间隙进行插入操作。实际上，事务B也可以在插入意向锁存在的情况下执行插入操作。然而，如果两个事务试图在同一个位置插入数据（即发生冲突），则它们必须等待对方完成操作。

意向锁的作用

在没有意向锁之前, 我们执行DDL操作时(修改表的字段或者表名之类), 需要事先判断表中是否有其他锁的存在,

这个判断的过程是一行一行的扫描过去, 效率极慢

现在出现意向锁之后, 在执行DDL操作前, 可以直接判断表中是否有意向锁的存在, 而不再需要去扫描整个表的行

所以意向锁通常被设计为表锁, 而非行锁, 而且意向锁通常都是相互兼容的, 这就是设计的原因

分析加锁范围和加锁类型

我们需要使用performance_schema.data_locks这张表来分析

使用书本上的案例来分析:

CREATE TABLE `z` (
  `a` int NOT NULL,
  `b` int DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

首先我们执行

BEGIN;
SELECT * from z where b = 3 for update;
SELECT * from `performance_schema`.data_locks;

lock_type 表示我们上的锁是表锁还是行锁, 明显第一个是表锁, 后续都是行锁

这个是我们查询表的表名

索引的名称或者字段名字

这里显示的是分别是我们z表的b字段和主键

第一个是意向排他锁, 也是个表锁

第二个是next-key lock

第三个是 record lock

第四个是 gap lock

第一个是表锁, 所以没有锁数据

第二个是 3, 5, 表示 b = 3 , id = 5 这行

第三个 5 表示主键 id = 5 这一行

第四个6, 7 , b = 6, id = 7 这一行

可以直接看这里

现在综合一下

表锁, 意向排他锁

我们执行的是SELECT * from z where b = 3 for update;, for update本身就是为了加上排他锁的

而在上排他锁之前, 需要上意向排他锁

前面的章节我们知道这里一个顺序问题, 先粗颗粒上意向锁, 再细颗粒上下排他或共享锁

根据第二行的数据, 我们可以分析出:

对b = 3 id = 5 这一行, 我们上 next-key lock, 是左开右闭区间, 所以锁的范围应该是b的(1.3, 3]

lock_mode 显示出他是 next-key lock 但是不需要 gap lock 的, 所以是 record lock

锁的范围是 id = 5 这一条

从 lock_mode 可以看出他是一个关于b字段的 gap lock, 而这个 6表示b=6这一行, 所以锁的范围是 (3, 6.7] 这个范围

所以综合一下, 锁的整理范围在 (1.3, 6.7]

Q: 这上面的 1.3 和 6.7 是什么意思?

A: 是一个带小数点的数字, 他的意思是 1 和 6 都表示 b 字段的值, 然后小数点的 3 和 7 表示主键

我们还可以带个例子

现在我们插入 b = 6 a = 6 这个row, 按照我的理解, 这条sql会阻塞

然后我们在插入 b = 6, a = 8 这条记录, 将不会阻塞
INSERT into z SELECT 6, 6; -- 阻塞
INSERT into z SELECT 8, 6; -- 插入成功

找一些实例实战一下

CREATE TABLE `t` (
  `id` int NOT NULL,
  `c` int DEFAULT NULL,
  `d` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `test`.`t` (`id`, `c`, `d`) VALUES (0, 1, 2);
INSERT INTO `test`.`t` (`id`, `c`, `d`) VALUES (1, 2, 3);
INSERT INTO `test`.`t` (`id`, `c`, `d`) VALUES (10, 11, 12);
INSERT INTO `test`.`t` (`id`, `c`, `d`) VALUES (13, 14, 15);
INSERT INTO `test`.`t` (`id`, `c`, `d`) VALUES (16, 17, 18);
INSERT INTO `test`.`t` (`id`, `c`, `d`) VALUES (19, 20, 21);

实例1

sessionA:

begin;
update t set d = d+0 where id = 10;
SELECT * from `performance_schema`.data_locks;
COMMIT;

record lock, 锁住的是 id=10 这一行

begin;
update t set d = d+0 where id = 7;
SELECT * from `performance_schema`.data_locks;
COMMIT;

主键产生的间隙锁, 锁的范围是: (1, 10]

接着我们在另一个sessionB中执行sql

insert into t value(8, 8, 8);

直接阻塞, sessionA已经上锁了, 上了(1, 10], 所以阻塞

update t set d=d+1 where id = 10;

允许执行, 因为这里两个 session 产生了两个快照

在可重复读的前提下, 产生了两个快照

案例2

sessionA:

begin;
SELECT id from t where t.c = 11 lock in share mode;
SELECT * from `performance_schema`.data_locks;

c=11 共享锁和 c=(11, 14] 共享间隙锁

sessionB

update t set d=d+0 where c=11;

阻塞

更新 c=11 这一行, 需要插入 排他锁 X, 但是这一行已经被 sessionA 上了共享锁, 不兼容, 所以阻塞了

sessionC:

INSERT into t SELECT 6, 6, 6;

阻塞, 在 sessionA上锁的范围中

如果你需要 insert row, 那么就需要插入意向锁, 不兼容

IX + S 的内容包含 X ==> 不兼容

判断不兼容的方法非常简单

如果是意向锁之间, 兼容

如果是意向锁与普通锁, 或者普通锁和普通锁之间: 可以计算他们的结果是否带有 X

比如:

IS + X = ISX 结果包含 X, 不兼容

IX + S = IXS 结果包含 X 不兼容

S + X = SX 结果包含 X 不兼容

IX + IX = IXIX 是意向锁之间, 兼容

案例3

sessionA

begin;
select * from t where c>=11 and c (2, 17]
sessionB
INSERT into t SELECT 6, 11, 6;

阻塞, 在加锁范围在中
sessionC
DELETE FROM t where t.c = 2; -- 允许删除
DELETE FROM t where t.c = 11; -- 不允许


X+X = XX 包含X不兼容阻塞
c=2 是可以删除的, 因为 2不在加锁范围中(2, 17]
案例4
begin;
select * from t where id>11 and id (1, 16]
案例5
begin;
select * from t where id>=10 and id (0, 13]
这里我们需要关注倒叙问题
begin;
select * from t where id>=10 and id [10, 13]
就因为一个 desc就导致范围是不同的

查看锁和事务的状态:
show PROCESSLIST;
SELECT * from information_schema.INNODB_TRX;
SELECT * from performance_schema.data_locks;
SELECT * from performance_schema.data_lock_waits;
SHOW ENGINE INNODB STATUS;

核心看这一行SELECT * from performance_schema.data_locks;

幻读
幻读的本质是在一次事务中, 多次执行相同的sql所获得的结果是不同的

记住一个关键词: 获得的结果, 也是说这个sql会产生一些返回值, 比如 select

举个例子
CREATE TABLE `z` (
  `a` int NOT NULL,
  `b` int DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test`.`z` (`a`, `b`) VALUES (1, 1);
INSERT INTO `test`.`z` (`a`, `b`) VALUES (3, 1);
INSERT INTO `test`.`z` (`a`, `b`) VALUES (5, 3);
INSERT INTO `test`.`z` (`a`, `b`) VALUES (7, 6);
INSERT INTO `test`.`z` (`a`, `b`) VALUES (10, 7);

begin;

select * from z where a

MySQL：意向锁、间隙锁、临键锁

那么如何实现多版本控制呢?

行锁的三种算法

record lock

gap Lock

next-key lock

插入意向锁

意向锁的作用

可以直接看这里

找一些实例实战一下

实例1

案例2

案例3

案例4

案例5

幻读

举个例子

相关文章

发布评论取消回复

宇宙之一粟

VirtualBox 7.1 Beta 发布：采用全新现代化 UI、更换 Logo

兼容MySQL程度最高的国产数据库来了

Llama 3.1 – 405B、70B 和 8B 的多语言与长上下文能力解析

MySQL：意向锁、间隙锁、临键锁

那么如何实现多版本控制呢?

行锁的三种算法

record lock

gap Lock

next-key lock

插入意向锁

意向锁的作用

可以直接看这里

找一些实例实战一下

实例1

案例2

案例3

案例4

案例5

幻读

举个例子

相关文章

发布评论 取消回复

宇宙之一粟

VirtualBox 7.1 Beta 发布：采用全新现代化 UI、更换 Logo

兼容MySQL程度最高的国产数据库来了

Llama 3.1 – 405B、70B 和 8B 的多语言与长上下文能力解析

发布评论取消回复