【MySQL】（五）InnoDB中各种锁的验证

最新推荐文章于 2021-12-22 17:29:45 发布
最新推荐文章于 2021-12-22 17:29:45 发布
阅读量341 收藏
点赞数 1
分类专栏： # MySQL 数据库 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_38249409/article/details/109530412
版权

文章目录

1.InnoDB锁的验证

接上一篇文章，【MySQL】锁机制，这里对里面提到的InnoDB中的锁做验证。
所有的验证都是在RR隔离级别下进行的。

首先创建并初始化test表数据：

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `sn` int(11) DEFAULT NULL,
  `uq` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_uq` (`uq`) USING BTREE,
  KEY `idx_sn` (`sn`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `test` (`id`, `name`, `sn`, `uq`) VALUES (1, '张三', 3, 3);
INSERT INTO `test` (`id`, `name`, `sn`, `uq`) VALUES (4, '李四', 5, 5);
INSERT INTO `test` (`id`, `name`, `sn`, `uq`) VALUES (7, '王五', 7, 7);
INSERT INTO `test` (`id`, `name`, `sn`, `uq`) VALUES (10, '赵六', 11, 11);

1.1.验证是否对索引加锁

验证没有加索引的字段。

首先用没加索引的name字段做筛选条件，先开启一个session，锁住一行数据。

-- sesison 1
begin;
select * from test where name = '张三' for update;
+----+------+----+----+
| id | name | sn | uq |
+----+------+----+----+
|  1 | 张三 |  3 |  3 |
+----+------+----+----+

再开启另外一个session，查询其它的数据、

-- session 2
select * from test where name = '张三' for update;
select * from test where name = '李四' for update;
select * from test where name = '王五' for update;
select * from test where name = '赵六' for update;

session2的几个查询结果都阻塞了。

验证加了索引的字段

使用加了索引的字段sn做测试，同样，先开启一个session，锁住一行数据。

-- session 1
begin;
select * from test where sn = 3 for update;
+----+------+----+----+
| id | name | sn | uq |
+----+------+----+----+
|  1 | 张三 |  3 |  3 |
+----+------+----+----+

再开启另外一个session，使用sn作为检索条件查询相同行数据。

-- session 2
begin;
select * from test where sn = 3 for update;

此时SQL运行阻塞，再尝试李四的数据。

-- session 3
begin;
select * from test where sn = 5 for update;
+----+------+----+----+
| id | name | sn | uq |
+----+------+----+----+
|  4 | 李四 |  5 |  5 |
+----+------+----+----+

发现李四可以成功查出，说明InnoDB就是对索引加的锁。

验证锁的传递

此外，MySQL的辅助索引最终都会指向主键索引，那么是否对辅助索引加锁时会同时对主键索引加锁呢？
下面对辅助索引加锁，验证是否可以给同一行的主键索引加锁。

-- session 1
begin;
select * from test where sn = 5 for update;
--session 2
begin;
select * from test where id = 4 for update;
select * from test where uq = 5 for update;

在给说sn=5这一行数据加锁后，下面不管是id=4还是uq=5执行是程序都挂起了，说明对辅助索引加锁时，同时会锁定的主键索引。

1.2.验证加锁算法

  • 记录锁(Rrcord Lock)：对唯一性索引（主键索引或唯一索引）做等值查询，如果精确的匹配到了一条记录，则锁住这条记录的索引。
  • 间隙锁(Gap Lock)：对唯一性或普通索引查询的单条记录不存在，则会对此查询条件两端的记录中间的间隙加锁；或精确命中普通索引的一行记录，则会对这行记录两端的间隙及其本身加锁，加锁范围是一个左开右开的区间。
  • 临键锁(Next-key Lock)：对索引做范围查询，包含记录和区间，对范围内的记录和区间加锁的同时，会对范围内最右一个记录的右侧的区间及右侧的第一个记录加锁。是一个左开右闭的区间。

唯一性索引记录锁

使用主键索引做验证，精确查询某一行已存在的记录，验证是否锁住间隙。

-- session 1
begin;
select * from test where id = 4 for update;
-- session 2
begin;
select * from test where id = 3 for update;
select * from test where id = 4 for update;
select * from test where id = 7 for update;

其中id=4程序阻塞，id=3,7时正常执行，说明锁住的是id=4这个记录的索引。

唯一性索引间隙锁

先删除上面插入的1、2、4、6数据，避免数据影响验证结果。然后精确查询一行不存的数据。

-- session 1
begin;
select * from test where id = 5 for update;
-- session 2
insert into test (id) values (3);
insert into test (id) values (4);
insert into test (id) values (5);
insert into test (id) values (6);
insert into test (id) values (7);
insert into test (id) values (8);

其中id=3,8顺利插入，id=4,7因唯一索引验证插入失败，id=5,6程序阻塞，在sesion1提交后，出现唯一索引验证插入失败。
此时锁住的是(4,7)。

普通索间隙锁

-- session 1
begin;
select * from test where sn = 5 for update;
-- session 2
insert into test (id,sn) values (11,3);
insert into test (id,sn) values (11,4);
insert into test (id,sn) values (11,5);
insert into test (id,sn) values (11,6);
insert into test (id,sn) values (11,7);

此时sn=3,4,5,6时都阻塞，sn=7插入成功，锁住的是[3,7)

唯一性索引临键锁

验证范围查询的加锁情况，主键id分别为1,4,7,10，先查看范围中不包含索引记录的情况，例如 > 4 且 < 7的情况。

-- session 1
begin;
select * from test where id > 4 and id < 7 for update;
-- session 2
insert into test (id) values (3);
insert into test (id) values (4);
insert into test (id) values (5);
insert into test (id) values (6);
insert into test (id) values (7);
insert into test (id) values (8);

此时id=3，8时成功插入，4提示主键重复，5，6，7阻塞，加锁范围是(4,7]。

再验证如果范围包含了7是个什么情况呢？

-- session 1
begin;
select * from test where id > 4 and id <= 7 for update;
-- session 2
insert into test (id) values (3);
insert into test (id) values (4);
insert into test (id) values (7);
insert into test (id) values (8);
insert into test (id) values (10);
insert into test (id) values (11);

除了上面id < 7时的验证结果外，id=8,10的时候也阻塞了，而11可以正常插入。此时加锁范围是(4,10]。

如果范围同样包含了4，会是什么结果呢？是否4之前的间隙和记录也会被锁住呢？

-- session 1
begin;
select * from test where id >= 4 and id <= 7 for update;
-- session 2
insert into test (id) values (3);
insert into test (id) values (4);
insert into test (id) values (11);

验证的3个插入记录中，只有id=4时阻塞，3和11都成功插入，此时加锁范围是[4,10]。

所谓临键锁，就是先找到查询范围内最右侧的索引记录值（升序的末尾记录值），将这个索引记录值的下一个记录值及其它们之间的索引间隙都额外的加上锁。

1.3.验证结论

①只有对唯一性索引做精确查询并命中时，才会加记录锁。
②唯一性索引无法精确查询数据时，或使用普通索引查询时，会加间隙锁。
③如果对索引做范围查询，会锁住间隙和记录，即临键锁。

挥之以墨
关注
专栏目录
mysql中InnoDB锁
L X D的博客
08-18 160
mysql中使用比较多的两种引擎是MyISAM和InnoDB MyISAM使用表级锁。 InnoDB使用行级锁。 表锁：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低 行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高 InnoDB InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。 数据库实现事务...
MySQL之InnoDB中的锁
爪哇岛咖啡种植农场
11-04 253
MySQL中的锁
mysql行级锁验证过程
qq_35702095的博客
01-15 442
行级锁 表锁 参考链接：https://www.cnblogs.com/lushilin/p/6135374.html   采用innodb引擎时，能命中索引就自动使用了行级锁，一些情况下使用表锁更高效时，会自动使用表锁 能否命中索引可用explain命令 在mysql客户端查看，type不为all就算命中。 explain命令 https://www.cnblogs.com/clphp/...
InnoDB 的各种锁
baidu_36146918的博客
12-22 226
InnoDB 的各种锁 脏读: 读到了别的事务回滚前的脏数据(不满足事务的隔离性, 一致性, 原子性) 不可重复读: 同一事务执行两次查询值不同(不满足事务的隔离性, 一致性) 幻读: 同一事务执行两次相同查询数量不同(不满足事务的隔离性, 一致性) 锁大类 latch: 一般称为闩锁（轻量级别的锁），作用对象是线程，维护缓存(innodb_buffer_pool_instances)中的数据。因为其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差 lock: 作用对象是事务，用于保
mysql中InnoDB中的各种锁
沉默的夏虫
06-25 439
一种分类方式： 行锁(索引上锁) 表锁（表上锁） 一种分类方式： 共享锁（以及获取前先获取意向共享锁）lock in share model 排它锁（以及获取前先获取意向排它锁）for update 一种分类方式（该分类前提—命中索引）： 记录锁(单条行锁) 间隙锁（无记录时候的临键锁退化为间隙锁） 临键锁(查询范围左侧那条记录至查询范围右侧那条记录之间的锁，左闭右开的锁住) 其他的：自增锁（针对自增键累加操作的锁） 参考：https://www.bilibili.com/video/BV1
验证mysql的InnoDB引擎的行锁和表锁
sinat_38852244的博客
07-07 482
基础知识 set autocommit=0; # 关闭自动提交 commit; 提交事务 for UPDATE 手工加锁 实验验证 1. innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的. 如下图所示 ROLE_NAME 这个字段没有索引，开启事务后，执行了查询语句，没有提交事务，锁住全表，导致第二条更新语句无法执行，当我commit以后，第二条语句立马执行成功，验证没有索引的情况下锁住全表 当给ROLE_NAME加上索引后，查询ROLE_NAME="Redis监控员"的人员，更新ROLE_
synchronized锁验证（偏向、轻量级、重量级）
全场梦游c的博客
09-28 188
看synchronized之前先了解一下对象头里面的内容： 一个小demo，运行一下 public class MyThred { static TestDemo testDemo; public static void main(String[] args) throws Exception { testDemo= new TestDemo(); System.out.println(ClassLayout.parseIns
mysql_innoDB 事务与锁详解
10-29
#### MySQL的InnoDB引擎中事务与锁 MySQL的InnoDB存储引擎支持多种类型的锁，以帮助实现事务的ACID特性。主要包括以下几种： 1. **共享锁（S）**：当多个事务试图同时读取相同的数据时使用。例如，使用`SELECT ......
MySQL InnoDB row_id边界溢出验证的方法步骤
09-09
在MySQL的InnoDB存储引擎中，每张表都需要有一个唯一的标识符来区分不同的记录，即使在没有显式定义主键的情况下也是如此。在这种情况下，InnoDB会自动创建一个名为`row_id`的系统列，它是一个无符号的长整型（`...
mysql5.5与mysq 5.6中禁用innodb引擎的方法
12-15
在MySQL数据库系统中，InnoDB和MyISAM是两种主要的存储引擎，它们各自具有不同的特点和适用场景。InnoDB是MySQL默认的存储引擎，它提供了事务处理、行级锁定以及外键支持，适合处理大量数据并需要高并发读写操作的...
mysql隔离级别验证_[InnoDB系列] - 实例解析Innodb的隔离级别以及锁模式
weixin_35070925的博客
02-28 67
1、隔离级别为：READ COMMITTEDREAD COMMITTED一个有些象Oracle的隔离级别。所有SELECT ... FOR UPDATE和SELECT ... LOCK IN SHAREMOD语句仅锁定索引记录，而不锁定记录前的间隙，因而允许随意紧挨着已锁定的记录插入新记录。UPDATE和DELETE语句使用一个带唯一搜索条件的唯一的索引仅锁定找到的索引记录，而不包括记录前的间隙。...
MYSQL_innodb的加锁机制实验
tianlianchao1982的专栏
06-07 709
MYSQL innodb在一定程度上实现了行锁的功能，在功能上与ORACLE差不多。但innodb行锁的实现与oracle不相同，innodb的行锁加在索引上，如果没有索引，整张表都会被锁定，下面是实验过程。 --session 1 mysql> create table test(id int,nick varchar(32)) engine=innodb ; Query OK, 0 ro
带你从根本理解mysql innodb的各种锁
u013125075的博客
02-15 358
锁的相关概念： 锁：数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。而锁就是并发控制的一个重要的手段。 表锁：对某个表进行加锁。能够保证不会出现死锁，但是发生锁冲突几率高，并发性能差。 排他锁（X）:排他锁也叫“写锁”，获得某个表的排他锁的事务便拥有了对这个...
深入了解mysql--gap locks,Next-Key Locks
aoduwo7136的博客
07-11 121
Next-Key Locks Next-Key Locks是在存储引擎innodb、事务级别在可重复读的情况下使用的数据库锁，官网上有介绍，Next-Key Locks是行锁和gap锁的组合。行锁是什么我们都很清楚，这篇文章主要简单分析一下mysql中的gap锁是什么。innodb默认的锁就是Next-Key locks。 GAP锁 gap锁，又称为间隙锁。存在的主要目的就是为...
InnoDB引擎-一致性读锁定(为查询添加锁)
makeliwei1的博客
09-02 637
一、为查询添加显示的锁 SELECT …..FOR UPDATE;在一个事务中为查询操作加一个排它锁，其他事务可以执行查询操作但是查询操作不允许加任何锁，否则会发生阻塞；对于其他事务如果要更新当前事务查询的行，那么会发生阻塞的情况。 SELECT……LOCK IN SHARE MODE;在一个事物中对查询的行加共享锁，此时其他事务对同一行执行查询操作可以加共享锁，但是不允许加排他锁，同时，其他...
sql的几种常用锁简述
weixin_30295091的博客
04-18 214
比较全的文章地址保存下：http://www.cnblogs.com/knowledgesea/p/3714417.html SELECT * FROM dbo.AASELECT * FROM dbo.B--1.排它锁--增删改查都不行--这个是数据库自己，为了出现数据错乱、脏数据自己加的处理机制--连接1begin tran update dbo.B set name='bb' ...
MySQL(InnoDB剖析):---锁之（行锁的3种算法(Record Lock、Gap Lock、Next-Key Lock)、解决Phantom Problem）
m0_46405589的博客
02-01 461
InnoDB支持3种行锁的算法，分别是： Record Lock：单个行记录上的锁 Gap Lock：间隙锁，锁定一个范围，但不包含记录本身 Next-Key Lock：Gap Lock与Record Lock的结合，锁定一个范围，并且锁定记录本身 一、Record Lock Record Lock总是会去锁住索引记录 如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB会使用隐式的主键来进行锁定 二、Gap Lock 附带在下面介绍 三、Next-..
深入解析MySQL InnoDB存储引擎
接下来，我们将关注InnoDB存储引擎，它是MySQL中最常用的存储引擎，支持事务处理、行级锁定和外键约束。InnoDB的体系结构包括： 1. **Master Thread**：主线程负责定期执行检查点（Checkpoint）操作，以确保数据的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

挥之以墨

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值