MySQL操作汇总4——事务

一、事务：

——是一个执行单元，要么全部执行，要么全部不执行。
如果单元中某条SQL语句一旦执行失败，整个单元都会回滚，返回到之前的状态。

事务的四大特征：

A原子性：事务是最小的单位，不可再分割，同一事务中的所有操作全部完成，要么都不完成。
B一致性：一个事务作为原子，从一致的数据库状态开始运行，事务结束时数据库还是一致的（如转账业务，无论事务执行是否成功，转账前后两个账号余额之和应该是不变的）。
C隔离性：事务1和事务2并发执行，它们之间是具有隔离性的，可用事务隔离级别来指定隔离性。（两个人做同一件事不会冲突）
D持久性：事务一旦提交（commit, rollback二选一），就不可以返回，被持久化到数据库了（事务提交后，即便数据库崩溃，也能恢复数据）。

事务开启：
1、修改默认提交 set autocommit=0;
2、begin
3、start transaction;

事务的提交：
commit;
事务手动回滚：
rollback;

二、存储引擎：

1、概念：在mysql中的数据用不同的存储技术存储在文件（或内存中）；
1、通过show engines，来查看mysql支持的存储引擎；
3、应用最多的存储引擎有：innodb、myisam、memory等，其中innodb支持事务，其余二者不支持。

三、事务用来做什么？

——事务其实是一个最小的不可分割的工作单元，事务能够保证一个业务的完整性。
比如银行转账：
a用户—>-100元

update user set money=money-100 where name=’a’;

b用户—>+100元

update user set money=money+100 where name=’b’;

实际程序中，如果只有一条语句执行成功，另外一条没有执行成功？
这会出现数据前后不一致，这是不允许的！
多条SQL语句要求要么同时成功，要么就同时失败。

四、mysql中如何控制事务？

1.MySQL默认是开启事务的（autocommit：自动提交）
——随便一个操作，就是一个事务，就是自动提交的！

select @@autocommit

默认事务开启的作用是什么？——当我们去执行一个SQL语句的时候，效果会立即体现出来，且不能回滚。

create database bank;
create table user(
    id int primary key,
	name varchar(20),
	money int
);

insert into user values(1,'a',1000);

事务回滚：撤销SQL语句执行效果
立即生效，不能回滚：

如何回滚？——设置MySQL自动提交为false

set autocommit=0;#关闭了MySQL的主动提交

则可以实现事务的回滚操作

再次测试：

insert into user values(2,'b',1000);

——成功回滚

关闭自动提交后，如果在rollback前就手动提交了(commit)，就无法实现回滚了！

——事务的特性：持久性！——一旦提交，就会持久地产生效果。
总结：
—自动提交：@@autocommit=1;
—手动提交:commit;
—事务回滚:@@autocommit=0; rollback;

update user set money=money-100 where name=’a’;
update user set money=money+100 where name=’b’;

——事务给我们提供了一个返回的机会！

set @@autocommit=1;（相当于大开关）
begin; 或者 start transcation; 都可以帮我们手动开启一个事务。—（相当于小开关，大小开关都开启可回滚）
手动开启事务1：

begin;
update user set money=money-100 where name=’a’;
update user set money=money+100 where name=’b’;

手动开启事务2

start transaction;
update user set money=money-100 where name='a';
update user set money=money+100 where name='b';

五、事务的隔离性：——四种

（1）read uncommitted; 读未提交
（2）read committed;读已提交
（3）repeatable read;可重复读
（4）serialiazble;串行化

1、read uncommitted：读未提交

insert into user values(3,'小明',1000);
insert into user values(4,'淘宝店',1000);

——如何查看隔离级别？
mysql 8.0:
—系统级别的：

select @@global.transaction_isolation;

—会话级别的：

select @@transaction_isolation;

——可重复读, 是mysql默认隔离级别！
——如何修改隔离级别？

set global transaction isolation level read uncommitted;

——读未提交
转账：小明在淘宝店买鞋子：800块钱

start transaction;
update user set money=money-800 where name='小明';
update user set money=money+800 where name='淘宝店';

小明给淘宝店发信息确认是否支付：
淘宝店查账发现已支付，发货，钱到账。
店主赚了钱，晚上请女朋友买买买，刚好要1800元，结账时发现卡里居然没有1800元了！
因为小明rollback了：

淘宝店主结账时查了一下，发现只有1000元了。就是说小明收了货，还不给钱！
——现象：如果两个不同的地方，都在进行操作，如果事务1开启后，他的数据可以被其他事务读取到。
这样会出现脏读 —— 在read uncommitted的情况下一个事务读到了另外一个事务还未提交的数据。
（事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据）
——实际开发中不允许脏读的出现。

2、read committed：读已提交

set global transaction isolation level read committed;
select @@global.transaction_isolation;

【例子】
小张：银行会计
start transaction开启事务做报表
select * from user;

小张出去上厕所去了。。。

小王：
此时小王在小张上厕所期间，在小张的电脑上开了个事务，增加了数据：
start transaction;
insert into user values(5,’c’,100);——开了一个户，存了100元

commit;——当即提交数据

小张上厕所回来后：
select avg(money) from user;

——发现明显不对！口算都知道平均是1000元！
虽然我只能读到另外一个事务提交的数据，但还是会出现问题，就是读取同一个表的数据，发现前后不一致。
——不可重复读现象。READ-COMMITTED（读已提交状态下）

3、repeatable read：可重复读

set global transaction isolation level repeatable read;
select @@global.transaction_isolation;

张全蛋—成都：
start transaction;
王尼玛—北京：
start transaction;
——两人都开启了事务。
张全蛋—成都：
insert into user values(6,‘d’,1000);
王尼玛—北京：
select * from user;

——查不到这条账户
张全蛋—成都：
commit;
——那么对方提交后能不能查到呢？
王尼玛—北京：
select * from user;

——还是查不到！
张全蛋—成都：
select * from user;

——而自己可以查到
王尼玛—北京：

 insert into user values(6,'d',1000);

ERROR 1062 (23000): Duplicate entry ‘6’ for key ‘PRIMARY’
——对方再次插入这条账户，就会报错。
——这种现象就叫做幻读！
事务a操作和事务b同时操作一张表，事务a提交的数据，也不能被事务b读到，就可以造成幻读！

4、serializable：串行化

—修改隔离级别为串行化

set global transaction isolation level serializable
select @@global.transaction_isolation;

张全蛋—成都：

start transaction;

王尼玛—北京：

start transaction;

——两人都开启了事务。
原始数据：

张全蛋—成都：

insert into user values(7,'赵铁柱',1000);
commit;
select * from user;

王尼玛—北京：
select * from user;

张全蛋—成都：

insert into user values(8,'王小花',1000);

——卧槽！这时候SQL语句卡住了！
——因为这时候的隔离级别是串行化的，就是当一边开启一个事务，在进行表的操作的时候，另一边是不能进行表的写入操作的，它会进入等待状态，就是等待你commit

王尼玛—北京：
在被卡住的器件，提交：commit;
张全蛋—成都：
之前的插入操作结束卡顿，通过了：Query OK, 1 row affected (0.07 sec)

串行化总结：
— 当user表被另一个事务操作的时候，其他事务里面的写操作，是不可以进行的；
— 进入排队状态（串行化），直到王尼玛这边的事务结束（commit）之后，张全蛋这边的写入操作才会执行。
— 前提：在没有等待超时的情况下。

串行化的问题是：性能特差！
性能：read uncommitted > read committed > repeatable read > serializable
依次容易出现：各种问题都会出现 幻读 不可重复读 没啥问题
——隔离级别越高，性能越差！但是出现的问题越少
MySQL默认隔离级别是repeatable read.

【补充案例】

（1）概念：由一条或多条sql语句组成，要么都成功，要么都失败。
（2）分类：
1）隐式事务：没有明显的开启和结束标记
如：DML语句中的insert、update、delete语句本身就是一条事务
why？如：insert into stuinfo values(1,‘Tom’,‘male’,20)，如果插入失败，不会只成功插入一部分数据
2）显式事务：具有明显的开启和结束标记
————一般由多条SQL语句组成
步骤：（先取消事务自动开启的功能）开启事务 —— 编写SQL语句 —— 结束事务

• 先取消事务自动开启的功能
  查看事务是否自动开启：

SHOW VARIABLES LIKE '%auto%';

autocommit = ON，为自动提交，需要关闭自动提交

操作的数据库表如下所示，两人各有一万元，他们相互转账：

#1、取消事务自动开启
SET autocommit = 0;
#2、开启事务
START TRANSACTION;
#3、编写事务的SQL语句
UPDATE USER SET money = money-5000 WHERE id = 1;
UPDATE USER SET money = money+5000 WHERE id = 2;
#4、结束事务：正常提交（COMMIT）或回滚（ROLLBACK）

#验证
SELECT * FROM USER;

——此处先不提交

如果直接rollback，表数据就不会改变。
commit后提交持久化。