[day07]事务

事务

-- 创建数据表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);
-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';
select * from account;
#已经自动提交的数据再进行rollback没用

1) 执行成功的情况： 开启事务 ->执行多条 SQL语句 ->成功提交事务
1) 执行失败的情况： 开启事务 ->执行多条 SQL语句-> 事务的回滚
事务提交

事务回滚

查看 MySQL是否开启自动提交事务
select @@sutocommit
取消自动提交事务
set @@sutocommit = 0

回滚点（存档）

设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候

事务的隔离级别

事务的四大特性 ACID

事务的隔离级别

事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题：

MySQL数据库有四种隔离级别

上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。

隔离级别越高，性能越差，安全性越高。

脏读的演示

将数据进行恢复：UPDATE account SET balance = 1000;

1. 打开 A 窗口登录 MySQL，设置全局的隔离级别为最低

   mysql -uroot -proot
   set global transaction isolation level read uncommitted;
   

   

2. 打开 B 窗口,AB 窗口都开启事务

   use day23;
   start transaction;
   

   

3. A 窗口更新 2 个人的账户数据，未提交

   update account set balance=balance-500 where id=1; 
   update account set balance=balance+500 where id=2;
   

   

4. B 窗口查询账户

   select * from account;
   

   

5. A 窗口回滚

   rollback;
   

   

6. B 窗口查询账户，钱没了

脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电话给李四说钱已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。

解决脏读的问题：将全局的隔离级别进行提升

将数据进行恢复：
UPDATE account SET balance = 1000;

1. 在 A 窗口设置全局的隔离级别为 read committed

set global transaction isolation level read committed;

B 窗口退出 MySQL，B 窗口再进入 MySQL

AB 窗口同时开启事务

1. A 更新 2 个人的账户，未提交

update account set balance=balance-500 where id=1;
update account set balance=balance+500 where id=2;

1. B 窗口查询账户

A 窗口 commit 提交事务

1. B 窗口查看账户

结论：read committed 的方式可以避免脏读的发生

不可重复读的演示

将数据进行恢复：
UPDATE account SET balance = 1000;

1. 开启 A 窗口

set global transaction isolation level read committed;

1. 开启 B 窗口，在 B 窗口开启事务

start transaction; select * from account;

1. 在 A 窗口开启事务，并更新数据

start transaction;
update account set balance=balance+500 where id=1;
commit;

1. B 窗口查询

select * from account;

两次查询输出的结果不同，到底哪次是对的？不知道以哪次为准。 很多人认为这种情况就对了，无须困惑， 当然是后面的为准。我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户，结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。

解决不可重复读的问题：

将全局的隔离级别进行提升为：repeatable read
将数据进行恢复：
UPDATE account SET balance = 1000;

1. A 窗口设置隔离级别为：repeatable read

set global transaction isolation level repeatable read;

1. 
2. B 窗口退出 MySQL，B 窗口再进入 MySQL

start transaction; select * from account;

1. A 窗口更新数据

start transaction;
update account set balance=balance+500 where id=1; commit;

1. B 窗口查询

select * from account;


结论：同一个事务中为了保证多次查询数据一致，必须使用 repeatable read 隔离级别

幻读的演示

在 MySQL 中无法看到幻读的效果。
但我们可以将事务隔离级别设置到最高，以挡住幻读的发生 将数据进行恢复：
UPDATE account SET balance = 1000;

1. 开启 A 窗口

set global transaction isolation level serializable; -- 设置隔离级别为最高

1. A 窗口退出 MySQL，A 窗口重新登录 MySQL

start transaction;
select count(*) from account;

1. 再开启 B 窗口，登录 MySQL
2. 在 B 窗口中开启事务，添加一条记录

start transaction; — 开启事务
insert into account (name,balance) values ('LaoWang', 500);

1. 在 A 窗口中 commit 提交事务，B 窗口中 insert 语句会在 A 窗口事务提交后立马运行

1. 在 A 窗口中接着查询，发现数据不变

select count(*) from account;

1. B 窗口中 commit 提交当前事务

1. A 窗口就能看到最新的数据

结论：使用 serializable 隔离级别，一个事务没有执行完，其他事务的 SQL 执行不了，可以挡住幻读