【示例】MySQL-事务控制示例：账户转账-savepoint关键字

前言

本文讲述MySQL中的事务，以账户转账为例，体会事务的概念，并讲解事务相关的一个关键字用法：savepoint

示例

数据准备

drop table if exists account;

create table account(
	id int primary key AUTO_INCREMENT comment 'ID',
	name varchar(10) comment '姓名',
	money double(10,2) comment '余额'
) comment '账户表';

insert into account(name, money) VALUES ('张三',2000), ('李四',2000);

未控制事务 | 转账正常

-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

所有SQL正常执行，没有出错。结果就是：张三账户余额-1000；李四账户余额+1000

未控制事务 | 异常情况

-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';

-- 手动写一行错误的SQL语句
select xxx xxx;

-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

只有前两个SQL执行了，第三个SQL没有执行，出现数据不一致了：张三的钱减少了，但是李四的钱没有增加

控制事务 | 示例

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

mysql> select * from account where name = '张三';		  					   # 展示张三账户金额
+----+--------+---------+
| id | name   | money   |
+----+--------+---------+
|  1 | 张三   | 2000.00 |
+----+--------+---------+
1 row in set (0.00 sec)

mysql> update account set money = money - 1000 where name = '张三';			# 将张三账户金额减少1000
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update account set money = money + 1000 where name = '李四';			# 将李四账户金额增加1000
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account where name = '张三';				# 查询张三账户金额（在事务里看，金额确实少了。但是数据库里面账户值还没变）
+----+--------+---------+
| id | name   | money   |
+----+--------+---------+
|  1 | 张三   | 1000.00 |
+----+--------+---------+
1 row in set (0.00 sec)

mysql> COMMIT;									# 上述操作均成功，提交事务，修改生效。若某一操作失败，需要回滚，COMMIT改为ROLLBACK
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account where name = '张三';
+----+--------+---------+
| id | name   | money   |
+----+--------+---------+
|  1 | 张三   | 1000.00 |
+----+--------+---------+
1 row in set (0.00 sec)

上述操作过程大致三个步骤：开启事务、执行一组SQL语句、提交或回滚事务。

事务开启方式

针对开启事务这一步，有如下几种方式：

# 方式1：关闭隐式事务
SELECT @@autocommit;
SET @@autocommit = 0;

# 方式2：手动开启事务
START TRANSACTION;

# 方式3：手动开启事务-另一种
BEGIN;

针对提交或回滚事务这一步，是看上数SQL操作是否都成功。若都成功，则提交；若有失败，则回滚，使数据恢复到修改前的状态：

COMMIT;   # 若SQL语句执行正常，提交事务

ROLLBACK; # 若SQL语句有执行异常，回滚事务

若是要用一个sql脚本来处理一组事务，则应该在提交或者回滚这一步加上判断条件：

# 开始事务
BEGIN;

#执行一组SQL
xxxxxx

# 提交或回滚
if(所有SQL执行成功) THEN
	COMMIT;
ELSE
	ROLLBACK;
END IF;

复杂的事务处理过程，通常可以借助第三方工具，例如脚本语言：Python、PHP等。

事务相关关键字 | savepoint

savepoint关键字可以使得回滚事务的时候只混滚部分代码处理的结果。

如下，是关于savepoint的大概作用流程：

# 开启事务
xxx

# 一组SQL
xxx修改操作1
xxx修改操作2
SAVEPOINT change1;
xxx查询操作1
xxx查询操作2，出问题报错了

# 因为修改操作都完成了，在查询结果是否正确的时候出了错误，就可以通过这样，只回滚change1到ROLLBACK TO change1之间的代码
ROLLBACK TO change1;

# ROLLBACK如果不加TO，就会回滚到初始位置，不管是否有savepoint
# 若是不想要某个savepoint点，可以删除
RELEASE SAVEPOINT change1;