保姆级实战验证Mysql InnoDB四种事物隔离级别

概念介绍

事物：用来保证一组操作，要么全部成功要么全部失败
隔离：因为在高并发情况下大概率会出现多个事物同时操作同一个数据，如果事物之间不进行隔离可能会出现意想不到的问题

InnoDB四种事物隔离级别

针对并发情况下的多事物之间互相影响的问题Mysql提供了四种事物隔离级别，分别如下:

• 读未提交（READ_UNCOMMITTED）

  允许读取其他事物未提交的写操作；
  因此如果其他事物做了回滚那么对于当前事物来说读到的数据就是脏数据

• 读已提交（READ_COMMITTED）

  只允许读取其他事物已提交的操作；
  在同一个事物内多次读取到的数据不能保持一致，也就是不可重复读

• 可重复读（REPEATABLE_READ）

  只能读取事物开启时的数据状态
  该隔离级别下可以保证每次读到的数据是一致的，但是如果事物内要插入却会出现主键冲突

• 序列化（SERIALIZABLE）

  最高级隔离，事物操作串行化执行，不能最大化利用数据库资源，吞吐量很低

实战

1. 数据准备

## 首先创建一张企业账户表
create table corp_account
(
    id int not null,
    account_name varchar(20) not null comment '账户名',
    amount decimal default 0.0 comment '账户余额',
    constraint corp_account_pk
        primary key (id)
)
    comment '企业账户表';
## 插入一条数据
INSERT INTO test.corp_account (id, account_name, amount) VALUES (1, 'cocoo', 100);

2. 验证各隔离级别下脏读、不可重复读和幻读的问题

2.1 读未提交：READ-UNCOMMITTED

## 开启一个事物:A
START TRANSACTION;
    ## 修改amount为90
    update corp_account set amount = 90 where id = 1;

    ## 此时本事物内查询amount为90
    select * from corp_account where id = 1;
    
    ## 不提交该事物

rollback ; ## 这句暂不执行

在事物A没提交的前提下，新开一个数据库连接的session

## 设置本session的事物隔离级别为读未提交
set tx_isolation = 'READ-UNCOMMITTED';

## 查询本session的事物隔离级别，确认事物隔离级别是否修改成功
SELECT @@tx_isolation;

## 开启一个新事物:B
START TRANSACTION;
    ## 查询id为1的记录，显示amount=90，说明读取到了事物A未提交的数据
    ## 此时如果事物A执行回滚(rollback)，事物B拿到的这个90就是脏数据了
    select * from corp_account where id = 1;

总结：读未提交会出现脏读的问题

2.2 读已提交: READ-COMMITTED

## 开启一个事物:A
START TRANSACTION;
    ## 修改amount为90
    update corp_account set amount = 90 where id = 1;

    ## 此时本事物内查询amount为90
    select * from corp_account where id = 1;
    
commit ; ## 这句暂不执行

同样的在事物A没提交的前提下，新开一个数据库连接的session

## 设置本session的事物隔离级别为读已提交
set tx_isolation = 'READ-COMMITTED';

## 查询本session的事物隔离级别，确认事物隔离级别是否修改成功
SELECT @@tx_isolation;

## 开启一个新事物:B
START TRANSACTION;
    ## 查询id为1的记录，显示amount=100，说明未读取到了事物A未提交的数据，
    ## 解决了读未提交中的脏读问题
    select * from corp_account where id = 1;

    ## 事物A执行提交(commit)后，再来查询会发现amount=90,说明读取到了事物A提交的变更
    select * from corp_account where id = 1;
    
    ## 此时我们会发现在同一个事物内两次读取同一条数据，结果不样：
    ## 第一次读取amount=100,第二次读取变成90了，不满足数据库事物ACID特性的数据一致性(consistency)，即不可重复读

总结：读已提交虽然解决了读未提交中的脏读问题，但是会出现不可重复读的问题

2.3 可重复读：REPEATABLE-READ

## 开启一个事物:A
START TRANSACTION;
    ## 修改amount为90
    update corp_account set amount = 90 where id = 1;

    ## 此时本事物内查询amount为90
    select * from corp_account where id = 1;
    
commit ; ## 这句暂不执行

同样的在事物A没提交的前提下，新开一个数据库连接的session

## 设置本session的事物隔离级别为可重复读
set tx_isolation = 'REPEATABLE-READ';

## 查询本session的事物隔离级别，确认事物隔离级别是否修改成功
SELECT @@tx_isolation;

## 开启一个新事物:B
START TRANSACTION;
    ## 1. 查询id为1的记录，显示amount=100，说明未读取到了事物A未提交的数据，
    ## 解决了脏读问题
    select * from corp_account where id = 1;

    ## 2. 事物A执行提交(commit)后，再来查询会发现amount还是100,说明无法读取到了事物A提交的变更
    ## 解决了不可重复读的问题
    select * from corp_account where id = 1;
    
    ## 3. 这里会有一个疑问，既然读取不到其他事物已提交的修改，
    ## 那如果本事物也执行更新操作不是会覆盖前一个事物已提交的数据吗？
    ## 假设你的更新逻辑是先select amount，然后在程序里计算出一个具体的值，再作为参数传递给update，那确实会出现覆盖的情况，所以只需向下面这样更新就没问题了
    update corp_account set amount = amount - 10
    ## 4. 这是因为RR级别解决不可重复读的问题只是针对select *这种查询使用了快照读，而对于udpate、delete、insert这类操作使用的当前读。而幻读就是因为写操作的当前读导致了RR隔离级别无法解决

RR隔离级别重现幻读

## 开启一个事物:A
START TRANSACTION;
    ## 新增一条id为2的数据
    INSERT INTO test.corp_account (id, account_name, amount) VALUES (2, 'google', 1000);
    
commit ; ## 待事物B开启后提交

同样的在事物A没提交的前提下，新开一个数据库连接的session

## 设置本session的事物隔离级别为可重复读
set tx_isolation = 'REPEATABLE-READ';

## 查询本session的事物隔离级别，确认事物隔离级别是否修改成功
SELECT @@tx_isolation;

## 开启一个新事物:B
START TRANSACTION;
    ## 1. 提交事物A
    ## 2. 查询id为2的记录，从上面那个例子我们已经知道此时是查不到。因为事物B开启的时候事物A还没提交。
    ## 3.1 这里页插入一条id为2的记录，下面的insert 回报主键冲突，select查不到insert却又冲突，这不就是像幻觉一样吗
    INSERT INTO test.corp_account (id, account_name, amount) VALUES (2, 'google', 1000);
    ## 3.2 我们还可以成功update事物A提交的那条ID为2的数据。很魔幻，查询不到却可以修改，这就是幻读
    update test.corp_account set amount = 900 where id = 2;

总结：可重复读虽然解决了读已提交中的不可重复读的问题，但是会出现幻读

2.4 序列化：SERIALIZABLE

新开一个事物：A，修改id为1的账户金额为80，但暂时不提交

start transaction ;
    update corp_account set amount = 80 where id = 1;

    select * from corp_account where id = 1;
commit ; # 暂不提交

同样的在事物A没提交的前提下，新开一个数据库连接的session

# 设置当前session的事物隔离级别为serializable
set tx_isolation = 'serializable';

# 确认是否设置成功
select @@tx_isolation;

# 开启新的事物：B，
start transaction;
    select * from corp_account where id = 1;

commit ;

可以看到我们的事物B只是查询Id为1的这条记录，来看下执行结果：

可以看到事物B里的执行逻辑被阻塞了；接着我们把事物A提交了再看下事物B的执行结果：

此时查询出的结果刚好是事物B提交的，这个时候如果事物B不提交，再次执行事物A，会发现事物A也同样会发生阻塞

总结：两个事物同时操作一个数据，只要有一个事物的隔离级别是序列化就会发生阻塞：前一个事物未提交，其他事物操作(无论是读还是写)会进入阻塞状态，需要等前一个事物提交后才能执行。

**以上没有主动设置事物隔离级别的回话都默认使用了InnoDB数据库默认的隔离级别-可重复读！**