19）事务

标签：事务 19 account 回滚 int 终端提交 balance

1、事务的必要性：

先来看一个例子，如下有一张账户表，字段包括账户号作为主键、账户名字、账户余额三个字段；

先向其中插入两行数据；

-- 先创建一个账户表：
create table account(
    account_no int auto_increment primary key,
    account_name char(10) not null,
    balance int unsigned
);
insert into account values(null,'甲',1000),(null,'乙',1000);

结果如下：

接着，我们创建一个存储过程，用于账户1向账户2转账：执行一次存储过程；

delimiter $$
create procedure transfer(in from_account int, in to_account int,in money int)
begin
    update account set balance=balance+money where account_no=to_account;
    update account set balance=balance-money where account_no=from_account;
end;
$$
delimiter ;

call transfer(1,2,800);
select * from account;

接着我们继续执行一次，还是1向2转账800；结果如下：

可以看到执行语句报错，但是执行的结果就是账户2的余额依然增加了800；账户1 的余额没有边，报溢出错误1690；这时银行会亏损这800；

原因如下：

MySQL默认自动提交，执行完语句后，就会对磁盘上的数据进行增删改查操作；

2、关闭MySQL自动提交

查看是否开启自动提交操作：

show variables like 'autocommit';

关闭自动提交的方法：下面两种方法只对当前会话有效；

1）显示

set autocommit=0;

2）隐式

start transaction；

隐式的关闭自动提交不会修改系统会话变量@@autocommit的值；

3、回滚

关闭自动提交后，把当前没有提交的操作进行撤销；语法为：

rollback;

我们上述关闭自动提交的基础上，进行一次更新操作；然后另打开一个终端，连接数据库，查看该 account 表中内容；

在当前终端执行update后，可以看到当前终端下，表内容已经是更新后的值；

但是在另一个终端里，表内容还是尚未执行 update 语句的值，就是因为关闭了自动提交，上述只是更新，没有提交；

然后我们可以在更新语句的终端里执行回滚操作，回退到之前的内容；

4、手动提交

1）显示提交：

commit;

2）隐式提交：

begin、set account = 1、start transaction、rename table、truncate等语句、ddl、dcl、锁语句等;

这里测试同上，一个终端提交，另一个终端里查看；

5、事务

1）直接全部回滚

先将之前创建的存储过程删掉；然后创建一个包含有捕获错误的错误处理代码的存储过程；对应的MySQL错误处理代码是1690；对应的错误处理程序是全部回滚；

完整的事务代码如下：

drop procedure transfer;
delimiter $$
create procedure transfer(in from_account int, in to_account int, in money int)
begin
    declare continue handler for 1690
    begin
     rollback;
    end;
    update account set balance=balance+money where account_no=to_account;
    update account set balance=balance-money where account_no=from_account;
    commit;
end;
$$
delimiter ;

测试如下：可以看到都不会增加；

2）部分回滚--保存点

可以回滚到保存点名的地方；实现部分提交，部分回滚；语法格式：

savepoint 保存点名;

我们考虑实现一个存储过程：插入两行数据，第二行数据是第一行的account_no；肯定会发生违反主键唯一约束的错误；但是我们要实现第一行数据提交，第二行回滚；

代码如下：

delimiter $$
create procedure savepoint1_proc()
begin
    declare continue handler for 1062
    begin
     rollback to B;
    end;
    start transaction;
    insert into account values(null,'丙',1000);
    savepoint B;
    insert into account values(last_insert_id(),'丁',1000);
    commit;
end;
$$
delimiter ;

测试：