MySQL数据库隔离详解（四种测试用例分析）

文章尾部附带数据表数据下载哟

一、事务的基本要素（ACID）

原子性（Atomicity）	事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
一致性（Consistency）	事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。
隔离性（Isolation）	同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
持久性（Durability）	事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

二、事务的并发问题

脏读	事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
不可重复读	事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
幻读	系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

三、MySQL事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

mysql 默认的事务隔离级别为repeatable-read

四，查看隔离级别

MySQL8 之前使用如下命令查看 MySQL 隔离级别：
查看默认的全局隔离级别和当前 session 的隔离级别

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

MySQL8 开始，通过如下命令查看 MySQL 默认隔离级别：

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;

更改当前隔离级别

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

五、用例子说明各个隔离级别的情况

1、读未提交

1）打开一个客户端A，并设置当前事务模式为read uncommitted（未提交读），查询表account的初始值：

 //修改隔离级别为未提交
 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 select * from tb_shop_type;

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表tb_shop_type

 start transaction;
 update tb_shop_type set name = '轰趴轰趴轰趴' where id = 9;

（3）这时，虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据：

（4）一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那之前的客户端A查询到的数据其实就是脏数据：

rollback;

2、读已提交

1）打开一个客户端A，并设置当前事务模式为read committed（读已提交），查询表tb_shop_type的所有记录：

 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 start transaction;
 select * from tb_shop_type;

2）在客户端A的事务提交之前，打开另一个客户端B，更新表tb_shop_type

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
select * from tb_shop_type;
start transaction;
update tb_shop_type set name = '轰趴123456' where id = 9;
select * from tb_shop_type;

（3）这时，客户端B的事务还没提交，客户端A不能查询到B已经更新的数据，解决了脏读问题：

（4）客户端B的事务提交

commit;
select * from tb_shop_type;

5）客户端A执行与上一步相同的查询，结果 与上一步不一致，即产生了不可重复读的问题。
同一个事务中两次读取操作，读到的数据不一致，因为在并发环境下，有别的会话修改了

select * from tb_shop_type;

3.可重复读

（1）打开一个客户端A，并设置当前事务模式为可重复读repeatable read，查询表user的所有记录

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
start transaction;
select * from tb_shop_type;

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表tb_shop_type并提交

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
select * from tb_shop_type;
start transaction;
update tb_shop_type set sort = sort -2 where id = 9;
commit;

（3）在客户端A第二次查询表tb_shop_type的所有记录，与步骤（1）查询结果一致，没有出现不可重复读的问题

（4）在客户端A，接着执行sort 更新操作，sort没有变成9-3=6
用的是步骤（2）中的7来算的，所以是7-3 = 4，数据的一致性倒是没有被破坏。

update tb_shop_type set sort = sort -3 where id = 9;
select * from tb_shop_type;

可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本）；
insert、update和delete会更新版本号，是当前读（当前版本）。

注：InnoDB 的MVCC（多版本并发控制），是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

SELECT

• InnoDB会根据以下两个条件检查每行记录：
• a. InnoDB 只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么在事务开始前已经存在的，要么是事务自身插入或者修改过的。
• b.行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

只有符合上述两个条件的记录，才能返回作为查询结果。
INSERT

• InnoDB 为新插入的每一行保存当前系统版本号作为行版本号。

DELETE

• InnoDB 为删除的每一行保存当前系统版本号作为行删除标识。

UPDATE

• InnoDB为插入一行新纪录，保存当前系统版本号，同时保存当前系统版本号到原来的行作为行删除标识。

MVCC 只在REPEATABLE 和 READ COMMITED 两个隔离级别下工作，其他两个隔离级别都和MVCC不兼容，因为READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务的数据行。而SERIALIZABLE则会对所有对所有读取的行都加锁。

（5）客户端B插入一条数据后提交

start transaction;
INSERT INTO `tb_shop_type` (`id`, `name`, `icon`, `sort`, `create_time`, `update_time`) VALUES ('20', '测试数据库隔离级别', '/types/hpg.png', 12, '2021-12-22 20:20:08', '2023-11-08 22:24:06');
commit;
select * from tb_shop_type;

（6） 在客户端A查询表account的所有记录，在客户端B修改前后，客户端A没有查出新增数据，所以没有出现幻读

（7）客户端B插入一条数据，并提交事务

start transaction;
INSERT INTO `tb_shop_type` (`id`, `name`, `icon`, `sort`, `create_time`, `update_time`) VALUES ('22', '大家加油哇', '/types/hpg.png', 12, '2021-12-22 20:20:08', '2023-11-08 22:24:06');
commit;
select * from tb_shop_type;

（8）客户端A修改表数据，并再次查询，出现了幻读

update tb_shop_type set sort = sort -3 where id = 22;
select * from tb_shop_type;

4.串行化

（1）打开一个客户端A，并设置当前事务模式Serializable,查询user表的初始值：\

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
start transaction;
select * from tb_shop_type;

（2）打开一个客户端B，并设置当前事务模式为serializable，插入一条记录报错，表被锁了插入失败，mysql中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到。

补充

1、事务隔离级别为读提交时，写数据只会锁住相应的行
　　2、事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。
　　3、事务隔离级别为串行化时，读写数据都会锁住整张表
　　 4、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。
　　 5、MYSQL MVCC实现机制参考链 接： 轻松理解MYSQL MVCC 实现机制_杨龙飞的博客的博客-CSDN博客_mvcc mysql 轻松理解MYSQL MVCC 实现机制

测试数据下载链接

https://www.aliyundrive.com/s/baSW2eByvJW

参考链接

MYSQL数据库事务的隔离级别（详解）
Mysql 的四种隔离级别