
create database if not exists bilibili; #创建数据库bilibili
use bilibili; #选择bilibili数据库，即可进行下面的代码演示
drop database if exists bilibili; #代码演示结束后删除bilibili数据库。这个小文件全部代码演示完再删
show databases;  #查看是否删除完成

-- -----------------------------------事务----------------------------------------------------

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);

-- 恢复数据。可随时恢复建表时的数据,有助于我们的代码演示
update account set money = 2000 where name = '张三' or name = '李四';

-- 正常转账操作如下 -- 张三给李四转账1000
#第一步:查询张三账户余额
select * from account where name = '张三';
#第二步:将张三账户余额减1000。即写的是更新语句
update account set money = money - 1000 where name = '张三';
#第三步:将李四账户余额加1000
update account set money = money + 1000 where name = '李四';

-- 异常转账操作如下 -- 张三给李四转账1000。由于模拟故障会有报红线，所以我们多行注释了，你们演示的时候解开就行了，不影响代码，就是看着难受
#第一步:查询张三账户余额
/*select * from account where name = '张三';
#第二步:将张三账户余额减1000。即写的是更新语句
update account set money = money - 1000 where name = '张三';
... #在SQL语句面板中，如果出现非SQL语句就会出现报错，刚好使用这个报错来模拟一下故障
#第三步:将李四账户余额加1000
update account set money = money + 1000 where name = '李四';*/ #自己解开注释，选中29~34行运行一下，直观的感受故障

# 选中上面那堆异常转账操作的代码，会发现张三减了1000，但是李四没有加1000
# 如果要解决这种问题，我们就需要把转账操作控制在一个事务的范围内，我们当前是有事务的，每一条SQL就是一个事务，
# 当一条SQL执行完之后，就会自动提交事务，然后就马上更新数据库数据。我们如何控制事务呢，有两种方式，如下

# 【第一种方式】

/*1. 查看/设置事务提交方式
select @@autocommit; #查看事务的提交方式，如果为1就是自动提交，为0就是手动提交
set @@autocommit = 0; #将事务的提交方式改为手动提交。可放心设置，只对当前的console窗口有效，不影响其他窗口
2. 提交事务
commit;
3. 回滚事务
rollback;*/

# 具体代码如下
select @@autocommit; #查询的结果是1，则此时事务的提交方式是自动提交
set @@autocommit = 0; #将事务的提交方式改为手动提交
# 然后去执行我们的故障代码，在上面的29~34行，被我注释了，自己解开运行一次
# 会发现我们的account表的数据并没有发生变化，保护了我们的数据没被故障影响
# 对于错误的操作，我们需要回滚事务，如下
rollback;
# 如果是正确的操作，例如执行最上面21~25行的代码，我们的数据也不会发生变化，需要我们执行commit手动提交，才会变化，如下
commit;

# 事务的原理: 当我们开启了手动提交事务之后，在当前这个console窗口执行的一切SQL语句都是临时修改了表中的数据，
# 表中的数据不会发生改变，直到我们运行commit才会把临时修改的数据同步到我们的数据库

# 【第二种方式】
# 在不修改用户提交方式的前提下，如何控制事务，如下

/*1. 开启事务
start transaction;
或
begin;
2. 提交事务
commit
3. 回滚事务
rollback;*/

# 具体代码如下
# 由于上面我们把事务提交方式改为了手动提交，我们现在要改回来
set @@autocommit = 1; #将事务的提交方式改为默认的自动提交
start transaction; #开启事务
# 然后去执行我们的故障代码，在上面的29~34行，被我注释了，自己解开运行一次
# 会发现我们的account表的数据并没有发生变化，此时我们就回滚事务，恢复至执行故障代码之前的数据
rollback;
# 如果是正确的操作，例如执行最上面21~25行的代码，我们的数据就会直接发生变化


-- -----------------------------------事务隔离级别----------------------------------------------------


/*事务隔离级别解决上节课的事务并发问题，例如解决脏读、不可重复读、幻读。√表示会出现问题，×表示不会出现问题
对于MySQL的默认隔离级别是repeatable read

隔离级别                     脏读         不可重复读           幻读
read uncommitted            √              √                √
read committed              ×              √                √
repeatable read(默认)        ×              ×                √
serializable                ×              ×                ×

read uncommitted的隔离级别最低，数据安全性差，但性能最高
serializable的隔离级别最高，但性能最差

查看事务隔离级别
select @@transaction_isolation;

设置事务隔离级别
set [session | global] transaction isolation level [read uncommitted | read committed | repeatable read | serializable];

@@表示查看系统变量信息
session表示会话级别，作用是对于当前客户端窗口有效
global表示全局级别，作用是对于所有客户端窗口有效*/

-- 查看事务隔离级别
select @@transaction_isolation; #查询结果是REPEATABLE-READ

-- 设置事务的隔离级别为read uncommitted
set session transaction isolation level read uncommitted;

-- 设置事务的隔离级别为默认的repeatable read
set session transaction isolation level repeatable read;

-- -----------------------------------事务隔离级别演示--------------------------------------------------

-- 由于需要模拟两个事务达到并发效果，为了演示更加方便和直观，此时我们使用电脑自带的命令行来演示
-- 下面打开两个命令行窗口，模拟两个客户端，也就是两个并发事务
-- 操作bilibili数据库、account表。并且我们在最上面已经有这些数据了，所以直接用

win+R, cmd, 回车, 为了区分窗口就叫这个窗口为Server
mysql -u root -p
'输入自己的mysql密码'
use bilibili;
set @@autocommit = 1;#演示之前先确保提交方式是自动提交
#1.演示read uncommitted隔离级别(演示脏读)
set session transaction isolation level read uncommitted;#设置隔离级别
select * from account;#查看数据，初始数据
start transaction;#开启事务
select * from account;#查看数据，然后去Client窗口执行update
select * from account;#查看数据，会发现数据变了，由于Client执行了update，但是Client没有commit提交，Server读取到了Client未提交的数据就称为脏读
commit;#演示完一个小知识点之后，要运行这行清理一下缓冲区
#2.演示read committed隔离级别(演示脏读)
set session transaction isolation level read committed;#设置隔离级别
start transaction;#开启事务
select * from account;#查看数据，然后去Client窗口执行update
select * from account;#查看数据，会发现数据没有变化，即不出现脏读。然后去Client窗口执行commit，表示提交
select * from account;#查看数据，会发现数据才发生变化。只有去Client窗口执行commit执行事务，数据才会更新到数据库，规避了脏读的出现
commit;#演示完一个小知识点之后，要运行这行清理一下缓冲区
#3.演示read committed隔离级别(演示不可重复读)
set session transaction isolation level read committed;#设置隔离级别
start transaction;#开启事务
select * from account;#查看数据，然后去Client窗口执行update
select * from account;#查看数据，会发现数据没有变化。只有当Client窗口提交事务，数据才会更新到数据库。然后去Client窗口执行commit，表示提交
select * from account;#查看数据，会发现数据才发生变化。这条查询语句和上面那条相同，但是查询的结果不同，就称为不可重复读
commit;#演示完一个小知识点之后，要运行这行清理一下缓冲区
#4.演示repeatable read隔离级别(演示不可重复读)
set session transaction isolation level repeatable read;#设置隔离级别
start transaction;#开启事务
select * from account;#查看数据，然后去Client窗口执行update
select * from account;#查看数据，当Client窗口执行了commit之后，这行的数据依旧不会变，解决了不可重复读
commit;#当我们commit之后，再在下一行select查询，才会看到数据变化
select * from account;#查看数据
commit;#演示完一个小知识点之后，要运行这行清理一下缓冲区
#5.演示repeatable read隔离级别(演示幻读)
set session transaction isolation level repeatable read;#设置隔离级别
start transaction;#开启事务
select * from account where id = 3;#第一次查询。回车会发现empty查询为空，因为我们并没有id为3的数据。然后去Client窗口insert添加这个数据
select * from account where id = 3;#Client窗口添加数据并提交之后，就会发现这行的查询还是为空(实际上在数据库是有的)，我们假如在下一行去添加id为3的数据
insert into account(id,name,money) values(3,'王兵',2000);#插入数据，会报错，说已有id为3的数据，但是我们在下一行再查询一下
select * from account where id = 3;#查询结果依旧为空，这种现象就称为幻读。实际数据库是有这条数据的，因为Client窗口确实添加了这条数据并提交了
commit;#演示完一个小知识点之后，要运行这行清理一下缓冲区
#6.演示serializable隔离级别(演示幻读)
set session transaction isolation level serializable ;#设置隔离级别
start transaction;#开启事务
select * from account where id = 4;#第一次查询。回车会发现empty查询为空，因为我们并没有id为4的数据。然后去Client窗口insert添加这个数据
#会发现由于我们在account表开启了事务，并且是serializable隔离级别，所以此时Client窗口是不能修改account表的，也就无法添加数据，只有我们
#的Server窗口才能添加数据，如下
insert into account(id,name,money) values(4,'小明',2000);
commit;#当我们commit提交之后，Client窗口才可以正常对account表插入数据。如果Client窗口在我这行的commit之前就插入了数据，那么那条
#插入数据的SQL语句就会堵塞，当我们在上一行执行了commit之后，那条堵塞的语句才会被执行
commit;#演示完一个小知识点之后，要运行这行清理一下缓冲区

win+R, cmd, 回车, 为了区分窗口就叫这个窗口为Client
mysql -u root -p
'输入自己的mysql密码'
use bilibili;
set @@autocommit = 1;#演示之前先确保提交方式是自动提交
#1.演示read uncommitted隔离级别(演示脏读)
start transaction;#开启事务
update account set money = money - 1000 where name = '张三';#更新数据，然后去Server窗口执行select
commit;#演示完一个小知识点之后，要运行这行清理一下缓冲区
#2.演示read committed隔离级别(演示脏读)
start transaction;#开启事务
update account set money = money - 1000 where name = '张三';#更新数据，然后去Server窗口执行select
commit;#提交之后，去Server窗口执行select
commit;#演示完一个小知识点之后，要运行这行清理一下缓冲区
#3.演示read committed隔离级别(演示不可重复读)
start transaction;#开启事务
update account set money = money + 1000 where name = '张三';#更新数据，然后去Server窗口执行select
commit;#提交之后，去Server窗口执行select
commit;#演示完一个小知识点之后，要运行这行清理一下缓冲区
#4.演示repeatable read隔离级别(演示不可重复读)
start transaction;#开启事务
update account set money = money + 1000 where name = '张三';#更新数据，然后去Server窗口执行select
commit;#提交之后，去Server窗口执行select
commit;#演示完一个小知识点之后，要运行这行清理一下缓冲区
#5.演示repeatable read隔离级别(演示幻读)
insert into account(id,name,money) values(3,'王五',2000);#插入数据，并且在下面那行提交
commit;#提交之后去Server窗口执行select
commit;#演示完一个小知识点之后，要运行这行清理一下缓冲区
#6.演示serializable隔离级别(演示幻读)
insert into account(id,name,money) values(4,'小明',2000);#插入数据，但是回车之后没反应，原因是Server窗口在操作account表，导
#致这个Client窗口无法操作account表，也就是上面那行的代码被堵塞了，只有当Server窗口执行了commit之后，上面那行代码才会正常执行，这样就
#解决了幻读的问题出现
commit;#演示完一个小知识点之后，要运行这行清理一下缓冲区

-- 总结:
#还没commit就查到数据，就称为脏读。commit之后才正常查到数据就称为不可重复读
#当Client窗口commit提交了新数据之后，在Server窗口select查询却查不到，并且在Server窗口insert插入相同新数据也插入不了，就称为幻读
