MySQL脏读、幻读、不可重复读问题

建测试表和数据

create table show_me_problem
(
	id int auto_increment,
	price int null,
	location varchar(256) null,
	constraint show_me_problem_pk
		primary key (id)
)
comment 'mysql并发问题演示表';

insert into show_me_problem values (1,1,'第一条');
insert into show_me_problem values (2,2,'第二条');
insert into show_me_problem values (3,3,'第三条');

我的mysql版本是：8.0.27

回顾隔离级别和并发问题

四种隔离级别：

• 读未提交：最低的隔离级别，会产生脏读、幻读、不可重复读问题。
• 读已提交：解决脏读问题，仍会产生幻读、不可重复读问题。
• 可重复读：解决不可重复读问题，仍会产生幻读问题。（MYSQL默认隔离级别）
• 串行化：读写不允许并发，效率较低。

三种问题描述：

• 脏读：指的是事务A，B交替进行，A在事务进行中读到了B未提交的数据。
• 幻读：指的是事务A，B交替进行，A事务在进行 先查询后新增 过程中，受B事务影响导致幻读数据，致使新增操作不成功问题。
• 不可重复读：一个 事务 范围内两个相同的查询却返回了不同数据。

脏读

如图：

1. 连接本地数据库，开启两个SQL窗口左右两边都执行如下语句，开启 读未提交隔离级别，并分别开启事务。

   set transaction isolation level read uncommitted ;
   start transaction;
   

2. 左边窗口执行查询语句，可以看到结果如下：

3. 此时，左边不要执行 commit ,执行右边的修改语句，将id为1的数据的price属性修改成222，右边事务也不要commit。

4. 此时再次执行左边的查询语句，可见结果如下：

   

5. 分析可知，我左边的事务并没有完成，右边的事务也没有完成，但是数据产生了相互影响，左边脏读到了右边没有提交的事务数据。

附上：

A窗口语句：

-- 读未提交
set transaction isolation level read uncommitted ;
start transaction;

select id, price, location
from show_me_problem
where id = 1;

rollback;
commit;

B窗口语句

-- 读未提交
set transaction isolation level read uncommitted ;
start transaction;

update show_me_problem
set price = 222
where id = 1;

rollback;
commit;

1. 切换读已提交这个隔离级别查看是否解决了脏读问题。这里还是先执行如下语句切换 读已提交隔离级别 ，和分别开启事务。

   set transaction isolation level read committed ;
   start transaction;
   

2. 首先先执行左边的查询语句结果如下：

   

3. 此时，左边不要执行 commit ,执行右边的修改语句，将id为1的数据的price属性修改成333，右边事务也不要commit。

4. 执行左侧查询语句可见：

   

5. 结果还是和上面一样，说明为提交的中间状态数据不会被脏读出来。

幻读

1. 切换可重复读隔离级别，并且分别开启事务。

   set transaction isolation level repeatable read ;
   start transaction;
   

2. 下面开始演示幻读 说一下演示思路：

3. A窗口 查询 id=4 的数据是否存在了。此时应该是没有的。A事务进行中

   

4. B窗口 插入一条id=4 的数据，并且提交事务。此时B窗口查询数据是可以查询id=4的数据是可以查询到的。

5. A窗口 再次进行查询确认是否有 id=4 数据了，查询出来还是不存在，此时A执行插入语句，但是会失败！

附上：

A窗口语句：

-- 可重复读
set transaction isolation level repeatable read ;
start transaction;

select id, price, location
from show_me_problem
where id = 4;



insert into show_me_problem values (4,1,'第四条');

rollback;
commit;

B窗口语句

-- 可重复读
set transaction isolation level repeatable read ;
start transaction;

insert into show_me_problem values (4,1,'第四条');

rollback;
commit;

不可重复读

1. 切换 读已提交隔离级别，并且分别开启事务。

   set transaction isolation level read committed ;
   start transaction;
   

2. 执行左边窗口的查询，结果如下：

   

3. 此时执行右侧更新语句，同时提交事务。此时状态是右侧执行了，并且提交了，左侧还在进行中，还未提交。

4. 左侧窗口再次执行查询语句，结果如下：

   

5. 可见，在同一个事务中，两次相同查询，竟然查询出不同结果，这就是不可重复读问题。

附上：

A窗口语句：

-- 读已提交
set transaction isolation level read committed ;
start transaction;

select id, price, location
from show_me_problem
where id =1;


rollback;
commit;

B窗口语句

-- 读已提交
set transaction isolation level read committed ;
start transaction;

update show_me_problem set price='333' where id = 1;

rollback;
commit;