【MySQL | 八、 事务管理】

什么是事务？

在 MySQL 中只有使用了 ==InnoDB 数据库引擎的数据库或表才支持事务， MyISAM 不支持。==事务（Transaction） 是一组（可以为一句或多句）SQL 语句的集合，这些语句作为一个整体执行，要么全部成功，要么全部失败。

事务的特性：

事务通常用 ACID 特性来描述：

• 原子性（Atomicity）
  事务中的所有操作要么全部成功，要么全部失败。如果事务中的任何一条语句失败，整个事务都会被回滚（Rollback），恢复到事务开始前的状态。
• 一致性（Consistency）
  事务执行后，数据库必须从一个一致的状态转换到另一个一致的状态。一致性可以理解为“目标达成过程是合理且符合规则的”。例如，转账操作中，总金额在转账前后应该保持一致。
• 隔离性（Isolation）
  多个并发事务之间相互隔离，一个事务的操作不会影响其他事务。MySQL 提供了不同的隔离级别（如 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE）来控制事务的隔离程度。
• 持久性（Durability）
  一旦事务提交（Commit），它对数据库的修改就是永久性的，即使系统发生故障也不会丢失。

事务的意义

事务的主要目的是确保数据库的完整性和一致性，特别是在并发操作和系统故障的情况下。

1. 确保数据完整性
   事务将一组操作（SQL 语句）打包成一个逻辑单元，要么全部成功，要么全部失败。
   例子：银行转账操作中，从账户 A 扣款和向账户 B 加款必须同时成功或同时失败。如果只有扣款成功而加款失败，数据就会不一致。
   意义：事务避免了部分操作成功、部分操作失败导致的数据不一致问题。
2. 支持并发操作
   在多用户或多应用同时访问数据库时，事务通过隔离性确保并发操作不会互相干扰。
   例子：用户 A 和用户 B 同时修改同一条数据，事务可以确保他们的操作按顺序执行，而不是互相覆盖。
   意义：事务保证了并发环境下的数据正确性。
3. 提供故障恢复机制
   在系统发生故障（如断电、崩溃）时，事务的持久性确保已提交的操作不会丢失，而未提交的操作会被回滚。
   例子：如果数据库在转账过程中崩溃，事务可以确保恢复到转账前的状态，避免数据损坏。
   意义：事务提高了系统的可靠性和容错能力。
4. 简化复杂操作
   事务将复杂的操作封装成一个逻辑单元，开发者不需要手动处理每一步操作的成功或失败。
   例子：在电商系统中，下单操作可能涉及库存减少、订单记录生成、支付状态更新等多个步骤，事务可以确保这些步骤要么全部成功，要么全部回滚。
   意义：事务简化了开发流程，降低了出错概率。
5. 支持一致性约束
   事务可以确保数据库从一个一致状态转换到另一个一致状态，满足业务规则和约束条件。
   例子：在库存管理系统中，库存数量不能为负数，事务可以确保所有操作都符合这一规则。
   意义：事务维护了数据的逻辑正确性。

事务的提交

没错，事务是需要提交的。

查看事务提交方式

show variables like 'autocommit';

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.41 sec)

autocommit = 1(ON):表示自动提交
autocommit = 0(OFF):表示禁止自动提交

事务的自动提交

在 MySQL 中，默认情况下，每条 SQL 语句都会自动提交autocommit = 1(ON)。

事务的手动提交

用 SET 来改变 MySQL 的自动提交模式:

mysql> SET AUTOCOMMIT=0; 
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)

autocommit = 0(OFF)时需要手动提交：

开始事务

使用 START TRANSACTION 或 BEGIN 语句开始一个新的事务。

执行SQL操作

在事务中执行一系列 SQL 操作（如 INSERT、 UPDATE、DELETE）。

事务操作

创建临时保存点: savepoint save2;（save2为保存点的名）
回滚操作:rollback to save2;（回滚到save2保存点）
rollback:回滚到最开始
如果一个事务被提交了（commit），则不可以回退（rollback）

提交事务

使用COMMIT进行事务提交。

示例：

#开始事务
START TRANSACTION;#操作1：从账户1扣款100元
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
#操作2：向账户2加款100元
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;#提交事务
COMMIT;

事务的隔离级别

并发访问的基本概念

在数据库领域，并发特指数据库系统同时处理多个事务的能力，允许不同用户/应用程序同时访问和修改数据。

MySQL服务会被多个客户端进程/线程并发访问，这些访问以事务方式执行。

并发事务的典型问题

如果没有适当的事务隔离机制（Isolation），并发事务之间会产生三种典型问题：

1. 脏读（Dirty Read）：事务A读取到事务B未提交的修改
2. 不可重复读（Non-repeatable Read）：事务A内两次读取同一数据，结果不同（因事务B在此期间修改了数据）
3. 幻读（Phantom Read）：事务A两次查询同一条件，得到不同记录集（因事务B在此期间新增/删除了数据）

对ACID特性的影响

这些问题会破坏事务的ACID特性中的：

• 一致性：事务执行后数据库应保持合法状态
• 隔离性：事务间不应相互干扰

事务隔离性指的是在并发环境下，一个事务的执行不应受到其他并发事务的干扰，每个事务都应该像是在独立执行一样。确保业务逻辑的正确执行，避免因并发导致的数据逻辑错误。
上述问题MySQL通过四种隔离级别解决这些问题：

隔离级别	脏读	不可重复读	幻读	备注
读未提交 EAD UNCOMMITTED	✓	✓	✓	最低隔离级别
读提交 READ COMMITTED	×	✓	✓	Oracle默认级别
可重复读 REPEATABLE READ（默认）	×	×	✓	MySQL默认级别
串行化 SERIALIZABLE	×	×	×	最高隔离级别

*注：MySQL的InnoDB引擎通过间隙锁（Gap Lock）在REPEATABLE READ级别也能避免大部分幻读

隔离级别如何实现：隔离，基本都是通过锁实现的，不同的隔离级别，锁的使用是不同的。常见有，表
锁，行锁，读锁，写锁，间隙锁(GAP),Next-Key锁(GAP+行锁)等，

查看和设置隔离属性

隔离属性作用域是事务或会话
@@global.tx_isolation：全局事务隔离级别
@@session.tx_isolation：当前会话隔离级别
@@tx_isolation:等同于@@session.tx_isolation

#查看全局事务隔离级别和（当前）会话事务隔离级别
SELECT@@global.tx_isolation,@@session.tx_isolation;#设置隔离事务级别
set [session | global] 
transaction isolation level 
[read uncommitted | read commit | repeatable read | serializable]; 
#设置当前会话的隔离事务级别为 serializable
set session 
transaction isolation level 
serializable;

• MySQL默认@@global.tx_isolation值为：REPEATABLE READ（可重复读）。
• 新会话的@@session.tx_isolation会继承@@global.tx_isolation的当前值。
• 全局事务隔离级别用于初始化会话隔离事务级别的值。
• 更新@@global.tx_isolation后只对于后续新创立的会话的@@session.tx_isolation生效。已有会话的隔离级别不变。
• 更新@@session.tx_isolation后只对于当前会话后续新建的事务生效。已开启的事务不生效。

各个隔离级别的详细介绍

• 每个事务的读取行为只受自身隔离级别控制
• 写操作会受其他事务的隔离级别间接影响（通过锁机制）
• 不同级别事务间的交互需要通过锁机制协调
  假设：有两个会话A、B。
  场景：银行转账业务C、D两个账户（初始余额：C=1000，D=1000）
  假设：会话B新建事务(进行向D账户转100，转三次，并新增一个E账户存1000)

读未提交EAD UNCOMMITTED

当A在新建事务前为此隔离等级此时不论B会话处于什么隔离级别,A会话下事务可以随时读取B下事务未提交的结果 A会话下的事务每次访问D账户的余额可能不同，同时随时可能新增一个账户 显示这是脏读、不可重复读和幻读

---

此隔离级别下的事务几乎没有隔离性。

读提交READ COMMITTED

当A会话在新建事务前为此隔离等级那么A会话下的事务在B会话下的事务未提交前是看不到D账户有任何变化的，但若B下的事务早于A下的事务进行了提交，那么A下的事务每次查询（提交前和提交后）的结果仍可能不同显然这是不可重复读和幻读。

可重复读REPEATABLE READ（默认）

在MYSQL中当A会话在新建事务前为此隔离等级 A会话下的事务看不到其他事务在A启动后提交的任何新数据

值得一提的是： 一般的数据库在可重复读情况的时候，无法屏蔽其他事务insert的数据(为什么？因为隔离性实现是对数据加锁完成的，而insert待插入的数据因为并不存在，那么一般加锁无法屏蔽这类问题),会造成虽然大部分内容是可重复读的，但是insert的数据在可重复读情况被读取出来，导致多次查找时，会多查找出来新的记录，就如同产生了幻觉。这种现象，叫做幻读。很明显，MySQL在RR级别的时候，是解决了幻读问题的(解决的方式是用Next-Key锁(GAP+行锁)解决的。

串行化SERIALIZABLE

对所有操作全部加锁，进行串行化，不会有问题。

隔离级别越严格，安全性越高，但数据库的并发性能也就越低，往往需要在两者之间找一个平衡点。