文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

【MySQL】说透锁机制(一)行锁 加锁规则 之 等值查询

2023-08-18 15:36

关注

如何控制并发是数据库领域中非常重要的问题之一,MySQL为了解决并发带来的问题,设计了事务隔离机制、锁机制、MVCC机制等等,用一整套机制来解决并发问题,接下来会分几篇来分析MySQL5.7版本InnoDB引擎的锁机制。

由于锁机制的内容很多,一篇写完字数太多,所以我决定分几篇来逐步更新。行锁更重要,优先从行锁说起,然后再说表锁。
对于行锁,行锁的S/X模式和3种算法是最基础的,然后再深入分析行锁的加锁规则等等几篇,本文主要深入分析行锁的加锁规则中的等值查询。


行级锁从锁的模式(lock_mode),可以分为共享锁和排它锁:

SQL语句对应上的行锁说明如下:

操作锁的模式说明
普通select语句无行锁在上文MVCC机制讲过,普通的 select 语句属于快照读
select…lock in share modeS显示(explicit)读锁, 上锁后,其它事务对锁定的索引记录仍可以上S锁,但阻塞其它事务对锁定的索引记录上X锁
select…for updateX显式(explicit)写锁,上锁后,阻塞其它事务对锁定的索引记录上S或X锁
insert/update/deleteX隐式(implicit)写锁,上锁后,阻塞其它事务对锁定的索引记录上S或X锁

InnoDB引擎有3种行锁的算法,都是锁定的索引:

Record Lock

Gap Lock

在这里插入图片描述

举个例子:当索引的值有10,20,30,40时,那么索引就存在如下间隙(圆括号表示不包括区间点):

(下界限, 10)(10, 20)(20, 30)(30, 40)(40, 上界限supremun)

因为是锁定索引之前的间隙,所以就存在如下间隙锁

间隙范围索引记录
(下界限, 10)10
(10, 20)20
(20, 30)30
(30, 40)40
(40, 上界限supremun)supremun

特殊说明:由于间隙锁是为了解决幻读问题,所以在读已提交(RC)事务隔离级别是显示禁用间隙锁的。

Next-key Lock

在这里插入图片描述

(下界限, 10](10, 20](20, 30](30, 40](40, 上界限supremun)

当给索引值20加上了Next-key Lock,那么这个范围是 (10,20] 包括20 ,而不包括10。

由于上界限supremun实际是个伪值,所以上界限并不是真正的索引记录。因此,实际上,这个Next-key Lock只锁定最大索引值之后的间隙。


明白了3种算法,那么这3种算法又是怎么落地的呢?
实际上,默认使用的是Next-key Lock,也就是 索引记录 和 间隙 全锁上。但也会在不同场景下降级优化为Gap Lock或Record Lock。那我们就来分析一下:
由于在读已提交(RC)事务隔离级别下,间隙锁是禁用的(官方说是仅用于外键约束检查和重复键检查),这不是重点,所以本文主要深入分析:在默认的可重复读(RR)事务隔离级别下的加锁规则 之 等值查询

等值查询也就是where条件: = ,因为行锁都是对索引上锁,所以我们主要分析InnoDB引擎常见的3类索引:

分析数据准备

准备一个ct(country team 国家队)表:id 是自增主键,abc是普通索引,abc_uk是唯一索引
并插入4条初始数据:

CREATE TABLE `ct` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(20) NOT NULL,  `abc` int(10) unsigned NOT NULL,  `abc_uk` int(10) unsigned NOT NULL,  `remark` varchar(100) DEFAULT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `uk_abc_uk` (`abc_uk`) USING BTREE,  KEY `idx_abc` (`abc`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;INSERT INTO `ct`(`id`, `name`, `abc`, `abc_uk`, `remark`) VALUES (10, '巴西', 10, 10, NULL),(20, '阿根廷', 20, 20, NULL),(30, '葡萄牙', 30, 30, NULL),(40, '法国', 40, 40, NULL);

预览下数据:

mysql> select * from ct;+----+--------+-----+--------+--------+| id | name   | abc | abc_uk | remark |+----+--------+-----+--------+--------+| 10 | 巴西   |  10 |     10 | NULL   || 20 | 阿根廷 |  20 |     20 | NULL   || 30 | 葡萄牙 |  30 |     30 | NULL   || 40 | 法国   |  40 |     40 | NULL   |+----+--------+-----+--------+--------+4 rows in set (0.00 sec)

我们新建Session1,做以下基本设置:

mysql> select @@tx_isolation;+-----------------+| @@tx_isolation  |+-----------------+| REPEATABLE-READ |+-----------------+
set tx_isolation='repeatable-read';
SET GLOBAL innodb_status_output=ON;SET GLOBAL innodb_status_output_locks=ON;
mysql> show variables like '%innodb_status_output%';+----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| innodb_status_output       | OFF   || innodb_status_output_locks | OFF   |+----------------------------+-------+

我操作的步骤,如下图:
在这里插入图片描述

这个Session1就留着我们分析锁来用,具体执行SQL我们新开另一个Session2,好了,准备开始~

3.1 聚集索引

我们先从聚集索引开始说起,那么这里也分等值条件有匹配无匹配索引两种情况,对应上的锁也是不同的,让我们来分别瞧一瞧:

有匹配索引

Session2执行SQL如下(按id=10):

begin;update ct set remark = '怀念2002年, 巴西夺冠, 中国进世界杯' where id = 10;

注意不要commit或rollback,以便于我们分析行锁

然后我们在"Session1"查看锁的详细信息

show engine innodb status\G; 

我们主要看TRANSACTIONS这段,如下图:
在这里插入图片描述
我们来分析一下,上图中包含的信息:

  1. 1 row lock(s)就代表上了1个行锁(不要理解成只锁了1行🐼);
  2. 具体的行锁信息从RECORD LOCKS开始:
    每个RECORD LOCKS都会标明上锁的索引,就是index后面的,当前是PRIMARY,即代表上锁的索引是聚集索引;
    可能有多条RECORD LOCKS(当前只有一条);
  3. RECORD LOCKS下面紧跟着是它所有的Record lock记录:
    每条Record lock下面是具体的索引物理记录,第0个就是索引记录的key:当前hex 0000000a是指十六制的10,所以可以得知这个行锁 锁的是id=10的聚集索引记录
    我们以第0个来识别是哪个索引key就可以了,下面的1~6是索引记录上携带的数据,聚集索引保存了所有字段信息,所以比较多,其它索引只有2行:索引值和聚集索引的值;
    另外,Record Lock也可能有多条,这里只上了1个行锁,所以只有一条Record lock, heap no。。。

小结:

等值查询 匹配到 聚集索引 时,行级锁 会上一把 无间隙的Record Lock
这里是因为聚集索引id具有唯一性,所以Next-key Lock降级优化为Record Lock。

无匹配索引

先在Session2 rollback上一个SQL,再执行SQL如下(按id=11 不存在):

beginupdate ct set remark = '没有id=11的记录~~' where id = 11;

注意不要commit或rollback,以便于我们分析行锁

然后我们在"Session1"查看锁的详细信息

show engine innodb status\G; 

我们主要看TRANSACTIONS这段,如下图:
在这里插入图片描述
小结:

等值查询 未匹配到 聚集索引 时,行级锁 会上一把 间隙锁

为什么是对 id=20 加的锁,而不是对 id=11 加的锁呢?

我们来分析一下:

  1. 行锁都是对索引记录加锁(除了伪值上界限supremun),因为id=11的索引不存在,所以无法对id=11加锁。
  2. 索引都是排好序的,按顺序从左向右扫描,直到找到 id=20 时,才可以确定 id=11 不存在,也就是说id=20 是 id =11 的next key,所以是对id=20的索引加锁,这里不是Next-key Lock而是间隙锁我觉得也是合理的,毕竟只锁间隙就可以了,范围是(10,20),不包括20。

按这么说,可能有同学又有疑问:如果id大于最大索引值,锁哪个索引记录?
咱们直接看结果,锁的伪值:上界限supremum,范围是(40, supremum),不包括40.

update ct set remark = '比最大id还要大!' where id = 41;

在这里插入图片描述

3.2 唯一索引

有匹配索引

先在Session2 rollback上一个SQL,再执行SQL如下(按abc_uk=10):

begin;update ct set remark = '怀念2002年, 巴西夺冠, 中国进世界杯' where abc_uk = 10;

注意不要commit或rollback,以便于我们分析行锁

然后我们在"Session1"里查看锁的详细信息

show engine innodb status\G; 

我们主要看TRANSACTIONS这段,如下图:
在这里插入图片描述
和聚集索引非常类似,不做赘述,但这里是上了2个行锁,所以有两条Record lock, heapno。。。

小结:

等值查询 匹配到 唯一索引 时,行级锁上了2把锁:

  1. 锁了一条唯一索引记录(abc_uk=10)
  2. 锁了一条聚集索引记录(id=10)

因为唯一索引具有唯一性,所以都是无间隙的Record Lock,这里也是Next-key Lock降级优化为Record Lock。

无匹配索引

先在Session2 rollback上一个SQL,再执行SQL如下(按abc_uk=35):

beginupdate ct set remark = '没有abc_uk=35的记录~~' where abc_uk = 35;

注意不要commit或rollback,以便于我们分析行锁

然后我们在"Session1"查看锁的详细信息

show engine innodb status\G; 

我们主要看TRANSACTIONS这段,如下图:
在这里插入图片描述
小结:

等值查询 未匹配到 唯一索引 时,行级锁 会上一把 间隙锁,与聚集索引规则相同,具体不做赘述。

3.3 普通索引

有匹配索引

先在Session2 rollback上一个SQL,再执行SQL如下(按abc=10):

begin;update ct set remark = '怀念2002年, 巴西夺冠, 中国进世界杯' where abc = 10;

注意不要commit或rollback,以便于我们分析行锁

然后我们在Session1里查看锁的详细信息

show engine innodb status\G; 

我们主要看TRANSACTIONS这段,如下图:
在这里插入图片描述
我们来分析一下:
这里就有意思了,上了3个行锁,还是3种不同的行锁,3种算法都齐了,咱们统一说一下怎么区分:

  1. RECORD LOCKS后面带locks rec but not gap:这说明是无间隙的Record Lock
  2. RECORD LOCKS后面带locks gap before rec:这说明是间隙锁Gap Lock
  3. RECORD LOCKS后面不带1和2的,就说明是默认的Next-key Lock

小结:

等值查询 匹配到 普通索引 时,行级锁上了3把锁:

  1. abc=10的普通索引记录上了Next-key Lock,这里的范围是:(下界值, 10]
  2. id=10的聚集索引记录上了Record Lock(单条)
  3. abc=20的普通索引记录上了Gap-key Lock,这里的范围是:(10, 20)

可以这样说:一个普通索引的等值查询update时,相当于把这条索引记录前后的空隙都锁上了~

这和聚集索引、唯一索引有着很大的不同,你知道这是为什么吗?
思考一下!!!
我们新开一个Session3先来验证一下吧:
在这里插入图片描述

特殊说明:
正常的锁超时异常是:ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
^C – query aborted 这是我不想等锁超时按Ctrl+C中止了🐼

验证第1把Next-key Lock

INSERT INTO `ct`(`id`, `name`, `abc`, `abc_uk`, `remark`)  VALUES (11, '英国', 1, 21, NULL);INSERT INTO `ct`(`id`, `name`, `abc`, `abc_uk`, `remark`)  VALUES (21, '英国', 9, 11, NULL);
update ct set remark = '怀念2002年, 巴西夺冠, 中国进世界杯' where abc = 10;

验证第2把Record Lock

update ct set remark = '怀念2002年, 巴西夺冠, 中国进世界杯' where id = 10;

验证第3把Gap Lock

INSERT INTO `ct`(`id`, `name`, `abc`, `abc_uk`, `remark`) VALUES (1, '英国', 11, 21, NULL); INSERT INTO `ct`(`id`, `name`, `abc`, `abc_uk`, `remark`) VALUES (21, '英国', 19, 1, NULL);

验证修改abc=20的索引记录,不会阻塞

update ct set remark = '梅西将迎卡塔尔世界杯首秀:这是我最后一届世界杯' where abc = 20;

我们来分析为什么 按abc=10 更新时, 却上了3把锁:

  1. 匹配上的索引记录需要上锁,所以 abc=10的索引上了锁,这里没有降级,就是使用默认的Next-key Lock;
  2. 给匹配索引记录的聚集索引上锁,这个与唯一索引是相同的规则,对应id=10的聚集索引记录上了Record Lock;
  3. 大家可能好奇为什么还锁abc=20的索引记录,是这样的:
    普通索引不具有唯一性,当在索引树从左向右扫描时,即使匹配到了记录,也依然不能停止,因为可能有多条匹配的记录!!!所以依然需要继续向右扫描,直到 abc = 20出现为止,这样,abc = 20 作为 next key,也需要上锁,这里上间隙锁也是可以理解的,毕竟只锁间隙就可以了。

通过这样分析,你是不是发现了上面说的不够严谨?

没错,我们再加一条记录,让abc = 10的记录不止一条:

INSERT INTO `ct`(`id`, `name`, `abc`, `abc_uk`, `remark`) VALUES (15, '克罗地亚', 10, 15, NULL);

我们再确认一下现在的记录(一直没有提交):

mysql> select * from ct;+----+----------+-----+--------+--------+| id | name     | abc | abc_uk | remark |+----+--------- +-----+--------+--------+| 10 | 巴西     |  10 |     10 | NULL   || 15 | 克罗地亚 |  10 |     15 | NULL   || 20 | 阿根廷   |  20 |     20 | NULL   || 30 | 葡萄牙   |  30 |     30 | NULL   || 40 | 法国     |  40 |     40 | NULL   |+----+----------+-----+--------+--------+5 rows in set (0.00 sec)

先在Session2 rollback上一个SQL,再执行SQL如下(按abc=10):

begin;update ct set remark = '怀念2002年, 巴西夺冠, 中国进世界杯' where abc = 10;Query OK, 2 rows affected (0.00 sec)Rows matched: 2  Changed: 2  Warnings: 0

这里看到已经是2行受影响了
注意不要commit或rollback,以便于我们分析行锁

然后我们在Session1里查看锁的详细信息

show engine innodb status\G; 

我们主要看差异,如下图:
在这里插入图片描述
一共上了5把锁,多的2把就是我们新增的那条id =15, abc = 10记录,对应的普通索引和聚集索引上的锁。

更严谨的小结:

等值查询 匹配到 普通索引 时,行级锁会上2m+1把锁,m是匹配的记录数:
上面例子匹配了2条记录,所以上了2*2+1 = 5把锁,分别是

  1. abc=10, id =10 的普通索引记录上了Next-key Lock,这里的范围是:(下界值, 10]
  2. abc=10, id =15 的普通索引记录上了Next-key Lock,这里的范围是:(下界值, 10]
  3. id=10的聚集索引记录上了Record Lock(单条)
  4. id=15的聚集索引记录上了Record Lock(单条)
  5. abc=20的普通索引记录上了Gap-key Lock,这里的范围是:(10, 20)

无匹配索引

先在Session2 rollback上一个SQL,再执行SQL如下(按abc=1):

beginupdate ct set remark = '没有abc=1的记录~~' where abc = 1;

注意不要commit或rollback,以便于我们分析行锁

然后我们在"Session1"查看锁的详细信息

show engine innodb status\G; 

我们主要看TRANSACTIONS这段,如下图:
在这里插入图片描述

小结:

等值查询 未匹配到 普通索引 时,行级锁 会上一把 间隙锁,与聚集索引和唯一索引的规则相同,具体不做赘述。

再次说明:本文分析加锁规则的事务隔离级别为:默认的可重复读(RR)事务隔离级别

有匹配索引
有唯一性的索引,都会降级为Record Lock。

无匹配索引

如果感觉不错,请收藏本专栏,后面还有更详细的锁机制陆续放出。
关注我 天罡gg 分享更多干货: https://blog.csdn.net/scm_2008
大家的「关注 + 点赞 + 收藏」就是我创作的最大动力!

同类文章:
https://blog.csdn.net/weixin_48460141/article/details/124284443
https://blog.csdn.net/qq_39360632/article/details/127013702

来源地址:https://blog.csdn.net/scm_2008/article/details/128177426

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯