MySQL 事务

什么是事务？

---

事务就是要保证一组数据库操作， 要么全部成功， 要么全部失败。 在MySQL中， 事务支持是在引擎层实现的。 你现在知道， MySQL是一个支持多引擎的系统， 但并不是所有的引擎都支持事务。 比如MySQL原生的MyISAM引擎就不支持事务， 这也是MyISAM被InnoDB取代的重要原因之一。

隔离性与隔离级别

---

提到事务， 你肯定会想到ACID（Atomicity、 Consistency、 Isolation、 Durability， 即原子性、 一致性、 隔离性、 持久性） ， 今天我们就来说说其中I， 也就是“隔离性”。

当数据库上有多个事务同时执行的时候， 就可能出现脏读（dirtyread） 、 不可重复读（non-repeatable read） 、 幻读（ phantom read） 的问题， 为了解决这些问题， 就有了“隔离级别”的概念。在谈隔离级别之前， 你首先要知道， 你隔离得越严实， 效率就会越低。 因此很多时候， 我们都要在二者之间寻找一个平衡点。 SQL标准的事务隔离级别包括： 读未提交（ read uncommitted） 、读提交（read committed） 、 可重复读（ repeatable read） 和串行化（ serializable ） 。 下面我逐一为你解释：

• 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
• 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
• 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
• 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

其中“读提交”和“可重复读”比较难理解， 所以我用一个例子说明这几种隔离级别。 假设数据表T中只有一列， 其中一行的值为1， 下面是按照时间顺序执行两个事务的行为。

示例：

mysql> create table T(c int); 
insert into T(c) values(1);

我们来看看在不同的隔离级别下，事务A会有哪些不同的返回结果，也就是图里面V1、V2、V3的返回值分别是什么。

• 若隔离级别是“读未提交”，则V1的值就是2。这时候事务B虽然还没有提交，但是结果已经被A看到了。 因此， V2、 V3也都是2。
• 若隔离级别是“读提交”，则V1是1，V2的值是2。事务B的更新在提交后才能被A看到。所以，V3的值也是2。
• 若隔离级别是“可重复读”，则V1、V2是1，V3是2。之所以V2还是1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
• 若隔离级别是“串行化”，则在事务B执行“将1改成2”的时候， 会被锁住。直到事务A提交后，事务B才可以继续执行。所以从A的角度看，V1、V2值是1，V3的值是2。

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下， 这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下， 这个视图是在每个SQL语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

我们可以看到在不同的隔离级别下，数据库行为是有所不同的。Oracle数据库的默认隔离级别其实就是“读提交”，因此对于一些从Oracle迁移到MySQL的应用，为保证数据库隔离级别的一致，你一定要记得将MySQL的隔离级别设置为“读提交”。

配置的方式是，将启动参数transaction-isolation的值设置成READ-COMMITTED。你可以用show variables来查看当前的值。

示例：

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

总结来说，存在即合理，哪个隔离级别都有它自己的使用场景，你要根据自己的业务情况来定。我想你可能会问那什么时候需要“可重复读”的场景呢？我们来看一个数据校对逻辑的案例。

案例：

假设你在管理一个个人银行账户表。一个表存了每个月月底的余额，一个表存了账单明细。这时候你要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。你一定希望在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。

这时候使用“可重复读”隔离级别就很方便。事务启动时的视图可以认为是静态的，不受其他事务更新的影响。

事务隔离的实现

---

理解了事务的隔离级别， 我们再来看看事务隔离具体是怎么实现的。 这里我们展开说明“可重复读”。在MySQL中， 实际上每条记录在更新的时候都会同时记录一条回滚操作。 记录上的最新值， 通过回滚操作， 都可以得到前一个状态的值。假设一个值从1被按顺序改成了2、 3、 4， 在回滚日志里面就会有类似下面的记录。

当前值是4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的read-view。如图中看到的，在视图A、B、C里面，这一个记录的值分别是1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC） 。 对于read-view A， 要得到1， 就必须将当前值依次执行图中所有的回滚操作得到。

同时你会发现，即使现在有另外一个事务正在将4改成5，这个事务跟read-view A、B、C对应的事务是不会冲突的。

你一定会问， 回滚日志总不能一直保留吧， 什么时候删除呢？答案是，在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。

什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的read-view的时候。

基于上面的说明， 我们来讨论一下为什么建议你尽量不要使用长事务？

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

在MySQL 5.5及以前的版本，回滚日志是跟数据字典一起放在ibdata文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。我见过数据只有20GB，而回滚段有200GB的库。最终只好为了清理回滚段， 重建整个库。

除了对回滚段的影响， 长事务还占用锁资源，也可能拖垮整个库。

事务的启动方式

---

MySQL事务是一组数据库操作的逻辑单元，具有原子性、一致性、隔离性和持久性的特性。事务的目的是将一系列操作作为一个整体，要么全部成功执行，要么全部回滚，以保证数据的一致性。

事务的启动方式、提交方式和回滚方式总结如下：

1）启动事务的方式：

• 隐式启动事务：隐式启动事务是指在自动提交模式下，每个SQL语句被视为一个独立的事务。当autocommit参数设置为1时，事务会在执行每个SQL语句时自动启动并立即提交。
• 显式启动事务：显式启动事务是指在手动提交模式下，通过使用 start transaction; 或 begin; 语句来显式地启动一个事务。在手动提交模式下，每个SQL语句都处于一个未提交的事务中，直到显式地使用 commit; 语句提交事务。

隐式启动事务举例：

假设有一个银行的数据库，其中有两个表：accounts（存储账户信息）和transactions（存储交易记录）。现在执行以下两个SQL语句：

insert into accounts (account_number, balance) values ('123456789', 1000);
update accounts set balance = balance - 100 where account_number = '123456789';

在自动提交模式下（autocommit=1），每个SQL语句都被视为一个独立的事务。因此，第一个SQL语句将启动一个事务并立即提交，将新的账户信息插入到accounts表中。然后，第二个SQL语句将启动另一个事务并立即提交，将账户余额减少100。这两个操作将作为独立的事务进行，无法回滚。

显示启动事务举例：

假设有一个电商网站的数据库，其中有两个表：orders（存储订单信息）和inventory（存储库存信息）。现在执行以下两个SQL语句：

start transaction;
insert into orders (order_id, product_id, quantity) values (1, 'ABC123', 2);
update inventory set quantity = quantity - 2 where product_id = 'ABC123';
commit;

在手动提交模式下（autocommit=0），通过使用 start transaction; 或 begin; 语句显式地启动一个事务。然后，执行第一个SQL语句将插入一个新的订单到orders表中，执行第二个SQL语句将更新inventory表中对应产品的库存数量。最后，使用 commit; 语句提交事务，将这两个操作作为一个原子操作进行提交。如果在执行过程中发生错误，可以使用 rollback; 语句回滚事务，撤销之前的操作。

通过显式启动事务，可以将多个SQL语句组合成一个事务，并在需要时进行手动提交或回滚，提供了更灵活的事务控制。

需要说明的是，无论 autocommit=0，还是 autocommit=1，只要使用 start transaction; 或 begin; 语句显示地启动一个事务，就需要使用 commit; 语句来显示提交该事务。

2）提交事务的方式：

• 手动提交事务：使用 commit; 语句显式地提交事务，将事务中的操作永久保存到数据库中。
• 自动提交事务：在 MySQL 的配置中设置 autocommit 参数为1，或者在连接 MySQL 时设置 autocommit=1，即可自动提交事务。每个SQL语句执行后都会立即生效，无法回滚。

3）回滚事务的方式：

• 手动回滚事务：使用 rollback; 语句显式地回滚事务，撤销事务中的所有操作，恢复到事务开始前的状态。
• 自动回滚事务：在发生错误或异常情况下，MySQL会自动回滚事务，撤销事务中的所有操作。

举例说明：

1）创建表

-- 创建表并插入两条数据
create table account(id int primary key AUTO_INCREMENT comment'ID', name varchar(10), money double(10,2));
insert into account(name,money) values('张三',2000),('李四',2000);

2）查看 autocommit 变量

-- 查看 autocommit 变量
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

默认情况下 autocommit=1，即默认事务自提交。

3）事务自提交测试

会话一：

-- 执行一系列数据库操作
update account set money=money-1000 where name='张三';
update account set money=money+1000 where name='李四';

会话二：

mysql> select * from account;
+----+--------+---------+
| id | name   | money   |
+----+--------+---------+
|  1 | 张三   | 1000.00 |
|  2 | 李四   | 3000.00 |
+----+--------+---------+

4）事务手动提交测试

关闭自提交：

-- 设置 autocommit 变量
set @@autocommit=0; 

会话一：

-- 手动启动、提交和回滚事务的示例
start transaction; -- 手动启动事务

-- 执行一系列数据库操作
update account set money=money-1000 where name='张三';
update account set money=money+1000 where name='李四';

-- 此时在会话二中执行 select * from account; 通过结果可发现上述 update 操作未生效   

commit; -- 手动提交事务，将操作永久保存到数据库中

-- 此时在会话二中执行 select * from account; 通过结果可发现上述 update 操作生效   

-- 手动回滚事务的示例
start transaction; -- 手动启动事务

-- 执行一系列数据库操作
update account set money=money-1000 where name='张三';
update account set money=money+1000 where name='李四';

rollback; -- 手动回滚事务，撤销事务中的所有操作

commit; -- 手动提交事务

-- 此时在会话二中执行 select * from account; 通过结果可发现上述 update 操作未生效  

在手动启动和提交事务的方式下，可以在多个SQL语句中形成一个事务，并根据需要进行回滚或提交。而自动启动和提交事务的方式适用于单个语句的简单操作。无论是手动还是自动回滚，都能确保事务中的操作在发生错误或异常时能够被撤销，保持数据的一致性。