8.2、事务操作

— 数据准备
create table account(
id int auto_increment primary key comment ‘主键ID’,
name varchar(10) comment ‘姓名’,
money int comment ‘余额’
) comment ‘账户表’;
insert into account(id, name, money) VALUES (null,’张三’,2000),(null,’李四’,2000);

转账操作 (张三给李四转账1000)
— 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 = ‘李四’;

(1)查看/设置事务提交方式
SELECT @@autocommit; #1—自动提交 0—手动提交
SET @@autocommit=0;
(2) 提交事务
COMMIT;(手动提交方式)
(3) 回滚事务
ROLLBACK;（出现异常回滚事务）
(4)开启事务
START TRANSACTION 或 BEGIN;

（1）控制事务的第一种方式：修改事务的提交方式，改为手动提交，出现异常回滚ROLLBACK;
SET @@autocommit=0;
— 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 = ‘李四’;
— 在执行过程中出现异常不要commit，应该回滚事务，来保证数据的一致性
ROLLBACK;

（2）控制事务的第二种方式：开启事务;
START TRANSACTION;
— 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 = ‘李四’;
ROLLBACK;