MySQL 必知必会(14)事务:怎么确保关联操作正确执行?

2022年12月21日 69点热度

事务:怎么确保关联操作正确执行

我们经常会遇到这样的场景:几个相互关联的数据操作,必须是全部执行,或者全部不执行,不可以出现部分执行的情况。比如说,你从微信账号里提现 100 元到银行卡上,这个动作就包括了相互关联的 2 个步骤,首先是微信账号减 100 元,然后是银行卡账号加 100 元(这里假设没有手续费)。假如因为某种异常,这 2 个操作只执行了一个,另外一个没有执行,就会出现你的钱少了 100 元,或者你的钱多了 100 元的情况,这肯定是不能接受的。

如何才能确保多个关联操作全部执行呢?这时就要用到事务了。接下来我就重点讲一讲什么是事务,以及如何正确使用事务。

什么是事务?

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

事务的语法结构如下所示:

START TRANSACTION 或者 BEGIN (开始事务)
一组DML语句
COMMIT(提交事务)
ROLLBACK(事务回滚)

我解释一下这几个关键字。

  • START TRANSACTION 和 BEGIN:表示开始事务,意思是通知 MySQL,后面的 DML 操作都是当前事务的一部分。
  • COMMIT:表示提交事务,意思是执行当前事务的全部操作,让数据更改永久有效。
  • ROLLBACK:表示回滚当前事务的操作,取消对数据的更改。

事务有 4 个主要特征,分别是原子性(atomicity)、一致性(consistency)、持久性(durability)和隔离性(isolation)

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

持久性非常好理解,我就不多说了,接下来我重点讲一讲事务的原子性、一致性和隔离性,这是确保关联操作正确执行的关键。

如何确保操作的原子性和数据的一致性?

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

销售流水表(demo.mytrans):

销售流水表

库存表(demo.inventory):

库存表

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

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

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

mysql> START TRANSACTION; -- 开始事务
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO demo.mytrans VALUES (1,1,5); -- 插入流水
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE demo.inventory SET invquantity = invquantity - 5 WHERE itemnumbe
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> COMMIT; -- 提交事务
Query OK, 0 rows affected (0.06 sec)

然后我们查询一下结果:

mysql> SELECT * FROM demo.mytrans; -- 流水插入成功了
+---------+------------+----------+
| transid | itemnumber | quantity |
+---------+------------+----------+
| 1 | 1 | 5.000 |
+---------+------------+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM demo.inventory; -- 库存消减成功了
+------------+-------------+
| itemnumber | invquantity |
+------------+-------------+
| 1 | 5.000 |
+------------+-------------+
1 row in set (0.00 sec)

这样,通过把 2 个相关操作放到事务里面,我们就实现了一个事务操作。

这里有一个坑,我要提醒你一下。事务并不会自动帮你处理 SQL 语句执行中的错误,如果你对事务中的某一步数据操作发生的错误不做处理,继续提交的话,仍然会导致数据不一致。

为了方便你理解,我举个小例子。

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

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

mysql> INSERT INTO demo.mytrans VALUES (1,5); -- 这个插入语句出错了
ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> UPDATE demo.inventory SET invquantity = invquantity - 5 WHERE itemnumbe
Query OK, 1 row affected (0.00 sec) -- 后面的更新语句仍然执行成功了
Rows matched: 1 Changed: 1 Warnings: 0

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

我们查一下表的内容:

mysql> SELECT * FROM demo.mytrans; -- 流水没有插入成功
Empty set (0.16 sec)

mysql> SELECT * FROM demo.inventory; -- 库存消减成功了
+------------+-------------+
| itemnumber | invquantity |
+------------+-------------+
| 1 | 5.000 |
+------------+-------------+
1 row in set (0.00 sec)

结果显示,流水插入失败了,但是库存更新成功了,这时候没有销售流水,但是库存却被消减了。

这就是因为没有正确使用事务导致的数据不完整问题。那么,如何使用事务,才能避免这种由于事务中的某一步或者几步操作出现错误,而导致数据不完整的情况发生呢?这就要用到事务中错误处理和回滚了:

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

那这里的关键就是判断操作是不是发生了错误。我们可以通过 MySQL 的函数 ROW_COUNT() 的返回,来判断一个 DML 操作是否失败,-1 表示操作失败,否则就表示影响的记录数。

mysql> INSERT INTO demo.mytrans VALUES (1,5);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| -1 |
+-------------+
1 row in set (0.00 sec)

另外一个经常会用到事务的地方是存储过程。由于存储过程中包含很多相互关联的数据操作,所以会大量使用事务。我们可以在 MySQL 的存储过程中,通过获取 SQL 错误,来决定事务是提交还是回滚:

mysql> DELIMITER // -- 修改分隔符为 //

mysql> CREATE PROCEDURE demo.mytest() -- 创建存储过程
    -> 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.05 sec)

mysql> DELIMITER ; -- 恢复分隔符为;

mysql> CALL demo.mytest(); -- 调用存储过程
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.000 |
+------------+-------------+
1 row in set (0.00 sec)

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

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

关于存储过程,我会在后面的课程里给你详细介绍。这里你只需要知道,在这个存储过程中,我使用了“DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;”这个语句,来监控 SQL 语句的执行结果,一旦发发生错误,就自动回滚并退出。通过这个机制,我们就实现了对事务中的 SQL 操作进行监控,如果发现事务中的任何 SQL 操作发生错误,就自动回滚。

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

如何用好事务的隔离性?

接下来,我们再学习下如何用好事务的隔离性。

超市经营者提出,门店要支持网上会员销售,现在我们假设会员张三是储值会员,他的会员卡里有 100 元。张三用会员卡到门店消费 100 元,他爱人用他的会员卡在网上消费 100 元。

张三在门店消费结算的时候,开启了一个事务 A,包括这样 3 个操作:

  1. 读取卡内金额为 100;
  2. 更新卡内金额为 0;
  3. 插入一条销售流水。

张三的爱人在网上购物,开启了一个事务 B,也来读取卡内金额。如果 B 读取卡内金额的操作,发生在 A 更新卡内金额之后,并且在插入销售流水之前,那么 B 读出的金额应该是多少呢?如果 B 读出 0 元,那么,A 有可能由于后面的操作失败而回滚。因此,B 可能会读到一条错误信息,而导致本来可以成功的交易失败。有什么办法可以解决这个问题呢?

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

MySQL 中的锁有很多种,功能也十分强大。咱们这门课里不要求你掌握锁,你只要知道,MySQL 可以用锁来控制事务对数据的操作,就可以了。

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

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

  1. READ UNCOMMITTED:可以读取事务中还未提交的被更改的数据。
  2. READ COMMITTED:只能读取事务中已经提交的被更改的数据。
  3. REPEATABLE READ:表示一个事务中,对一个数据读取的值,永远跟第一次读取的值一致,不受其他事务中数据操作的影响。这也是 MySQL 的默认选项。
  4. SERIALIZABLE:表示任何一个事务,一旦对某一个数据进行了任何操作,那么,一直到这个事务结束,MySQL 都会把这个数据锁住,禁止其他事务对这个数据进行任何操作。

一般来讲,使用 MySQL 默认的隔离等级 REPEATABLE READ,就已经够了。不过,也不排除需要对一些关键的数据操作,使用最高的隔离等级 SERIALIZABLE。

举个例子,在我们的超市项目中,就对每天的日结操作设置了最高的隔离等级。因为日结要进行大量的核心数据计算,包括成本、毛利、毛利率、周转率,等等,并把结果保存起来,作为各类查询、报表系统、决策支持模块的基础,绝对不能出现数据错误。

当然,计算完成之后,你也不要忘记把隔离等级恢复到系统默认的状态,否则,会对日常的系统营运效率产生比较大的影响。

事务的隔离性对并发操作非常有用。当许多用户同时操作数据库的时候,隔离性可以确保各个连接之间互相不影响。这里我要提醒你的是,正确设置事务的隔离等级很重要。

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

总结

事务可以确保事务中的一系列操作全部被执行,不会被打断;或者全部不被执行,等待再次执行。事务中的操作,具有原子性、一致性、永久性和隔离性的特征。但是这并不意味着,被事务包裹起来的一系列 DML 数据操作就一定会全部成功,或者全部失败。你需要对操作是否成功的结果进行判断,并通知 MySQL 针对不同情况,分别完成事务提交或者回滚操作,才能最终确保事务中的操作全部成功或全部失败。

MySQL 支持 4 种不同的事务隔离等级,等级越高,消耗的系统资源也越多,你要根据实际情况进行设定。

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

思考题

学完了这节课以后,如果现在有人对你说,事务就是确保事务中的数据操作,要么全部正确执行,要么全部失败,你觉得这句话对吗?为什么?

Baimi

世上只有两种编程语言:一种是总是被人骂的,一种是从来没人用的。