当前位置： 首页 > news >正文

mysql-锁的算法(记录锁、间隙锁、临键锁)

news 2025/5/2 19:09:34

1.行锁的三种算法

有3种行锁算法，分别是：

  • Record Lock：单个行记录上的锁，没有主键，会使用隐式的主键进行锁定
  • Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
  • Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身
    ​​

2.锁区间

锁区间：区间从字⾯意思解释就是⼀个范围。
创建时机：mysql的锁区间是在当前会话创建后事物开始时当前数据库已存在数据相邻数据之间两两作为区间边界值。
在这里插入图片描述
Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。

例如一个索引有10,11,13,和20这四个值，那么该索引可能被Next-Key Locking的区间为：

2.1间隙锁锁区间

​​在这里插入图片描述
触发条件：

  1. 范围查询落入一个锁区间
  2. 主键索引或者辅助等值查询不存在，等值落入一个锁区间
  3. 辅助索引等值查询存在，落入2个锁区间（其中一个是间隙锁锁区间）

可锁定的区间
（-∞, 10）、（10,11）、（11,13）、（13,20）、（20，+∞）

2.2临键锁锁区间

在这里插入图片描述

触发条件：

  1. 当范围查询超过多个间隙锁锁区间
  2. 辅助索引等值查询存在，落入2个锁区间（其中一个是临键锁锁区间）

Next-Key Lock 是结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。
可锁定的区间
（-∞, 10]、（10,11]、（11,13]、（13,20]、（20，+∞）

采用Next-Key Lock的锁定技术称为Next-key Locking。其设计目的是为了解决Phantom Problem（幻读），而利用这种锁定技术，锁定的不是单个值，而是一个范围，是谓词锁的一种改进。除了next-key locking，还有previous-key locking技术。同样上诉的索引10、11、13和20，若采用previous-可以locking技术， 那么可锁定的区间为：
（-∞, 10）、[10,11)、[11,13)、[13,20)、[20，+∞）

辅助索引锁区间的锁定会造成该区间内所有主键索引记录锁也进行锁定

若事务T1已经通过next-key locking锁定了如下范围：
(10,11]、(11,13]
当插入新的人记录12时，则锁定的范围会变成：
(10,11]、(11,12]、(12,13]

3.记录锁（Record Lock）

当查询的索引还有唯一属性时，InnoDB存储引擎会对Next-key Lock进行优化吗，将其降为Record Lock，即仅锁住索引本身，而不是范围。看下面的例子，首先根据如下代码创建测试表t

DROP TABLE IF EXISTS t;
CREATE TABLE t(a INT PRIMARY KEY);INSERT INTO t SELECT 1;
INSERT INTO t SELECT 2;
INSERT INTO t SELECT 5;

接着来执行表的SQL语句。

时间会话A会话2
1BEGIN;
2SELECT * FROM t WHERE a=5 FOR UPDATE
3BEGIN;
4INSERT INTO t serlect 4;
5COMMIT; #成功，不需要等待
6COMMIT

表t共有1、2、5三个值，在上面的例子中，在会话A中首先对a=5进行X锁定。而由于a是主键且唯一，因此锁定的仅是5这个值而不是（2,5）这个范围，这样在会话B中插入值4而不会阻塞，可以立即插入并返回。即锁定由Next-key Lock 算法降级为了Record Lock，从而提高应用的并发性。
正如前面锁介绍的，Next-Key Lock 降级为Record Lock 仅在查询的列是唯一索引的情况下。若辅助索引，则情况会完全不同。同样，首先根据如下代码创建测试表z：

CREATE TABLE z(a INT, b INT, PRIMARY KEY(a), KEY(b));
insert into z select 1,1;
insert into z select 3,1;
insert into z select 5,3;
insert into z select 7,6;
insert into z select 10,8;

表z的列b是辅助索引，若在会话A中执行下面的SQL语句：

SELECT * FROM z WHERE b=3 FOR UPDATE

很明显，这是SQL语句通过索引列b进行查询，因此其使用传统的Next-Key Locking技术枷锁，并且由于有两个索引，其需要分别进行锁定。对于聚簇索引，其仅对列a等于5的索引加上Record Lock。而对于辅助索引，其加上的是Next-Key Lock，锁定的范围(1,3]，特别需要注意的是，InnoDB存储引擎还会对辅助索引下一个键值加上gap Lock，即还有一个辅助索引范围（3,6）的锁。因此，若在新会话B中运行下面sql语句，都会阻塞：

SELECT *  FROM z WHERE a=5 LOCK IN SHARE MODE;
INSERT INTO z SELECT 4,2;
INSERT INTO z SELECT 6,7;
UPDATE z SET b=1 WHERE a=5;

第一个和第四个SQL语句不能执行，因为会话A中执行的SQL语句已经对聚簇索引列中a=5的值加上X锁，因此执行会被阻塞。第二个SQL语句，主键插入4，没问题，但是插入的辅助索引值2在锁定的范围（1,3]中，因此执行同样会被阻塞。第三个SQL语句，插入的主键6没有杯锁定，5也不在范围（1,3]之间，但插入的值5在另一个锁定的范围（3,6）中，故同样需要等待。而下面的SQL语句，不会被阻塞，可以立即执行：

INSERT INTO z SELECT 8,6;
INSERT INTO z SELECT 2,0;
INSERT INTO z SELECT 6,7;

从上面的例子中可以看到，Gap Lock的作用是为了组织多个事务将记录插入到同一个范围内，而这会导致Phantom Problem问题的产生。
用户可以通过以下两种方式显式地关闭Gap Lock:

  • 将事务的隔离级别设置为 READ COMMITED
  • 将参数innodb_locks_unsafe_for_binlog设置为1

在上述的配置下，除外键约束和唯一性检查依然需要的Gap Lock，其余情况仅使用Record Lock进行锁定。单需要牢记的是，上述设置破坏了事务的隔离性，并且对于replication，可能会导致主从数据的不一致。此外从性能上来看，READ COMMITED也不会优于默认的事务隔离级别READ REPEATABLE.
在InnoDB存储引擎中，对于INSERT的操作，其会检查插入记录的下一条记录是否被锁定，若已经被锁定，则不允许查询。对于上面的例子，会话A已经锁定了表z中b=3的记录，即锁定了（1，3]的范围，这是若在其他会话中进行如下的插入同样会导致阻塞：

INSERT INTO z SELECT 2,2

因为在辅助索引列b上插入值为2的记录时，会检测到下一个记录3已经被索引。而将加插入修改为如下的值，可以立即执行：

INSERT INTO z SELECT 2,0

最后需再次提醒的时，对于唯一键值的锁定，Next-key Lock 降级为 Record Lock仅存在于查询所有的唯一索引列。若唯一索引由多个列组成，而查询进士查找多个唯一索引列中的其中一个，那么查询其实是range类型查询，而不是point类型查询，故InnoDB存储引擎依然使用 Next-key Lock进行锁定。

在 读已提交（Read Committed, RC） 隔离级别下，InnoDB 的锁机制与 可重复读（Repeatable Read, RR） 隔离级别有所不同。具体到外键约束和唯一性检查时，以下是详细的解释：

1. 唯一性检查

锁类型：仅使用记录锁（Record Locks）
  • 在 读已提交（RC） 隔离级别下，当插入或更新一条记录涉及唯一索引时，InnoDB 只会对实际存在的记录加 记录锁（Record Locks）。
  • 如果要插入的值不存在于现有记录中，InnoDB 不会加 间隙锁（Gap Locks）。
  • 这意味着：
    • 如果 value = 20 不存在，则不会对 (10, 30) 的间隙加锁。
    • 其他事务可以自由插入 value = 20 的记录，而不会被阻塞。
原因：
  • 在 RC 隔离级别下，InnoDB 不需要防止幻读（Phantom Reads），因此不需要使用间隙锁。
  • 唯一性检查只需要确保当前插入的值不与现有记录冲突即可，而无需锁定整个范围。

2. 外键约束检查

锁类型：主要使用记录锁（Record Locks）
  • 在 读已提交（RC） 隔离级别下，当插入或更新一条记录涉及外键约束时，InnoDB 主要对外键引用的现有记录加 记录锁（Record Locks）。
  • InnoDB 不会对间隙加锁，除非显式要求（例如通过触发器或其他机制）。
  • 这意味着：
    • 如果 customer_id = 2 不存在，则不会对 (1, 3) 的间隙加锁。
    • 其他事务可以自由插入 customer_id = 2 的记录，而不会被阻塞。
注意事项：
  • 外键约束检查依赖于父表中的数据是否存在。如果父表中没有对应的记录，插入操作会直接失败，并抛出 [Err] 1452 错误。
  • 因此，即使不加间隙锁，外键约束仍然可以通过记录锁来保证一致性。

总结

在 读已提交（RC） 隔离级别下：

  • 唯一性检查：仅使用 记录锁（Record Locks），不会加 间隙锁（Gap Locks）。
  • 外键约束检查：主要使用 记录锁（Record Locks），不会加 间隙锁（Gap Locks）。

这种行为的主要目的是提高并发性能，因为 RC 隔离级别不需要防止幻读。然而，这也可能导致在高并发场景下出现一些潜在的数据一致性问题，尤其是在涉及唯一性约束或外键约束的情况下。

对比：RC vs RR

特性读已提交（RC）可重复读（RR）
唯一性检查仅使用记录锁使用记录锁 + 间隙锁
外键约束检查仅使用记录锁使用记录锁 + 间隙锁
幻读预防不需要，不加间隙锁需要，加间隙锁
并发性能更高较低

实践建议

  • 如果您的应用场景需要更高的并发性能，并且可以接受一定程度的数据一致性风险（例如允许短时间内的幻读），可以选择 读已提交（RC） 隔离级别。
  • 如果您的应用更注重数据的一致性和完整性（例如需要防止幻读），则应选择 可重复读（RR） 隔离级别。

希望这能解答您的疑问！如果您还有其他问题，请随时告诉我！


http://www.mrgr.cn/news/97905.html

相关文章：

  • LeetCode算法题(Go语言实现)_38
  • Spring事务系列 三
  • 44、Spring Boot 详细讲义（一）
  • wsl2+ubuntu22.04安装blender教程（详细教程）
  • 解决 vite.config.ts 引入scss 预处理报错
  • Adaptive AUTOSAR 状态管理和转换——ActionItemList
  • 【C++游戏引擎开发】第13篇：光照模型与Phong基础实现
  • 1200 模拟量输入输出功能
  • python调用本地部署的大模型(llama3.2)
  • Vue3 + TypeScript 的 Hooks 实用示例
  • XTG900P可编程网关在焦化厂数据采集的应用
  • 编译freecad
  • xHCI 上 USB 读写分析
  • openharmony—release—4.1开源鸿蒙源码编译踩坑记录
  • Backtrader从0到1——第一个回测策略
  • ubuntu20.04在mid360部署direct_lidar_odometry(DLO)
  • 如何通过前端表格控件实现自动化报表？1
  • Cursor Agent 模式实现复杂工作流的编排与执行
  • 百度地图小区边界爬取
  • 创建型模式究竟解决了什么问题