技术文章
当前位置:首页 > Mysql技术文章 > 详解MySQL是如何解决幻读的

MySQL解决幻读的实操方法

  • 发布时间:
  • 作者:码农之家原创
  • 点击:137

这篇文章主要知识点是关于mysql解决幻读、mysql怎么解决幻读、MySQL可重复读级别能够解决幻读吗 的内容,如果大家想对相关知识点有系统深入的学习,可以参阅以下电子书

高性能MySQL
高性能MySQL影印完整版
  • 类型:MySQL大小:392 MB格式:PDF出版:电子工业出版社作者:施瓦茨,扎伊采夫,特卡琴科,宁海元,周振
立即下载

更多Mysql相关的学习资源可以参阅 Mysql电子书程序设计电子书 等栏目。

详解MySQL是如何解决幻读的

一、什么是幻读

在一次事务里面,多次查询之后,结果集的个数不一致的情况叫做幻读。

而多出来或者少的哪一行被叫做 幻行

二、为什么要解决幻读

在高并发数据库系统中,需要保证事务与事务之间的隔离性,还有事务本身的一致性。

三、MySQL 是如何解决幻读的

如果你看到了这篇文章,那么我会默认你了解了 脏读 、不可重复读与可重复读。

1. 多版本并发控制(MVCC)(快照读)

多数数据库都实现了多版本并发控制,并且都是靠保存数据快照来实现的。

以 InnoDB 为例,每一行中都冗余了两个字断。一个是行的创建版本,一个是行的删除(过期)版本。版本号随着每次事务的开启自增。事务每次取数据的时候都会取创建版本小于当前事务版本的数据,以及过期版本大于当前版本的数据。

普通的 select 就是快照读。

select * from T where number = 1;

原理:将历史数据存一份快照,所以其他事务增加与删除数据,对于当前事务来说是不可见的。

2. next-key 锁 (当前读)

next-key 锁包含两部分

  1. 记录锁(行锁)
  2. 间隙锁

记录锁是加在索引上的锁,间隙锁是加在索引之间的。(思考:如果列上没有索引会发生什么?)

select * from T where number = 1 for update;
select * from T where number = 1 lock in share mode;
insert
update
delete

原理:将当前数据行与上一条数据和下一条数据之间的间隙锁定,保证此范围内读取的数据是一致的。

其他:MySQL InnoDB 引擎 RR 隔离级别是否解决了幻读
引用一个 github 上面的评论 地址:

Mysql官方给出的幻读解释是:只要在一个事务中,第二次select多出了row就算幻读。
a事务先select,b事务insert确实会加一个gap锁,但是如果b事务commit,这个gap锁就会释放(释放后a事务可以随意dml操作),a事务再select出来的结果在MVCC下还和第一次select一样,接着a事务不加条件地update,这个update会作用在所有行上(包括b事务新加的),a事务再次select就会出现b事务中的新行,并且这个新行已经被update修改了,实测在RR级别下确实如此。

如果这样理解的话,Mysql的RR级别确实防不住幻读

有道友回复 地址:

在快照读读情况下,mysql通过mvcc来避免幻读。
在当前读读情况下,mysql通过next-key来避免幻读。
select * from t where a=1;属于快照读
select * from t where a=1 lock in share mode;属于当前读

不能把快照读和当前读得到的结果不一样这种情况认为是幻读,这是两种不同的使用。所以我认为mysql的rr级别是解决了幻读的。

先说结论,MySQL 存储引擎 InnoDB 隔离级别 RR 解决了幻读问题。

如引用一问题所说,T1 select 之后 update,会将 T2 中 insert 的数据一起更新,那么认为多出来一行,所以防不住幻读。看着说法无懈可击,但是其实是错误的,InnoDB 中设置了 快照读 和 当前读 两种模式,如果只有快照读,那么自然没有幻读问题,但是如果将语句提升到当前读,那么 T1 在 select 的时候需要用如下语法: select * from t for update (lock in share mode) 进入当前读,那么自然没有 T2 可以插入数据这一回事儿了。

注意
next-key 固然很好的解决了幻读问题,但是还是遵循一般的定律,隔离级别越高,并发越低。

以上所述是小编给大家介绍的MySQL是如何解决幻读的详解整合,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对码农之家网站的支持!

MySQL可重复读级别能够解决幻读吗

引言

之前在深入了解数据库理论的时候,了解到事物的不同隔离级别可能存在的问题。为了更好的理解所以在MySQL数据库中测试复现这些问题。关于脏读和不可重复读在相应的隔离级别下都很容易的复现了。但是对于幻读,我发现在可重复读的隔离级别下没有出现,当时想到难道是MySQL对幻读做了什么处理?

测试:

创建一张测试用的表dept:

CREATE TABLE `dept` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

insert into dept(name) values("后勤部")

事物 1 事物 2
begin begin
select * from dept
- insert into dept(name) values("研发部")
- commit
select * from dept
commit

根据上面的流程执行,预期来说应该是事物1的第一条select查询出一条数据,第二个select查询出两条数据(包含事物2提交的数据)。

但是在实际测试中发现第二条select实际上也只查询处理一条数据。这是但是根据数据库理论的可重复读的实现(排他锁和共享锁)这是不应该的情况。

在了解实际原因前我们先复习下事物的相关理论。

数据库原理理论

事物

事务(Transaction),一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。在关系数据库中,一个事务可以是一组SQL语句或整个程序。

为什么要有事物

一个数据库事务通常包含对数据库进行读或写的一个操作序列。它的存在包含有以下两个目的:

  • 为数据库操作提供了一个从失败中恢复到正常状态的方法,同时提供了数据库在异常状态下仍能保持一致性的方法。
  • 当多个应用程序在并发访问数据库时,可以在这些应用程序之间提供一个隔离方法,保证彼此的操作互相干扰。

事物特性

事务具有4个特性:原子性、一致性、隔离性、持久性。这四个属性通常称为 ACID 特性。

  • 原子性(atomicity):
    一个事务应该是一个不可分割的工作单位,事务中包括的操作要么都成功,要么都不成功。
  • 一致性(consistency):
    事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
  • 隔离性(isolation):
    一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据在事物未提交前对并发的其他事务是隔离的,并发执行的各个事务之间不能互相影响。
  • 持久性(durability):
    一个事务一旦成功提交,它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

事物之间的几个特性并不是一组同等的概念:

如果在任何时刻都只有一个事物,那么其天然是具有隔离性的,这时只要保证原子性就能具有一致性。

如果存在并发的情况下,就需要保证原子性和隔离性才能保证一致性。

数据库并发事物中存在的问题

如果不考虑事务的隔离性,会发生以下几种问题:

  • 脏读:脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。当一个事务正在多次修改某个数据,而在这个事务中这多次的修改都还未提交,这时一个并发的事务来访问该数据,就会造成两个事务得到的数据不一致。
  • 不可重复读:不可重复读是指在对于数据库中的某条数据,一个事务范围内多次查询返回不同的数据值(这里不同是指某一条或多条数据的内容前后不一致,但数据条数相同),这是由于在查询间隔,该事物需要用到的数据被另一个事务修改并提交了。不可重复读和脏读的区别是,脏读是某一事务读取了另一个事务未提交的脏数据,而不可重复读则是读取了其他事务提交的数据。需要注意的是在某些情况下不可重复读并不是问题。
  • 幻读:幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作,这时事务T2又对这个表中插入了一行数据项,而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据,会发现还有一行没有修改,其实这行是从事务T2中添加的,就好像产生幻觉一样,这就是发生了幻读。幻读和不可重复读都是读取了另一条已经提交的事务(这点就脏读不同),所不同的是不可重复读可能发生在update,delete操作中,而幻读发生在insert操作中。

排他锁,共享锁

排它锁(Exclusive),又称为X 锁,写锁。

共享锁(Shared),又称为S 锁,读锁。

读写锁之间有以下的关系:

  • 一个事务对数据对象O加了 S 锁,可以对 O进行读取操作,但是不能进行更新操作。加锁期间其它事务能对O 加 S 锁,但是不能加 X 锁。
  • 一个事务对数据对象 O 加了 X 锁,就可以对 O 进行读取和更新。加锁期间其它事务不能对 O 加任何锁。

即读写锁之间的关系可以概括为:多读单写

事物的隔离级别

在事物中存在以下几种隔离级别:

  • 读未提交(Read Uncommitted):解决更新丢失问题。如果一个事务已经开始写操作,那么其他事务则不允许同时进行写操作,但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现,即事物需要对某些数据进行修改必须对这些数据加 X 锁,读数据不需要加 S 锁。
  • 读已提交(Read Committed):解决了脏读问题。读取数据的事务允许其他事务继续访问该行数据,但是未提交的写事务将会禁止其他事务访问该行。这可以通过“瞬间共享读锁”和“排他写锁”实现, 即事物需要对某些数据进行修改必须对这些数据加 X 锁,读数据时需要加上 S 锁,当数据读取完成后立刻释放 S 锁,不用等到事物结束。
  • 可重复读取(Repeatable Read):禁止不可重复读取和脏读取,但是有时可能出现幻读数据。读取数据的事务将会禁止写事务(但允许读事务),写事务则禁止任何其他事务。Mysql默认使用该隔离级别。这可以通过“共享读锁”和“排他写锁”实现,即事物需要对某些数据进行修改必须对这些数据加 X 锁,读数据时需要加上 S 锁,当数据读取完成并不立刻释放 S 锁,而是等到事物结束后再释放。
  • 串行化(Serializable):解决了幻读的问题的。提供严格的事务隔离。它要求事务序列化执行,事务只能一个接着一个地执行,不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的,必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

MySQL中的隔离级别的实现

上面的内容解释了一些数据库理论的概念,但是在MySQL、ORACLE这样的数据库中,为了性能的考虑并不是完全按照上面介绍的理论来实现的。

MVCC

多版本并发控制(Multi-Version Concurrency Control, MVCC)是MySQL中基于乐观锁理论实现隔离级别的方式,用于实现读已提交和可重复读取隔离级别的实现。

实现(隔离级别为可重复读)

在说到如何实现前先引入两个概念:

系统版本号:一个递增的数字,每开始一个新的事务,系统版本号就会自动递增。

事务版本号:事务开始时的系统版本号。

在MySQL中,会在表中每一条数据后面添加两个字段:

创建版本号:创建一行数据时,将当前系统版本号作为创建版本号赋值

删除版本号:删除一行数据时,将当前系统版本号作为删除版本号赋值

SELECT

select时读取数据的规则为:创建版本号<=当前事务版本号,删除版本号为空或>当前事务版本号。

创建版本号<=当前事务版本号保证取出的数据不会有后启动的事物中创建的数据。这也是为什么在开始的示例中我们不会查出后来添加的数据的原因

删除版本号为空或>当前事务版本号保证了至少在该事物开启之前数据没有被删除,是应该被查出来的数据。

INSERT

insert时将当前的系统版本号赋值给创建版本号字段。

UPDATE

插入一条新纪录,保存当前事务版本号为行创建版本号,同时保存当前事务版本号到原来删除的行,实际上这里的更新是通过delete和insert实现的。

DELETE

删除时将当前的系统版本号赋值给删除版本号字段,标识该行数据在那一个事物中会被删除,即使实际上在位commit时该数据没有被删除。根据select的规则后开启懂数据也不会查询到该数据。

MVCC真的解决了幻读?

从最开始我们的测试示例和上面的理论支持来看貌似在MySQL中通过MVCC就解决了幻读的问题,那既然这样串行化读貌似就没啥意义了,带着疑问继续测试。

测试前数据:

MySQL可重复读级别能够解决幻读吗

事物 1 事物 2
begin begin
select * from dept
- insert into dept(name) values("研发部")
- commit
update dept set name="财务部"(工作中如果不想被辞退一定要写where条件)
commit

根据上面的结果我们期望的结果是这样的:

id  name
1   财务部
2   研发部

但是实际上我们的经过是:

MySQL可重复读级别能够解决幻读吗

本来我们希望得到的结果只是第一条数据的部门改为财务,但是结果确实两条数据都被修改了。这种结果告诉我们其实在MySQL可重复读的隔离级别中并不是完全解决了幻读的问题,而是解决了读数据情况下的幻读问题。而对于修改的操作依旧存在幻读问题,就是说MVCC对于幻读的解决时不彻底的。

快照读和当前读

出现了上面的情况我们需要知道为什么会出现这种情况。在查阅了一些资料后发现在RR级别中,通过MVCC机制,虽然让数据变得可重复读,但我们读到的数据可能是历史数据,不是数据库最新的数据。这种读取历史数据的方式,我们叫它快照读 (snapshot read),而读取数据库最新版本数据的方式,叫当前读 (current read)。

select 快照读

当执行select操作是innodb默认会执行快照读,会记录下这次select后的结果,之后select 的时候就会返回这次快照的数据,即使其他事务提交了不会影响当前select的数据,这就实现了可重复读了。快照的生成当在第一次执行select的时候,也就是说假设当A开启了事务,然后没有执行任何操作,这时候B insert了一条数据然后commit,这时候A执行 select,那么返回的数据中就会有B添加的那条数据。之后无论再有其他事务commit都没有关系,因为快照已经生成了,后面的select都是根据快照来的。

当前读

对于会对数据修改的操作(update、insert、delete)都是采用当前读的模式。在执行这几个操作时会读取最新的记录,即使是别的事务提交的数据也可以查询到。假设要update一条记录,但是在另一个事务中已经delete掉这条数据并且commit了,如果update就会产生冲突,所以在update的时候需要知道最新的数据。也正是因为这样所以才导致上面我们测试的那种情况。

select的当前读需要手动的加锁:

select * from table where ? lock in share mode;
select * from table where ? for update;

有个问题说明下

在测试过程中最开始我以为使用begin语句就是开始一个事物了,所以在上面第二次测试中因为先开始的事物1,结果在事物1中却查到了事物2新增的数据,当时认为这和前面MVCC中的select的规则不一致了,所以做了如下测试:

MySQL可重复读级别能够解决幻读吗

SELECT * FROM information_schema.INNODB_TRX //用于查询当前正在执行中的事物

可以看到如果只是执行begin语句实际上并没有开启一个事物。

下面在begin后添加一条select语句:

MySQL可重复读级别能够解决幻读吗

所以要明白实际上是对数据进行了增删改查等操作后才开启了一个事物。

如何解决幻读

很明显可重复读的隔离级别没有办法彻底的解决幻读的问题,如果我们的项目中需要解决幻读的话也有两个办法:

  • 使用串行化读的隔离级别
  • MVCC+next-key locks:next-key locks由record locks(索引加锁) 和 gap locks(间隙锁,每次锁住的不光是需要使用的数据,还会锁住这些数据附近的数据)

实际上很多的项目中是不会使用到上面的两种方法的,串行化读的性能太差,而且其实幻读很多时候是我们完全可以接受的。

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对码农之家的支持。

以上就是本次给大家分享的关于Mysql的全部知识点内容总结,大家还可以在下方相关文章里找到MySQL性能优化总结、 MySQL中的CONCAT函数使用方法、 MySQL 5.7增强版Semisync Repl、 等mysql文章进一步学习,感谢大家的阅读和支持。

上一篇:MySql Date函数用法总结

下一篇:Mysql存储过程和存储函数用法详解

展开 +

收起 -

MySQL幻读 相关电子书
学习笔记
网友NO.291363

详解MySQL的主从复制、读写分离、备份恢复

一、MySQL主从复制 1、简介 我们为什么要用主从复制? 主从复制目的: 可以做数据库的实时备份,保证数据的完整性; 可做读写分离,主服务器只管写,从服务器只管读,这样可以提升整体性能。 原理图: 从上图可以看出,同步是靠log文件同步读写完成的。 2、更改配置文件 两天机器都操作,确保 server-id 要不同,通常主ID要小于从ID。一定注意。 # 3306和3307分别代表2台机器# 打开log-bin,并使server-id不一样#vim /data/3306/my.cnflog-bin = /data/3306/mysql-binserver-id = 1#vim /data/3307/my.cnflog-bin = /data/3307/mysql-binserver-id = 3#检查1、[root@bogon ~]# egrep "log-bin|server-id" /data/3306/my.cnflog-bin = /data/3306/mysql-binserver-id = 1[root@bogon ~]# egrep "log-bin|server-id" /data/3307/my.cnf log-bin = /data/3307/mysql-binserver-id = 32、[root@localhost ~]# mysql -uroot -p -S /data/3306/mysql.sock -e "show variables like 'log_bin';"Enter password:……

网友NO.337115

Mysql(MyISAM)的读写互斥锁问题的解决方法

由于没办法在短期内增加读的服务器,所以采取对Mysql进行了一些配置,以牺牲数据实时性为代价,来换取所有服务器的生命安全。呵呵,具体相关调整以及思路如下: MyISAM在读操作占主导的情况下是很高效的。可一旦出现大量的读写并发,同InnoDB相比,MyISAM的效率就会直线下降,而且,MyISAM和 InnoDB的数据存储方式也有显著不同:通常,在MyISAM里,新数据会被附加到数据文件的结尾,可如果时常做一些UPDATE,DELETE操作之后,数据文件就不再是连续的,形象一点来说,就是数据文件里出现了很多洞洞,此时再插入新数据时,按缺省设置会先看这些洞洞的大小是否可以容纳下新数据,如果可以,则直接把新数据保存到洞洞里,反之,则把新数据保存到数据文件的结尾。之所以这样做是为了减少数据文件的大小,降低文件碎片的产生。但 InnoDB里则不是这样……

网友NO.673068

详细解读MySQL中COUNT函数的用法

MySQL的COUNT函数是最简单的功能,非常有用的计算,预计由一个SELECT语句返回的记录数。 要了解COUNT函数考虑的EMPLOYEE_TBL的的表具有以下记录: mysql SELECT * FROM employee_tbl;+------+------+------------+--------------------+| id | name | work_date | daily_typing_pages |+------+------+------------+--------------------+| 1 | John | 2007-01-24 | 250 || 2 | Ram | 2007-05-27 | 220 || 3 | Jack | 2007-05-06 | 170 || 3 | Jack | 2007-04-06 | 100 || 4 | Jill | 2007-04-06 | 220 || 5 | Zara | 2007-06-06 | 300 || 5 | Zara | 2007-02-06 | 350 |+------+------+------------+--------------------+7 rows in set (0.00 sec) 现在根据上面的表,要算在此表中的行数,那么可以做如下的假设: mysqlSELECT COUNT(*) FROM employee_tbl ;+----------+| COUNT(*) |+----------+| 7 |+----------+1 row in set (0.01 sec) 同样地,想数一数的话就可以计算一下如下记录为Zara的数量: mysqlSELECT COUNT(*) FROM employee_tbl - WHERE name="Z……

网友NO.740552

基于mysql+mycat搭建稳定高可用集群负载均衡主备复制读写分离操作

数据库性能优化普遍采用集群方式,oracle集群软硬件投入昂贵,今天花了一天时间搭建基于mysql的集群环境。 主要思路 简单说,实现mysql主备复制--利用mycat实现负载均衡。 比较了常用的读写分离方式,推荐mycat,社区活跃,性能稳定。 测试环境 MYSQL版本:Server version: 5.5.53,到官网可以下载WINDWOS安装包。 注意:确保mysql版本为5.5以后,以前版本主备同步配置方式不同。 linux实现思路类似,修改my.cnf即可。 A主mysql。192.168.110.1:3306, 用户root,密码root。操作系统:win7 x64,内存:4g 安装路径:C:\Program Files\MySQL\MySQL Server 5.5\bin B备mysql。192.168.110.2:3306, 用户root,密码root。操作系统:win2003 x64,内存:1g 安装路径:C:\Program Files\MySQL\MySQL Server 5.5\bin A主、B备的mysql中创建sync_test数据库 实现mysql主备复制 主要思路:A主mysql开启日志,B备mysql读取操作日志,同步执行……

网友NO.909234

利用mycat实现mysql数据库读写分离的示例

什么是MyCAT 一个彻底开源的,面向企业应用开发的大数据库集群 支持事务、ACID、可以替代MySQL的加强版数据库 一个可以视为MySQL集群的企业级数据库,用来替代昂贵的Oracle集群 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品 一个新颖的数据库中间件产品 MyCAT关键特性 支持SQL92标准 支持MySQL、Oracle、DB2、SQL Server、PostgreSQL等DB的常见SQL语法 遵守MySQL原生协议,跨语言,跨平台,跨数据库的通用中间件代理。 基于心跳的自动故障切换,支持读写分离,支持MySQL主从,以及galera cluster集群。 支持Galera for MySQL集群,Percona Cluster或者MariaDB cluster 基于Nio实现,有效管理线程,解决高并发问题。 支持数据的多片自动路由与聚合,支持sum,count,max等常用的聚合函数,支持跨库分页。 支……

<
1
>

Copyright 2018-2020 xz577.com 码农之家

电子书资源由网友、会员提供上传,本站记录提供者的基本信息及资源来路

鸣谢: “ 码小辫 ” 公众号提供回调API服务、“ 脚本CDN ”提供网站加速(本站寻求更多赞助支持)

版权投诉 / 书籍推广 / 赞助:520161757@qq.com

上传资源(网友、会员均可提供)

查看最新会员资料及资源信息