MySQL的事务

March 30, 2024

前言

当我们想批量执行一些 DML（insert，update，delete）的 SQL 语句，并且这些语句在概念上能构成一项连续性的功能的时候，我们可以使用数据库的事务来完成。

为什么需要事务，是因为我们希望这一系列相关的语句，它们能够构成为单一的原子，要么全部成功，要么全部失败，失败后，能够退回数据库修改前的状态，成功时也能够保证数据库的约束不被破坏，确保数据库的一致性。

事务的出现，很大程度上是因为我们需要一种抽象，来简化过程式代码的编程模型，不再需要花大量的时间考虑各种各样的潜在的异常和并发问题，当这个抽象的功能由数据库引擎（InnoDB）来实现以后，我们可以依赖于这个抽象的事务概念节省很多的时间和精力。

举个常见的转账例子，Alice（id=1，balance=1000）给 Bob（id=2，balance=0）转账 200 元，是由四条语句构成：

# 1. 查询 Alice 的余额（为了在程序中判断存在 Alice 的账户，以及该账户是否有足够的余额来转账）
select balance into @alice_balance from t_user where id = 1;
# 2. 将 Alice 的余额扣除 200 元
update t_user set balance = @alice_balance - 200 where id = 1;
# 3. 查询 Bob 的余额（为了在程序中判断是否存在 Bob 的账户，以及 balance 字段是否 not null）
select balance into @bob_balance from t_user where id = 2;
# 4. 将 Bob 的余额增加 200 元
update t_user set balance = @bob_balance + 200 where id = 2;

这四条语句就应该组成一个完整的事务。

在四条语句的执行中，若发生异常，没有事务特性的保证，可能会发生，Alice 的账号少了 200 元，但是由于异常（比如网络问题）没有执行到最后一条语句，导致 Bob 的账号并没有增加 200 元，相当于钱凭空消失了，根本原因就在于，四条语句是分开执行的，没有作为一个原子性的事务。

事务的特性

事务的特性分成四部分，简称 ACID：

A（Atomicity），原子性，在一个事务中的所有语句（一条或者多条）要么全部成功，要么全部失败，不存在部分更新的情况。
C（consistency），一致性，事务只能以允许的方式来改变数据，事务执行前和执行后始终保持数据的一致性。
I（isolation），隔离性，在同时发生事务（并发）的情况下，这些事务不应该使数据库处于不一致的状态，系统中的每个事务都应该像是唯一事务一样执行，互相不影响。
D（durability），持久性，一旦事务提交，其所做所有修改将被永久性的持久化到数据库当中，即使系统崩溃，修改的数据也不会丢失。

原子性

原子性，之前提到的转账就是最好的例子，如果成功了，那么两个 update 语句都应该被执行成功（Alice 减少 200 元，Bob 增加 200 元），如果失败了，那么两个 update 语句都应该失败，不能出现部分成功的情况。

一致性

一致性，在网上有各种各样的解释，很多都模糊不清。我的理解是：当一个事务以原子的形式，隔离的执行在一个保证了持久性的数据库上时，并且该数据库此前具有数据的一致性，那么在事务提交后，该数据库也应该保持数据的一致性。

也就是说，事务执行的前后，数据库将从一个正确的状态转变成另一个正确的状态。正确的状态并不仅仅局限于数据库的约束（主键约束，唯一性约束，外键约束等），还应该保持应用层层面上的正确状态，而应用层的正确状态和一致性，是由业务需求和程序员来定义的（比如，余额、库存不能为负数）。

所以一致性，本质上是应用层程序员利用其他三个数据库特性（原子性，隔离性，持久性）来执行一个事务，并且保证事务提交后，无论是在数据库层面约束还是应用层层面的约束都不被破坏，一旦破坏一致性，则可以回滚事务。

隔离性

隔离性，当存在并发的事务（多个事务同时执行）的情况下，事务与事务之间保持隔离，一个事务的执行不能被另外一个事务的同时执行所干扰。

持久性

持久性，指的是在事务完成后，所有的更新修改都会被持久化到数据库中，即使断电，也不会丢失事务所做出的修改。

MySQL 事务相关的语法

使用 START TRANSACTION 或者 BEGIN 来开启一个事务，然后输入你想要放进这个事务中的所有语句，如果确认没有问题，可以使用 COMMIT 提交事务，如果中间存在异常，则可以使用 ROLLBACK 来进行回滚。

在默认的 MySQL 命令行中，是自动提交的，可以通过以下语句查看自动提交的状态：

SHOW VARIABLES LIKE 'autocommit';

默认是 ON，说明你在命令行中的每一条语句的执行，都是以单个事务进行自动提交的。

如果希望关闭自动提交，则使用以下语句：

SET autocommit = 0;

此时，执行 DML 的 SQL，需要手动执行 commit 语句才会提交事务。

提交和回滚

如果事务中所有的语句都执行成功，可以执行 commit 提交事务，事务所做的修改，就被持久化到数据库里，而如果期间有任何语句执行错误，可以执行 rollback 回滚事务。

commit 和 rollback 并不会自动判断 sql 语句是否全部成功还是部分失败，需要自己执行 commit 和 rollback。

事务的并发问题

并发事务下，会造成以下问题：

更新丢失（Lost Update）：两个事务同时更新某行某列的数据，后者的更新会覆盖前者的更新。
脏读（Dirty Read）：当前事务可以读取到另一个未提交的事务的修改内容。
不可重复读（Non-repeatable Read）：当前事务只能读取另一个事务已经提交的内容，例如：当前事务存在两个相同的 select 语句，由于在执行过程中，另一个事务在两个 select 之间提交了，导致当前事务的两个 select 查询出来的内容不一致。
幻读（Phantom Read）：当前事务两次 select 语句查询出来的记录数量不一致，可能变多了也可能变少了。

事务的隔离级别

SQL 定义了四种隔离级别，隔离级别定义了一个事务与其他事务在资源或者数据修改方面的隔离程度。

读取未提交（Read Uncommitted）

这是第一种级别，也是实际生产中不太常用的级别。在该级别下，所有事务都可以看到其他未提交事务的执行内容，也就是会发生上一节提到的脏读问题。

读取提交（Read Committed）

第二种级别，是大多数数据库默认的级别，但不是 MySQL 默认的级别。

它满足一个简单的条件：一个事务只能看到已经提交的其他事务的执行结果。

该隔离界别会导致不可重复读的问题，这意味着我们在一个事务中执行两个一模一样的 select 语句可能会得到不一样的结果。

可重复读（Repeatable Read）

第三种级别，也是 MySQL 的默认级别。

它确保在

串行化（Serializable）

第四种级别，也是最高的级别，也是在生产中不常用的类型。

它强制事务排序，使事务之间不产生冲突，从而解决幻读的问题，在读的行上加锁，缺点也很明显，性能差，可能会导致大量的超时现象和锁竞争。

隔离级别的设置和案例测试

这一节将改变数据库的不同隔离级别，来测试出脏读，不可重复读，幻读等问题，如果没有真实看到这些问题的存在，那么对事务并发所产生的诸多问题将永远停留在理论上面。

在改变隔离界别之前，我们需要知道如何查看当前的隔离级别，可以通过 MySQL 变量来查看隔离的级别：

SHOW VARIABLES LIKE "%transaction_isolation%";

SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;

# 结果: REPEATABLE-READ

结果显示，全局和会话的默认的隔离级别是“可重复读”。

新开两个客户端，模拟事务的并发，设置会话的事务隔离级别的语句如下：

set session transaction isolation level 事务隔离级别;
# 事务隔离界别可选项为 4 种：
# 1. READ UNCOMMITTED
# 2. READ COMMITTED
# 3. REPEATABLE READ
# 4. SERIALIZABLE

两个客户端分别模拟并发中的事务 1 和事务 2。

读未提交

设置客户端-2 为读未提交的隔离级别：

set session transaction isolation level read uncommitted;

在这种隔离级别下，会产生脏读的问题，即，一个事务读取到了另一个事务未提交的内容。假设客户端-1 开启了一个事务，但是并未提交，这时由于客户端-2 设置的是读未提交的隔离级别，那么客户端-2 的事务就会读取到客户端-1 未提交事务的修改内容。

假设现有一条记录：

id    name    balance
1     Alice     600

客户端-1 开启一个事务，并且给 Alice 的账户余额上增加 100 元，并且不提交：

# 客户端-1
start transaction;
update t_user set balance = balance + 100 where id = 1;

此时，在客户端-2 中查询 Alice 的余额：

select * from t_user where id = 1;
# 结果显示为 700，客户端-2 的事务看到了客户端-1 的未提交事务的操作内容

执行步骤	客户端-1	客户端-2	说明
第 1 步		set session transaction isolation level read uncommitted;start transaction;select * from t_user where id = 1;	将客户端-2的事务隔离级别设置为读未提交，并查看 Alice 当前的余额，为 600
第 2 步	start transaction;update t_user set balance = balance + 100 where id = 1;		客户端-1 开启一个事务，并且将 Alice 的余额增加 100，并不提交该事务
第 3 步		select * from t_user where id = 1;	客户端-2 此时查看 Alice 当前的余额，为 700

读已提交

读已提交的隔离级别可以解决脏读的问题，事务仅能读取别的已经提交的事务的结果，但是依然存在不可重复读的的问题。

客户端-2 设置读已提交的隔离界别：

set session transaction isolation level read committed;

客户端-1 开启事务，客户端-2 开启事务，客户端-2 读取 Alice 余额

# 客户端-1 开启事务
start transaction;
# 客户端-2 开启事务
start transaction;

# 客户端-2 读取 Alice 余额-600
select * from t_user where id = 1;

然后客户端-1 为 Alice 余额增加 100，客户端-2 读取 Alice 余额

# 客户端-1 更新 Alice 余额
update t_user set balance = balance + 100 where id = 1;

# 客户端-2 查看 Alice 余额-600
select * from t_user where id = 1;

客户端-2 此时看到 Alice 的余额依然是 600，说明脏读问题已经在该隔离级别解决，因为客户端-1 没有 commit，所以客户端-2 不会读取到客户端-1 的修改内容。

但此时如果客户端-1 进行 commit，客户端-2 就会产生不可重复读的问题，即，客户端-2 的一个事务内，读取到了不同的结果

# 客户端-1 commit
commit;
# 客户端-2 查看 Alice 余额-700
select * from t_user where id = 1;

可重复读

可重复读，可以解决不可重复读的问题，但是还存在幻读的问题。

客户端-2 设置可重复读的隔离界别：

set session transaction isolation level repeatable read;

客户端-1，客户端-2 分别开启一个事务，客户端-1 插入一个新的用户并提交：

# 客户端-1 开启事务
start transaction
# 客户端-2 开启事务
start transaction

# 客户端-1 插入一个 id 为 3 的新用户
insert into t_user values (3, 'Tom', 0);
# 客户端-1 提交事务
commit;

此时客户端-2 查询所有的用户：

# 客户端-2 查询所有的用户
select * from t_user;
# 结果
1 Alice 600
2 Bob   0

客户端-2 的事务并没有看到客户端-1 新增的用户，此时客户端-2 插入 id 为 3 的新用户

# 客户端-2 也插入一个 id 为 3 的新用户
insert into t_user values (3, 'Jack', 0);

# MySQL 报错:
ERROR 1062 (23000): Duplicate entry '3' for key 't_user.PRIMARY'

客户端-2 没有查询到 id 为 3 的用户，但是确爆出了主键重复的异常信息，就好像是幻觉一样，所以该问题被称为“幻读”。