开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySQL - 深入理解 MySQL 的事务和隔离级别 -> 正文阅读

[大数据]MySQL - 深入理解 MySQL 的事务和隔离级别

Server version: 5.7.36 MySQL Community Server (GPL)
mysql官方文档：

1. 事务的ACID理论

1.1 什么是ACID理论？

ACID，是指数据库管理系统（DBMS）在写入或更新资料的过程中，为保证事务（transaction）是正确可靠的，所必须具备的四个特性：

原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation，又称独立性）
持久性（Durability）

即在数据库系统中，为了保证一系列数据操作的正确性，可能需要事务的支持，而为了做到功能这么强大的事务，那么这个事务功能就需要支持ACID四个特性。

在这里插入图片描述

1.2 ACID的四个特性

原子性 - Atomicity

undo log实现事务的原子性。

一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

在这里插入图片描述

一致性 - Consistency

指的是事务开始之前和事务结束之后，数据库的完整性限制未被破坏。一致性包括两方面的内容，分别是约束一致性和数据一致性。

约束一致性： 创建表结构时所指定的外键、Check、唯一索引等约束，可惜在 MySQL 中不支持 Check 。
数据一致性： 是一个综合性的规定，因为它是由原子性、持久性、隔离性共同保证的结果，而不是单单依赖于某一种技术。

隔离性 - Isolation

指的是一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对其他的并发事务是隔离的。

InnoDB 支持的隔离性有 4 种，隔离性从低到高分别为：读未提交、读提交、可重复读、可串行化。锁和多版本控制（MVCC）技术就是用于保障隔离性的（后面课程详解）。

在这里插入图片描述

持久性 - Durability

redo log 主要保证事务的持久性。

指的是一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，后续的操作或故障不应该对其有任何影响，不会丢失。

如下图所示，一个“提交”动作触发的操作有：binlog落地、发送binlog、存储引擎提交、flush_logs，check_point、事务提交标记等。这些都是数据库保证其数据完整性、持久性的手段。

在这里插入图片描述
MySQL 的持久性也与 WAL 技术相关，redo log 在系统 Crash 重启之类的情况时，可以修复数据，从而保障事务的持久性。通过原子性可以保证逻辑上的持久性，通过存储引擎的数据刷盘可以保证物理上的持久性。

1.3 ACID 中 C 与 CAP 定理中 C 的区别?

ACID 理论和 CAP 理论都有一个 C，也都叫一致性 Consistent，所以很多人都会把这两个概念当做是一个概念，把自己弄的迷迷糊糊，团团转。
首先我要声明，这两个C肯定是有区别的：

ACID 的 C

ACID 的 C 指的是事务中的一致性，在一串对数据进行修改的操作中，保证数据的正确性。即数据在事务期间的多个操作中，数据不会凭空的消失或增加，数据的每一个增删改操作都是有因果关系的；比如用户A想用户B转了200块钱，不会出现用户A扣了款，而用户B没有收到的情况

CAP 的 C

CAP 的 C 则指的是分布式环境中，多服务之间的复制是异步，需要一定耗时的，不是即时瞬间完成。所以可能会造成某个节点的数据修改，将修改的数据同步到其他服务需要一定的时间，如果此时有并发请求过来，请求负载均衡到多个节点，可能会出现多个节点获取的数据不一致的问题，因为请求有可能会落在还没完成数据同步的节点上；而C就是为了做到在分布式集群环境读到的数据是一致的；当然这里的C也有分类，如强一致性，弱一致性，最终一致性

所以： ACID 的 C 着重强调单数据库事务操作时，要保证数据的完整和正确性; 而 CAP 理论中的 C 指的是对一个数据多个备份的读写一致性。

2. 事务的并发问题

前面讲到了事务的隔离性，如果要提升系统的吞吐量，当有多个任务需要处理时，应当让多个事务同时执行，这就是事务的并发。既然事务存在并发执行，那必然产生同一个数据操作时的冲突问题，来看一下都会出现什么问题。

更新丢失

Lost Update，当两个事务更新同一行数据时，双方都不知道对方的存在，就有可能覆盖对方的修改。比如两个人同时编辑一个文档，最后一个改完的人总会覆盖掉前面那个人的改动。

脏读

事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

不可重复读

事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

幻读

系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

3. MySQL 事务的隔离级别

在这里插入图片描述

以下是SQL-92定义的四种事务隔离级别标准：

Read Uncommitted 读未提交

限制最弱的事务级别，忽略其他事务放置的锁，该级别下的事务可以在读取其他事务修改后（插/删/更）但未提交的的数据；只能保证多个操作的原子性，完全不能解决并发问题；无法解决脏读，不可重复读，幻读。

Read Committed 读已提交

Oracle、SQL Server 的默认级别，指定事务执行期间（未提交）不能读取其他事务还未提交的数据，解决了脏读。但可以读取到其他事务（插/删/更）操作后并提交了的数据，从而造成多次查询的数据不一致，即不可重复读。同时也不法避免幻读，因为当前事务执行期间，其他事务可以插入新记录。

Repeatable Read 可重复读

MySQL 的默认级别，解决了不可重复读，它确保同一事务（未提交）的多个实例在并发读取数据时，会看到同样的数据行。不过理论上会出现幻读，简单的说幻读指的的当用户读取某一范围的数据行时，另一个事务又在该范围插入/删除了新行，当用户在读取该范围的数据时会发现有新的幻影行/丢失数据。

Serializable 事务同步，串行化

所有的增删改查串行执行。它通过强制事务排序，解决相互冲突，从而解决幻读的问题。这个级别可能导致大量的超时现象的和锁竞争，效率低下。

数据库的事务隔离级别越高，并发问题就越小，但是并发处理能力越差（代价）。读未提交隔离级别最低，并发问题多，但是并发处理能力好。以后使用时，可以根据系统特点来选择一个合适的隔离级别，比如对不可重复读和幻读并不敏感，更多关心数据库并发处理能力，此时可以使用 Read Commited 隔离级别。

事务隔离级别，针对 InnoDB 引擎，支持事务的功能。像 MyISAM 引擎没有关系。

事务隔离级别和锁的关系

事务隔离级别是 SQL92 定制的标准，相当于事务并发控制的整体解决方案，本质上是对锁和MVCC使用的封装，隐藏了底层细节。
锁是数据库实现并发控制的基础，事务隔离性是采用锁来实现，对相应操作加不同的锁，就可以防止其他事务同时对数据进行读写操作。
对用户来讲，首先选择使用隔离级别，当选用的隔离级别不能解决并发问题或需求时，才有必要在开发中手动的设置锁。

MySQL 默认隔离级别：可重复读。

Oracle、SQLServer 默认隔离级别：读已提交。

一般使用时，建议采用默认隔离级别，然后存在的一些并发问题，可以通过悲观锁、乐观锁等实现处理。

4. MySQL SQL命令模拟测试

select @@tx_isolation
查询当前会话的事务隔离级别
select @@global.tx_isolation
查看当前数据库的全局事务隔离级别
set session transaction isolation level repeatable read
设置当前会话隔离级别
set global transaction isolation level repeatable read
查看数据库全局隔离级别
set autocommit = 0\ set autocommit = 1
会话级别，0取消自动提交，则当执行语句commit或rollback时才算结束,1开启自动提交；数据库默认是自动提交
show variables like '%autocommit%'
查看是否开启自动提交
begin / start transaction
开启事务
rollback/rollback work
回滚事务
select * from information_schema.INNODB_TRX
查看当前正在执行的事务

5. 相关问题

5.1 InnoDB 的 RR 级别下其实有部分场景还是会出现不可重复读问题的

首先我们都知道标准的SQL定义中，RR级别(可重复读)是能解决不可重复读问题的，即可以避免不可重复读问题；那什么是不可重复读？我们来回忆一下

事务A先查询了金额，是200块钱，未提交。事务B在事务A查询完之后，修改了金额，变成了300,在事务A提交了；如果此时事务A再查询一次数据，就会发现钱跟上一次查询不一致，这就是不可重复读

但事实上MySQL InnoDB引擎下的RR级别的表现是什么呢？我们可以在MySQL InnoDB的RR级别下用两种场景进行测试当前读和快照读

表1:

事务A	事务B
开启事务	开启事务
快照读(无影响)查询金额为500	快照读查询金额为500
更新金额为400
提交事务
	select `快照读`金额为500
	select lock in share mode`当前读`金额为400

在上表的顺序下，事务B的在事务A提交修改后的快照读是旧版本数据，而当前读是实时新版本实时数据400

表2:

事务A	事务B
开启事务	开启事务
快照读（无影响）查询金额为500
更新金额为400
提交事务
	select `快照读`金额为400
	select lock in share mode`当前读`金额为400

这两种场景的唯一区别就是表2中事务B在事务A提交事务前执行过一次快照读，如果我们了解快照读的话，我们就会知道同一事务下，后续快照读的结果会受到前面快照读结果的影响

所以我们可以整理出一下观点：

1.RR级别下的当前读，是会出现不可重复读问题的
2.RR级别下，没有提前查询的快照读也是会出现不可重复读问题的（不过这种情况未必属于不可重复读的范畴，因为不可重复读强调的是事务B要提前查询一次，后面再查询一次，发现不一致；而没有提前查询的快照读，只有一次查询，本质上没有前后数据的对比，所以从概念理解上，这种场景不应该划入不可重复读问题中）
3.RR级别下，只有提前查询过的快照读才是能避免不可重复读问题的

所以总结：

只有情况1和情况3才符合不可重复读问题定义的验证场景，情况2还无法构成对不可重复读的验证
所以我们可以简单的理解成RR级别下的当前读会出现不可重复读问题，而快照读不会出现不可重复读问题；即MySQL的RR级别解决了快照读（普通select）的不可重复读问题，其他情况属于特殊情况

RR级别以下的RC和RUC级别的本身就会出现不可重复读现象，而S级的串行则连快照读都没有，所以就不再讨论范凑内

5.2 InnoDB 的 RR 级别下是如何避免幻读的？

对此我们可以分为表象和内在，表象就是看起来像是什么东西实现的，内在就是实际上是什么实现的，毕竟学习的过程就是透过表象看本质的过程嘛

表象： 快照读（非阻塞读） | 伪MVCC
内在： next-key锁（行锁，Gap锁）

学习之前，我们先来了解一下，什么是MySQL InnoDB下的当前读和快照读?

当前读

像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

快照读

像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

MySQL 到底是怎么解决幻读的？- @知乎