【MySQL系列】事务

MySQL事务

一、事务的ACID特性

1、ACID

ACID 是数据库管理中常用的一组特性，用于确保事务在执行过程中的可靠性和一致性。

• 原子性（Atomicity）

  事务中的所有操作，要么全部成功提交，要么全部失败回滚，不存在部分执行的情况。

• 一致性（Consistency）

  事务执行前后，数据库中 整个数据的完整性 应该是一致的。

• 隔离性（Isolation）

  并行事务之间相互独立。（一个事务所做的修改在最终提交以前，对其它事务是不可见的）

• 持久性（Durability）

  事务一旦成功提交，其所做的修改将永久保存在数据库中，即使发生故障，数据也不会丢失。

2、一致性的理解

只有满足一致性（C），事务的执行结果才是正确的。

• 无并发：事务串行执行，隔离性（I）一定能够满足。此时只要能满足原子性（A），就一定能满足一致性（C）。
• 有并发：事务并行执行，事务不仅要满足原子性（A），还需要满足隔离性（I），才能满足一致性（C）。
• 事务的持久性（D）是为了能应对数据库崩溃的情况。

总结：事务的ACID就是通过AID来保证这个C的过程。C是目的，AID是手段。（AID依赖于数据库，C依赖于开发者）

3、一致性的举例

举例1：A账户有200元，转账300元出去，此时A账户余额为-100元

• 此时数据是不一致的。因为从数据的合法性来看，余额必须>=0。

举例2：A账户有200元，转账50元给B账户，A账户的钱扣了，但是B账户因为各种意外，余额没有增加。

• 此时数据是不一致的。因为从数据的完整性来看，A账户 和 B账户 的总余额应该不变。

二、事务的并发问题

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题。

1、脏读（必须解决）

在一个事务中读取到另一个事务修改的 未提交的数据

T₁ 修改一个数据，T₂ 随后读取这个数据。如果 T₁ 回滚了这次修改，那么 T₂ 读取的数据就是脏数据。

2、不可重复读

在一个事务中，多次读取同一数据，获取到了不同的值（其他事务 update 导致的 数据上的变化）

T₂ 读取一个数据，T₁ 对该数据做了修改。如果 T₂ 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

3、幻读

在一个事务中 读取到了 之前不存在的值（其他事务 insert 导致的 数量上的变化）

T₁ 读取某个范围的数据，T₂ 在这个范围内插入新的数据，T₁ 再次读取这个范围的数据，和第一次读取结果不同。

4、修改丢失/覆盖

T₁ 和 T₂ 两个事务都对一个数据进行修改，T₁ 先修改，T₂ 随后修改，T₂ 的修改覆盖了 T₁ 的修改。

三、事务的隔离级别

1、四种隔离级别

事务的并发问题，可以通过加锁来控制，也可以通过设置事务的隔离级别解决。

级别	名称	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	Read Uncommitted	✓	✓	✓	-
2	读已提交	Read Committed	×	✓	✓	Oracle、SQL Server
3	可重复读	Repeatable Read	×	×	✓	MySQL
4	串行化	Serializable	×	×	×	-

级别1最低，级别4最高。隔离级别越高，安全性越高，性能越低。

2、隔离级别的理解

1. 读未提交： Read uncommitted
   • 概念：一个事务还没提交时，它做的变更就能被别的事务看到。
   • 实现：直接返回记录上的最新值，没有视图的概念。
2. 读已提交：Read committed
   • 概念：一个事务提交之后，它做的变更才会被其他事务看到。
   • 实现：在每个SQL语句开始执行的时候，创建一个视图。（注意：这个视图/快照是基于整库的）
3. 可重复读：Repeatable read
   • 概念：一个事务执行过程中，前后看到的数据是一致的。
   • 实现：在事务启动的时候，创建一个视图，整个事务存在期间都用这个视图。
4. 串行化：Serializable
   • 概念：对于同一行记录，“写”会加“写锁”，“读”会加“读锁”，事务无法并发执行。
   • 实现：直接用加锁的方式来避免并行访问。

MySQL在可重复读级别下，通过 MVCC + Next-Key Lock 还解决了幻读的问题。

• MVCC解决了快照读下的幻读问题
• Next-Key Lock 解决了 当前读下的幻读问题

四、事务的执行原理

数据库事务的执行原理涉及到数据库管理系统（DBMS）的内部机制，其基本流程如下：

1. 事务开始

   当应用程序发起一个事务时，数据库管理系统会为该事务分配一个唯一的事务标识，并将事务状态设置为进行中。

2. 读取数据

   读取数据时，DBMS会确保事务能够看到一致性的数据视图，确保事务期间读取到的数据应该是一致的。

3. 修改数据（例如插入、更新或删除记录）

   修改数据时，DBMS会将修改的数据记录到事务日志中，而不是直接修改数据库的数据文件。（确保能回滚到之前的状态）

4. 事务提交

   执行成功提交事务，将事务日志中的修改记录应用到数据库中的数据文件中，并释放事务所持有的锁资源。

5. 事务回滚

   执行失败回滚事务，撤销事务执行过程中的所有修改操作，并将数据库恢复到事务开始时的状态。

总的来说，数据库事务的执行原理涉及到数据库管理系统的多个内部机制，包括数据读取、修改、锁管理、事务日志等，这些机制共同保证了数据库事务的一致性、隔离性、持久性和原子性。

五、事务的命令操作

1、事务的开启和提交

自动开启，自动提交（默认）

# sql语句开始，自动开启事务
update account set balance = balance + 500 where name = 'Roce';
# sql语句结束，自动提交事务	

自动开启，手动提交

# 关闭事务的自动提交 (1开启，0关闭; @会话级别，对当前连接生效，@@全局级别，对所有连接生效)
set @@autocommit = 0;	

# sql语句开始，自动开启事务
update account set balance = balance + 500 where name = 'Roce';
commit;		# 手动提交事务
rollback;	# 手动回滚事务

手动开启，手动提交

start transaction; 	# 手动开启事务（手动开启会取消自动提交，临时性的，事务结束就会恢复）
update account set balance = balance + 500 where name = 'Roce';
commit;				# 手动提交事务
rollback;			# 手动回滚事务

手动开启，手动提交并自动启动下一个事务

set autocommit = 1;		# 开启事务的自动提交
start transaction; 		# 手动开启事务（手动开启会取消自动提交，临时性的，事务结束就会恢复）
update account set balance = balance + 500 where name = 'Roce';
commit work and chain;	# 手动提交并自动启动下一个事务

立即启动一个事务

# 立即启动一个事务
start transaction with consistent snapshot;

注意：begin/start transaction 命令并不是一个事务的起点

• 在执行到它们之后的第一个操作InnoDB表的语句（第一个快照读语句），事务才真正启动。

2、事务的回滚点

有时我们并不需要回滚到最开始的状态，可能只需要回滚到中间的某个位置，这时就可以设置回滚点。

# 设置回滚点
savepoint 回滚点名称

# 回到回滚点
rollback to 回滚点名称

3、事务的隔离级别

# 查询事务隔离级别
show variables like 'transaction_isolation';

# 设置事务隔离级别（全局级别，重启后生效，当前连接不生效）
set global transaction isolation level 四种级别名字;

# 设置事务隔离级别（会话级别，只对当前连接生效，重启后失效）
set session transaction isolation level 四种级别名字;

# 事务的隔离级别名称
READ UNCOMMITTED	读未提交
READ COMMITTED		读已提交
REPEATABLE READ 	可重复读
SERIALIZABLE	    串行化

4、查询长事务

可以在 information_schema 库的 innodb_trx 表中查询长事务，比如下面这个语句

# 查找持续时间超过60s的事务
select * from information_schema.innodb_trx 
where TIME_TO_SEC(timediff(now(), trx_started)) > 60