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

MySQL悲观锁(排他锁)级别

因为加锁是跟数据库的隔离级别息息相关的。而常用的数据库隔离级别也就RC(读已提交)和RR(可重复读),所以本文分别根据RC(读已提交)和RR(可重复读)隔离级别展开讲述。

一、环境准备

设置数据库隔隔离级别

mysql> set global transaction isolation level READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

自动提交关闭

mysql> set @@autocommit=0;  //设置自动提交关闭
Query OK, 0 rows affected (0.00 sec)mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

建表语句

CREATE TABLE `user`
(`id`        int(11) unsigned NOT NULL AUTO_INCREMENT,`user_no`   varchar(16)  DEFAULT NULL COMMENT '用户编号',`user_name` varchar(16)  DEFAULT NULL COMMENT '用户名',`age`       int(3)       DEFAULT NULL COMMENT '年龄',`address`   varchar(128) DEFAULT NULL COMMENT '地址',PRIMARY KEY (`id`),UNIQUE KEY `un_idx_user_no` (`user_no`),KEY `idx_user_name` (`user_name`)
) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8;

初始化数据(接下来的实验证明,都是基于这几条初始数据)

insert into user values(null,'0001','user01',18,'北京');
insert into user values(null,'0002','user02',19,'上海');
insert into user values(null,'0003','user03',20,'广州');
insert into user values(null,'0004','user04',21,'深圳');
insert into user values(null,'0005','user05',22,'杭州');

Mysql版本

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.22    |
+-----------+
1 row in set (0.00 sec)+-----------+
| @@version |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)

数据锁信息:

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; -- 5.7.x版本
SELECT * FROM performance_schema.data_locks; -- 8.0.x版本

锁分类:行级锁分类

二、RR_5.7.x

2.1 主键

操作:使用主键ID作为条件查询,然后新开启一个事务去更新数据。

分析思路

  1. 如果更新数据被阻塞,则说明加锁成功;
  2. 如果更新其他数据成功,则说明是行锁,如果更新其他数据失败则说明是表锁。
  3. 部分场景会测试插入操作;后续所有操作基本雷同。

执行悲观锁查询:

select * from user where id = 1 for update;

执行更新操作:

update user set age = age +1 where id = 1;

此处更新操作被阻塞,说明数据锁定成功。

在此场景下,来看一下数据库加的什么锁。

当第二条语句被阻塞时,执行查看锁信息语句:

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

注意,必须是正在执行第二条语句,且第二条语句处于阻塞状态下,上述语句才能查询到数据。

查询结果如下:

第二条记录为for update锁表语句,第一条记录为单纯的update语句。可以看出,此场景下,lock_mode为X,lock_type为RECORD,lock_data为1。

  • lock_mode为X(排他锁):即写锁,允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。
  • lock_type为RECORD,说明是行级锁,
  • lock_data表示锁定了1条记录。

执行更新其他记录操作:

update user set age = age +1 where id = 2;

执行成功。

结论:当查询条件为主键时,select for update为行级锁。

当我们执行完一个场景之后,我们需要执行commit命令将当前事物提交。

2.2 唯一索引

执行悲观锁操作:

select * from user where user_no = '0001' for update;

执行更新操作:

update user set age = age +1 where id = 1;

此处更新操作被阻塞,说明数据锁定成功。

查询锁信息,同 场景一的主键一致。

执行更新其他记录操作:

update user set age = age +1 where id = 2;

执行成功。

结论:当查询条件为唯一索引时,select for update为行级锁。

2.3 普通索引

执行悲观锁操作:

select * from user where user_name = 'user01' for update;

执行更新操作:

update user set age = age +1 where id = 1;

此处更新操作被阻塞,说明数据锁定成功。

查询锁信息:

此时,锁类型不仅仅是X排他锁,同时还添加了GAP(间隙锁),也就是说针对数据添加了排他间隙锁。

执行更新其他记录操作:

update user set age = age +1 where id = 2;

执行成功。

此时再进行一笔插入操作:

insert into user values(null,'0006','user05',23,'重庆');

执行成功。

由于存在了间隙锁,再执行一笔user_name与查询条件相同的插入操作:

insert into user values(null,'0008','user01',24,'成都');

执行阻塞,说明此时有排他间隙锁的存在。

结论:当查询条件为普通索引时,select for update为行级锁,同时会有排他间隙锁存在,当插入数据满足锁语句查询条件(相等、范围等)时,会发生阻塞。

2.4 无索引

执行悲观锁操作:

select * from user where address = '北京' for update;

执行更新操作:

update user set age = age +1 where id = 1;

此处更新操作被阻塞,说明数据锁定成功。

执行更新其他记录操作:

update user set age = age +1 where id = 2;

执行被阻塞。

此时查询锁表信息展示如下:

这里比较奇怪是lock_type,很明显,上述锁操作已经锁住了整张表,但lock_type依旧为RECORD。出处暂时有些费解。

结论:当查询条件无索引时,select for update为表级锁。

2.5 索引-范围查询

执行悲观锁操作:

select * from user where id > 1 for update;

执行更新操作:

update user set age = age +1 where id = 1;

执行成功,说明并没有锁定id为1的记录。

执行插入操作:

insert into user values(null,'0007','user07',24,'武汉');

插入操作被阻塞。这是因为插入的数据生成的id满足大于1的条件,会被阻塞。

所信息如下:

此时,lock_type虽然是RECORD,但是lock_data显示supremum pseudo-record ,这就是InnoDB为了解决幻读问题的临键锁(Next-key Lock),这里间隙锁和临键锁可以看做是一样的。

需要注意的是:supremum pseudo-record有可能是间隙锁,需要结合死锁日志里的heap no判断。heap no 1是间隙锁。

结论:当查询条件有索引且查询条件为范围时,select for update会采用间隙锁或临键锁,对指定范围内的数据进行加锁。当然,当查询条件无索引时,与 场景RR-无索引一致,为表锁。

三、RC_5.7.x

3.1 主键

执行悲观锁查询:

select * from user where id = 1 for update;

执行更新操作:

update user set age = age +1 where id = 1;

此处更新操作被阻塞,说明数据锁定成功。

锁信息与RR事务相同。

执行更新其他记录操作:

update user set age = age +1 where id = 2;

执行成功。

结论:当查询条件为主键时,select for update为行级锁。

3.2 唯一索引

执行悲观锁操作:

select * from user where user_no = '0001' for update;

执行更新操作:

update user set age = age +1 where id = 1;

此处更新操作被阻塞,说明数据锁定成功。

查询锁信息,与RR一致。

执行更新其他记录操作:

update user set age = age +1 where id = 2;

执行成功。

结论:当查询条件为唯一索引时,select for update为行级锁。

3.3 普通索引

执行悲观锁操作:

select * from user where user_name = 'user01' for update;

执行更新操作:

update user set age = age +1 where id = 1;

此处更新操作被阻塞,说明数据锁定成功。

查询锁信息如下:

再把RR 场景下的锁信息贴出来:

可以看出,RC事务隔离级别时比RR事务隔离级别时少了一个GAP(间隙锁)。

执行更新其他记录操作:

update user set age = age +1 where id = 2;

执行成功。

此时再进行一笔插入操作:

insert into user values(null,'0009','user01',24,'郑州');

执行成功。

再验证下间隙锁是否真的不存在,执行一笔user_name与查询条件相同的插入操作:

insert into user values(null,'0008','user01',24,'成都');

执行成功,说明此时间隙锁的不存在了。

结论:当查询条件为普通索引时,select for update为行级锁,无间隙锁。

3.4 无索引

执行悲观锁操作:

select * from user where address = '北京' for update;

执行更新操作:

update user set age = age +1 where id = 1;

此处更新操作被阻塞,说明数据锁定成功。

锁信息如下:

显示基于主键的排他锁,这块挺出乎意料的,并没有进行表锁。

执行更新其他记录操作:

update user set age = age +1 where id = 2;

执行成功。

再执行一笔插入操作,插入数据与查询条件address一致:

insert into user values(null,'0011','user01',24,'北京');

执行成功。

结论:当查询条件无索引时,select for update为行级锁,也就说,在RC事务隔离级别下,即便无索引,也是只锁记录,与通常的直知觉不同。

原因:会出现上述情况的原因是,本来如果锁条件上没有索引,MySQL会走聚簇(主键)索引进行全表扫描过滤,每条记录都会添加上X锁。但为了效率,MySQL会对扫描过程中不满足条件的记录进行解锁操作。

3.5 索引-范围查询

执行悲观锁操作:

select * from user where id > 1 for update;

执行更新操作:

update user set age = age +1 where id = 1;

执行成功,说明并没有锁定id为1的记录。

执行更新操作:

update user set age = age +1 where id = 2;

操作被阻塞。这是因为操作的数据的id满足大于1的条件,会被阻塞。

所信息如下:

结论:当查询条件有索引且查询条件为范围时,select for update对指定范围内的数据进行加锁。

四、RR_8.0.x

4.1 主键

执行悲观锁查询:

select * from user where id = 1 for update;

执行更新操作:

update user set age = age +1 where id = 1;

此处更新操作被阻塞,说明数据锁定成功。

查看数据库对应的锁:

SELECT * FROM performance_schema.data_locks;

注意,在MySQL 8中,采用了performance_schema替代了MySQL5中基于INFORMATION_SCHEMA的锁查询方式。

上述查询结果中,有两条记录。lock_type字段展示锁范围,lock_mode字段展示了锁的类型。可以看到,该SQL语句先是在表范围上加了一把IX(意向排他锁,表锁)。然后,在记录(Record)范围上添加了一把X(排他锁),一把REC_NOT_GAP(行锁),综合起来就是对这条记录添加了行级排他锁,其他事务不能够再对其添加任何锁了。

这里,既然在表的层面上添加了IX(意向排他锁),为什么不是表锁呢?这是因为意向排他锁的作用仅仅表名意向的锁,当其他事务要对全表的数据进行加锁时,那么就不需要判断每一条数据是否被加锁了。

事务在给一行记录加排他锁前,必须先取得该表的IX锁,意向排他锁之间相互兼容,可以并行,不会产生冲突。意向排他锁存在的意义是为了更高效的获取表锁,主要目的是显示事务正在锁定某行或者试图锁定某行。

继续实验,执行更新其他记录操作:

update user set age = age +1 where id = 2;

执行成功。

结论:当查询条件为主键时,select for update为行级锁。

4.2 唯一索引

执行悲观锁操作:

select * from user where user_no = '0001' for update;

执行更新操作:

update user set age = age +1 where id = 1;

此处更新操作被阻塞,说明数据锁定成功。

查询锁信息:

此时,可以看到三把锁,一把表级别的IX锁,一把基于唯一索引的行级排他锁,一把基于主键的行级排他锁。

执行更新其他记录操作:

update user set age = age +1 where id = 2;

执行成功。

结论:当查询条件为唯一索引时,select for update为行级锁。

4.3 普通索引

执行悲观锁操作:

select * from user where user_name = 'user01' for update;

执行更新操作:

update user set age = age +1 where id = 1;

此处更新操作被阻塞,说明数据锁定成功。

查询锁信息:

此时,可以看到四把锁,一把表级别的IX锁,一把基于普通索引的X排他锁,一把基于主键的行级排他锁,一把基于普通索引的X,GAP排他间隙锁。

执行更新其他记录操作:

update user set age = age +1 where id = 2;

执行成功,说明更新操作没有影响。

既然有排他间隙锁,此时需再测试一笔插入操作:

insert into user values(null,'0006','user05',23,'重庆');

执行成功。

再执行一笔插入操作:

insert into user values(null,'0007','user01',24,'武汉');

注意这里插入的记录user_name与锁查询条件相同,发现操作被阻塞。

通过两笔插入操作可以看出,排他间隙锁会阻塞符合查询条件(user_name='user01')的数据的插入。

结论:当查询条件为普通索引时,select for update为行级锁,同时会多一把排他间隙锁,如果插入数据满足锁语句的查询条件(等于、范围条件等),则无法插入。

4.4 无索引

执行悲观锁操作:

select * from user where address = '北京' for update;

执行更新操作:

update user set age = age +1 where id = 1;

此处更新操作被阻塞,说明数据锁定成功。

查询锁信息:

此时,数据库一共加了8把锁,一把表级别的IX意向排他锁,6把基于主键的针对数据记录(总共6条)的X锁,一把针对记录的supremum pseudo-record锁。

执行更新其他记录操作:

update user set age = age +1 where id = 2;

执行被阻塞。

结论:当查询条件无索引时,select for update为表级锁。

4.5 索引-范围查询

执行悲观锁操作:

select * from user where id > 1 for update;

执行更新操作:

update user set age = age +1 where id = 1;

执行成功,说明并没有锁定id为1的记录。

执行插入操作:

insert into user values(null,'0007','user07',24,'武汉');

插入操作被阻塞。这是因为插入的数据生成的id满足大于1的条件,会被阻塞。

查询锁信息如下:

此时,锁信息对比 场景RR-无索引,少了一条不满足条件记录(id=1)的锁,其他符合条件的数据均被锁。

结论:当查询条件有索引且查询条件为范围时,select for update会采用间隙锁或临键锁,对指定范围内的数据进行加锁。

完成了上面针对RR事务隔离级别的验证,下面将数据库事务隔离级别切换为RC。

set global transaction isolation level READ COMMITTED;

注意,此处可能需要重启数据库,如果通过命令配置无效,可通过数据库配置文件进行配置,重启。

另外,也可以通过在所有命令窗口执行session级别的设置,也可以达到效果,设置完成之后注意需要进行验证。

五、RC_8.0.x

5.1 主键

执行悲观锁查询:

select * from user where id = 1 for update;

执行更新操作:

update user set age = age +1 where id = 1;

此处更新操作被阻塞,说明数据锁定成功。

锁信息同RR。

继续实验,执行更新其他记录操作:

update user set age = age +1 where id = 2;

执行成功。

结论:当查询条件为主键时,select for update为行级锁。

5.2 唯一索引

执行悲观锁操作:

select * from user where user_no = '0001' for update;

执行更新操作:

update user set age = age +1 where id = 1;

此处更新操作被阻塞,说明数据锁定成功。

锁信息同RR。

执行更新其他记录操作:

update user set age = age +1 where id = 2;

执行成功。

结论:当查询条件为唯一索引时,select for update为行级锁。

5.3 普通索引

执行悲观锁操作:

select * from user where user_name = 'user01' for update;

执行更新操作:

update user set age = age +1 where id = 1;

此处更新操作被阻塞,说明数据锁定成功。

查询锁信息:

对照一下RR 场景下的锁信息:

可以看出RC场景下比RR场景下少了一条行级间隙锁。

执行更新其他记录操作:

update user set age = age +1 where id = 2;

执行成功,说明更新操作没有影响。

验证一下是否有排他间隙锁,此时需再测试一笔插入操作:

insert into user values(null,'0010','user05',23,'重庆');

执行成功。

再执行一笔插入操作:

insert into user values(null,'0007','user01',24,'武汉');

注意这里插入的记录user_name与锁查询条件相同,执行成功,说明真的不存在X,GAP(排他间隙锁)。

结论:当查询条件为普通索引时,select for update为行级锁。

5.4 无索引

执行悲观锁操作:

select * from user where address = '北京' for update;

执行更新操作:

update user set age = age +1 where id = 1;

此处更新操作被阻塞,说明数据锁定成功。

查询锁信息:

对照一下RR场景:

对于RR场景,RC场景下,只有一条排他行锁(X,REC_NOT_GAP)。

执行更新其他记录操作:

update user set age = age +1 where id = 2;

执行成功。

结论:当查询条件无索引时,select for update为行级锁。这里的原因与 场景3.4一致。

5.5 索引-范围查询

执行悲观锁操作:

select * from user where id > 1 for update;

执行更新操作:

update user set age = age +1 where id = 1;

执行成功,说明并没有锁定id为1的记录。

执行插入操作:

insert into user values(null,'0012','user12',24,'--');

执行成功。

查询锁信息如下:

对照RR场景下的锁信息:

此时,RC场景下,少了临键锁,排他锁也变为了行级排他锁。

结论:当查询条件有索引且查询条件为范围时,select for update会对指定范围内的数据进行加锁,只会阻塞符合条件的记录,不影响插入操作。

六、总结

完成了上面的实验之后,我们通过一个表格来总结一下所有的场景和结论。

版本 主键 唯一索引 普通索引 无索引 范围查询
5.7.x_RR X:行锁 X,行锁 X,GAP:行锁,间隙锁,条件范围内会阻塞 表锁 指定范围加锁,insert阻塞
5.7.x_RC X:行锁 X,行锁 X,行锁,无间隙锁; 行锁 指定范围加锁,更新、insert阻塞
8.0.x_RR X,REC_NOT_GAP:行级排他锁 X,REC_NOT_GAP:行级排他锁 X;X,REC_NOT_GAP;X,GAP:行锁+排他间隙锁,阻塞范围内insert; 表锁,每条记录一个X锁 指定范围加锁,insert阻塞
8.0.x_RC X,REC_NOT_GAP:行级排他锁 X,REC_NOT_GAP:行级排他锁 X,REC_NOT_GAP:行锁,无间隙锁; X,REC_NOT_GAP:行锁 指定范围加锁,不阻塞insert

从上面表中我们可以总结出以下结论(基于RR、RC两种事务隔离级别):

  • 无论哪个版本的MySQL,查询条件为主键、唯一索引、普通索引的情况下,为行锁;
  • 查询条件为普通索引时,事务隔离级别为RR时,MySQL还会添加一个间隙锁,条件内的插入、更新会被阻塞;
  • 事务隔离级别为RR时,查询条件无索引,为表锁;
  • 事务隔离级别为RC时,查询条件无索引,为行锁;
  • 查询条件为范围时,有索引的情况下,除MySQL 8.0.x RC场景下不阻塞插入操作,其他场景均阻塞指定范围更新、插入操作;

通过上面的结论,我们可以看出,并不是简单的说“有索引就是行锁,无索引就是表锁”,因为在事务隔离级别为RC时,无索引,同样表现(被优化)为行锁。

七、拓展

7.1 唯一索引_8.0为什么两把x锁?

唯一索引的结构如下:

唯一索引和主键索引都加了锁,为什么主键索引上的记录也要加锁呢?

如果并发的一个SQL,通过唯一索引条件,来更新主键索引:update user set user_name = '007' where id = 2;此时,如果select...for update语句没有将主键索引上的记录加锁,那么并发的update就会感知不到select...for update语句的存在,违背了同一记录上的更新/删除需要串行执行的约束。

http://www.hskmm.com/?act=detail&tid=21588

相关文章:

  • Swagger 3.0 + Knife4j 入门到实战:Spring Boot API 文档搭建、注解详解与生产环境安装
  • 打破信息孤岛,构建统一视界:视频融合平台EasyCVR在智慧校园建设中的核心作用
  • Linux ssh/scp/sftp命令利用及免密登录配置
  • PySimpleGUI 中有哪些可以单独使用的函数?
  • Learning Continuous Image Representation with Local Implicit Image Function
  • Fastadmin开发两个APP端,接口token验证
  • 网易伏羲受邀亮相2025云栖大会,展示AI领域前沿创新成果
  • 2025 年人工智能培训机构最新推荐榜单:前五合规运营与产业适配能力深度解析及选择指南大模型培训/智能体培训/Agent培训机构推荐
  • 9年了 - ukyo-
  • js 获取下一个月时间和下一年的时间
  • 【Rust GUI开发入门】编写一个本地音乐播放器(5. 制作音乐列表组件) - Jordan
  • 【Nordic】nRF9151的SLM例程常用AT指令说明
  • sql server经典语句「转」
  • Codeforces 2149G Buratsuta 3 题解 [ 蓝 ] [ 摩尔投票 ] [ 线段树 ] [ 随机化 ] [ 主席树 ] [ 根号分治 ]
  • 2025 年最新推荐软件开发机构榜:聚焦微服务架构与 724 小时服务的优质厂商精选指南人力资源管理系统/资产管理系统/数据中台管理系统/流程管理系统软件开发公司推荐
  • 【半导体物理 | 学习笔记】第一章 半导体中的电子状态
  • 计数(5):多项式相关
  • 最新WTAPI开发微信机器人教程说明
  • 线性DP - 学习笔记
  • 2025 年最新制氮机厂家权威推荐排行榜:聚焦行业优质厂商综合实力,助力企业精准选购优质设备制氮机产生氮气/氮气纯化/设备改造/维修/保养/半导体用制氮机厂家推荐
  • 2025 年氨分解设备厂家最新推荐榜单:含半导体 / 冶金 / 稀土行业专用设备及品牌综合实力排名
  • 2025 阳台装修公司推荐榜:最新优质厂商资质 / 技术 / 服务测评,杭州浙江地区优选指南杭州阳台装修/浙江阳台装修公司推荐
  • bitset
  • 网易雷火胡志鹏:AI驱动未来,游戏科技重塑虚拟创造力与现实生产力
  • idea打包推送maven仓库及同时推送到不同的maven仓库,本地和云上的腾讯云
  • 2025 阳台柜厂家最新推荐榜:企业资质 / 材质 / 服务全景解析,选对品牌少走弯路杭州阳台柜/浙江阳台柜厂家推荐
  • 2025 年除湿机厂家最新权威推荐排行榜:实力厂家技术口碑评测及场景适配选购指南吊顶/泳池/车库/防爆/调温/新风除湿机厂家推荐
  • 2025 年液氨蒸发器厂家联系方式,众众电热:多领域加热设备供应与定制化解决方案提供商
  • 【Batch】批量修改文件后缀
  • 【solace】基于docker部署solace环境