用sql演示脏读，不可重复读，幻读

最新推荐文章于 2023-11-24 02:55:13 发布
最新推荐文章于 2023-11-24 02:55:13 发布
阅读量761 收藏 5
点赞数 2
分类专栏： Mysql
原文链接：https://blog.csdn.net/zzti_erlie/article/details/103199555
版权

介绍

要聊事务，不可避免的要提到数据库事务的四大特性

原子性（Atomic）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）

今天只聊隔离性，其他的比较好理解，建议看推荐阅读中关于事务的讲解。

先放一个表格，看看4个隔离级别会出现的各种问题，网上的解释一大堆。看完后还是一脸蒙蔽，感觉懂了，又好像没懂。因为没有具体的演示例子，索性自己尝试复现这几个问题，果然理解的清清楚楚。

√ 为会发生，×为不会发生

隔离级别脏读不可重复读幻读
read uncommitted（未提交读）√√√
read committed（提交读）×√√
repeatable read（可重复读）××√
serializable （可串行化）×××

先看MySQL版本，5.5.61，下文的所有实验在这个版本上都能浮现

select version()

 
 

 
 
  • 1

MySQL查看隔离级别

SELECT @@tx_isolation

 
 

 
 
  • 1

MySQL在会话层面设置隔离级别

set session transaction isolation level 隔离级别

 
 

 
 
  • 1

开启事务

start transaction

 
 

 
 
  • 1

提交事务

commit

 
 

 
 
  • 1

回滚事务

rollback

 
 

 
 
  • 1

建立如下表

CREATE TABLE `account` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `balance` int(3) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

 
 

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

用Navicat（其他工具也行）开2个查询的tab页，表示2个会话
在这里插入图片描述
在2个Tab页面分别执行如下语句

SELECT @@tx_isolation

 
 

 
 
  • 1

输入都是REPEATABLE-READ，表明MySQL默认隔离级别为REPEATABLE-READ（可重复）读。

脏读

表中的数据如下，设置隔离级别为未提交读
在这里插入图片描述
按照时间在2个Tab页依次执行如下命令

时间客户端A（Tab A）客户端B（Tab B）
T1set session transaction isolation level read uncommitted;
start transaction;（开启事务）
update account set balance = balance + 1000 where id = 1;
select * from account where id = 1;
设置为未提交读，给张三账号+1000，输出为2000
T2set session transaction isolation level read uncommitted;
start transaction;
select * from account where id = 1;
查询余额输出为2000
T3rollback
T4commit
T5select * from account where id = 1;
查询余额输出为1000

举个例子概述一下这个过程，财务给张三发了1000元的工资，然后张三查询自己的账户，果然多了1000元，变成了2000元，结果财务操作过程有误，事务回滚。当张三再查账户时，却发现账户只有1000元。

脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

再举一个严重的例子，证明一下危害
表中的数据如下
在这里插入图片描述

时间客户端A（Tab A）客户端B（Tab B）
T1set session transaction isolation level read uncommitted;
start transaction;
update account set balance = balance - 1000 where id = 1;
update account set balance = balance + 1000 where id = 2;
T2set session transaction isolation level read uncommitted;
start transaction;
select balance from account where id = 2;
update account set balance = balance - 1000 where id = 2;
更新语句被阻塞
T3rollback
T4commit

执行完成，数据库中的数据如下
在这里插入图片描述

时间解释
T11给2转1000
T22的余额够1000元，购买1000元的东西，更新语句被阻塞
T31回滚，1的余额变成1000，2的余额变成0
T42成功扣款，余额0-1000=-1000

现在好了，银行无缘无故损失1000元。

不可重复读

表中的数据如下，设置隔离级别为提交读
在这里插入图片描述

时间客户端A（Tab A）客户端B（Tab B）
T1set session transaction isolation level read committed;
start transaction;
select * from account where id = 2;
查询余额输出为0
T2set session transaction isolation level read committed;
start transaction;
update account set balance = balance + 1000 where id = 2;
select * from account where id = 2;
commit;
查询余额输出1000
T3select * from account where id = 2;
commit;
查询余额输出1000

不可重复读是指在事务1内，读取了一个数据，事务1还没有结束时，事务2也访问了这个数据，修改了这个数据，并提交。紧接着，事务1又读这个数据。由于事务2的修改，那么事务1两次读到的的数据可能是不一样的，因此称为是不可重复读。

当然你可以在T2时间段客户端B修改完id=2的账户余额但没有commit的时候，在客户端A查询id=2的账户余额，发现账户余额为0，可以证明提交读这个隔离级别不会发生脏读。

现在用上面的例子看一下可重复读是个什么过程？
表中的数据如下，设置隔离级别为可重复读
在这里插入图片描述

时间客户端A（Tab A）客户端B（Tab B）
T1set session transaction isolation level repeatable read;
start transaction;
select * from account where id = 2;
查询余额输出为0
T2set session transaction isolation level repeatable read;
start transaction;
update account set balance = balance + 1000 where id = 2;
select * from account where id = 2;
commit;
查询余额输出1000
T3select * from account where id = 2;
commit;
查询余额输出0

仔细看这个例子和上面的例子在T3时间段的输出，理解了什么叫可重复读了吧？当我们将当前会话的隔离级别设置为可重复读的时候，当前会话可以重复读，就是每次读取的结果集都相同，而不管其他事务有没有提交。

但是在可重复读的隔离级别上，会产生幻读的问题。

幻读

表中的数据如下，设置隔离级别为可重复读
在这里插入图片描述
先上一段《高性能MySQL》对于幻读的解释

所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。InnoDB存储引擎通过多版本并发控制（MVCC）解决了幻读的问题。

用大白话解释一下，就是事务1查询id<10的记录时，返回了2条记录，接着事务2插入了一条id为3的记录，并提交。接着事务1查询id<10的记录时，返回了3条记录，说好的可重复读呢？结果却多了一条数据。

MySQL通过MVCC解决了这种情况下的幻读，我们可以验证一下

时间客户端A（Tab A）客户端B（Tab B）
T1set session transaction isolation level repeatable read;
start transaction;
select count(*) from account where id <= 10;
输出2
T2set session transaction isolation level repeatable read;
start transaction;
insert into account (id, name, balance) values (3, “王五”, 0);
select count(*) from account where id <= 10;
commit;
输出3
T3select count(*) from account where id <= 10;
commit;
输出2

这种情况下的幻读被解决了，我再举一个例子

表中的数据如下
在这里插入图片描述

时间客户端A（Tab A）客户端B（Tab B）
T1set session transaction isolation level repeatable read;
start transaction;
select count(*) from account where id = 3;
输出为0
T2set session transaction isolation level repeatable read;
start transaction;
insert into account (id, name, balance) values (3, “王五”, 0);
commit;
T3insert into account (id, name, balance) values (3, “王五”, 0);
主键重复，插入失败
T4select count(*) from account where id = 3;
输出为0
T5rollback;

select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，这个就有问题了。

很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。

总的来说幻读就是事务A对数据进行操作，事务B还是可以用insert插入数据的，因为使用的是行锁，这样导致的各种奇葩问题就是幻读，表现形式很多，就不列举了。

当隔离级别设置为可串行化，强制事务串行执行，避免了前面说的幻读的问题。

欢迎关注

参考博客

[1]https://juejin.im/post/5ba0c3a6e51d450e597b2fb4
[2]https://uule.iteye.com/blog/1109647
[3]https://www.jianshu.com/p/4e3edbedb9a8
[4]https://www.cnblogs.com/itcomputer/articles/5133254.html
《剑指Java面试-Offer直通车》

                                </div>
come11234
关注
专栏目录
面试官：脏读，不可重复读，幻读是如何发生的？
小识的博客
03-03 1万+
介绍 数据库事务的四大特性 原子性（Atomic） 一致性（Consistency） 隔离性（Isolation） 持久性（Durability） √ 为会发生，×为不会发生 隔离性 脏读 不可重复读 幻读 read uncommitted（未提交读） √ √ √ read committed（提交读） × √ √ repeatable read（可重复读） × × √ ...
MySQL锁与脏读、不可重复读、幻读详解
悦分享
09-04 2235
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的 计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一 个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。本章我们着重讨论MySQL锁机制 的特点，常见的锁问题，以及解决MySQL锁问题的一些方法或建议。
数据库事务的四个隔离级别浅析
进击的程序猿
09-24 5333
数据库事务的隔离级别有4个，由低到高依次为Read uncommitted 、Read committed、Repeatable read 、Serializable ，这四个级别可以逐个解决脏读 、不可重复读 、幻读 这几类问题。 插叙：解释一下脏读，不可重复读，幻读的定义 √: 可能出现    ×: 不会出现 脏
SQL Server 中的事务与事务隔离级别以及如何理解脏读, 未提交读,不可重复读和幻读产生的过程和原因...
weixin_34319640的博客
09-25 197
原本打算写有关 SSIS Package 中的事务控制过程的，但是发现很多基本的概念还是需要有 SQL Server 事务和事务的隔离级别做基础铺垫。所以花了点时间，把 SQL Server 数据库中的事务概念，ACID 原则，事务中常见的问题，问题造成的原因和事务隔离级别等这些方面的知识好好的整理了一下。 其实有关 SQL Server 中的事务，说实话因为内容太多, 话题太广，稍微力度控制不...
MySQL事务隔离级别以及脏读、幻读、不可重复读示例
晓呆同学的专栏
01-02 5589
1、脏读、幻读、不可重复读示例；2、MySQL中的四种事务隔离级别
MySQL幻读SQL示例
最新发布
学亮编程手记
11-24 375
在MySQL中，默认的隔离级别是可重复读（REPEATABLE READ），它可以避免幻读的情况发生。幻读通常发生在较低的隔离级别（如读提交）下，因此需要明确设置隔离级别为较低的级别才能模拟幻读。需要注意的是，幻读可能导致查询结果的不一致性，因为数据可能在事务提交之前发生了变化。在实际应用中，通常建议使用较高的隔离级别（如可重复读或串行化），以避免幻读和其他并发问题。此时，插入或删除操作尚未提交，数据仍处于未提交状态。通过读取受到未提交操作的影响的数据，就产生了幻读。默认情况下，隔离级别应该是。
脏读、不可重复读、幻读的解析与演示——基于Mssql数据库
QQZJ_CJW的博客
12-16 275
@[TOC]脏读、不可重复读、幻读的解析与演示——基于Mssql数据库 1. 解析 1.1脏读 脏读指的是，在两个并发事务A和B中，事务B会读到事务A还没有提交的记录。 例：A账户有500元，B账户有0元。A给B转账500元，A输入转账金额还没点确认的时候，B此时查询账户余额，发现余额已经为500元了，这就是脏读。 1.2不可重复读（针对updata操作） 不可重复读指的是，在同一个事务中，对同一...
mysql中脏读、不可重复读、幻读以及四种隔离级别的浅显理解
sinat_32438859的博客
05-01 2772
mysql中： 事务：事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。这个单独单元成为不可分割的一个整体。 对于同时运行的多个事务，如果没有适当的隔离机制，就会引发如下问题： 脏读 不可重复度 幻读 mysql提供四种隔离级别，分别是： read uncommitted 读未提交数据（什么也不能防止） read committed 读已提交数据（仅...
代码实现ORACLE 脏读，幻读，不可重复读
05-30
以下是 ORACLE 脏读、幻读、不可重复读的代码实现： ...需要注意的是，以上代码示例只是为了演示脏读、幻读、不可重复读的现象，实际开发中应该避免出现这些问题，可以通过设置事务隔离级别来避免这些问题的发生。
MySQL事务隔离级别：彻底理解脏读、幻读、不可重复读
* **未提交读（READ UNCOMMITTED）**：事务可以读取未提交的数据，存在脏读、幻读、不可重复读问题。 * **提交读（READ COMMITTED）**：事务只能读取已提交的数据，解决了脏读问题，但仍存在幻读、不
Sql SqlServer 脏读、 不可重复读和幻读
weixin_46879188的博客
12-06 792
1.脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问这个数据，然后使用了这个数据。不可重复读 ：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次
Mysql的幻读问题--个人看法
qq_58339096的博客
09-07 135
"幻读"是ANSI SQL 标准提出的概念，如下所示翻译：P2（“幻读”）：事务 T1 读取了满足特定搜索条件的记录行集合 N，然后事务 T2 又生成了一条或多条满足事务 T1 所使用的搜索条件的记录。如果事务 T1 用相同的搜索条件重复第一次读取，则会得到不同的记录行集合。
MySQL~数据并发问题（脏写、脏读、不可重复读、幻读）与SQL隔离级别演示
zxc的博客
05-10 1763
文章目录数据并发问题脏写脏读不可重复读幻读MySQL中的隔离级别如何设置事务的隔离级别 数据并发问题 脏写 脏读 不可重复读 幻读 四个问题严重性从高到低： 脏写、脏读、不可重复读、幻读 MySQL中的隔离级别 脏写的问题非常严重，不论是哪种隔离级别，都不允许脏写的问题发生 查看默认的隔离级别： mysql> show variables like 'transaction_isolation'; +-----------------------+-----------------+ | Va
【sql DB】浅谈脏读
知世俗而不世俗
12-25 7632
【前言】        小编在做基础系统维护的时候，接触到了修改服务器上的sql server数据库里面的数据，之前的时候小编也学过sql的东西，不过现在全忘了（增删改查，这些基本的还是会的），在删除某一条数据的时候出现了这样一个问题，要删除这一条数据但是就是删除不了，而且在删除记录里面还有我之前操作过的删除记录，这就很奇怪了。于是乎，多方求助终于解决了，下面给大家简单的介绍一下 【问题】
不可重复读和幻读的区别——实例详解
ClonH的博客
01-17 260
不可重复读和幻读的区别 需要用到的mysql语句： sql语句 作用 set session stransaction isolation level 隔离级别 设置隔离级别 start stransaction
关于sql脏读不可重复读幻读清晰易懂
qq_47738901的博客
05-17 306
标题 定义： 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚, T1读取的内容就是临时且无效的。 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行。之后, 如果 T1 再次读取同一个表, 就会多出几行。 1.脏读： 举例：以淘宝为例： ①某Cu
sql怎么读_大白话讲解脏写、脏读、不可重复读和幻读
weixin_39562615的博客
12-05 1787
一般对于我们的业务系统去访问数据库而言，它往往是多个线程并发执行多个事务的，对于数据库而言，它会有多个事务同时执行，可能这多个事务还会同时更新和查询同一条数据，所以这里会有一些问题需要数据库来解决我们来看看，如果多个事务要是对缓存里的同一条数据同时进行更新或者查询，此时会产生哪些问题呢？这里实际上会涉及到脏写、脏读、不可重复读、幻读四种问题。脏写脏写，意思是说有两个事务，事务 A 和事务 B 同时...
【MySQL】幻读是什么？如何避免幻读？
柠檬树
08-20 2785
一、前言 建表语句和初始化语句如下： CREATE TABLE `t`( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY(`id`), KEY `c`(`c`) )ENGINE=InnoDB; insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,...
脏读,不可重复读,幻读的问题演示和解决
汤键的博客
07-12 457
脏读,不可重复读,幻读的问题演示和解决
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值