Navicat 环境测试 innodb 的事务隔离级别产生的幻读和不可重复读

原创

猎户星座1

修改于 2020-10-21 10:13:15

1.9K0

修改于 2020-10-21 10:13:15

文章被收录于专栏：Java Study

自己被事务隔离级别及产生的错误烦扰太多次了，最近在集中学mysql 要解决一下，mysql 的问题并重视起来。所以记录一下实验的过程：

话不多说：

实验环境：自己的是本机mysql 8.0 使用Navicat 15 窗口来进行会话实验。

实验准备：

一：查看自己的innodb 的隔离级别：

  select @@global.transaction_isolation;           // 用Navicat  开一个查询窗口 允许命令
  或：
  show global variables;                          // 查看你数数据库的全局变量配置  然后找到隔离级别 transaction_isolation

运行结果：

当前事务的隔离级别为可重复读

二：然后准备一张表：随便你自己正在用的一张表就行，没有什么特殊的要求，毕竟隔离级别对什么样结构的表都是适用的。

我的实验表结构如下：

CREATE TABLE `content`  (
  `tid` int NOT NULL AUTO_INCREMENT,
  `t_content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  `ip_address` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `timeStamp` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`tid`) USING BTREE
)ENGINE = InnoDB AUTO_INCREMENT = 20036 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

三：设置事务不自动提交

set autocommit = 0;        // 将自动提交设置为0 不进行自动提交 自动提交将看不到效果 最好两个事务都开启

那么我们首先测试一下可重复的读的隔离级别下是否会出现不可以重复读的情况

分为两个session 1 session 2 实验环境下我都设置自动提交未0 为了清晰。

session 1

begin;
SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1';

查询结果：和开始一样

session 2 执行一个更新语句

begin
UPDATE   content set t_content = '测试可重复读1' WHERE ip_address= '0:0:0:0:0:0:0:1'

结果: 执行成功影响了4 行

session2 要执行 commit 提交事务，如果我们不提交就变成测试脏读和隔离级别应该设置成未提交读

此时我们在session1 下再执行开始的查询语句，这时候我们就要注意，如果返回的数据和我们起初一样那就说，我们的session1执行事务过程中，没有看到其他事务提交后的结果，也就是可重复读，我们读取到的还是我们事务开始时的视图。相反如果数据不一致就导致了不可重复读，查询到的是更新后的视图。

SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1';

结果返回：和开始查询到的数据是一致的，因此隔离事务是对的没有产生我们怀疑的不可重复读。

session1 此时如果提交事务再查询查询到的就是我们修改后的数据，因为刚刚那个事务已经进行提交了。

seesion1 commit 后执行

SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1';  的数据

测试可重复读（隔离级别）

的执行流程如下，相当的简单，但能够反映问题

session1 的语句执行流程

1
begin
2
SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1';

5
SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1';

COMMIT

session2 的语句执行流程

1
BEGIN

3
UPDATE   content set t_content = '测试可重复读1' WHERE ip_address= '0:0:0:0:0:0:0:1'
4
commit

‘

测试读提交（隔离级别）

那么如果我们修改innodb 的事务隔离级别测试一下结果是否还是这样：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;           // 修改innodb 的隔离级别
 
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED ;            // 修改为读提交

重复上面的步骤：就可以看到，当seesion1 最后一次查询时，原本在可重复读的条件下的数据和开始时一样，但此时发生了变化。说明隔离级别发生了变化。

踩坑提示，修改完数据库的隔离级别后，重新打开连接一下数据库，再测试更佳。

sql 的执行过程和上面的一致，参照

测试幻读的出现（现象）

调回隔离级别为可重复读一般出现的为插入操作也就是说，我们正常的update 和delete 操作并不会产生那说明 insert和更新和删除是不一样的，通过可重复读的隔离级别可以知道，是通过mvvc 和在事务执行前一个创建的视图来进行的，delete 和 update 都是对视图上已经有的进行操作，而拆入是对视图上没有的进行操作，所以会不一样。

此时我们

session1 执行：

begin
SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1';

session2 执行：

BEGIN
INSERT into content VALUES (249,'xxafa','0:0:0:0:0:0:0:1','2020年08月11日01时32分01秒')
commit

session1 执行： ***

SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1';

但此时并没有发生我们期待的发生，此时的查询结果还是和开始的一样。

通过看别人的文章发现，现象是对的，需要我们在session1 中执行一次更新或者删除操作，会发现影响到的是比之前多一行的，

比如本来4行，session 2插入了一行现在就是5 行，但在*** 处我们看到的就是4 行，说明insert 是已经插入成功的。

看到别人的做法后继续操作

session1 ：

UPDATE   content set t_content = '测试幻读’' WHERE ip_address= '0:0:0:0:0:0:0:1'

结果：发现是影响了5行：

我们此时再查询一边：

SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1';

结果：发现此时查询到的为5行，说明还是造成了幻读。但是再经历了一次 dml 操纵后

session1 执行过程：

1
begin
2
SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1';

5
SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1';

6
UPDATE   content set t_content = '测试幻读' WHERE ip_address= '0:0:0:0:0:0:0:1'

7
SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1';

COMMIT

session2执行过程

1
BEGIN

3
INSERT into content VALUES (249,'xxafa','0:0:0:0:0:0:0:1','2020年08月11日01时32分01秒')

4
commit

还有既然默认的隔离会出现幻读的问题，那么我们怎么解决呢？

通过加上排他锁也就是写锁来解决幻读的问题

我们尝试上面的流程：

session1


begain 
SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1'  for UPDATE;

加上写锁

session2

begin
INSERT into content VALUES (245,'xxafa','0:0:0:0:0:0:0:1','2020年08月11日01时32分01秒')   // 当我们执行这条语句时 当前线程会被阻塞住， 现象如下

只有当 session1 的事务提交后才会不堵塞，这条插入语句（当然dml 操做都不行）才能被执行了。也就是加读锁后对要操作的这行会人工了进入到了串行化的步骤，因此，不会出现幻读的问题。

想做这次实验是看到这个前辈的做的实验：还有最后的这个幻读也是参考它做的

https://zhuanlan.zhihu.com/p/103580034?utm_source=wechat_session

网上的这个也讲的不错，

https://blog.csdn.net/zhangvalue/article/details/88527378

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

云数据库 MySQL

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

云数据库 MySQL

登录后参与评论

0 条评论

热度