MySQL修改数据集表之浅析事务

有以下场景：张三有两张银行卡，卡号为1234567890的有1000元钱，卡号为0987654321的卡有0元钱，现从1234567890卡取出100块钱转到0987654321卡，则取出钱的卡需要减去100，转入卡需要增加100，现实生活中这两个update操作必须同时成功同时失败。

create table account(
  id char(10) primary key,
  card_id varchar(20) unique,
  name varchar(8) not null,
  money float(10,2) default 0
);
insert into account values('1','1234567890','张三',1000);
insert into account (id,card_id,name) values('2','0987654321','张三’);

进行update操作：

update account set money=money-100 where card_id= '1234567890';
update account set money=money+100 where card_id= '0987654321';
select * from account

更新成功。但是如果第二条update语句出现语法错误，那么就会导致用户张三损失100元，因此我们使用需要使用事务来保证一组DML同时成功或者同时失败。
1、事务定义
Transaction

• 事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的作业(例如银行账户转账业务，该业务就是一个最小的工作单元)
• 一个完整的作业需要批量的DML(insert、update、delete)语句共同联合完成
• 事务只和DML语句有关，或者说DML语句才有事务。

2、事务开启
任何一条DML语句(insert、update、delete)执行都标志事务的开启，但是若不加规定一个语句只代表一个事务，我们可以通过手动开启事务+提交/回滚事务来确保一组DML语句在同一个事务内。

• start transaction

start transaction
update account set money=money-100 where card_id= '1234567890';
update account set money=moey+100 where card_id= '0987654321';#此处我们故意写错DML语句
commit;
select * from account

执行结果程序报错，我们单独查询表得：

第一行update语句同样没有作用到account表，以为此时这两句DML语句属于同一事务，同时成功或者同时失败。

• set autocommit = 0;

set autocommit = 0;
update account set money=money-100 where card_id= '1234567890';
update account set money=moey+100 where card_id= '0987654321';
select * from account

在使用set autocommit = 0把autocommit变量设置为零，禁用autocommit模式之后，必须使用commit把变更存储到磁盘中，或着如果想要忽略从事务开始进行以来做出的变更，使用rollback。
执行结果：

发现表中数据已更改，为什么？因为事务中所做的每一个操作在事务被提交之前都是临时的，在commit或rollback语句执行之前，DML语句首先影响该用户的数据库缓冲区，因为这些操作可以被恢复，而且因为是在该用户的数据缓冲区，所以在原SQL窗口中查询得到的是数据缓冲区的结果，不是数据库真实的数据；要想拿到数据库真实的数据，需要在新SQL窗口中执行查询语句，这样针对不同的用户，MySQL服务器用读一致性来确保每个用户看到的数据和上次提交时的数据相同。
另一个窗口查询：select * from account

3、事务提交/回滚

set autocommit = 0;
update account set money=money-100 where card_id= '1234567890';
update account set money=moey+100 where card_id= '0987654321';#此处我们故意写错DML语句
commit;

在另一个SQL窗口执行查询结果：

此处显然不该使用commit。

set autocommit = 0;
update account set money=money-100 where card_id= '1234567890';
update account set money=moey+100 where card_id= '0987654321';#此处我们故意写错DML语句
rollback;

执行结果：

数据正确。
到此我们发现一个问题，使用set autocommit = 0似乎并没有保证DML语句一同成功或者失败，那为什么还要使用这种方式呢？因为在java代码中我们可以通过是否出现异常来决定是进行commit还是进行rollback，所以这种方法在便于在java代码中实现事务，保证数据一致性。详见jdbc实现事务回滚/提交

注意

当一个用户执行DML语句时，如果其事物没有结束，则受影响的行被锁定，此时其它用户不能改变受影响行中的数据，后面用户所执行的DML语句会一直处于执行状态，直至前一个用户所执行DML语句事务结束

• 在一个SQL Window中执行如下SQL语句，并且不执行事务结束语句

set autocommit = 0;
update student set name='小强' where id='0e0180a3-ec2f-4a72-b1ae-28d5f2be36ea';

• 然后再在一个新的SQL Window中执行如下SQL语句

set autocommit = 0;
delete from student where id='0e0180a3-ec2f-4a72-b1ae-28d5f2be36ea';

由于update语句执行后没有结束事务，所以delete语句执行后一致处于执行状态直至超时报错，当update语句的事务结束，则delete语句结束执行状态。