在 Mysql 事务中执行 SELECT 时是否需要加 FOR UPDATE?

文章目录

前言

    在这里,我只是单纯的做个实验以更好的理解 MySQL 事务以及事务的隔离级别,具体的关于事务的理论,将在后续整理后发表。

    我们在做订单结算业务时,通常会有一连串的业务要处理,比如:扣除用户的余额、记录订单、记录消费记录等等。为了保证数据的一致性,我们通常会选用事务来处理订单结算业务,但是当我们要扣除用户余额的时候通常会考虑,是否应该在事务中查询用户时加上 for update 来锁定该行数据以免被选中 防止最终余额不正确的事情发生

    出于这样的疑虑,主要还是因为我个人对事务的理解深度不够,所以想着以实验来论证是否有这样的必要性,并记录整个实验过程来加深理解。

准备实验

实验材料:

  • PHPStudy 集成开发环境
  • Navicat for MySQL 管理工具

首先,我们需要建立一个实验用的数据库:

create database test; -- 创建一个数据库

然后,在数据库中建立一个数据表,具体结构如下:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `balance` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

使用 Navicat for MySQL 软件选中 test 数据库 右键鼠标 选择 命令列界面,依次打开 2 个 命令列界面 ,打开后如下图:
在 Mysql 事务中执行 SELECT 时是否需要加 FOR UPDATE?

实验过程

我们将在下面的实验中用到以下 SQL 语句:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 将事务隔离级别设置为 不可重复读(read-committed)
START TRANSACTION; -- 开始事务
COMMIT; -- 提交事务
ROLLBACK; -- 回滚事务
INSERT INTO users(name) VALUES('admin'); -- 插入一条记录
SELECT * FROM users; -- 查看用户表记录
UPDATE users SET balance = balance + 8 WHERE name='admin'; -- 更新用户余额

首先,我们开始插入一条实验数据:

INSERT INTO users(name) VALUES('admin');

在打开的两个命令列界面里分别将事务隔离级别设置为 RC

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected

在其中一个命令列界面 (简称为 A 客户端) 里开始一个事务,并将用户余额自增 8

mysql> START TRANSACTION;
Query OK, 0 rows affected

mysql> UPDATE users SET balance = balance + 8 WHERE name='admin';
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM users;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | admin | 8       |
+----+-------+---------+
1 row in set

现在我们看到 admin 的余额已经成为 8 了。现在我们在另一个命令列界面 (简称为 B 客户端) 里开启一个事务,并查看 admin 的余额

mysql> START TRANSACTION;
Query OK, 0 rows affected

mysql> SELECT * FROM users;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | admin | 0       |
+----+-------+---------+
1 row in set

我们会发现 admin 的余额为 0 这是因为事务的 隔离性(Isolation) 让事务之间的数据没有干扰。

注意: 当事务隔离级别为 read-uncommitted 时,此时查询出来的余额会是 8,虽然 A 客户端事务未提交,会出现幻读情况。

接下来,继续在 B 客户端将 admin 余额自增 8

mysql> UPDATE users SET balance = balance + 8 WHERE name='admin';

当你执行语句后,会发现语句被挂起来了,等隔一段时间后会提示超时:

mysql> UPDATE users SET balance = balance + 8 WHERE name='admin';
1205 - Lock wait timeout exceeded; try restarting transaction

这是因为事务的 隔离性 当 A 客户端事务未提交前,B 客户端事务执行更新操作时,B 客户端事务线程将会被挂起,直至等待超时或 A 客户端事务结束。

现在,我们将 A 客户端的事务进行提交

mysql> COMMIT;
Query OK, 0 rows affected

然后,返回至 B 客户端再执行一次更新余额的操作

mysql> UPDATE users SET balance = balance + 8 WHERE name='admin';
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM users;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | admin | 16      |
+----+-------+---------+
1 row in set

你会看到 admin 的余额已经从 8 增至 16 ,结果符合我们的预期。

结论

通过上述实验,我得出如下结论:

在事务中执行 SELECT 时无需加 FOR UPDATE 来锁定行数据用于 防止数据更新后的最终结果不正确的情况。因为事务的 隔离性 会帮我们预防。

注:此结论中的 隔离性隔离级别 无关,因为每个隔离级别都尝试了一下,均在 B 客户端执行 UPDATE 操作时,如 A 客户端事务未结束前都会被挂起直至等待超时。如果 隔离级别 设置为 串行化(serializable) A 客户端事务未结束前,B 客户端执行操作 A 事务中的相关表操作都会被挂起直至等待超时。

什么时候加?

当你在事务中 需要对更新的字段进行判断时,比如当用户余额不足时应该给予提醒,而不是最终执行后出现 负的余额或 SQL 错误等超预期的错误。

上一篇:第四周课程总结和实验报告2


下一篇:[c++基础]文件流