mysql8 Record Locks ，Gap Locks， Next-Key Locks实验2 唯一索引和非唯一索引，无索引情况

已于 2022-05-06 15:56:11 修改
阅读量810 收藏 1
点赞数
文章标签： mysql 数据库
于 2022-05-05 15:30:22 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xuejianxinokok/article/details/124589587
版权

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

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

上文讨论了 RR 隔离级别 主键索引的加锁情况，
本文接着讨论 RR 隔离级别下 二级唯一索引和普通二级索引 以及无索引时的加锁情况。

4.3 二级唯一索引等值查询

这次我们对 f1 字段操作，这是一个二级 唯一索引字段

4.3.1 锁定存在的值
-- 查询存在的值 11
START TRANSACTION;
SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f1=11  FOR UPDATE ;
-- 当前线程THREAD_ID=44
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
|        44 | 11 | 11 | 11 | 11 |
+-----------+----+----+----+----+
1 row in set (0.0 sec)

查询锁的信息，发现有3个锁，如下图所示：

在这里插入图片描述

【行号1】 是一个表级的意向锁

【行号2】 是一个加在 二级唯一索引 ui_f1上的 record 锁，锁类型为 X(排他),REC_NO__GAP 说明没有对间隙加锁

值得注意的是 LOCK_DATA为【11，11】 ，我们知道二级索引的record 组成为（key,主键），所以第一个11 是二级索引字段f1 的值，而第二个11 是主键的值

【行号3】 是一个主键索引上的 排他record 锁

4.3.2 锁定不存在的值
-- 查询存在的值 12
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f1=12  FOR UPDATE;
Empty set (0.00 sec)

查询加锁情况：

在这里插入图片描述

【行2】 对ui_f1 在数据（13，13）上添加了 X,GAP 锁，这其实就是一个 record lock和gap lock 的组合

这是个 next key lock,锁定的范围应该是 (11,13]

我们再开启一个连接验证一下锁定的范围:

-- 插入9 ok
mysql> INSERT INTO locktest (id,f1,f2,f3) VALUES (9,9,9,9);
Query OK, 1 row affected (0.00 sec)
-- 插入12 阻塞
INSERT INTO locktest (id,f1,f2,f3) VALUES (12,12,12,12);
-- 插入 14 ok
mysql> INSERT INTO locktest (id,f1,f2,f3) VALUES (14,14,14,14);
Query OK, 1 row affected (0.00 sec)

4.4 二级唯一索引范围查询

验证完唯一索引的等值查询加锁情况，我们开始验证范围查询

4.4.1 单范围查询
START TRANSACTION;
mysql> SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f1>11  FOR UPDATE;
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
|        44 | 13 | 13 | 13 | 13 |
|        44 | 20 | 20 | 20 | 20 |
+-----------+----+----+----+----+
2 rows in set (0.00 sec)

加锁信息如下：

在这里插入图片描述

4.4.2 双范围查询
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f1>11 AND t.f1<20  FOR UPDATE;
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
|        44 | 13 | 13 | 13 | 13 |
+-----------+----+----+----+----+
1 row in set (0.00 sec)

加锁信息如下图：

在这里插入图片描述

4.4.3 不存在的双范围查询
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> -- 记录 12 和21 都不存在
mysql> SELECT * FROM locktest t WHERE t.f1>=12 AND t.f1<=21 FOR UPDATE;
+----+----+----+----+
| id | f1 | f2 | f3 |
+----+----+----+----+
| 13 | 13 | 13 | 13 |
| 20 | 20 | 20 | 20 |
+----+----+----+----+
2 rows in set (0.00 sec)

加锁信息如下：

在这里插入图片描述

4.5 非唯一索引等值查询

这次我们对 f2 字段操作，这个一个二级非唯一索引字段

4.5.1 锁定存在的值
-- 查询存在的值 11
START TRANSACTION;

SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f2=11  FOR UPDATE ;
-- 当前线程THREAD_ID=44
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
|        44 | 11 | 11 | 11 | 11 |
+-----------+----+----+----+----+
1 row in set (0.0 sec)

查询锁的信息，发现有4个锁，如下图所示：

在这里插入图片描述

【行号1】 是一个表级的意向锁

【行号2】 是一个加在 二级索引 i_f2 上的 record 锁，锁类型为 X(排他), 值得注意的是 LOCK_DATA为【11，11】 ，我们知道二级索引的record 组成为（key,主键），所以第一个11 是二级索引字段f1 的值，而第二个11 是主键的值

【行号3】 是一个主键索引上的 排他record 锁

【行号4】 是一个 next key lock,因为 这是一个组合锁(X,GAP),锁定的数据为(13,13)，也因为 13 是我们查询条件11 的下一个key

综上来看，加锁的范围是 (11,13]

也就是说对于二级非唯一索引的等值查询也会产生 next key lock

我们用以下sql 验证以下加锁范围

-- 启动一个新窗口执行以下操作

-- 插入 <11 的数据
mysql> INSERT INTO locktest (id,f1,f2,f3) VALUES (9,9,9,9);
Query OK, 1 row affected (0.00 sec)

-- 插入 =11 的数据 ，阻塞
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO locktest (id,f1,f2,f3) VALUES (111,111,11,11);

在这里插入图片描述

注意 【行2】 的 X,GAP,INSERT_INTENTION 无法获取锁 正在WATING

-- 插入>11 的数据,阻塞

START TRANSACTION;
INSERT INTO locktest (id,f1,f2,f3) VALUES (12,12,12,12);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ozkmj4KQ-1651735032664)(./assets/20220505141220-image.png)]

-- 插入 =13 的数据,不阻塞，这说明  在13 上的X,GAP 锁并不阻止 13 的插入

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO locktest (id,f1,f2,f3) VALUES (1313,1313,13,13);
Query OK, 1 row affected (0.00 sec)


-- 插入>13 的数据，不阻塞
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO locktest (id,f1,f2,f3) VALUES (14,14,14,14);
Query OK, 1 row affected (0.00 sec)
4.5.2 锁定不存在的值
-- 查询存在的值 12

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f2=12  FOR UPDATE;
Empty set (0.00 sec)

查询加锁情况：

在这里插入图片描述

【行2】 对 i_f2 在数据（13，13）上添加了 X,GAP 锁，这其实就是一个 record lock和gap lock 的组合

这是个 next key lock,锁定的范围应该是 (11,13]

4.6 非唯一索引范围查询

验证完 非唯一索引的等值查询加锁情况，我们开始验证范围查询

4.6.1 单范围查询
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f2>11  FOR UPDATE;
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
|        44 | 13 | 13 | 13 | 13 |
|        44 | 20 | 20 | 20 | 20 |
+-----------+----+----+----+----+
2 rows in set (0.00 sec)

加锁信息如下：

在这里插入图片描述

4.6.2 双范围查询
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f2>11 AND t.f2<20  FOR UPDATE;
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
|        44 | 13 | 13 | 13 | 13 |
+-----------+----+----+----+----+
1 row in set (0.00 sec)

加锁信息如下图：

在这里插入图片描述

4.6.3 不存在的双范围查询
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> -- 记录 12 和21 都不存在
mysql> SELECT * FROM locktest t WHERE t.f2>=12 AND t.f2<=21 FOR UPDATE;
+----+----+----+----+
| id | f1 | f2 | f3 |
+----+----+----+----+
| 13 | 13 | 13 | 13 |
| 20 | 20 | 20 | 20 |
+----+----+----+----+
2 rows in set (0.00 sec)

加锁信息如下：

在这里插入图片描述

4.7 无索引等值查询

这次我们对 f3 字段操作，这个一个 无索引字段

4.7.1 锁定存在的值
-- 查询存在的值 11
START TRANSACTION;

SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f3=11  FOR UPDATE ;
-- 当前线程THREAD_ID=44
+-----------+----+----+----+----+
| THREAD_ID | id | f1 | f2 | f3 |
+-----------+----+----+----+----+
|        44 | 11 | 11 | 11 | 11 |
+-----------+----+----+----+----+
1 row in set (0.0 sec)

查询锁的信息，发现数据库中的数据都添加了锁，如下图所示：

在这里插入图片描述

我们用以下sql 验证以下加锁范围

-- 启动一个新窗口执行以下操作

-- 插入 <11 的数据  阻塞 无法插入
mysql> INSERT INTO locktest (id,f1,f2,f3) VALUES (9,9,9,9);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GLTiJz19-1651735032673)(./assets/20220505150509-image.png)]

注意 【行2】 的 X,GAP,INSERT_INTENTION 无法获取锁 正在WATING

-- 插入 =11 的数据 ，阻塞
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO locktest (id,f1,f2,f3) VALUES (111,111,11,11);

在这里插入图片描述

-- 插入>11 的数据,阻塞

START TRANSACTION;
INSERT INTO locktest (id,f1,f2,f3) VALUES (12,12,12,12);

在这里插入图片描述

-- 插入 =13 的数据,阻塞

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO locktest (id,f1,f2,f3) VALUES (1313,1313,13,13);
Query OK, 1 row affected (0.00 sec)

在这里插入图片描述

4.7.2 锁定不存在的值
-- 查询存在的值 12

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT PS_CURRENT_THREAD_ID() THREAD_ID ,t.* FROM locktest t WHERE t.f3=12  FOR UPDATE;
Empty set (0.00 sec)

查询加锁情况：

在这里插入图片描述

4.8 无索引范围查询

这里就不一一验证了，因为在无索引的情况下都是全表锁定。这也说明了加索引的重要性

xuejianxinokok
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
当Mysql行锁遇到复合主键与多列索引详解
09-09
Next-Key Locking不仅锁定索引记录本身，还可能锁定索引记录之间的间隙（Gap Locks），以防止其他事务在此范围内插入新的记录。然而，当查询的索引是唯一索引（包括主键索引）时，Next-Key Locking会降级为Record ...
mysql中的锁机制深入讲解
12-16
5. **临键锁(Next-key Locks)**：临键锁是记录锁和间隙锁的组合，锁定特定的索引记录以及记录前面的间隙，防止插入导致幻读。在RR隔离级别下，InnoDB默认使用临键锁，除非查询能完全匹配索引。 6. **插入意向锁...
MySQL锁--02---行锁 (记录锁(Record Locks))
时光里的博客
12-17 2671
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录行锁 行锁
mysql 数据库锁 Record Locks、Gap Locks、Next-Key Locks
一名数据库爱好者的专栏
03-04 289
Next-Key Locks 是 MySQL 的 InnoDB 存储引擎的一种锁实现。 MVCC 不能解决幻影读问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。 Record Locks 锁定一个记录上的索引，而不是记录本身。 如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。 Gap Locks 锁定索
MySQL数据库锁：Record Lock，Gap Lock 和 Next-Key Lock，锁住的是什么？
最新发布
CTZL123456的博客
06-07 981
记录锁，它是针对索引记录的锁，锁定的总是索引记录。在多用户数据库系统中，多个事务可能会同时尝试读取或修改同一条记录，确保只有一个事务能在某一时刻修改该记录，其他事务只能读取，或者在写锁释放后再进行修改。Gap Lock，间隙锁，它是一种行级锁，锁住两个索引记录之间的间隙，而不是实际的数据记录，由InnoDB隐式添加。如下图：(1,3) 表示锁住记录1 和记录3 之间的间隙，这样记录2 就无法插入，间隙可能跨越单个索引值、多个索引值，甚至是空。在InnoDB中，间隙锁是通过索引来实现的。
mysql8 Record Locks ，Gap Locks， Next-Key Locks 实验1
xuejianxinokok的专栏
04-30 1081
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. 那么怎么理解 *
mysql 唯一索引 锁表_MySQL InnoDB中唯一索引和非唯一索引时的加锁情况
weixin_42509894的博客
01-19 995
MySQL InnoDB中唯一索引和非唯一索引时的加锁情况MySQL的默认隔离级别为RR，这篇文章讨论一下唯一索引和非唯一索引时，使用lock read(consistent read不加锁，不讨论)时的不同的加锁策略。 前提条件是使用了RR隔离级别,并且使用了索引扫描。假设有以下表的定义和数据：mysql>create table test1 (year int);mysql>ins...
MYSQL知识点回顾：InnoDB的锁机制（一）记录锁
weixin_36531037的博客
10-28 655
MYSQL知识点回顾：InnoDB的锁机制（一）记录锁
MySQL常用面试题（附答案）
11-19
- 行锁（Record Locks）、间隙锁（Gap Locks）、临键锁（Next-Key Locks）。 23. **行锁实现**： - 通过索引来锁定记录，避免全表扫描。 24. **MySQL 性能指标**： - 查询响应时间、吞吐量、CPU使用率、内存...
最热门的MySQL 面试题汇总
04-26
22. **MySQL中的锁**：主要包括行锁（如Record Lock、Gap Lock、Next-Key Lock）、表锁、页锁等。 23. **四种隔离级别**：Read Uncommitted（读未提交）、Read Committed（读已提交）、Repeatable Read（可重复读）...
MySQL InnoDB update锁表问题Record Locks
做技术，贵在学习与坚持！
07-22 2276
最近做的项目中出现了锁表的问题，导致表无法进行事务(DML)操作，经过排查发现，我们项目中的购物车表有个字段cart_id为uuid类型，并且未设置索引，这里的场景是根据cart_id进行更新内容，Mysql InnoDB行锁不使用索引锁表的时候会锁整张表。 通过命令查询锁日志找到相关锁的sql语句： show engine innodb status; mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 11
mysql8 RC隔离级别 Record Locks ，Gap Locks， Next-Key Locks实验3和总结
xuejianxinokok的专栏
05-06 961
5. RC 隔离级别下加锁行为 由于隔离级别的实现是靠加锁来实现的 所以在 考察完 RR 隔离级别下加锁的情形后 我们需要分析另一个常用的隔离级别RC 首先需要把数据库的全局隔离级别设置为RC set global TRANSACTION ISOLATION level read COMMITTED; show global variables like 'transaction_isolation'; -- 恢复为RR -- set global TRANSACTION ISOLATION leve
通过一个例子详解 MySQL record lock(记录锁) 的加锁方式
落落的专栏 专注SQL调优 性能调优
10-11 2227
本文基于MySQL8.0.19，下面通过一个例子讲解 MySQL record lock(记录锁) 的加锁方式 SESSION1: mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 8.0.19 | +-----------+ 1 row in set (0.01 sec) mysql> CREATE TABLE t(id INT UNSIGNED PRIMARY KEY ,NAME VARCH
MYSQL 从Record lock 到 Next-Key Locks 到 GAP_LOCK
sql server的专栏
04-04 156
大多数人第一次提到锁，可能认为锁可能是针对磁盘上的物理的数据记录，实际上，所有的操作都在内存中完成，锁怎么可能是针对磁盘上的物理数据呢？在认识到锁都是在内存中产生的后，锁是在什么范围，...
nextkey-lock出现条件
qq1805696978的博客
04-28 372
Next-key lock 行锁 + gap锁。 1、没有设置索引或者没有命中索引，全表锁（x锁 + gap锁） 2、命中非唯一索引，gap锁 3、命中唯一索引，x锁
看来，MySQL next-key lock 的 bug 并没有被修复！
程序员小航
06-06 177
前言 在上一篇文章《MySQL next-key lock 加锁范围是什么？》中已经介绍了主键索引的加锁范围，现在来回顾一下： 加锁时，会先给表添加意向锁，IX 或 IS； 加锁是如果是多个范围，是分开加了多个锁，每个范围都有锁；（这个可以实践下 id < 20 的情况） 主键等值查询，数据存在时，会对该主键索引的值加行锁 X,REC_NOT_GAP； 主键等值查询，数据不存在时，会对查询条件主键值所在的间隙添加间隙锁 X,GAP； 主键等值查询，范围查询时情况则比较复杂： 8.0.17 版本是前
mysql索引
qq_45610772的博客
09-19 686
索引 1：什么是索引？ 索引其实就是一种为了加速对数据表中的数据行的检索从而创建的分散存储的数据结构(也可以打个比喻：就是书中的目录) 注意： 1： 一般关系型数据库的中的索引是 硬盘级索引，就是说索引是存放在硬盘中的 2：如果程序访问我们数据库中的数据行数如果是几万的数据的规模的话，其实我们无需在意索引，但是当数据规模达到几千万上亿的情况下我们一般就会使用到索引 2：索引的作用： 索引在数据库中的作用其实是类似于目录在书本中的作用，用来提高查找信息的速度，用索引查找信息的时候，无需对整张表进行扫描，可
MYSQL加鎖
mywaya2333的博客
11-17 661
鎖
MySQL索引与锁机制详解：从B+树到聚簇索引
“mysql索引和锁机制ppt介绍” 在MySQL数据库中，索引和锁机制是提高数据访问效率和保证数据一致性的重要手段。这份PPT详细介绍了这两个概念。 首先，索引是数据库管理系统中用于加速数据检索的数据结构。MySQL...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值