Mysql加锁流程详解

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: Mysql加锁流程详解

前言


前面已经给大家分享了Mysql中有哪些锁、锁的分类以及相互间的兼容性。本节继续分享Mysql的加锁流程。


由于InnoDB引擎才支持行级锁,以下内容都是基于InnoDB引擎介绍。


一、锁的内存结构


对一条记录加锁本质上是内存中创建的一个锁结构跟这条记录相关联。

所以锁本质上就是内存中的一种数据结构。


那么我们在操作一个事务的时候,如果对应多条记录,是不是要针对多条记录生成多个内存的锁结构呢?比如我们执行select * from tb_user for update的时候,tb_user表中如果存在1万条数,那么难道要生成1万个内存的锁结构吗?那当然不会是这样的。其实,如果符合以下几个条件,那么这些记录的锁就可以放到一个内存中的锁结构里了,条件如下所示:


1.加锁操作时在同一个事务中

2.需要被加锁的记录在同一个页中

3.需要加锁的类型是一致的

4.锁的等待状态是一致的


那么这么多次的锁结构,它到底是怎么组成的呢?

主要是由6部分组成的。分别为:锁所在的事务信息、索引信息、表锁或行锁信息、type_mode、其他信息、与heap_no对应的比特位。如下图所示:

68.png


锁所在的事务信息

一个锁结构对应一个事务,那么这里就存储着锁对应的事务信息。它其实只是一个指针,可以通过它获取到内存中关于该事务的更多信息,比如:事务id是多少。


索引信息

对于行级锁来说,这里记录的就是加锁的记录属于哪个索引。


表锁/行锁信息

(1)、对于表锁,主要是来记录对哪张表进行的加锁操作以及其他的信息。

(2)、对于行锁,内容包括3部分:

Space ID:记录所在的表空间ID。

Page Number:记录所在的页号。

n_bits:一条记录对应一个bit


type_mode

它是由32个bit组成的,分别为:lock_mode、lock_type、lock_wait和rec_lock_type,如下图所示:

67.png


二、加锁流程


1、加锁的基本流程

66.png

【上图解释如下:】

(1)、一开始是没有锁结构与记录进行关联的,即:上图第一个图例所示。

(2)、当一个事务T1想对这条记录进行改动时,会看看内存中有没有与这条记录关联的锁结构,如果没有,就会在内存中生成一个锁结构与这条记录相关联,即:上图第二个图例所示。我们把该场景称之为获取锁成功或者加锁成功。

(3)、此时又来了另一个事务T2要访问这条记录,发现这条记录已经有一个锁结构与之关联了,那么T2也会生成一个锁结构与这条记录关联,不过锁结构中的is_waiting属性值为true,表示需要等待。即:上图第三个图例所示。我们把该场景称之为获取锁失败/加锁失败。

(4)、事务T1提交之后,就会把它生成的锁结构释放掉,然后检测一下还有没有与该记录关联的锁结构。结果发现了事务T2还在等待获取锁,所以把事务T2对应的锁结构的is_waiting属性设置为false,然后把该事务对应的线程唤醒,让T2继续执行。


2、根据主键加锁

65.png

对应sql语句,其中id字段是自增主键:


update user set age = 10 where id = 49;


说明:

1、基于主键(聚簇索引)进行等值查询时,如果对应的值存在,则只需添加标准记录锁Record Lock。如果对应的值不存在,则需要在查询id所在的索引间隙添加间隙锁Gap Lock。

2、基于主键(聚簇索引)进行范围查询时,采用采用Next Key Lock添加行锁。


3、根据二级索引加锁

64.png

对应sql语句,其中name字段上有普通索引:


update user set age = 10 where name = 'Tom';


说明:

1、基于辅助索引进行查询时,会先在辅助索引上加锁,然后在聚簇索引上加锁。

2、基于辅助索引进行查询时,聚簇索引上加锁算法采用Record Lock,即只锁记录不锁间隙。


4、根据非索引字段查询加锁

对应sql语句,其中age字段上没有索引:


update user set name = 'Tom' where age = 10;


说明:

1、查询不走索引时,会在聚簇索引上加锁,加锁算法采用Next Key Lock,并且会锁定全表范围。


注意是通过Next Key Lock锁定的全表范围,而不是通过表级锁直接锁表。


5、加锁规律

1.InnoDB中默认采用Next Key Lock加锁,Next Key Lock加锁范围前开后闭。

2.行锁都是加在索引上,如果通过聚集索引查询则在聚集索引上加锁,通过辅助索引查询则需要同时在辅助索引和聚集索引上加锁,不走索引则在聚集索引上加锁。

3.查找过程中访问到的索引才会加锁。注意是访问到的索引而不是满足查询条件的索引。

4.基于主键和唯一索引进行等值查询,Next Key Lock会退化为行锁Record Lock。

5.索引上的等值查询,没有满足条件的记录时,Next-key lock退化为间隙锁,加锁范围是查询值所在的间隙。

6.通过辅助索引查询并加锁时,需要进行回表查询然后在聚集索引上采用行锁Record Lock加锁。

7.范围查询采用Next Key Lock加锁。


三、影响锁的因素


数据库的隔离等级,SQL 语句和当前数据库数据会共同影响该条 SQL 执行时数据库生成的锁模式,锁类型和锁数量。

63.png


MySQL 的隔离等级对加锁有影响,所以在分析具体加锁场景时,首先要确定当前的隔离等级。


读未提交(Read Uncommitted 后续简称 RU):可以读到未提交的读,基本上不会使用该隔离等级,所以暂时忽略。

读已提交(Read Committed 后续简称 RC):存在幻读问题,对当前读获取的数据加记录锁。

可重复读(Repeatable Read 后续简称 RR):不存在幻读问题,对当前读获取的数据加记录锁,同时对涉及的范围加间隙锁,防止新的数据插入,导致幻读。

序列化(Serializable):从 MVCC 并发控制退化到基于锁的并发控制,不存在快照读,都是当前读,并发效率急剧下降,不建议使用。

这里说明一下,RC 总是读取记录的最新版本,而 RR 是读取该记录事务开始时的那个版本,虽然这两种读取的版本不同,但是都是快照数据,并不会被写操作阻塞,所以这种读操作称为 快照读(Snapshot Read)。


MySQL 还提供了另一种读取方式叫当前读(Current Read),它读的不再是数据的快照版本,而是数据的最新版本,并会对数据加锁,根据语句和加锁的不同,又分成三种情况:

SELECT ... LOCK IN SHARE MODE:加共享(S)锁
SELECT ... FOR UPDATE:加排他(X)锁
INSERT / UPDATE / DELETE:加排他(X)锁


当前读在 RR 和 RC 两种隔离级别下的实现也是不一样的:RC 只加记录锁,RR 除了加记录锁,还会加间隙锁,用于解决幻读问题。


在RR隔离级别下:

MVCC机制解决的是select查询的幻读问题,而通过当前读的方式是通过加间隙锁解决的幻读问题。


四、锁信息查看


1、查看锁的sql语句

-- 查看当前所有事务
select * from information_schema.innodb_trx;
-- 查看加锁信息(MySQL5.X)
select * from information_schema.innodb_locks;
-- 查看锁等待(MySQL5.X)
select * from information_schema.innodb_lock_waits;
--查看加锁信息(MySQL8.0)
SELECT * FROM performance_schema.data_locks;
--查看锁等待(MySQL8.0)
SELECT * FROM performance_schema.data_lock_waits;
-- 查看表锁
show open tables where In_use>0;
-- 查看最近一次死锁信息
show engine innodb status;

这里主要介绍通过查询performance_schema.data_locks表,查看事务中加锁的情况。


设置标签

DROP TABLE if EXISTS user;
CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `account` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '账号',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_account` (`account`) USING BTREE,
  KEY `ik_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `user` (`id`,`account`,`name`, `age`, `email`) VALUES (3, '000003', '老万', 12, '101@qq.com');
INSERT INTO `user` (`id`,`account`, `name`, `age`, `email`) VALUES (10, '000010', '老张', 15, '101@qq.com');
INSERT INTO `user` (`id`,`account`, `name`, `age`, `email`) VALUES (20, '000020', '老王', 15, '101@qq.com');
INSERT INTO `user` (`id`,`account`, `name`, `age`, `email`) VALUES (30, '000030', '老王', 30, '101@qq.com');


开启mysql命令行窗口,开启事务执行加锁:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE  `user` set age=18 WHERE id = 4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

查看加锁情况:


SELECT * FROM performance_schema.data_locks;


执行结果:

62.png


2、data_locks表字段说明

字段介绍:


ENGINE 表使用的存储引擎,这里是InnoDB

ENGINE_TRANSACTION_ID 事务ID

OBJECT_SCHEMA 加锁的表空间,这里的表空间是test

OBJECT_NAME 加锁的表名,这里是user

INDEX_NAME加锁的索引名称,表级锁为null,行级锁为加锁的索引名称。这里PRIMARY表示是主键索引上添加锁。

LOCK_TYPE 锁类型:TABLE对应表级锁,RECORD对应行级锁。

LOCK_MODE 加锁模式,对应具体锁的类型,比如:IX 意向排他锁,X,GAP 排他间隙锁。

LOCK_STATUS 锁的状态,GRANTED 已获取,WAITING 等待中

LOCK_DATA 加锁的数据,这里的10表示,在主键索引值为10的记录上加锁。由于加的是间隙锁GAP,这里锁定的是3~10这个间隙。如果值为supremum pseudo-record,表示高于索引中的任何值,锁定正无穷的范围。


3、lock_mode说明


这里需要重点对 LOCK_MODE 加锁模式进行说明:

LOCK_MODE值

锁类型

IX

意向排他锁

IS

意向共享锁

AUTO_INC

自增主键锁

X

排他临键锁(Next Key) ,既锁记录,也锁间隙

S

共享临键锁(Next Key) ,既锁记录,也锁间隙

X,REC_NOT_GAP

排他标准记录锁(Record),只锁记录,不锁间隙

S,REC_NOT_GAP

共享标准记录锁(Record) ,只锁记录,不锁间隙

S,GAP

共享间隙锁(GAP),只锁间隙
X,GAP 排他间隙锁(GAP) ,只锁间隙
INSERT_INTENTION 插入意向锁



总结


本文主要对Mysql加锁流程进行了详细说明。

1、了解锁的内存结构,注意行锁是可以合并的,并不需要为每条记录都添加一个锁。

2、熟悉根据主键查询加锁,根据二级索引查询加锁,以及不走索引的查询的加锁规律。

3、通过data_locks表查看加锁信息。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
5月前
|
SQL AliSQL 关系型数据库
MYSQL的全局锁和表锁
本文介绍了MySQL中的锁机制,包括全局锁、表级锁及其应用场景。全局锁通过`Flush tables with read lock (FTWRL)`实现,主要用于全库逻辑备份,但会阻塞更新和结构变更操作。表级锁分为显式表锁(`lock tables`)和元数据锁(MDL),前者用于控制并发访问,后者自动加锁以确保读写正确性。文章还探讨了如何安全地为小表添加字段,建议通过设置DDL等待时间或使用MariaDB/AliSQL的NOWAIT/WAIT功能避免业务阻塞。这些方法有助于在高并发场景下优化数据库性能与安全性。
114 0
|
10月前
|
SQL 存储 缓存
MySQL执行流程
本文介绍了MySQL的执行流程,分为server层和引擎层。server层包含连接器、查询缓存、解析器、预处理器、优化器等组件,负责SQL的接收、解析、优化及执行;引擎层负责数据的存储与读取。文章详细解释了各组件的功能,如连接器负责用户身份认证,查询缓存提高查询效率,解析器进行SQL的词法和语法分析,预处理器验证表和字段的存在性,优化器选择最优执行计划,最终由查询执行引擎完成查询并将结果返回给客户端。
208 0
MySQL执行流程
|
12月前
|
SQL 关系型数据库 MySQL
MySQL 锁
MySQL里常见的几种锁
186 3
|
7月前
|
SQL 算法 搜索推荐
mysql 之order by工作流程
本文深入解析了MySQL中`ORDER BY`的排序机制,通过具体示例展示了排序过程及性能优化方法。文章首先分析了基于内存和磁盘的排序方式,包括`sort_buffer_size`的影响以及临时文件的使用场景。接着介绍了`rowid`排序算法,该算法通过减少参与排序的数据量来提升性能,并对比了其与传统排序的区别。此外,还探讨了随机查询`ORDER BY RAND()`的执行流程及其优化策略。最后提到了MySQL 5.6引入的优先队列排序算法,适用于仅需部分有序结果的场景。文章结合`optimizer_trace`工具详细说明了各配置参数对排序行为的影响,为优化查询提供了实用指导。
mysql 之order by工作流程
|
8月前
|
关系型数据库 MySQL 网络安全
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
324 25
|
9月前
|
存储 关系型数据库 MySQL
MySQL进阶突击系列(06)MySQL有几种锁?| 别背答案,现场演示一下
本文详细解析了MySQL InnoDB存储引擎的锁机制,涵盖读锁、写锁、意向锁、记录锁、间隙锁和临键锁等8种锁类型。重点探讨了不同锁类型的加锁与释放方式,以及事务并发场景下的实战验证。通过具体示例,展示了在不同情况下锁的行为及其对事务的影响。文章还特别强调了锁的作用范围主要是索引,并解释了锁如何影响数据的读写操作。最后总结了并发事务中加锁规则,帮助读者深入理解MySQL的锁机制。
|
11月前
|
存储 SQL NoSQL
|
12月前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
594 1
|
12月前
|
关系型数据库 MySQL Java
MySQL数据锁:Record Lock,Gap Lock 和 Next-Key Lock
本文基于 MySQL 8.0.30 版本及 InnoDB 引擎,深入解析三种行锁机制:记录锁(Record Lock)、间隙锁(Gap Lock)和临键锁(Next-key Lock)。记录锁锁定索引记录,确保事务唯一修改;间隙锁锁定索引间的间隙,防止新记录插入;临键锁结合两者,锁定范围并记录自身,有效避免幻读现象。通过具体示例展示了不同锁的作用机制及其在并发控制中的应用。
1128 2
|
12月前
|
存储 关系型数据库 MySQL
MySQL数据库锁:共享锁和独占锁
本文详细介绍了`InnoDB`存储引擎中的两种行级别锁:共享锁(S锁)与排他锁(X锁)。通过具体示例展示了这两种锁的工作机制及其在`InnoDB`与`MyISAM`引擎中的表现差异。文章还提供了锁的兼容性矩阵,帮助读者更好地理解锁之间的互斥关系。最后总结了两种锁的特点及适用场景。适合希望深入了解`MySQL`并发控制机制的读者阅读。
426 1

热门文章

最新文章

推荐镜像

更多