Mysql 事务原理

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
日志服务 SLS,月写入数据量 50GB 1个月
简介: Mysql 事务原理

1、事务

事务 (transaction) :并发控制的前提下,用户定义的一个操作序列。这些操作要么都做(提交),要么都不做(回滚),不可分割。

目的:将数据库从一种一致性状态转换为另一种一致性状态,保证系统始终处于一个完整且正确的状态。

1.1、ACID 特性

  • 原子性 (Atomicity)
  • 一致性 (Consistency)
  • 隔离性 (Isolation)
  • 持久性 (Durability)

1.1.1、原子性

一个事务中的操作,要么都做,要么都不做,不存在中间状态,是一个不可分割的工作单位。若事务执行过程发生错误,回滚到事务初始状态。

实现机制: undo log

* undo log

undo log 回滚日志,存放在共享表空间内,用于存储旧版本的数据。

undo log是逻辑日志,回滚时将数据库逻辑地恢复到原来的样子,也就是说,根据 undo log 记录的事务 DML 操作,做之前的相反操作,实现回滚操作,保证了事务的原子性和一致性。

此外,undo log 也可以用来实现 MVCC。用户读取记录时,若该记录被其他事务占用,当前事务可以通过 undo log 读取之前事务 DML 操作提交后的行版本信息,以此实现非锁定读。

总结 undo log 的作用

  • 事务回滚:记录事务 DML 操作步骤,通过逆运算(逻辑取反)实现事务回滚。
  • MVCC:记录事务 DML 操作提交后产生的行版本信息。

1.1.2、一致性

事务的前后,数据满足完整性约束,数据库保持一致性状态。

一致性指的是事务将数据库从一种一致性状态转变为下一种一致性的状态,在事务执行前后,数据库完整性约束没有被破坏。一个事务需要提交后才会被其他事务可见。

一致性的种类

  • 数据一致性(完整性约束),必须遵守,例如主键特性、外键特性等
  • 预期一致性(逻辑一致性),适当破坏,提高并发速度。例:查询是否存在,不存在写入,可能出现数据查询不存在,插入时却存在,报错。

实现机制:原子性 + 隔离性 + 一致性实现。

1.1.3、* 隔离性

并发事务间相互隔离,互不影响,避免多个事务并发异步执行进而导致数据的不一致。

实现机制:为了提升性能,设定不同程度的隔离级别,适度破坏逻辑一致性。

  • :用来并发处理 DML 操作。数据库中提供粒度锁的策略,针对表(聚集索引 B+ 树)、页(聚集索引 B+ 树叶子节点)、行(叶子节点中某一段行记录)三种粒度加锁
  • MVCC :多版本并发控制。主要解决一致性非锁定读,通过记录和获取行版本信息,而不是使用锁来限制读操作,从而实现高效并发读性能。

1.1.4、持久性

事务一旦提交,其结果就是永久性的,即使系统故障也不丢失。

实现机制: redo log

redo log

redo log 重做日志,用于实现事务的持久性。

事务提交后,事务 DML 操作持久化,记录事务 DML 操作对应物理页修改的内容,写入 redo log 磁盘文件。发生宕机等故障时,恢复数据库数据。

redo log 属于 WAL (Write Ahead Log)预写日志,用于数据恢复,顺序写磁盘,再通过其他线程异步刷到 B+ 树。与 redis 的aof类似。

1.2、事务控制语句

innoDB 中一条 sql 语句是一个事务。若想多条语句构成事务,采用事务控制语句。

-- 显示开启事务
 START | BEGIN TRANSACTION
 -- 提交事务,并使得已对数据库做的所有修改持久化
 COMMIT
 -- 回滚事务,结束用户的事务,并撤销正在进行的所有未提交的修改
 ROLLBACK
 -- 创建一个保存点,一个事务可以有多个保存点
 SAVEPOINT identifier
 -- 删除一个保存点
 RELEASE SAVEPOINT identifier
 -- 事务回滚到保存点
 ROLLBACK TO [SAVEPOINT] identifier

2、隔离级别

ISO 和 ANIS SQL 标准制定了四种事务隔离级别,目的在于提升数据库并发性能。

不同隔离级别的区别在于读操作的加锁,写操作均加排他锁。隔离级别的高低,与事务请求锁的数量和保持锁时间的长短相关。级别越高,性能越低;级别越低,逻辑一致性受到的影响越大。

2.1、隔离级别的分类

2.1.1、读未提交 RU

读未提交,READ UNCOMMITTED。事务还未提交,其他事务就看到修改。

  • 读:不做处理
  • 写:自动加 X 锁

逻辑错误:脏读。

2.1.2、读已提交 RC

读已提交 (RC) ,READ COMMITTED。事务提交后,其他事务可以看到修改。大部分数据库采用的隔离级别,如 oracle, SQL Server 等。

  • 读:MVCC,读取最新版本的行数据
  • 写:自动加 X 锁

MVCC 在事务期间每次读取数据时,生成新的 read view,这也意味着同一事务多次读取同一条数据可能出现数据不一致(不可重复读)。因为在多次读取数据期间可能有其他事务修改并提交了该条记录。

逻辑错误:不可重复读。

2.1.3、可重复读 RR

可重复读 (RR) ,REPEATABLE READ。Mysql 采用的默认隔离级别

  • 读:MVCC,读取事务开始前版本的行数据
  • 写:自动加 X 锁

MVCC 在事务启动时,生成新的 read view,整个事务期间读取数据使用这个 read view,这样保证了在事务期间读到的数据都是事务启动前的记录。

逻辑错误:幻读。

2.1.4、可串行化 SC

可串行化,SERIALIZABLE

  • 读:自动加 S 锁 (next-key locking)
  • 写:自动加 X 锁

2.2、并发读异常

不同隔离级别由于逻辑一致性问题造成的并发异常。

2.2.1、脏读

一个事务读到了另一个事务未提交的修改(读到脏数据)。

脏读在 RU 隔离级别存在。在读写分离的场景下,可以将 slave 节点设置为 READ UNCOMMITTED。此时脏读不影响,在 slave 上查询并不需要特别精准的返回值。

例如:session B 读到了 session A 中事务未提交的脏数据。

seq session A session B
1 SET @@tx_isolation = 'READ UNCOMMITTED'; SET @@tx_isolation = 'READ UNCOMMITTED';
2 BEGIN; BEGIN;
4 UPDATE account_t SET money = money - 100 WHERE name = 'A';
5 SELECT money FROM account_t WHERE name = 'A';
6 COMMIT; COMMIT;

2.2.2、不可重复读

一个事务读到了另一个事务的提交的修改,同一事务内两次读取同一个数据不一样。

不可重复读在 RC 隔离级别存在。一般来说,不可重复读的问题可以接受,因为读到已经提交的数据,不会带来很大的问题。

例如:session B 读到了 session A 中事务提交的修改,造成两次读取同一个数据不一样。

seq session A session B
1 SET @@tx_isolation = 'READ COMMITTED'; SET @@tx_isolation = 'READ COMMITTED';
2 BEGIN; BEGIN;
4 SELECT money FROM account_t WHERE name = 'A';
5 UPDATE account_t SET money = money - 100 WHERE name = 'A';
6 COMMIT ; SELECT money FROM account_t WHERE name = 'A';
7 COMMIT;

2.2.3、幻读

同一事务两次读取同一个范围内的记录得到的结果集不一样。

幻读在 RR 隔离级别存在,仅在当前读下出现。

例如:由于 session A 的事务提交了插入操作,导致 session B 两次查询范围的结果不一样。

seq session A session B
1 SET @@tx_isolation = 'REPEATABLE READ'; SET @@tx_isolation = 'REPEATABLE READ';
2 BEGIN; BEGIN;
4 SELECT * FROM account_t WHERE id >= 2;
5 INSERT INTO account_t VALUES (4, 'D', 1000);
6 COMMIT ;
7 SELECT * FROM account_t WHERE id >= 2;
COMMIT;

解决:通过读加锁(next-key locking)

seq session A session B
1 SET @@tx_isolation = 'REPEATABLE READ'; SET @@tx_isolation = 'REPEATABLE READ';
2 BEGIN; BEGIN;
4 SELECT * FROM account_t WHERE id >= 2 FOR UPDATE | IN SHARE MODE;
INSERT INTO account_t VALUES (4, 'D', 1000);
6 COMMIT ;
7 SELECT * FROM account_t WHERE id >= 2;
COMMIT;

2.2.4、总结

  • 脏读:一个事务读到了另一个事务未提交的修改
  • 不可重复读:一个事务读到了另一个事务提交后的修改,UPDATE
  • 幻读:同一事务两次读取同一个范围内的记录得到的结果集不一样,INSERT DELETE

2.2.5、测试代码

DROP TABLE IF EXISTS `account_t`;
 CREATE TABLE `account_t` (
     `id` INT(11) NOT NULL,
     `name` VARCHAR(225) DEFAULT NULL,
     `money` INT(11) DEFAULT 0,
     PRIMARY KEY(`id`),
     KEY `idx_name` (`name`)
 ) ENGINE = innoDB AUTO_INCREMENT=0 DEFAULT CHARSET = utf8;
 SELECT * FROM `account_t`;
 INSERT INTO `account_t` VALUES (1, 'A', 1000), (2, 'B', 1000), (3, 'B', 1000);
 ROLLBACK;
 -- 脏读:一个事务读取了另一个未提交事务的修改
 -- 隔离级别:READ UNCOMMITTED
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 BEGIN
 -- 脏读事务1
 UPDATE `account_t` SET `money` = `money` - 100 WHERE `name` = 'A';
 -- 脏读事务2
 SELECT `money` FROM `account_t` WHERE `name` = 'A';
 COMMIT;
 -- 不可重复读:一个事务内两次读取同一个数据不一样
 -- 隔离级别:READ COMMITTED,解决了脏读问题
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 BEGIN
 -- 不可重复读事务1
 UPDATE `account_t` SET `money` = `money` - 100 WHERE `name` = 'A';
 -- 不可重复读事务2
 SELECT `money` FROM `account_t` WHERE `name` = 'A';
 COMMIT;
 -- 幻读:一个事务内两次读取同一个范围内的记录得到的结果集不一样。
 -- 隔离级别:REPEATABLE READ,解决了不可重复读
 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 BEGIN
 -- 幻读事务1
 INSERT INTO `account_t` VALUES (4, 'D', 1000);
 -- 幻读事务2
 SELECT * FROM `account_t` WHERE `id` >= 2;
 -- 幻读事务2:解决幻读问题
 SELECT * FROM `account_t` WHERE `id` >= 2 FOR UPDATE;
 COMMIT;

3、MVCC

多版本并发控制 MVCC(Multiversion Concurrency Control),用来实现一致性的非锁定读。非锁定读是指不需要等待访问记录 X 锁的释放。因此 MVCC 没有读写阻塞,只有写写阻塞,提高了并发性。

MVCC 只在读已提交和可重复读的隔离级别下工作,与其他隔离级别不兼容。

MVCC 通过 read view,聚集索引隐藏列,undo log 来实现,关键是解决事务的可见性问题。

3.1、当前读 & 快照读

innoDB 支持的读方式

  • 当前读(锁定读):读取最新版本的记录,对读取的记录加锁(悲观锁),保证其他并发事务不能修改当前记录
  • select ... lock in share mode | for update
  • insert, delete, update,不管什么隔离级别,DML 操作均加锁。
  • undo log
  • 快照读(非锁定读):读取记录的一个快照,对读取的记录不加锁。普通 SELECT 语句

MVCC 的读指的是快照读(非锁定读),因为没有事务需要对历史数据进行 DML 操作。

在读已提交和可重复读的隔离级别下,对于快照数据的定义不同

  • RC 级别:读取当前事务锁定行的最新行记录。
  • RR 级别:读取启动事务时的行记录版本。

3.2、read view

read view 是事务进行快照读的时候产生的读视图,保存了当前事务开启时所有活跃事务的列表。

3.2.1、read view 生成

在读已提交和可重复读的隔离级别下,read view 的区别仅在于创建 read view 的时机不同:

  • RC 级别:每次读取数据时,生成新的 read view。
  • RR 级别:启动事务时,生成新的 read view,一直使用到事务提交。

3.2.2、read view 属性

  • m_ids:创建 read view 时,活跃事务的事务 id 列表。活跃事务指的是已启动但是未提交的事务。
  • min_trx_id:创建 read view 时,活跃事务的最小事务 id
  • max_trx_id:创建 read view 时,预分配给下一个未开启事务的 id,即全局事务的最大事务 id + 1
  • creator_trx_id:创建该 read view 的事务的事务 id

创建 read view 时,read view 属性与事务状态(已提交 - 启动未提交 - 未启动)的关系

事务状态


3.3、聚集索引隐藏列

聚集索引记录的隐藏列

  • trx_id:事务修改记录时,trx_id 记录生成该版本的事务 id。
  • roll_pointer:事务修改记录时,将旧记录写入 undo log,roll_pointer 指向旧版本记录,通过它可以找到修改前的记录。

聚集索引隐藏列

3.4、undo log

用户读取记录时,若该记录被其他事务占用,当前事务可以通过 undo 读取之前事务 DML 操作提交后的行版本信息,以此实现非锁定读。

innoDB 通过 undo log 保存每个记录的多个版本,每个事务读到的记录版本可能是不一样的。在同一个事务中,用户只能看到该事务创建快照之前已经提交的修改和该事务本身做的修改。

3.4、事务的可见性

事务的可见性问题:当前事务访问某条记录时该记录是否可见

判断规则是:判断生成该版本的事务 id (trx_id)与生成 read view 的当前事务 id 的关系。具体来说,生成 read view 的当前事务处于启动未提交状态,重点判断生成该版本事务的所处状态。

  • trx_id = creator_trx_id:当前事务访问自己修改的记录,该版本记录对当前事务可见。
  • trx_id < min_trx_id:生成该版本的事务在当前事务生成 read view 前已提交,该版本记录对当前事务可见。
  • trx_id > max_trx_id:生成该版本的事务在当前事务生成 read view 后启动,该版本记录对当前事务不可见。
  • min_trx <= trx_id <= max_trx_id,判断生成该版本的事务 id 是否在启动未提交的事务 id 列表中
  • 存在:生成该版本记录的事务已启动但未提交,该版本记录对当前事务不可见
  • 不在:生成该版本记录的事务已提交,该版本记录对当前事务可见

整理一下,方便记忆:

  • trx_id = creator_trx_id,版本事务 = 当前事务
  • trx_id < min_trx_id:版本事务已提交,可见。
  • trx_id > max_trx_id: 版本事务后启动,不可见。
  • min_trx <= trx_id <= max_trx_id,版本事务 id 是否在启动未提交的事务 id 列表中
  • 存在:版本事务启动未提交,不可见
  • 不在:版本事务已提交,可见
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
23天前
|
关系型数据库 MySQL Linux
MySQL原理简介—6.简单的生产优化案例
本文介绍了数据库和存储系统的几个主题: 1. **MySQL日志的顺序写和数据文件的随机读指标**:解释了磁盘随机读和顺序写的原理及对数据库性能的影响。 2. **Linux存储系统软件层原理及IO调度优化原理**:解析了Linux存储系统的分层架构,包括VFS、Page Cache、IO调度等,并推荐使用deadline算法优化IO调度。 3. **数据库服务器使用的RAID存储架构**:介绍了RAID技术的基本概念及其如何通过多磁盘阵列提高存储容量和数据冗余性。 4. **数据库Too many connections故障定位**:分析了MySQL连接数限制问题的原因及解决方法。
|
18天前
|
存储 缓存 关系型数据库
MySQL底层概述—9.ACID与事务
本文介绍了数据库事务的ACID特性(原子性、一致性、隔离性、持久性),以及事务控制的演进过程,包括排队、排它锁、读写锁和MVCC(多版本并发控制)。文章详细解释了每个特性的含义及其在MySQL中的实现方式,并探讨了事务隔离级别的类型及其实现机制。重点内容包括:ACID特性(原子性、持久性、隔离性和一致性的定义及其实现方式)、事务控制演进(从简单的全局排队到复杂的MVCC,逐步提升并发性能)、MVCC机制(通过undo log多版本链和Read View实现高效并发控制)、事务隔离级别(析了四种隔离级别(读未提交、读已提交、可重复读、可串行化)的特点及适用场景)、隔离级别与锁的关系。
|
24天前
|
SQL Java 关系型数据库
MySQL原理简介—3.生产环境的部署压测
本文介绍了Java系统和数据库在高并发场景下的压测要点: 1. 普通系统在4核8G机器上每秒能处理几百个请求 2. 高并发下数据库建议使用8核16G或更高配置的机器 3. 数据库部署后需进行基准压测,以评估其最大承载能力 4. QPS和TPS的区别及重要性 5. 压测时需关注IOPS、吞吐量、延迟 6. 除了QPS和TPS,还需监控CPU、内存、磁盘IO、网络带宽 7. 影响每秒可处理并发请求数的因素包括线程数、CPU、内存、磁盘IO和网络带宽 8. Sysbench是数据库压测工具,可构造测试数据并模拟高并发场景 9. 在增加线程数量的同时,必须观察机器的性能,确保各硬件负载在合理范围
125 72
|
26天前
|
SQL 存储 关系型数据库
MySQL原理简介—1.SQL的执行流程
本文介绍了MySQL驱动、数据库连接池及SQL执行流程的关键组件和作用。主要内容包括:MySQL驱动用于建立Java系统与数据库的网络连接;数据库连接池提高多线程并发访问效率;MySQL中的连接池维护多个数据库连接并进行权限验证;网络连接由线程处理,监听请求并读取数据;SQL接口负责执行SQL语句;查询解析器将SQL语句解析为可执行逻辑;查询优化器选择最优查询路径;存储引擎接口负责实际的数据操作;执行器根据优化后的执行计划调用存储引擎接口完成SQL语句的执行。整个流程确保了高效、安全地处理SQL请求。
173 76
|
14天前
|
关系型数据库 MySQL 数据库
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
随着数据量增长和业务扩展,单个数据库难以满足需求,需调整为集群模式以实现负载均衡和读写分离。MySQL主从复制是常见的高可用架构,通过binlog日志同步数据,确保主从数据一致性。本文详细介绍MySQL主从复制原理及配置步骤,包括一主二从集群的搭建过程,帮助读者实现稳定可靠的数据库高可用架构。
40 9
RDS用多了,你还知道MySQL主从复制底层原理和实现方案吗?
|
21天前
|
SQL 存储 关系型数据库
MySQL原理简介—9.MySQL索引原理
本文详细介绍了MySQL索引的设计与使用原则,涵盖磁盘数据页的存储结构、页分裂机制、主键索引设计及查询过程、聚簇索引和二级索引的原理、B+树索引的维护、联合索引的使用规则、SQL排序和分组时如何利用索引、回表查询对性能的影响以及索引覆盖的概念。此外还讨论了索引设计的案例,包括如何处理where筛选和order by排序之间的冲突、低基数字段的处理方式、范围查询字段的位置安排,以及通过辅助索引来优化特定查询场景。总结了设计索引的原则,如尽量包含where、order by、group by中的字段,选择离散度高的字段作为索引,限制索引数量,并针对频繁查询的低基数字段进行特殊处理等。
MySQL原理简介—9.MySQL索引原理
|
19天前
|
存储 关系型数据库 MySQL
MySQL底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
MySQL底层概述—6.索引原理
|
21天前
|
SQL 监控 关系型数据库
MySQL原理简介—12.MySQL主从同步
本文介绍了四种为MySQL搭建主从复制架构的方法:异步复制、半同步复制、GTID复制和并行复制。异步复制通过配置主库和从库实现简单的主从架构,但存在数据丢失风险;半同步复制确保日志复制到从库后再提交事务,提高了数据安全性;GTID复制简化了配置过程,增强了复制的可靠性和管理性;并行复制通过多线程技术降低主从同步延迟,保证数据一致性。此外,还讨论了如何使用工具监控主从延迟及应对策略,如强制读主库以确保即时读取最新数据。
MySQL原理简介—12.MySQL主从同步
|
2天前
|
自然语言处理 搜索推荐 关系型数据库
MySQL实现文档全文搜索,分词匹配多段落重排展示,知识库搜索原理分享
本文介绍了在文档管理系统中实现高效全文搜索的方案。为解决原有ES搜索引擎私有化部署复杂、运维成本高的问题,我们转而使用MySQL实现搜索功能。通过对用户输入预处理、数据库模糊匹配、结果分段与关键字标红等步骤,实现了精准且高效的搜索效果。目前方案适用于中小企业,未来将根据需求优化并可能重新引入专业搜索引擎以提升性能。
|
23天前
|
SQL 缓存 关系型数据库
MySQL原理简介—7.redo日志的底层原理
本文介绍了MySQL中redo日志和undo日志的主要内容: 1. redo日志的意义:确保事务提交后数据不丢失,通过记录修改操作并在系统宕机后重做日志恢复数据。 2. redo日志文件构成:记录表空间号、数据页号、偏移量及修改内容。 3. redo日志写入机制:redo日志先写入Redo Log Buffer,再批量刷入磁盘文件,减少随机写以提高性能。 4. Redo Log Buffer解析:描述Redo Log Buffer的内存结构及刷盘时机,如事务提交、Buffer过半或后台线程定时刷新。 5. undo日志原理:用于事务回滚,记录插入、删除和更新前的数据状态,确保事务可完整回滚。
104 22