前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Navicat 环境测试 innodb 的事务隔离级别 产生的幻读 和 不可重复读

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 的隔离级别:

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

运行结果:

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

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

就把他当成你现在数据库里的表就行
就把他当成你现在数据库里的表就行

我的实验表结构如下:

代码语言:javascript
复制
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;

三:设置事务不自动提交

代码语言:javascript
复制
set autocommit = 0;        // 将自动提交设置为0 不进行自动提交 自动提交将看不到效果 最好两个事务都开启

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

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

session 1

代码语言:javascript
复制
begin;
SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1'; 

查询结果: 和开始一样

session 2 执行 一个更新语句

代码语言:javascript
复制
begin
UPDATE   content set t_content = '测试可重复读1' WHERE ip_address= '0:0:0:0:0:0:0:1'  

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

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

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

代码语言:javascript
复制
SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1'; 

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

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

seesion1 commit 后执行

代码语言:javascript
复制
SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1';  的数据 

测试可重复读(隔离级别)

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

session1 的语句执行流程

代码语言:javascript
复制
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 的语句执行流程

代码语言:javascript
复制
1
BEGIN

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

测试读提交 (隔离级别)

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

代码语言:javascript
复制
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;           // 修改innodb 的隔离级别
 
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED ;            // 修改为读提交 

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

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

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

测试幻读的出现(现象)

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

此时我们

session1 执行:

代码语言:javascript
复制
begin
SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1';

session2 执行:

代码语言:javascript
复制
BEGIN
INSERT into content VALUES (249,'xxafa','0:0:0:0:0:0:0:1','2020年08月11日01时32分01秒')
commit

session1 执行: ***

代码语言:javascript
复制
SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1';

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

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

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

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

session1 :

代码语言:javascript
复制
UPDATE   content set t_content = '测试幻读’' WHERE ip_address= '0:0:0:0:0:0:0:1'

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

我们此时再查询一边:

代码语言:javascript
复制
SELECT * FROM content  where ip_address = '0:0:0:0:0:0:0:1';

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

session1 执行过程:

代码语言:javascript
复制
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执行过程

代码语言:javascript
复制
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

代码语言:javascript
复制

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

加上写锁

session2

代码语言:javascript
复制
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 删除。

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

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

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 实验环境: 自己的是本机mysql 8.0 使用Navicat 15 窗口来进行会话实验。
  • 实验准备:
  • 测试可重复读(隔离级别)
  • 测试读提交 (隔离级别)
  • 测试幻读的出现(现象)
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档