MySQL必知必会12：事务-确保关联操作执行

阅读整理自《MySQL 必知必会》- 朱晓峰，详细内容请登录 极客时间 官网购买专栏。

事务

事务是 MySQL 的一项功能，它可以使一组数据操作（也叫 DML 操作，是英文 Data Manipulation Language 的缩写，包括 select、insert、update 和 delete），要么全部执行，要么全部不执行，不会因为某种异常情况（比如硬件故障、停电、网络中断等）出现只执行一部分操作的情况。

start transaction 或者 begin (开始事务)
一组 DML 语句
commit (提交事务)
rollback(事务回滚)

• start transaction 和 begin：表示开始事务，通知 MySQL 后的的 DML 操作都是当前事务的一部分
• commit：表示提交事务，执行当前事务的全部操作，让数据更改永久有效
• rollback：表示回滚当前事务的操作，取消对数据的更改

事务有4个特征：原子性、一致性、持久性和隔离性。

• 原子性：事务中的操作要么全部执行，要么全部不执行，像一个整体，不能从中间打断
• 一致性：数据的完整不会因为事务的执行而受到破坏
• 隔离性：多个事务同时执行时，互不干扰；不同的隔离级别，互相独立的程度不同
• 持久性：事务对数据的修改是永久有效的，不会因为系统故障而失效

---

一致性

数据准备：

一个超市的收银员帮顾客结账的简单场景，在系统中，结算的动作主要就是销售流水的产生和库存的消减。这里会涉及销售流水表和库存表，如下所示：

demo.mytrans

transid	itemnumber	quantity

demo.inventory

itemnumber	invquantity
1	10

create table demo.mytrans 
(
transid int,
itemnumber int,
quantity int
);

create table demo.inventory
(
itemnumber int,
invquantity int
);
insert demo.inventory values (1, 10);

业务：

假设门店销售了 5 个商品编号是 1 的商品，这个动作实际上包括了 2 个相互关联的数据库操作：

• 向流水表中插入一条“1 号商品卖了 5 个”的销售流水；
• 把库存表中的 1 号商品的库存减 5

这里包含了 2 个 DML 操作，为了避免意外事件导致的一个操作执行了而另一个没有执行的情况，把它们放到一个事务里面，利用事务中数据操作的原子性，来确保数据的一致性：

mysql> start transaction;
Query OK, 0 rows affected (3.06 sec)

mysql> insert into demo.mytrans values(1, 1, 5);
Query OK, 1 row affected (0.01 sec)

mysql> update demo.inventory set invquantity=invquantity-5 where itemnumber=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

查询结果：

mysql> select * from demo.mytrans;
+---------+------------+----------+
| transid | itemnumber | quantity |
+---------+------------+----------+
|       1 |          1 |        5 |
+---------+------------+----------+

mysql> select * from demo.inventory;
+------------+-------------+
| itemnumber | invquantity |
+------------+-------------+
|          1 |           5 |
+------------+-------------+

另外：事务并不会自动帮处理 SQL 语句执行中的错误，如果对事务中的某一步数据操作发生的错误不做处理，继续提交的话，仍然会导致数据不一致。

---

事务中的报错

假如插入一条销售流水的语句少了一个字段，执行的时候出现错误了，如果不对这个错误做回滚处理，继续执行后面的操作，最后提交事务，结果就会出现没有流水但库存消减了的情况：

mysql> select * from demo.mytrans;
+---------+------------+----------+
| transid | itemnumber | quantity |
+---------+------------+----------+
|       1 |          1 |        5 |
+---------+------------+----------+

mysql> select * from demo.inventory;
+------------+-------------+
| itemnumber | invquantity |
+------------+-------------+
|          1 |           5 |
+------------+-------------+

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into demo.mytrans values (1,5);
ERROR 1136 (21S01): Column count does not match value count at row 1  -- 开启事务后，报错

mysql> update demo.inventory set invquantity=invquantity-5 where itemnumber=1;  -- 后面的语句仍然执行成功了
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)  -- 事务提交成功了

mysql> select * from demo.mytrans;
+---------+------------+----------+
| transid | itemnumber | quantity |
+---------+------------+----------+
|       1 |          1 |        5 |   -- 流水没有插入成功，还是原来的数据
+---------+------------+----------+

mysql> select * from demo.inventory;
+------------+-------------+
| itemnumber | invquantity |
+------------+-------------+
|          1 |           0 |   -- 库存消减成功了
+------------+-------------+

这就是因为没有正确使用事务导致的数据不完整问题。

为了避免这种由于事务中的某一步或者几步操作出现错误，而导致数据不完整的情况发生，就要用到事务中错误处理和回滚了：

• 如果发现事务中的某个操作发生错误，要及时使用回滚；
• 只有事务中的所有操作都可以正常执行，才进行提交

关键就是判断操作是不是发生了错误。

通过 MySQL 的函数 row_count() 的返回，来判断一个 DML 操作是否失败，-1 表示操作失败，否则就表示影响的记录数。

mysql> insert into demo.mytrans values(1, 5);
ERROR 1136 (21S01): Column count does not match value count at row 1

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|          -1 |
+-------------+

另外一个经常会用到事务的地方是存储过程。

由于存储过程中包含很多相互关联的数据操作，所以会大量使用事务。我们可以在 MySQL 的存储过程中，通过获取 SQL 错误，来决定事务是提交还是回滚：

mysql> select * from demo.inventory;   -- 测试前数据
+------------+-------------+
| itemnumber | invquantity |
+------------+-------------+
|          1 |          10 |
+------------+-------------+

mysql> select * from demo.mytrans;
Empty set (0.00 sec)

mysql> delimiter //                                -- 修改分隔符为 //
mysql> create procedure demo.test_rollback()       -- 创建存储过程
-> begin                                           -- 开始程序体
-> declare exit handler for sqlexception rollback; -- 定义SQL操作发生错误是自动回滚
-> start transaction;                              -- 开始事务
-> insert into demo.mytrans values (1,5);
-> update demo.inventory set invquantity = invquantity - 5;
-> commit;                                         -- 提交事务
-> end
-> //                                              -- 完成创建存储过程
Query OK, 0 rows affected (0.01 sec)
 
mysql> delimiter ;                                 -- 恢复分隔符为 ； 这里 delimiter 需要和 ; 之间有一个空格
mysql> call demo.test_rollback();                  -- 调用存储过程
Query OK, 0 rows affected (0.00 sec)

mysql> select * from demo.mytrans;    -- 销售流水没有插入
Empty set (0.00 sec)

mysql> select * from demo.inventory;   -- 库存也没有消减，说明事务回滚了
+------------+-------------+
| itemnumber | invquantity |
+------------+-------------+
|          1 |          10 |
+------------+-------------+

先通过delimiter //语句把 MySQL 语句的结束标识改为//（默认语句的结束标识是 ; ）。这样做的目的是告诉 MySQL 一直到 // 才是语句的结束，否则，MySQL 会在遇到第一个 ; 的时候认为语句已经结束，并且执行。这样就会报错，自然也就没办法创建存储过程了。

创建结束以后，还要录入//，告诉 MySQL 存储过程创建完成了，并且通过 DELIMITER ;，再把语句结束标识改回到;。

总之，要把重要的关联操作放在事务中，确保操作的原子性，并且对失败的操作进行回滚处理。只有这样，才能真正发挥事务的作用，保证关联操作全部成功或全部失败，最终确保数据的一致性。

---

用好隔离性

案例：

• 张三在门店消费结算的时候，开启了一个事务 A，包括3个操作：
  • 读取卡内金额为 100；
  • 更新卡内金额为 0；
  • 插入一条销售流水
• 张三的爱人在网上购物，开启了一个事务 B，也来读取卡内金额

如果 B 读取卡内金额的操作，发生在 A 更新卡内金额之后，并且在插入销售流水之前，那么 B 读出的金额应该是多少？如果 B 读出 0 元，而此时 A 又存在可能由于后面的操作失败而回滚，金额重新变为原来的100。因此，B 可能会读到一条错误信息，而导致本来可以成功的交易失败。

这个时候，就会用到 MySQL 的另外一种机制：“锁”。MySQL 可以把 A 中被修改过而且还没有提交的数据锁住，让 B 处于等待状态，一直到 A 提交完成，或者失败回滚，再释放锁，允许 B 读取这个数据。这样就可以防止因为 A 回滚而导致 B 读取错误的可能了。

通过对锁的使用，可以实现事务之间的相互隔离。锁的使用方式不同，隔离的程度也不同。

MySQL 支持 4 种事务隔离等级。

• read uncommitted：可以读取事务中还未提交的被更改的数据
• read committed：只能读取事务中已经提交的被更改的数据
• repeatable read：表示一个事务中，对一个数据读取的值，永远跟第一次读取的值一致，不受其他事务中数据操作的影响。这也是 MySQL 的默认选项
• serializable：表示任何一个事务，一旦对某一个数据进行了任何操作，那么，一直到这个事务结束，MySQL 都会把这个数据锁住，禁止其他事务对这个数据进行任何操作

实际业务场景中，操作计算完成之后，需要把隔离等级恢复到系统默认的状态，否则，会对日常的系统营运效率产生比较大的影响。

一方面，对于一些核心的数据更改操作，可能需要较高的隔离等级，比如涉及金额的修改；另一方面，要考虑资源的消耗，不能使系统整体的效率受到太大的影响。所以，要根据具体的应用场景，正确地使用事务。

---

小结

事务可以确保事务中的一系列操作全部被执行，不会被打断；或者全部不被执行，等待再次执行。

事务中的操作，具有原子性、一致性、永久性和隔离性的特征。但是这并不意味着，被事务包裹起来的一系列 DML 数据操作就一定会全部成功，或者全部失败。

需要对操作是否成功的结果进行判断，并通知 MySQL 针对不同情况，分别完成事务提交或者回滚操作，才能最终确保事务中的操作全部成功或全部失败。

在 MySQL 中，并不是所有的操作都可以回滚。

比如创建数据库、创建数据表、删除数据库、删除数据表等，这些操作是不可以回滚的，所以，在操作的时候要特别小心，特别是在删除数据库、数据表时，最好先做备份，防止误操作。