MySQL 事务原理:锁机制

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL 事务原理:锁机制

锁机制用于管理对共享资源的并发访问,实现事务的隔离级别 。


一、锁类型


MySQL当中事务采用的是粒度锁:针对表(B+树)、页(B+树叶子节点)、行(B+树叶子节点当中某一段记录行)三种粒度加锁。


因此可分为全局锁、表级锁和行级锁。全局锁是针对数据库加锁,表级锁是针对表或页进行加锁;行级锁是针对表的索引加锁。


1.1 全局锁

全局锁(Global Lock)是一种数据库锁机制,它可以锁定整个数据库系统,阻止其他事务对数据库进行写入或修改操作。当一个事务获取到全局锁时,其他事务将无法执行任何对数据库写入的操作,直到全局锁被释放。


-- 全局锁,整个数据库处于只读状态,其他操作均阻塞
FLUSH TABLES WITH READ LOCK
-- 释放全局锁
UNLOCK TABLES

全局锁用于全库逻辑备份。这样在备份数据库期间,不会因为数据或表结构的更新,而出现备份文件的数据与预期的不一样。


但是在备份期间,业务只能读数据,不能更新数据, 造成业务停滞。


1.2 表级锁

表级锁又分为表锁、元数据锁、意向锁、自增锁。


1.2.1 表锁

表锁(Table-level lock)用于锁定整个表,控制对表的并发访问。当一个事务获取到表级锁时,其他事务将被阻塞,无法同时对该表进行写操作或修改操作。


表锁是一种粗粒度的锁,适用于需要对整张表进行操作的场景。

LOCK TABLES 表名 READ|WRITE
UNLOCK TABLES

1.2.2 元数据锁

元数据锁(MetaData Lock)用于保护数据库对象的元数据(如表结构、索引信息等)。当一个事务获取到元数据锁后,其他事务将无法修改该元数据,直到锁被释放。元数据锁将阻止并发事务对元数据的并行修改,防止出现不一致或损坏的元数据状态。


1.2.3 意向锁

意向锁(Intention Lock)用于快速判断表里是否有记录加锁。


当一个事务要获取一个表中某些行的排他锁或共享锁时,它需要首先获取该表的意向锁,可以快速判断表里是否有行记录加锁,从而避免全表扫描查询是否有记录被加锁了。


意向锁分为两种类型:


意向共享锁(Intent Shared Lock,IS):一个事务要获取某表中某些行的共享锁时,需要先获取该表的意向共享锁。意向共享锁不互斥,多个事务可以同时获取。


意向排他锁(Intent Exclusive Lock,IX):一个事务要获取某表中某些行的排他锁时,需要先获取该表的意向排他锁。意向排他锁与意向共享锁互斥,当一个事务持有意向排他锁时,其他事务无法获取该表的意向排他锁或意向共享锁。目的:为了告诉其他事务,此时这条表被一个事务在访问;作用:排除表级别读写锁 (全面扫描加锁)


例如,当一个事务要获取某表的排他锁时,它可以先检查是否已经有其他事务持有意向共享锁,如果有,则可以知道在表级上可能存在其他事务正在读取,而不必尝试获取排他锁。


1.2.4 自增锁

自增锁(Auto-Increment Lock)实现自增约束,当往表插入数据时会使用auto-inc锁来加锁,语句结束后释放锁,而不是在事务结束时释放(这和行级锁有区别,行级锁是在事务结束才释放锁)。


当一个事务要插入新数据并获取下一个自增值时,它首先会获取一个自增锁。一旦事务获得了自增锁,它就可以安全地执行插入操作,并确保每次插入都会得到一个唯一且连续的自增值。其他事务在等待自增锁被释放之前,无法获取下一个自增值,从而避免了冲突和重复。


但是,自增锁在对大量数据进行插入操作时,阻塞其他事务的插入操作,影响性能。因此, 在 Mysql 5.1.22 版本后仅对 AUTO_INCREMENT字段加上轻量级锁,当字段自增后,立即释放锁,而不需要等待整个插入语句执行完后才释放锁。


1.3 行级锁

行级锁的类型有


  • 记录锁,也就是仅仅把一条(行)记录锁上;
  • 间隙锁,锁定一个范围,但是不包含记录本身;
  • 临键锁:记录锁 + 间隙锁的组合,锁定一个范围,并且锁定记录本身。


1.3.1 记录锁

记录锁(record lock),所以一行记录,可分为:

共享锁

共享锁(Shared Lock,也称为读锁、S锁):多个事务可以同时获取同一行的共享锁,用于读取数据。共享锁之间不互斥,多个事务可以同时持有共享锁并进行读操作,但无法同时持有排他锁或修改锁。


1)在 SERIALIZABLE 隔离级别下,默认帮读操作加共享锁;

2)在 REPEATABLE READ 隔离级别下,需手动加共享锁,可解决幻读问题;

3)在 READ COMMITTED 隔离级别下,没必要加共享锁,采用的是 MVCC;

4)在 READ UNCOMMITTED 隔离级别下,既没有加锁也没有使用MVCC;


排他锁


排他锁(Exclusive Lock,也称为写锁、X锁):只有一个事务可以获取同一行的排他锁,用于修改数据。排他锁与共享锁和其他排他锁互斥,一个事务持有排他锁时,其他事务无法获取共享锁或排他锁。


在4种隔离级别下,都添加了排他锁,事务提交或事务回滚后释放锁。


1.3.2 间隙锁

间隙锁(Gap Lock)用于锁定两个索引键之间的空隙(即不存在的值)。主要是锁范围,但不包含记录本身,是全开区间。RR级别及以上支持。


当一个事务执行范围查询时,可能存在其他事务在查询结果范围内插入新行或修改已有行。为了防止幻读(Phantom Read)的情况发生,间隙锁可以锁定这些空隙,以确保数据的一致性。

(解决了快照读的幻读问题。但对于当前读,仍需要手动加锁 )


1.3.3 临键锁

临键锁(Next-Key Lock),记录锁 与 间隙锁的组合,用于锁范围和记录。包含记录本身是左开右闭区间。RR级别及以上支持,解决了幻读问题。


1.3.4 插入意向锁

插入意向锁(Insert Intention Lock)是一种间隙锁形式的意向锁,在insert 操作的时候产生。在多事务同时写入不同数据至同一索引间隙的时候,并不需要等待其他事务完成,不会发生锁等待。


假设有一个记录索引包含键值 4 和 7,两个不同的事务分别插入5 和 6,每个事务都会产生一个加在 4-7 之间的插入意向锁,获取在插入行上的排它锁,但是不会被互相锁住,因为数据行并不冲突。


1.4 锁的兼容性

1.4.1 兼容性1


S X IS IX AI
S 兼容 冲突 兼容 冲突 冲突
X 冲突 冲突 冲突 冲突 冲突
IS 兼容 冲突 兼容 兼容 兼容
IX 冲突 冲突 兼容 兼容 兼容
AI 冲突 冲突 兼容 兼容 冲突

由于 innodb 支持的是行级别的锁,意向锁并不会阻塞除了全表扫描以外的任何请求。

1)意向锁之间是互相兼容的。

2)IS 只对排他锁不兼容。

3)当想为某一行添加 S 锁,先自动为所在的页和表添加意向锁IS,再为该行添加 S 锁。

4)当想为某一行添加 X 锁,先自动为所在的页和表添加意向锁IX,再为该行添加 X 锁。

5)当事务试图读或写某一条记录时,会先在表上加上意向锁,然后才在要操作的记录上加上读锁或写锁。这样判断表中是否有记录加锁就很简单了,只要看下表上是否有意向锁就行了。意向锁之间是不会产生冲突的,也不和 AUTO_INC 表锁冲突,它只会阻塞表级读锁或表级写锁,另外,意向锁也不会和行锁冲突,行锁只会和行锁冲突。


1.4.2 兼容性2


Gap 持有 Insert Intention 持有 Record 持有 Next-key 持有
Gap 请求 兼容 兼容 兼容 兼容
Insert Intention 请求 冲突 兼容 兼容 冲突
Record 请求 兼容 兼容 冲突 冲突
Next-key 请求 兼容 兼容 冲突 冲突

横向:表示已经持有的锁;纵向:表示正在请求的锁;


一个事务已经获取了插入意向锁,对其他事务是没有任何影响的;


一个事务想要获取插入意向锁,如果有其他事务已经加了 gap lock 或 Next-key lock 则会阻塞;这个是重死锁之源;


二、锁的CUDP


2.1 查询


  • MVCC:undo log 实现历史版本记录
  • S 锁:lock in share mode
  • X 锁:for update
  • 不做任何处理:读未提交隔离级别使用的策略


2.2 删除、更新

自动添加 X 锁


2.3 插入


  • 插入意向锁:特殊的间隙锁,同时会使用 X 锁。
  • 自增锁:特殊表锁实现


三、锁的对象


行级锁是针对表的索引加锁,索引包括聚集索引和辅助索引。


表级锁是针对页或表进行加锁。


重点讨论 InnoDB 在 read committed 和 repeatable read 级别下锁的情况。


假设存在如下的students 表作为实例,其中 id 为主键,no(学号)为辅助唯一索引,name(姓名)和 age(年龄)为二级非唯一索引,score(学分)无索引。


id no name age score
15 S0001 Bob 25 34
18 S0002 Alice 24 77
20 S0003 Jim 24 5
30 S0004 Eric 23 91
37 S0005 Tom 22 22
49 S0006 Tom 25 83
50 S0007 Rose 23 89

1)聚集索引,查询命中: UPDATE students SET score = 100 WHERE id = 15;

命中就进行写操作,也就是加X锁

9547a88f2a7ba9842d6c73785dab40a7_7d198260d6444e39b15ff73d07b6b323.png

2)聚集索引,查询未命中: UPDATE students SET score = 100 WHERE id = 16;

未命中,加gap锁。RC级别没有gap锁,不加。

RR级别加在(15,18)间加gap锁,阻止其他事务在这个区间操作(修改、插入),避免幻读。

44d14ed3941b26dbbaafefb0ad8176cb_59461b2283c0451ab71a185fc53830a6.png

3)辅助唯一索引,查询命中: UPDATE students SET score =100 WHERE no = ‘S0003’;

d321a2a4ccaa49bc077d378272466c56_decabcaaba114561b8d91d14d064f213.png

4)辅助唯一索引,查询未命中: UPDATE students SET score = 100 WHERE no = ‘S0008’;

4ebca9f0414a2425de69fa555590f97f_d1c6a158b13d47cea3879f7b37db8780.png

5)辅助非唯一索引,查询命中: UPDATE students SET score = 100 WHERE name = ‘Tom’;


RR级别下,可重复读,因此需要加gap锁,阻止其他事务再插入Tom

52b21c403c97bc07652888acdf762a1e_1c11473dac0845beacd7fef38206271e.png

6)辅助非唯一索引,查询未命中: UPDATE students SET score = 100 WHERE name = ‘John’;

RR级别,按字典顺序,在JIm和Rose间插入gap锁

95753db4c42112023a55f272b68720a7_7a6d1ad2bc954a8a9379de047be0bf4b.png

7)无索引: UPDATE students SET score = 100 WHERE score = 22;

在无索引的情况下,全表查询,按扫描顺序,逐行加锁,效率最低。

725989cea260294f26e7dbdb99d7b9c8_9336a77d5f10475ca3b0cb5ce33210b1.png


8)聚集索引,范围查询: UPDATE students SET score = 100 WHERE id <= 20;

47adbe68543d39181d567b69544d807e_6d8f2c5e2b144bcdac7f17e3bdae8872.png


9)辅助索引,范围查询: UPDATE students SET score = 100 WHERE age <= 23;

a3089a3077b1aee33befed5f1832adeb_b0ff1e443f974231b3e28b5332b02332.png

注意:事务对聚集索引 B+ 树的范围查询是按序的,不会有死锁。但是对于辅助索引 B+ 树的修改却不一定有序,可能会导致死锁。比如事务A加锁顺序1、2,事务B加锁顺序2、1


10)修改索引值: UPDATE students SET name = ‘John’ WHERE id = 15;

8d5a08f57a851e2019551418a7c011cc_61a1fba450224d519c20f14762a008bf.png


四、并发死锁


死锁:两个或两个以上的事务在执行过程中,因争夺锁资源而造成的一种互相等待的现象。

MySQL 中采用 wait-for graph(等待图-采用非递归深度优先的图算法实现)的方式来进行死锁检测。


4.1 相反加锁顺序导致死锁

这种情况下有两种原因造成死锁:

1)不同表的加锁顺序相反


2)相同表不同行加锁顺序相反

相同表不同行加锁顺序相反造成死锁有很多变种,其中容易忽略的是给辅助索引行加锁的时候,同时会给聚集索引行加锁;同时还可能出现在外键索引时,给父表加锁,同时隐含给子表加锁;触发器同样如此,这些都需要视情况分析。


解决办法是:调整加锁顺序;


4.2 锁冲突导致死锁

innodb 在 RR 隔离级别下,最常见的是插入意向锁与 gap 锁冲突造成死锁;主要原理为:一个事务想要获取插入意向锁,如果有其他事务已经加了 gap lock 或 Next-key lock 则会阻塞;


当一个事务在范围内插入新行时,它需要获取插入意向锁以指示自己的意图。同时,如果其他事务正在读取或修改这个范围内的数据,它们会尝试获取 gap 锁以阻止插入其他具有相同值的行。


然而,当两个事务同时尝试在不同的范围内插入行,并且范围存在重叠时,死锁可能会发生。具体来说,假设事务A插入范围R1内的新行,并持有了该范围的插入意向锁。同时,事务B插入范围R2内的新行,并持有了该范围的插入意向锁。如果R1和R2存在重叠部分,那么事务A会尝试获取R2的gap锁,而事务B会尝试获取R1的gap锁。这样,事务A无法继续执行因为需要R2的gap锁,而事务B也无法继续执行因为需要R1的gap锁,这就导致了死锁的发生。


解决办法是:更换语句或者降低隔离级别;


4.3 如何避免死锁

1)尽可能以相同顺序来访问索引记录和表


2)如果能确定幻读和不可重复读对应用影响不大,考虑将隔离级别降低为 RC


3)添加合理的索引,不走索引将会为每一行记录加锁,死锁概率非常大


4)尽量在一个事务中锁定所需要的所有资源,减小死锁概率


5)避免大事务,将大事务分拆成多个小事务;大事务占用资源多,耗时长,冲突概率变高


6)避免同一时间点运行多个对同一表进行读写的概率;


4.4 例子

例1


DROP TABLE IF EXISTS `account_t`;
CREATE TABLE `account_t` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(255) DEFAULT NULL,
  `money` INT(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
)ENGINE = INNODB AUTO_INCREMENT=0 DEFAULT CHARSET = utf8;
INSERT INTO `account_t` VALUES (1, 'C', 1000),(2, 'B', 1000),(3, 'A', 1000);
SELECT * FROM account_t;
-- 按顺序分别在事务1和2执行下面
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
-- 死锁事务1
UPDATE `account_t` SET `money` = `money` - 100 WHERE `id` = 1;
-- 死锁事务2
UPDATE `account_t` SET `money` = `money` - 100 WHERE `id` = 2;
-- 死锁事务1
UPDATE `account_t` SET `money` = `money` + 100 WHERE `id` = 2;  //要获取id = 2的锁,但这个此时被事务2持有。所以会发生阻塞
-- 死锁事务2
UPDATE `account_t` SET `money` = `money` - 100 WHERE `id` = 1;  // 报错,发生死锁
rollback

事务1持有id=1的锁的同时,又想获取id=2的锁。同样,事务2持有id=2的锁的同时,又想获取id=1的锁。造成死锁局面。


例2


DROP TABLE IF EXISTS `dl_mark_t`;
CREATE TABLE `dl_mark_t` (
  `a` INT(11) NOT NULL DEFAULT '0',
  `b` INT(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `uk_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `dl_mark_t` VALUES (1,1),(5,4),(20,20),(25,12);
SELECT * from dl_mark_t;
-- 死锁情况 1  
//如果事务 T1 持有了一个间隙锁,而事务 T2 尝试获取同一间隙内的插入意向锁,
//而 T1 的操作还没有提交或回滚,就会造成死锁。
//事务一在插入时由于跟事务二插入的记录唯一键冲突,所以对 b=10 这个唯一索引加 X 锁(Next-key)并处于锁等待,
//事务二再插入 b=9 这条记录,需要获取插入意向锁和事务一持有的 Next-key 锁冲突,从而导致死锁。
BEGIN
-- 死锁事务 2
insert into `dl_mark_t` values(26,10);  -- b=10不存在,插入的时候加了(4,20)的gap锁
-- 死锁事务 1
insert into `dl_mark_t` values(30,10);  -- 事务1加了(4,20)的插入意向锁,即等待事务2释放锁,阻塞
-- 死锁事务 2
insert into `dl_mark_t` values(40,9);   -- 事务2加了(4,20)的插入意向锁,即等待事务1释放锁,阻塞
-- 死锁情况 2
/*
1. 三个事务依次执行 insert 语句,由于 b是唯一索引,所以后两个事务会出现唯一键冲突。
但此时要注意的是事务一还没有提交,所以并不会立即报错。事务二和事务三为了判断是否出现唯一键冲突,
必须进行一次当前读,加的锁是 Next-Key 锁,所以进入锁等待。要注意的是,就算在 RC 隔离级别下,一样会加 Next-Key 锁,所以说出现 GAP 锁不一定就是 RR 隔离级别;
3. 事务一回滚,此时事务二和事务三成功获取记录上的 S 锁;
4. 事务二和事务三继续执行插入操作,需要依次请求记录上的插入意向锁(插入意向锁和 GAP 锁冲突,所以事务二等待事务三,事务三等待事务二,形成死锁。
*/
BEGIN
-- 死锁事务 1
insert into `dl_mark_t` values(27, 29);
-- 死锁事务 2
insert into `dl_mark_t` values(28, 29);
-- 死锁事务 3
insert into `dl_mark_t` values(29, 29);
-- 死锁事务 1
ROLLBACK;

例2不太好理解,看一下下面几张图

49544f3a935cd59dbd39b71da3c18449_9e5f2ad356854c7394aa290cede01072.png

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
6
分享
相关文章
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
随着数据量增长和业务扩展,单个数据库难以满足需求,需调整为集群模式以实现负载均衡和读写分离。MySQL主从复制是常见的高可用架构,通过binlog日志同步数据,确保主从数据一致性。本文详细介绍MySQL主从复制原理及配置步骤,包括一主二从集群的搭建过程,帮助读者实现稳定可靠的数据库高可用架构。
28 9
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
54 25
MySQL底层概述—10.InnoDB锁机制
本文介绍了:锁概述、锁分类、全局锁实战、表级锁(偏读)实战、行级锁升级表级锁实战、间隙锁实战、临键锁实战、幻读演示和解决、行级锁(偏写)优化建议、乐观锁实战、行锁原理分析、死锁与解决方案
MySQL底层概述—10.InnoDB锁机制
MySQL底层概述—9.ACID与事务
本文介绍了数据库事务的ACID特性(原子性、一致性、隔离性、持久性),以及事务控制的演进过程,包括排队、排它锁、读写锁和MVCC(多版本并发控制)。文章详细解释了每个特性的含义及其在MySQL中的实现方式,并探讨了事务隔离级别的类型及其实现机制。重点内容包括:ACID特性(原子性、持久性、隔离性和一致性的定义及其实现方式)、事务控制演进(从简单的全局排队到复杂的MVCC,逐步提升并发性能)、MVCC机制(通过undo log多版本链和Read View实现高效并发控制)、事务隔离级别(析了四种隔离级别(读未提交、读已提交、可重复读、可串行化)的特点及适用场景)、隔离级别与锁的关系。
MySQL底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
MySQL底层概述—6.索引原理
MySQL原理简介—12.MySQL主从同步
本文介绍了四种为MySQL搭建主从复制架构的方法:异步复制、半同步复制、GTID复制和并行复制。异步复制通过配置主库和从库实现简单的主从架构,但存在数据丢失风险;半同步复制确保日志复制到从库后再提交事务,提高了数据安全性;GTID复制简化了配置过程,增强了复制的可靠性和管理性;并行复制通过多线程技术降低主从同步延迟,保证数据一致性。此外,还讨论了如何使用工具监控主从延迟及应对策略,如强制读主库以确保即时读取最新数据。
MySQL原理简介—12.MySQL主从同步
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
MySQL原理简介—10.SQL语句和执行计划
本文介绍了MySQL执行计划的相关概念及其优化方法。首先解释了什么是执行计划,它是SQL语句在查询时如何检索、筛选和排序数据的过程。接着详细描述了执行计划中常见的访问类型,如const、ref、range、index和all等,并分析了它们的性能特点。文中还探讨了多表关联查询的原理及优化策略,包括驱动表和被驱动表的选择。此外,文章讨论了全表扫描和索引的成本计算方法,以及MySQL如何通过成本估算选择最优执行计划。最后,介绍了explain命令的各个参数含义,帮助理解查询优化器的工作机制。通过这些内容,读者可以更好地理解和优化SQL查询性能。
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
98 42

推荐镜像

更多
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等