MySQL进阶之（十）事务和隔离级别

🕗 发布于 2024-07-23 13:48 mysql 数据库 事务四种隔离级别事务四大特性

十、事务和隔离级别

10.1 事务
10.2 事务的隔离级别

10.1 事务

10.1.1 事务介绍

MySQL 事务主要用于处理操作量大、复杂度高的数据。比如：张三给李四转钱这个操作，必须同时完成两步：扣除张三账户中的钱、新增李四账户中的钱。这两步数据库操作就构成了一个事务。

事务是一组逻辑操作单元，使数据从一种状态变换到另一种状态。事务是数据库区别于文件系统的重要特性之一，有了事务就会让数据库始终保持一致性，同时还能通过事务机制恢复到某个时间点，这样可以保证已提交到数据库的修改不会因为系统崩溃而丢失。

在 MySQL 中只有使用了 InnoDB 存储引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert、update、delete语句。

事务处理的原则：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚到最初状态。

10.1.2 事务四大特性

一般来说，事务必须满足 4 个条件（ACID）：原子性（Atomicity，或称为不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚，不存在中间的状态。如果事务执行过程中发生错误，那么就会被回滚到事务开始前的状态，就像这个事务从来没有被执行过一样。
一致性：指事务执行前后，数据从一个合法性状态变换到另一个合法性状态，数据库的完整性没有被破坏。其中，满足预定的约束的状态就叫做合法的状态，这个状态是由自己定义的。
隔离性：指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的。隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同的隔离级别，包括读未提交（Read uncommitted）、读提交（Read committed）、可重复读（Repeatable read）和串行化（Serializable）。
持久性：指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，即便是系统故障也不会丢失。持久性是通过事务日志来保证的，包括重做日志和回滚日志。

总结：ACID 是事务的四大特性，在这四个特性中，原子性是基础，隔离性是手段，一致性是约束条件，而持久性是目的。

注意：在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT = 0，用来禁止使用当前会话的自动提交。

10.1.3 事务的状态

事务其实是一个抽象的概念，它其实对应着一个或多个数据库操作，MySQL 根据这些操作所执行的不同阶段把事务大致分成几个状态：

活动的（active）：事务对应的数据库操作正在执行过程中时，就说该事务处在活动的状态。
部分提交的（partially committed）：当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，就说该事务处在部分提交的状态。
失败的（failed）：事务处在活动的或部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或者直接断电等）而无法继续执行，或者人为的停止当前事务的执行，就说该事务处在失败的状态。
中止的（aborted）：如果事务执行了一部分而变为失败的状态，那么就需要把已经修改的事务中的操作还原到事务执行前的状态。也就是要撤销失败事务对当前数据库造成的影响，这个撤销的过程也称为回滚。当回滚操作执行完毕时，也就是数据库恢复到了执行事务之前的状态，就说该事务处在了中止的状态。
提交的（committed）：当一个处在部分提交状态的事务将修改过的数据都同步到磁盘上后，就说该事务处在了提交的状态。

在这里插入图片描述

10.1.4 如何使用事务

01、事务控制语句

BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

02、事务操作

MySQL 事务分为隐式事务和显式事务，默认是隐式事务。执行 insert、update、delete 操作的时候，数据库自动开启事务、提交事务或回滚事务。其中，是否开启隐式事务是由变量 autocommit 控制的，该值默认情况下为 true。

⭐ 显式事务

事务需要手动开启、提交或回滚，由开发者自己控制。其中，一个事务的完成过程分为三个步骤：

步骤1：开启事务

使用 START TRANSACTION 或 BEGIN 显式开启一个事务：
```
BEGIN;
START TRANSACTION;
```
其中， START TRANSACTION 语句与 BEGIN 相比，后面能跟几个修饰符。
① READ OLAY：标识当前事务是一个只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。
② READ WAITE：标识当前事务是一个读写事务，属于该事务的数据库操作既可以读取数据，也可以修改数据。
③ WAIT CONSISTENT SNAPSHOT：启动一致性读。

注意：READ OLAY 和 READ WAITE 是用来设置所谓的事务访问模式的，就是以只读还是读写的方式来访问数据库中的数据。一个事务的访问模式不能同时设置为只读的也设置为读写的。
另外，只读事务中只是不允许修改那些其他事务也能访问到的表中的数据，对于临时表来说（使用 create tmeporary table 创建的表），由于它们只能在当前会话中可见，所以只读事务其实也是可以对临时表进行增、删、改操作的。
步骤2：一系列 DML 操作（不含 DDL）

步骤3：事务结束的状态（提交或中止）

# 提交事务，当事务提交后，对数据库的修改是永久性的
commit;
# 回滚事务。即撤销正在进行的所有没有提交的修改
rollback;
# 将事务回滚到某个保存点
rollback to [savepoint]

其中，关于 savepoint 相关的操作有：

# 在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点
savepoint 保存点名称;
# 删除某个保存点
release savepoint 保存点名称;

如果中间操作错误，就使用 savepoint 来设定一个保存点，将事务回滚到某个保存点。

⭐ 隐式事务

MySQL 中有一个系统变量 autocommit：

SHOW VARIABLES LIKE '%autocommit%';

在这里插入图片描述
默认情况下，如果不显式的使用 START TRANSACTION 或 BEGIN 语句开启一个事务，那么每一条语句都算是一个独立的事务，这种特性称为事务的自动提交。也就是说，不以 START TRANSACTION 或 BEGIN 语句显式的开启一个事务，每条语句都相当于是在一个独立的事务中执行。

如果想要关闭这种自动提交的功能，可以使用下面两种方法：

显式的使用 START TRANSACTION 或 BEGIN 语句开启一个事务。这样在本次事务提交或回滚前会暂时关闭掉自动提交的功能。
将系统变量 autocommit 的值设置为 OFF
```
set autocommit = OFF;
# 或
set autocommit = 0;
```
这样写入的多条语句就算是属于同一个事务了，直到显式的写出 commit 语句来把这个事务提交掉，或者显式的写出 rollback 语句来把这个事务回滚掉。

总结：默认情况下 autocommit 为 true，在没有 START TRANSACTION 或 BEGIN 时，DML 语句会自动提交。当 autocommit 为 false 时，DML 不会自动提交。但是，DDL 语句不受 autocommit 变量的影响，都会自动提交。

10.2 事务的隔离级别

MySQL 是一个客户端/浏览器架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称为一个会话（Session）。每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是某个事务的一部分，也就是对于服务器来说可能同时处理多个事务。事务有隔离性的特性，理论上在某个事务对某个数据进行访问时，其他事务应该进行排队，当该事务提交之后，其他事务才可以继续访问这个数据。但是这样对性能的影响太大了，我们既想保持事务的隔离性，又想让服务器在处理访问同一数据的多个事务时性能尽量高些，那就要看二者如何权衡取舍了。

10.2.1 事务数据可见性和并发问题

下面是访问相同数据的事务在不保证串行执行的情况下可能会出现的问题：

01、脏写（Dirty Write）/更新丢失

对于两个事务 A、B，如果事务 A 修改了事务 B 未提交的已修改的数据，那就意味着发生了脏写。如下图所示，如果事务 B 回滚了，就会造成事务 A 已提交的数据丢失。
在这里插入图片描述

Session A 和 Session B 各开启了一个事务，Session B 中的事务先将 studentno 列为 1 的记录的 name 更新为 ‘李四’，然后 Session A 中的事务又接着把这条 studentno 列为 1 的记录的 name 更新为 ‘张三’。如果 Session B 中的事务在最后进行了回滚，那么 Session A 中的更新也将不存在，这个现象就称为脏写。

02、脏读（Dirty Read）/读未提交

对于两个事务 A、B，如果事务 A 读取了已经被事务 B 更新了但还没有被提交的数据，之后事务 B 回滚了，事务 A 读取的内容就是临时且无效的。
在这里插入图片描述

在 Session A 和 Session B 中各开启了一个事务，Session B 中的事务先将 studentno 列为 1 的记录的 name 列更新为 ‘张三’，然后 Session A 中的事务再去查询这条 studentno 为 1 的记录，如果读到列 name 的值为 ‘张三’，而Session B 中的事务进行了回滚，那么 Session A 中相当于读到了一个不存在的数据，这种现象就叫做脏读。

03、不可重复读（Non-Repeatable Read）/读已提交

对于两个事务 A、B，事务 A 读取了一个字段，然后事务 B 更新了该字段。之后事务 A 再次读取同一个字段时，与第一次读取到的值不一样了，这就意味着发生了不可重复读。
在这里插入图片描述
上图中 Session B 中提交了几个隐式事务，这些事务都修改了 studentno 列为 1 的记录的列 name 的值，每次事务提交后，如果 Session A 中的事务都可以看到最新值，这种现象就被称为不可重复读。

04、幻读（Phantom）/可重复读

对于两个事务 A、B，事务 A 从一个表中读取了一个字段，然后事务 B 在该表中插入了一些新的行。之后，如果事务 A 再次读取同一个表，就会多出几行数据，这就意味着发生了幻读。

在这里插入图片描述
Session A 中的事务先根据条件 studentno > 0 这个条件查询表 student，得到了 name 列值为 ‘张三’ 的记录。之后 Session B 中提交了一个隐式事务，向表 student 中插入了一条新记录。当 Session A 中的事务再次根据相同的条件 studentno > 0 查询表 student 时，得到的结果集中包含 Session B 中的事务新插入的那条记录，这种现象被称为幻读。

注意：

幻读强调的是一个事务按照某个相同条件多次读取记录时，后读取到了之前没有读到的记录。
对于之前已经读取到的记录，之后又读取不到了这种情况，相当于是每一条记录都发生了不可重复读的现象。

10.2.2 四种隔离级别

事务的隔离级别主要是解决了多个事务之间数据可见性及数据正确性的问题（或者说为了解决并发控制可能产生的异常问题），数据库定义了四种事务的隔离级别：

READ UNCOMMITTED：读未提交。在该隔离级别下，所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。
READ COMMITTED：读已提交。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是 MySQL 默认的）。可以避免脏读，但不可重复读、幻读问题仍然存在。
REPEATABLE READ ：可重复读。事务 A 在读到一条数据之后，此时事务 B 对该数据进行了修改并提交，那么事务 A 再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍然存在。这是 MySQL 的默认隔离级别。
SERIALIZABLE：可串行化。确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避免脏读、不可重复读和幻读。

在上面四种隔离级别中，隔离级别越高，数据库的并发性能就越差：
在这里插入图片描述
在 SQL 标准中规定，针对不同的隔离级别，并发事务可以发生不同严重程度的问题：

隔离级别	脏读可能性	不可重复读可能性	幻读可能性
READ-UNCOMMITTED	有	有	有
READ-COMMITTED	无	有	有
REPEATABLE-READ	无	无	有
SERIALIZABLE	无	无	无

无论哪种隔离级别，都不允许脏写的情况发生，因为脏写这个问题太严重了。

注意：
在 MySQ L中，REPEATABLE-READ 也是可以避免幻读的，通过加行锁（独占锁）（select * from … for update）实现。同时即便当前记录不存在，比如 id=3 不存在，当前记录也会获取一把记录锁（因为 InnoDB 的行锁锁定的是索引，与记录实体存不存在没关系，存在就加 X锁，不存在就加间隙锁 ），其他事务则无法插入索引的记录，故杜绝幻读。
在 SERIALIZABLE 隔离级别下，MySQL 会隐式的添加行(X)锁/gap(X)锁，从而其他事务会被阻塞。

10.2.3 设置事务的隔离级别

MySQL支持标准的四种隔离级别，默认隔离级别为 REPEATABLE-READ：

show variables like 'transaction_isolation';
# 或
select @@transaction_isolation;

# MySQL 5.7.20 之前 
show variables like 'tx_isolation';

在这里插入图片描述
设置事务的隔离级别，可通过修改配置文件和 set 命令。

修改配置文件

修改mysql中的my.init文件，将隔离级别设置为：READ-COMMITTED，如下：

transaction-isolation=READ-COMMITTED

通过 set 命令

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
-- 其中level有4种值：
level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

-- 或者
SET [GLOBAL|SESSION] TRANSACTION_ISOLATION LEVEL
-- 其中level有4种值：
level: {
     REPEATABLE-READ
   | READ-COMMITTED
   | READ-UNCOMMITTED
   | SERIALIZABLE
}

关于设置时使用 GLOBAL 或 SESSION 的影响：

使用 GLOBAL 关键字（在全局范围影响）

当前已经存在的会话无效。只针对执行完该语句之后产生的会话起作用。
使用 SESSION 关键字（在会话范围影响）

对当前会话的所有后续的事务有效。如果在事务之间执行，则对后续的事务有效。该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务。

10.2.4 隔离级别的选择

读未提交( Read Uncommitted )

读未提交是隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。
读已提交( Read Committed )

在 Read Committed 隔离级别下，一个事务可能会遇到不可重复读（Non Repeatable Read）的问题。不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。
可重复读( Repeatable Read )

在 Repeatable Read 隔离级别下，一个事务可能会遇到幻读（Phantom Read）的问题。幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。幻读就是没有读到的记录，以为不存在，但其实是可以更新成功的，并且，更新成功后，再次读取，就出现了。
可串行化( Serializable )

Serializable 是最严格的隔离级别。在 Serializable 隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。虽然 Serializable 隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。

隔离级别越高，并发性也低，比如最高级别 SERIALIZABLE 会让事物串行执行，并发操作变成串行了，会导致系统性能直接降低。

读已提交（READ-COMMITTED）、可重复读( Repeatable Read )通常用的比较多。

原文地址：https://blog.csdn.net/qq_50994235/article/details/140587980

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：Android DataBinding从入门到精通
下一篇：spring(二)

MYSQL常用基本操作总结
SQL查询中各个关键字的执行先后顺序： from > on> join > where > group by > with > having >select
阅读更多2024-09-19
JAVA并发编程系列之Semaphore信号量剖析
候选人，心中万马奔腾！！！吐了一口82年老血，当场砸电脑回家！原因是：腾讯T2面试，现场限时3分钟+限最多20行代码，模拟地铁口安检进站。其中安检入口10个，当前排队人数是100个，每个人安检进站耗时
阅读更多2024-09-19
24年蓝桥杯及攻防世界赛题-MISC-2
24年蓝桥杯及攻防世界赛题-MISC-2
阅读更多2024-09-19
干货-并发编程提高——重谈 RUNNABLE-上篇（十四）
直接看它的 Javadoc 中的说明：一个在 JVM 中执行的线程处于这一状态中。（A threadexecuting而传统的进（线）程状态一般划分如下：注：这里的进程指早期的单线程进程，这里所谓进程
阅读更多2024-09-19
phpstudy 建站使用 php8版本打开 phpMyAdmin后台出现网页提示致命错误：（phpMyAdmin这是版本问题导致的）
将网站根目录phpMyAdmin4.8.5里面的文件换成官网下载的5.2.1版本即可。重启网站，打开phpMyAdmin后台即可（若打不开更改 mysql密码即可）解决方法：官网下载phpmyadm
阅读更多2024-09-19
零工市场小程序：保障灵活就业
截止2024年高校毕业生达到1179万，在今年的经济情况下，就业市场就面临着比较大的压力，许多毕业生面临一时之间难以找到合适的工作的问题，那么求职者就会需要一份临时的工作来得到报酬，面对传统的找零工方
阅读更多2024-09-19
Linux中权限和指令
mv指令是move的缩写，用来，经常用来备份文件或目录。
阅读更多2024-09-19
Redis 底层揭秘：事务与 Lua 脚本的工作原理
定义Lua 是一种轻量级的脚本语言，它可以在 Redis 中被执行，用于实现复杂的逻辑操作。优势与事务相比，Lua 脚本具有更高的性能和更好的灵活性。Lua 脚本可以在 Redis 服务器端一次性执行
阅读更多2024-09-19
Vue3使用shapefile读取矢量数据，以数组形式返回坐标点
【代码】Vue3使用shapefile读取矢量数据，以数组形式返回坐标点。
阅读更多2024-09-19
WEB 编程：使用富文本编辑器 Quill 配合 WebBroker 后端
评估了好几个，最后选择这个开源的。把前端代码，存储为一个单独的文本文件，方便随便哪个页面需要的时候可以使用。相当于封装为一个独立的对象，方便代码重用。
阅读更多2024-09-19