首页 > 数据库 >MySQL高级锁之临键锁

MySQL高级锁之临键锁

时间：2023-07-18 22:33:30浏览次数：26  
标签：-- t2 高级 阻塞 临键 num MySQL where select

2.4 临键锁

2.4.1 临键锁的区间测试

临键锁（Next-Key Lock）：临键锁是查询时InnoDB根据查询的条件而锁定的一个范围，这个范围中包含有间隙锁和记录数；临键锁=间隙锁+记录锁。 其设计的目的是为了解决Phantom Problem(幻读)；主要是阻塞insert，但由于临键锁中包含有记录锁，因此临键锁所锁定的范围内如果包含有记录，那么也会给这些记录添加记录锁，从而造成阻塞除insert之外的操作； Tips：临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。 临键锁锁住的区间为：记录+区间（左开右闭）左开右闭：不锁住左边，锁右边 测试表：

drop table if exists t2;

CREATE TABLE `t2`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) ,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ;

INSERT INTO `t2`(`id`, `num`) VALUES (5, 5);
INSERT INTO `t2`(`id`, `num`) VALUES (10, 10);
INSERT INTO `t2`(`id`, `num`) VALUES (15, 15);
INSERT INTO `t2`(`id`, `num`) VALUES (20, 20);

-- 创建普通索引
create index idx_num on t2(num);

-- 创建唯一索引
create unique index idx_num on t2(num);

-- 删除索引
drop index idx_num on t2;
  • 区间示意图：

MySQL高级锁之临键锁_记录锁

Tips：间隙锁只会阻塞insert，记录锁会阻塞任意的锁（单要注意排他锁和共享锁的关系）； 【测试案例-01-间隙锁】 临键锁的触发不仅把条件区间（11-16）的数据行锁住了，还把临键的数据行统统锁住了；锁住的区间为：(10,15]、(15,20]

锁住的id范围：10（不含）~20（含）

session1

session2

begin;



begin;

select * from t2 where id>11 and id<16 for update;



insert into t2 values(10,0); -- 不阻塞


insert into t2 values(11,0); -- 阻塞


insert into t2 values(15,0); -- 阻塞


insert into t2 values(16,0); -- 阻塞


insert into t2 values(18,10); -- 阻塞


insert into t2 values(20,0); -- 阻塞


insert into t2 values(21,0); -- 不阻塞

rollback;



rollback;

【案例测试-02-记录锁】临键锁是间隙锁+记录数的；上述案例中测试了临键锁中的间隙锁，这次我们来测试一下临键锁中的记录锁；

session1

session2

begin;



begin;

select * from t2 where id>11 and id<16 for update;



select * from t2 where id=12 for update; -- 间隙锁,不阻塞


select * from t2 where id=15 for update; -- 记录数,阻塞


select * from t2 where id=17 for update; -- 间隙锁,不阻塞


select * from t2 where id=20 for update; -- 记录数,不阻塞

rollback;



rollback;

2.4.2 临键锁-普通列

我们刚刚测试的是以主键索引进行测试，如果采用不同的列（普通列、普通索引、唯一索引/主键索引等），则临键锁中的间隙锁和记录锁住的内容大不相同；如果查询的是普通列，那么触发的临键锁为：表级别的间隙锁+表级别的记录锁

  • 测试表：
drop table if exists t2;

CREATE TABLE `t2`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) ,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ;

INSERT INTO `t2`(`id`, `num`) VALUES (5, 5);
INSERT INTO `t2`(`id`, `num`) VALUES (10, 10);
INSERT INTO `t2`(`id`, `num`) VALUES (15, 15);
INSERT INTO `t2`(`id`, `num`) VALUES (20, 20);
1）间隙锁

【案例测试-01-表级别间隙锁】

session1

session2

begin;



begin;

select * from t2 where num=11 for update;



insert into t2 values(null,3); -- 阻塞


insert into t2 values(null,5); -- 阻塞


insert into t2 values(null,8); -- 阻塞


insert into t2 values(null,10); -- 阻塞


insert into t2 values(null,18); -- 阻塞


insert into t2 values(null,21); -- 阻塞

rollback;



rollback;

Tips：innoDB查询如果没有使用到索引默认触发表级临键锁，把所有的间隙都锁住了

2）记录锁

以普通列查询除了会触发表级别的临键锁外，同时还会触发表级别的记录锁；【案例测试-02-表级别记录锁】

session1

session2

begin;



begin;

select * from t2 where num=11 for update;



select * from t2 where id=3 for update; -- 间隙锁,不阻塞


select * from t2 where id=5 for update; -- 记录数,阻塞


select * from t2 where id=8 for update; -- 间隙锁,不阻塞


select * from t2 where id=15 for update; -- 记录数,阻塞


select * from t2 where id=18 for update; -- 间隙锁,不阻塞


select * from t2 where id=20 for update; -- 记录数,阻塞

rollback;



rollback;

2.4.3 临键锁-普通索引

如果查询的列为普通索引列，要看被查询的记录是否在临界值，以及是否是范围查询，才能判断临建锁的范围；

  • 被查询的记录是否在临界值情况：
  • 非临界值：那么间隙锁为当前记录所在的区间，记录锁则不会生效（记录锁不存在）；
  • 临界值：那么间隙锁为相邻的两个区间，记录锁退化成行锁（即只会锁住被查询的那条记录）；
  • 范围查询情况：间隙锁为范围所涉及到的所有区间，记录锁也会升级为范围锁涉及到的区间
drop table if exists t2;

CREATE TABLE `t2`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) ,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ;

INSERT INTO `t2`(`id`, `num`) VALUES (5, 5);
INSERT INTO `t2`(`id`, `num`) VALUES (10, 10);
INSERT INTO `t2`(`id`, `num`) VALUES (15, 15);
INSERT INTO `t2`(`id`, `num`) VALUES (20, 20);

-- 创建普通索引
create index idx_num on t2(num);
1）非临界值

当使用普通索引列查询，查询的记录不处于临界值时，那么间隙锁为被查询记录所在的区间，记录锁则不会生效；【测试案例-01-间隙锁】

session1

session2

begin;



begin;

-- 触发间隙锁，锁住(15,20]区间


select * from t2 where num=17 for update;



insert into t2 values(null,15); -- 阻塞


insert into t2 values(null,18); -- 阻塞


insert into t2 values(null,20); -- 不阻塞

rollback;



rollback;

num=17这条记录不是会锁定(15,20]区间吗？为什么15被阻塞了，20反而没被阻塞呢？这里需要牵扯到另一个问题了，在InnoDB中，相同的普通索引的叶子节点是以主键的顺序进行排列的，我们来模拟一下刚刚插入的数据在B+Tree上的变化：

MySQL高级锁之临键锁_mysql_02

只考虑叶子节点的变化，可以看到在上图在演变的过程中产生了分裂情况（假设每个叶子节点都只存储两个元素），如果普通索引的重复值太多势必会造成大量的分裂情况，减低插入效率，因此索引列不宜选择重复率太大的列； 再看下图数据库表中实际存储的列的样子我们就会明白为什么num=20不阻塞，num=15阻塞了

  • num索引列排列情况：

MySQL高级锁之临键锁_临键锁_03

查询示意图：

MySQL高级锁之临键锁_临键锁_04

【测试案例-02-间隙锁】 当我们把id列的影响也计算进来时，数据就符合我们正常分析的情况了：

session1

session2

begin;



begin;

-- 触发间隙锁，锁住(15,20]区间


select * from t2 where num=17 for update;



insert into t2 values(14,15); -- 不阻塞


insert into t2 values(18,18); -- 阻塞


insert into t2 values(19,20); -- 阻塞

rollback;



rollback;

【测试案例-03-记录锁】当使用普通索引列查询，查询的记录不处于临界值时，那么间隙锁为被查询记录所在的区间，记录锁则不会生效

session1

session2

begin;



begin;

-- 没有num=17的这条记录，记录锁不会存在


select * from t2 where num=17 for update;



select * from t2 where num=15 for update; -- 不阻塞


select * from t2 where num=16 for update; -- 不阻塞


select * from t2 where num=17 for update; -- 不阻塞


select * from t2 where num=20 for update; -- 不阻塞

rollback;



rollback;

2）临界值

【测试案例-01-间隙锁】当使用普通索引列来查询，并且查询的记录处于临界值时，那么间隙锁为相邻的两个区间，记录锁退化成行锁；下面案例将会锁住(10,15]、(15,20]两个区间

session1

session2

begin;



begin;

-- 触发的间隙锁的区间为(10,15]、(15,20]


select * from t2 where num=15 for update;



insert into t2 values(null,8); -- 不阻塞


insert into t2 values(null,10); -- 阻塞


insert into t2 values(null,11); -- 阻塞


insert into t2 values(null,15); -- 阻塞


insert into t2 values(null,18); -- 阻塞


insert into t2 values(null,20); -- 不阻塞

rollback;



rollback;

发现实际插入的数据跟我们分析的情况不一致，这个时候我们依然也要观察B+Tree的实现：

  • 索引底层构建过程：

MySQL高级锁之临键锁_锁_05

  • 临键锁区间：

MySQL高级锁之临键锁_临键锁_06

15处于(10,15]和(15,20]两个临键区间，因此在两个区间内的数据行都被锁住了

【测试案例-02-记录锁】 当使用普通索引列来查询，并且查询的记录处于临界值时，那么间隙锁为相邻的两个区间，记录锁退化成行锁；

session1

session2

begin;



begin;

-- 记录锁只锁住num=15这行记录


select * from t2 where num=15 for update;



select * from t2 where num=10 for update; -- 不阻塞


select * from t2 where num=12 for update; -- 不阻塞


select * from t2 where num=15 for update; -- 阻塞


select * from t2 where num=18 for update; -- 不阻塞


select * from t2 where num=20 for update; -- 不阻塞


select * from t2 where num=22 for update; -- 不阻塞

rollback;



rollback;

3）范围值

【测试案例-01-间隙锁】当使用普通索引进行条件范围查询时，那么间隙锁查询范围所涉及到的区间，记录锁也会升级为查询范围涉及到的区间；

session1

session2

begin;



begin;

-- 间隙锁为(10,20]区间


select * from t2 where num>11 and num <16 for update;



insert into t2 values(9,10); -- 不阻塞


insert into t2 values(11,10); -- 阻塞（参考B+Tree的构建）


insert into t2 values(11,11); -- 阻塞


insert into t2 values(12,12); -- 阻塞


insert into t2 values(15,15); -- 阻塞（被记录锁阻塞）


insert into t2 values(18,18); -- 阻塞


insert into t2 values(19,20); -- 阻塞


insert into t2 values(21,20); -- 不阻塞（参考B+Tree的构建）

rollback;



rollback;

  • 分析底层B+Tree构建情况：

MySQL高级锁之临键锁_锁_07

【测试案例-02-记录锁】 当使用普通索引进行条件范围查询时，那么间隙锁查询范围所涉及到的区间，记录锁也会升级为查询范围涉及到的区间；

session1

session2

begin;



begin;

-- 记录锁的区间为(10,20]区间


select * from t2 where num>11 and num <16 for update;



select * from t2 where num=10 for update; -- 不阻塞（左开右闭）


select * from t2 where num=12 for update; -- 不阻塞（属于间隙）


select * from t2 where num=15 for update; -- 阻塞（触发记录锁）


select * from t2 where num=16 for update; -- 不阻塞（属于间隙）


select * from t2 where num=18 for update; -- 不阻塞（属于间隙）


select * from t2 where num=20 for update; -- 阻塞（左开右闭，触发记录锁）


select * from t2 where num=21 for update; -- 不阻塞（即是间隙，也不在区间）

rollback;



rollback;

2.4.4 临键锁-主键和唯一索引

如果查询的是唯一索引或主键索引，也要看被查询的记录是否在临界值；是否是范围查询等

  • 被查询的记录是否在临界值情况：
  • 不在临界值：间隙锁为当前被查询的记录所在的区间，记录锁会消失；
  • 在临界值：间隙锁会消失，记录锁退化成行锁
  • 范围查询情况：间隙锁为范围查询所涉及到的所有区间，记录数也会升级为范围所涉及到的区间（和普通索引的效果一致）；

创建唯一索引：

-- 删除索引
drop index idx_num on t2;
-- 创建唯一索引
create unique index idx_num on t2(num);
1）非临界值

唯一索引在查询非临界值的记录时和普通索引的特点一样，即==间隙锁为当前记录所在的区间，记录锁不生效；==【测试案例-01-间隙锁】

session-01

session-02

begin;



begin;

-- 间隙锁锁住的区间为(15,20]


select * from t2 where num=17 for update;



insert into t2 values(null,11); -- 不阻塞


insert into t2 values(null,15); -- 不阻塞


insert into t2 values(null,16); -- 阻塞


insert into t2 values(null,18); -- 阻塞


insert into t2 values(null,20); -- 不阻塞


insert into t2 values(null,21); -- 不阻塞

  • 分析num列索引的B+Tree底层构建情况：

MySQL高级锁之临键锁_间隙锁_08

Tips：唯一索引冲突时MySQL会立即响应，不会触发临键锁 【测试案例-02-记录锁】 唯一索引在查询非临界值的记录时，记录锁不生效；

session1

session2

begin;



begin;

-- 没有num=17的这条记录，记录锁不会存在


select * from t2 where num=17 for update;



select * from t2 where num=15 for update; -- 不阻塞


select * from t2 where num=16 for update; -- 不阻塞


select * from t2 where num=17 for update; -- 不阻塞


select * from t2 where num=20 for update; -- 不阻塞

rollback;



rollback;

2）临界值

在使用唯一索引查询临界值时，间隙锁会消失，记录锁会退化成行锁；【测试案例-01-间隙锁】

session1

session2

begin;



begin;

select * from t2 where num=15 for update;



insert into t2 values(null,4); -- 不阻塞


insert into t2 values(null,8); -- 不阻塞


insert into t2 values(null,11); -- 不阻塞


insert into t2 values(null,15); -- 阻塞(阻塞的原因是记录锁，而不是间隙锁)


insert into t2 values(null,28); -- 不阻塞

rollback;

insert into t2 values(null,20); -- 不阻塞


rollback;

【测试案例-02-记录锁】

session1

session2

begin;



begin;

-- 记录锁只锁住num=15这行记录


select * from t2 where num=15 for update;



select * from t2 where num=10 for update; -- 不阻塞


select * from t2 where num=12 for update; -- 不阻塞


select * from t2 where num=15 for update; -- 阻塞


select * from t2 where num=18 for update; -- 不阻塞


select * from t2 where num=20 for update; -- 不阻塞


select * from t2 where num=22 for update; -- 不阻塞

rollback;



rollback;

3）范围值

【测试案例-01-间隙锁】当使用普通索引进行条件范围查询时，那么间隙锁查询范围所涉及到的区间，记录锁也会升级为查询范围涉及到的区间；

session1

session2

begin;



begin;

-- 间隙锁为(10,20]区间


select * from t2 where num>11 and num <16 for update;



insert into t2 values(9,10); -- 不阻塞


insert into t2 values(11,10); -- 阻塞（参考B+Tree的构建）


insert into t2 values(11,11); -- 阻塞


insert into t2 values(12,12); -- 阻塞


insert into t2 values(15,15); -- 阻塞（被记录锁阻塞）


insert into t2 values(18,18); -- 阻塞


insert into t2 values(19,20); -- 阻塞


insert into t2 values(21,20); -- 不阻塞（参考B+Tree的构建）

rollback;



rollback;

  • 分析底层B+Tree构建情况：

MySQL高级锁之临键锁_记录锁_09

【测试案例-02-记录锁】 当使用普通索引进行条件范围查询时，那么间隙锁查询范围所涉及到的区间，记录锁也会升级为查询范围涉及到的区间；

session1

session2

begin;



begin;

-- 记录锁的区间为(10,20]区间


select * from t2 where num>11 and num <16 for update;



select * from t2 where num=10 for update; -- 不阻塞（左开右闭）


select * from t2 where num=12 for update; -- 不阻塞（属于间隙）


select * from t2 where num=15 for update; -- 阻塞（触发记录锁）


select * from t2 where num=16 for update; -- 不阻塞（属于间隙）


select * from t2 where num=18 for update; -- 不阻塞（属于间隙）


select * from t2 where num=20 for update; -- 阻塞（左开右闭，触发记录锁）


select * from t2 where num=21 for update; -- 不阻塞（即是间隙，也不在区间）

rollback;



rollback;

2.4.5 临键锁总结

临键锁是InnoDB在查询数据时锁定的一个范围，这个范围包含有间隙锁和记录锁；根据查询的条件不同、列的类型不同（是否是索引等）触发的临键锁范围也不同；

  • 普通列：临键锁中的间隙锁和记录数均为表级别；
  • 普通索引列：
  • 非临界值：间隙锁为被查询的记录所在的区间，记录锁不生效
  • 临界值：间隙锁为被查询记录所在的相邻两个区间，记录数退化为行锁
  • 范围值：间隙锁和记录数均为查询条件所涉及到的区间
  • 唯一索引或主键索引列：
  • 非临界值：间隙锁为被查询的记录所在的区间，记录锁不生效
  • 临界值：间隙锁失效，记录锁退化为行锁
  • 范围值：间隙锁和记录数均为查询条件所涉及到的区间

Tips：临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

标签：--,t2,高级,阻塞,临键,num,MySQL,where,select
From： https://blog.51cto.com/lscl/6769145

相关文章

  • MySQL高级锁之自增锁
    2.5自增锁MySQL的自增锁是指在使用自增主键（AutoIncrement）时，为了保证==唯一性和正确性==，系统会对自增字段进行加锁。这样可以确保同时插入多条记录时，每条记录都能够获得唯一的自增值。创建测试表：droptableifexistst3;CREATETABLE`t3`(`id`int(11)NOTNULLAUTO_INC......
  • PyMySQL插入字典类型的数据
    importpymysqlMYSQL_CONFIG={'host':'localhost',#IP地址'port':3306,#端口'user':'root',#用户名'passwd':'123456',#密码'db':'layui',......
  • Ubuntu安装MySQL、nginx、nacos
    Ubuntu安装mysql1、安装mysql#此命令直接安装最新版mysql8.0sudoapt-getinstallmysql-server2、查看默认用户及密码cat/etc/mysql/debian.cnfuser =debian-sys-maintpassword =s8hjWltF0W2EBU323、登录该用户mysql-udebian-sys-maint-pusemysql;updateuse......
  • mysql text 长度定义
    MySQLText字段长度定义作为一名经验丰富的开发者，我将教你如何实现“MySQLText字段长度定义”。下面我将分步骤向你介绍整个过程，并附上相应的代码示例。步骤步骤说明1创建数据库表2设计Text字段3定义Text字段的长度步骤1：创建数据库表首先，我们需要创建一......
  • mysql 事务自动回滚
    MySQL事务自动回滚在MySQL数据库中，事务是一组原子性操作的集合，它们要么全部成功执行，要么全部失败回滚。事务可以保证在并发环境下数据的一致性和完整性。当一个事务执行出现错误或异常时，数据库会自动回滚到事务开始之前的状态，保证数据的完整性。事务的基本概念在MySQL中，事务由以......
  • mysql 事务和存储过程
    MySQL事务和存储过程1.事务的概念和流程事务是指一组数据库操作，它们作为一个逻辑单元一起执行，并且要么全部都执行成功，要么全部都回滚到事务开始前的状态。MySQL中的事务主要用于保证数据的完整性和一致性。以下是MySQL事务的基本流程：步骤描述1开启事务2执行SQL......
  • mysql 事务当中为什么出现幻读
    MySQL事务中为什么出现幻读1.介绍在MySQL数据库中，事务是一组原子性的操作，可以确保数据的一致性和完整性。然而，在一些特定情况下，即使使用了事务，仍然可能出现幻读的问题。幻读指的是在一个事务中，当对某个表进行查询时，其他事务在这个事务的锁尚未释放之前，插入了新的数据，导致前一个......
  • mysql 使用查询结果update
    MySQL使用查询结果更新的流程在MySQL中，我们可以使用查询结果来更新数据。下面是实现这个过程的步骤表格：步骤描述第一步连接到MySQL数据库第二步编写查询语句第三步执行查询语句第四步获取查询结果第五步编写更新语句第六步执行更新语句下面我......
  • mysql 时间加5分钟
    MySQL时间加5分钟MySQL是一种流行的关系型数据库管理系统，广泛用于各种应用程序的数据存储和处理。在MySQL中，我们经常需要对日期和时间进行计算和操作。本文将介绍如何在MySQL中将时间加5分钟，以及提供相应的代码示例。使用DATE_ADD函数在MySQL中，我们可以使用DATE_ADD函数来对时间......
  • mysql 时间范围
    MySQL时间范围MySQL是一个广泛使用的关系型数据库管理系统，它提供了丰富的功能来处理和操作数据。在许多应用中，我们经常需要根据时间范围来查询和分析数据。本文将介绍如何在MySQL中使用时间范围来查询数据，并提供一些代码示例来帮助您更好地理解。时间和日期类型在MySQL中，......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99