Mysql的隔离级别与脏读、幻读、不可重复读之间的关系

Mysql的隔离级别

MySQL数据库默认的隔离级别是可重复读(REPEATABLE READ)。

• READ UNCOMMITTED（读取未提交的数据）：一个事务可以读取另一个事务还未提交的数据，可能会导致脏读、不可重复读和幻读等问题。
• READ COMMITTED（读取已提交的数据）：一个事务只能读取另一个事务已经提交的数据，可以避免脏读问题，但是可能会出现不可重复读和幻读问题。
• REPEATABLE READ（可重复读）：一个事务在执行期间看到的数据是一致的，不会受到其他事务的影响，可以避免脏读和不可重复读问题，但是可能会出现幻读问题。
• SERIALIZABLE（串行化）：所有事务串行执行，可以避免脏读、不可重复读和幻读等问题，但是并发性非常低，可能会导致性能问题。

不同隔离级别所具备的能力

REPEATABLE READ

下面测试一下REPEATABLE READ下能否避免脏读

执行步骤

1. 执行事务一但是不提交
2. 执行事务二看能否读到事务一未提交的值

执行sql

注意两个Sql脚本文件一定要分开保存并执行，否则后一个事务的语句会影响前一个sql的语句

-- 第一个事务
-- 开启事务
START TRANSACTION;
-- 执行语句
UPDATE user set username = 'test' where id = 1;
-- 提交事务
COMMIT;
-- 回滚事务
-- ROLLBACK;

-- 测试结果的sql
select * from user where id = 1

-- 第二个事务
-- 开启事务
START TRANSACTION;
-- 执行语句
select * from user where id = 1;
-- 提交事务
COMMIT;
-- 回滚事务
-- ROLLBACK;

-- 测试结果的sql
select * from user where id = 1

测试结果

1. 在事务一脚本中执行：START TRANSACTION;UPDATE user set username = ‘test’ where id = 1;select * from user where id = 1可以看到数据被更改了
2. 在事务二脚本中执行：START TRANSACTION;select * from user where id = 1;COMMIT;发现读出来的还是原先的

下面测试一下REPEATABLE READ下能否避免不可重复读

执行步骤

1. 执行事务一到第一个select为止
2. 执行事务二COMMIT掉
3. 执行事务一到第二个select

执行sql

• 注意两个Sql脚本文件一定要分开保存并执行，否则后一个事务的语句会影响前一个sql的语句

-- 第一个事务
-- 开启事务
START TRANSACTION;
-- 执行语句
select * from user where id = 1;
-- 中间过程省略
-- 中间过程省略
-- 中间过程省略
-- 中间过程省略
select * from user where id = 1;
-- 提交事务
COMMIT;
-- 回滚事务
-- ROLLBACK;

-- 测试结果
select * from user where id = 1

-- 第二个事务
-- 开启事务
START TRANSACTION;
-- 执行语句
UPDATE user set username = 'test' where id = 1;
-- 提交事务
COMMIT;
-- 回滚事务
-- ROLLBACK;

-- 测试结果
select * from user where id = 1

测试结果

实验证明REPEATABLE READ下可以避免不可重复读

下面测试一下REPEATABLE READ下能否避免幻读

执行步骤

事务1	事务2
begin	begin
select * from user where id > 1;
	insert into user(id,name) values(5,‘hwx’)
	commit
select * from user where id > 1;
commit

执行Sql

• 注意两个Sql脚本文件一定要分开保存并执行，否则后一个事务的语句会影响前一个sql的语句
• 注意在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此,幻读在“当前读”下才会出现（查询语句添加for update，表示当前读）；
• FOR UPDATE：这部分语句是加锁的指令，用于确保在事务内对选定的数据行进行排他性的操作。通过使用FOR UPDATE，查询所得的记录将被锁定，该锁将持续到事务提交或回滚完成。 加锁的目的是为了防止其他并发事务对这些被选中的数据行进行修改，从而维护数据的一致性。

-- 第一个事务
-- 开启事务
START TRANSACTION;
-- 执行语句
select * from user2 where id >= 1;
-- 中间过程省略
-- 中间过程省略
-- 中间过程省略
-- 中间过程省略
select * from user2 where id >= 1;
-- 提交事务
COMMIT;
-- 回滚事务
-- ROLLBACK;

-- 测试结果
select * from user2 where id >= 1

-- 第二个事务
-- 开启事务
START TRANSACTION;
-- 执行语句
INSERT user2(id, username, userPassword) VALUES(1, 'hwx', '123456');
-- 提交事务
COMMIT;
-- 回滚事务
-- ROLLBACK;

-- 测试结果
select * from user2 where id >= 1

测试结果

测试证明REPEATABLE READ可以避免幻读，这是为什么呢？ 参考：MySQL 可重复读隔离级别，完全解决幻读了吗？_

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了），解决的方案有两种：

• 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
• 针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

这两个解决方案是很大程度上解决了幻读现象，但是还是有个别的情况造成的幻读现象是无法解决的。

第一个发生幻读现象的场景

第二个发生幻读的场景

除了上面这一种场景会发生幻读现象之外，还有下面这个场景也会发生幻读现象。

• T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id > 100 得到了 3 条记录。
• T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；
• T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select … for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录

幻读和不可重复读的区别

幻读和不可重复读的区别在于操作类型。幻读针对的是插入和删除操作，不可重复读针对的是修改操作。不可重复读在同一个事务内多次读取同一数据行，结果可能不一致；而幻读则是在同一个事务内多次执行相同的查询，结果集可能出现新增或删除的情况。这两种情况都是由于并发事务对数据库数据进行了修改或删除所导致的。

总结

1. 不管是脏读、不可重复读、幻读都会对业务执行产生影响，应该想办法避免。
2. 四种隔离级别从上到下，并发级别逐渐递减，执行准确性不断提升。mysql选择可重复读作为默认级别是有原因的。
3. 可重复读隔离级别通过快照读和当前读可以完全解决不可重复读问题，部分解决幻读问题，不过幻读的问题是可以通过设计规避的