MySQL事务隔离级别验证

本文深入探讨了数据库事务的ReadView验证,详细阐述了RC(读已提交)和RR(可重复读)两种隔离级别的工作原理。在RC中,每次查询都会创建新的ReadView来判断数据可见性,而在RR中,ReadView只在事务开始时创建一次。MVCC(多版本并发控制)借助Undo日志和ReadView实现事务可见性判断,确保并发访问的正确性。此外,文章还提及了索引的重要性和使用策略,以及如何通过Explain分析SQL查询是否利用了索引。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一:完成ReadView的验证
1.创建表结构
在这里插入图片描述
2.查询出的初始化值
在这里插入图片描述
二:验证RC(读已提交)的ReadView功能
1.设置三个会话窗口的隔离级别
在这里插入图片描述
步骤
在这里插入图片描述
先全部会话窗口同时开启事务,会话一执行更新操作,会话二在之后执行更新操作
在这里插入图片描述
由于前面的事务未提交,对于会话三的窗口,执行select创建的ReadView的事务在活跃的事务范围内,对于其他会话的数据不可见,查询出来还是原来的数据
在这里插入图片描述
这时,会话一执行提交事务,然后会话三在执行select创建的ReadView的最小活跃事务id比会话一的事务id大,表示会话一的数据对会话三的查询可见
在这里插入图片描述
最后,会话二执行提交事务,同会话一的情况一致,会话三可以查询到会话二的数据,然后会话三也提交事务
在这里插入图片描述
RC结论:
每次执行select的操作都会创建一个新的ReadView去记录当前活跃的事务id,通过版本链表判断是否对当前事务可见

三:验证RR(可重复读)的可见性
所有会话窗口设置可重复读隔离级别
在这里插入图片描述
所有会话窗口执行开启事务,然后会话一执行更新操作,接着会话二执行更新操作,接着会话三执行查询,由于可重复读select创建ReadView只会一次,所以当前的活跃事务表包含这个三个事务,所以会话三查不到数据
在这里插入图片描述
然后会话一执行commit提交事务,会话三执行查询操作,由于可重复读的ReadView只创建一次,对于会话一的事务id还是在会话三的活跃事务表中,所以对于会话三还是不可见
在这里插入图片描述
之后会话二执行提交事务,同理会话一的情况,对于会话三还是不可见
在这里插入图片描述
最后,会话三提交事务,执行查询时会创建一个新的ReadView,所以对于之前修改的数据可见
在这里插入图片描述
RR结论
在事务开始后第一次读取数据时生成一个ReadView。对于使用RR 隔离级别的事务来说,只会在第一次
执行查询语句时生成一个ReadView ,之后的查询就不会重复生成了。我们还是用例子看一下是什么效
果。

MVCC:多版本并发控制,用来对数据库数据进行并发访问,实现事务;核心是读不加锁,读写不冲突
MVCC是依赖于Undo日志和ReadView

Undo日志保存着记录修改前的快照;对于insert和update操作记录,只对当前的事务起效,对于其他事务不可见,而update操作记录会生成一个undo日志的版本链表
在这里插入图片描述
ReadView是一张存储事务id记录表
在这里插入图片描述
m_low_limit_id:事务id下限,表示当前系统中活跃的读写事务中最小的事务id,m_ids事务列表
中的最小事务id
m_ids:表示在生成ReadView时,当前系统中活跃的读写事务id列表
m_up_limit_id:事务id上限,表示生成ReadView时,系统中应该分配给下一个事务的id值
m_creator_trx_id:表示生成该ReadView的事务的事务id

可见性:
开启事务执行第一次查询时,首先生成ReadView,然后依据Undo日志和ReadView按照判断可见性,
按照下边步骤判断记录的版本链的某个版本是否可见。

循环判断规则如下:

1.如果被访问版本的trx_id 属性值,小于ReadView中的事务下限id,表明生成该版本的事务在生
成ReadView 前已经提交,所以该版本可以被当前事务访问。
2.如果被访问版本的trx_id 属性值,等于ReadView中的m_creator_trx_id ,可以被访问。
3.如果被访问版本的trx_id 属性值,大于等于ReadView中的事务上限id,在生成ReadView 后才产
生的数据,所以该版本不可以被当前事务访问。
4.如果被访问版本的trx_id 属性值,在事务下限id和事务上限id之间,那就需要判断是不是在
m_ids 列表中。
4.1如果在,说明创建ReadView 时生成该版本的事务还是活跃的,该版本不可以被访问;
4.2如果不在,说明创建ReadView 时生成该版本的事务已经被提交,该版本可以被访问。

循环判断Undo log中的版本链某一的版本是否对当前事务可见,如果循环到最后一个版本也不可见的
话,那么就意味着该条记录对该事务不可见,查询结果就不包含该记录。

索引
是为了提高高效的查询效率,通过空间换时间的理论的一种数据结构
优点:提高查询效率,降低IO操作,降低排序操作和CPU的消耗
缺点:耗费磁盘空间,降低更新表的效率。

索引的分类:
主键索引:索引列中的值必须是唯一的不允许有空值
唯一索引:索引列中的值必须是唯一的,但是允许为空值
普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值
全文索引:只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引
空间索引:MySQL在5.7之后的版本支持了空间索引,而且支持OpenGIS几何数据模型
前缀索引:在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时,可以指定索引列的长度,
但是数值类型不能指定
组合索引:由多个字段联合的普通索引

InnoDB创建索引的具体规则如下:
1.在表上定义主键PRIMARY KEY,InnoDB将主键索引用作聚簇索引。
2.如果表没有定义主键,InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
3.如果以上两个都没有,InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该
ROWID字段会在插入新行时自动递增。

索引的注意事项:
1.表记录很少不需创建索引
2.一个表的索引个数不能过多
3.频繁更新的字段不建议作为索引
4.区分度低的字段,不建议建索引
5.在InnoDB存储引擎中,主键索引建议使用自增的长整型,避免使用很长的字段
6.不建议用无序的值作为索引
7.尽量创建组合索引,而不是单列索引
8.不要在索引上做计算
9.索引上不要使用不等,不要判断null
10.索引字段字符串要加单引号
11.索引匹配要满足最左匹配原则

如何知道 SQL 是否用到了索引
通过Explain执行计划中的possible_key 与 key

索引的原理:
hash数据结构:key-value适合做的缓存
二叉查找树:极端情况需要做全表扫描,而且对磁盘io不友好
红黑树:不支持范围查询,索引占用空间大
B树:空间占用大,无法快速查询范围
B+树:继承B树的优点,优化了空间的占用和范围查询,非叶子节点不保存数据

<think>首先,用户的问题是:“我想了解MySQL数据库事务隔离级别及其特性 请问MySQL 事务隔离级别详解”。这是一个关于MySQL事务隔离级别的查询。 根据系统级指令: - 所有行内数学表达式必须使用$...$格式,但在这个主题中,可能没有数学表达式,所以我可以忽略或确保如果有就正确使用。 - 独立公式必须使用$$...$$格式并单独成段。 - LaTeX语法正确:确保任何公式正确。 - 使用中文回答:整个回答用中文。 - 生成相关问题:在回答结束后,添加相关问题部分。 - 回答中引用的段落末尾自然地添加引用标识:例如[^1],根据提供的引用内容。 用户可见层指令: - 回答结构清晰,帮助用户逐步解决问题,保证回答真实可靠。 - 参考站内引用:提供了几个引用,我需要参考它们来构建回答。 提供的引用: - [^1]: 串行化隔离级别,最高级别,避免脏读、不可重复读、幻读,但牺牲并发性能。有示例代码。 - [^2]: MySQL事务隔离级别详解,强调隔离级别与问题(脏读、幻读等)的关系。 - [^3]: 默认隔离级别是可重复读,通过命令查看。 - [^4]: 详细解释四种隔离级别:读未提交、读提交、可重复读、串行化;包括特性、问题、实现方式(MVCC和锁)。 我的回答需要基于这些引用,结构化地解释MySQL事务隔离级别。 结构化回答: 1. **介绍事务隔离级别**:解释什么是事务隔离级别,为什么重要。 2. **列出四种隔离级别**:根据引用[^4],有读未提交、读提交、可重复读、串行化。 3. **详细每个级别**: - 特性。 - 解决的问题(脏读、不可重复读、幻读)。 - 性能影响。 - 实现方式(如MVCC、锁)。 4. **默认隔离级别**:根据引用[^3],MySQL InnoDB默认是可重复读。 5. **示例代码**:引用[^1]有设置隔离级别的代码。 6. **总结**:比较各级别。 确保使用引用标识: - 在引用的段落末尾添加[^1], [^2], 等。 生成相关问题:在回答结束后,添加“相关问题”部分,列出几个相关问题。 回答大纲: - 开头:介绍事务隔离级别。 - 主体:逐步解释四种级别。 - 结尾:总结和设置方法。 - 相关问题:生成3-5个相关问题。 用中文回答。 最终输出格式: - 回答内容。 - §§相关问题§§ - 相关问题列表。</think>### MySQL 事务隔离级别详解 MySQL事务隔离级别数据库管理系统中用于控制多个并发事务之间相互影响的关键机制。它定义了事务在读取和写入数据时的可见性规则,从而确保数据的一致性和完整性。MySQL 支持四种标准的事务隔离级别:读未提交(Read Uncommitted)、读提交(Read Committed)、可重复读(Repeatable Read)和串行化(Serializable)。每种级别在解决并发问题(如脏读、不可重复读和幻读)的同时,会带来不同的性能影响。下面我将逐步解释这些隔离级别的特性、实现原理和实际应用。 #### 1. **事务隔离级别的基本概念** - 事务隔离级别是为了解决并发事务中的常见问题而设计的: - **脏读(Dirty Read)**:一个事务读取了另一个未提交事务修改的数据。如果后者回滚,会导致读取到无效数据。 - **不可重复读(Non-repeatable Read)**:同一事务中多次读取同一数据,结果不一致(因为其他事务修改了数据)。 - **幻读(Phantom Read)**:同一事务中多次查询同一范围的数据,结果集不一致(因为其他事务插入了新数据)。 - 隔离级别越高,数据一致性越强,但并发性能越低;反之,级别越低,性能越高,但数据风险越大[^4]。 #### 2. **四种事务隔离级别的详细特性** MySQL隔离级别可以通过 SQL 命令设置(如 `SET SESSION TRANSACTION ISOLATION LEVEL ...`),以下是各级别的详解: - **读未提交(Read Uncommitted)** - **特性**:事务可以读取其他未提交事务修改的数据。这是最低的隔离级别,性能最高,但数据安全性最差。 - **解决的问题**:无。脏读、不可重复读和幻读都可能发生。 - **实现方式**:直接读取数据页的最新版本,无需加锁或使用快照。 - **性能影响**:并发性能最佳,但可能导致数据不一致。适用于对一致性要求低的场景,如日志分析[^4]。 - **示例问题**:事务 A 修改数据但未提交,事务 B 读取该数据后,A 回滚,B 读取的数据无效(脏读)。 - **读提交(Read Committed)** - **特性**:事务只能读取已提交的数据。避免了脏读,但不可重复读和幻读仍可能发生。 - **解决的问题**:脏读。 - **实现方式**:使用多版本并发控制(MVCC)技术。每个 SQL 语句执行前生成一个 Read View(数据快照),确保只读取已提交的数据。 - **性能影响**:性能较高,适用于大多数 OLTP 系统(如电商订单处理)。但频繁的快照生成可能增加开销[^4]。 - **示例问题**:事务 A 读取数据后,事务 B 修改并提交数据,A 再次读取时结果改变(不可重复读)。 - **可重复读(Repeatable Read)** - **特性**:MySQL InnoDB 存储引擎的默认隔离级别事务在整个执行过程中看到的数据一致,避免了脏读和不可重复读,但幻读可能发生(通过间隙锁可部分缓解)。 - **解决的问题**:脏读和不可重复读。 - **实现方式**:基于 MVCC 和锁机制。事务启动时生成一个 Read View,整个事务期间复用该快照。对于写操作,使用行锁防止冲突。 - **性能影响**:平衡了数据一致性和并发性能。是 MySQL 的推荐设置,适用于财务系统等需要高一致性的场景[^3][^4]。 - **示例问题**:事务 A 查询数据范围,事务 B 插入新数据并提交,A 再次查询时可能发现“幻影行”(幻读)。 - **串行化(Serializable)** - **特性**:最高隔离级别事务串行执行,完全避免了脏读、不可重复读和幻读,但牺牲了并发性。 - **解决的问题**:所有并发问题(脏读、不可重复读、幻读)。 - **实现方式**:使用读写锁(读锁和写锁)。当多个事务访问同一数据时,后访问的事务必须等待前一个完成。 - **性能影响**:并发性能最低,可能导致大量事务阻塞。适用于对数据一致性要求极高的场景,如银行转账[^1][^4]。 - **示例代码**:设置隔离级别并执行事务。 ```sql -- 设置串行化隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 开启事务 START TRANSACTION; -- 查询数据 SELECT * FROM table_name; -- 修改数据 UPDATE table_name SET column_name = new_value WHERE condition; -- 提交事务 COMMIT; ``` #### 3. **MySQL 默认隔离级别及查看方法** - MySQL InnoDB 的默认隔离级别是**可重复读(Repeatable Read)**。您可以通过 SQL 命令查看当前设置: - MySQL 5.7 及之前:`SELECT @@tx_isolation;` - MySQL 8.0 及之后:`SELECT @@transaction_isolation;` - 示例输出: ``` +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ ``` 这确保了事务启动时数据视图的一致性[^3]。 #### 4. **隔离级别的比较与选择建议** - **比较表**(基于引用[^4]): | 隔离级别 | 脏读 | 不可重复读 | 幻读 | 性能 | 实现机制 | |---------------|------|------------|------|------|-------------------| | 读未提交 | √ | √ | √ | 最高 | 直接读取最新数据 | | 读提交 | × | √ | √ | 高 | MVCC(语句级快照)| | 可重复读(默认)| × | × | √ | 中 | MVCC(事务级快照)+ 锁 | | 串行化 | × | × | × | 低 | 读写锁 | - 注:√ 表示可能出现问题,× 表示避免问题。 - **选择建议**: - 优先使用默认的**可重复读**,它在性能和一致性间取得平衡。 - 高并发场景(如 Web 应用)可考虑**读提交**以提升吞吐量。 - 严格数据一致性需求(如金融系统)使用**串行化**,但需监控性能瓶颈。 - 避免**读未提交**,除非对数据错误容忍度高。 #### 5. **如何设置和验证隔离级别** - **设置方法**: ```sql -- 设置会话级隔离级别(仅当前连接有效) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 或设置全局级(所有新会话) SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; ``` - **验证方法**:执行上述查看命令。事务启动后,隔离级别生效,确保在 `START TRANSACTION` 前设置[^1][^3]。 总之,MySQL事务隔离级别数据库并发控制的核心。默认的可重复读级别已能处理大多数场景,但您应根据应用需求(如数据一致性与性能权衡)进行调整。实践中,结合 MVCC 和锁机制,MySQL 在保证 ACID 属性的同时优化了并发效率[^2][^4]。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值