mysql 中的脏读与幻读_Mysql之脏读、不可重复读、幻读的区别

我用的Mysql版本是5.7.19。

事物高并发的三种异常问题

数据库在在高并发时，事物会出现三种异常问题。

脏读：在事物还没有提交前，修改的数据可以被其他事物所看到。

不可重复读：在一个事物中使用相同的条件查询一条数据，前后两次查询所得到的数据不同，这是因为同时其他事物对这条数据进行了修改(已提交事物)，第二次查询返回了其他事物修改的数据。

幻读：在一个事物A中使用相同的条件查询了多条数据，同时其他事物添加或删除了符合事物A中查询条件的数据，这时候当事物A再次查询时候会发现数据多了或者少了，与前一次查询的结果不相同。

注意：不可重复读与幻读很容易搞混，他们的区别在于：

不可重复读：是同一条记录(一条数据)的内容被其他事物修改了，关注的是update、delete操作一条数据的操作.

幻读：是查询某个范围(多条数据)的数据行变多或变少了，在于insert、delete的操作。

隔离级别

想要解决上述三种问题，只要利用所把并发操作变成串行操作就可以完美的解决上述三个问题了，但是这回牺牲掉高并发的性能优势，使数据库处理数据的速度变慢。

没有一种通用的方法可以完美的解决所有问题，所以SQL标准定义了四种隔离级别来分别解决上述的问题，在性能与问题直接找到一个适合的方案。

读未提交(READ UNCOMMITTED )

读已提交(READ COMMITTED)

可重复读(REPEATABLE READ)

可串行化(SERIALIZABLE)

不同隔离级别可以解决的的异常问题如下：

image.png

读未提交：在一个事物没有提交的情况下，其他事物可以看到该事物中对数据的修改。

读已提交：在一个事物提交前，其他事物看不到该事物对数据的修改，有时也叫不可重复读，因为两次相同条件的查询，可能会得到不同的结果。

可重复读：在同一个事物中按照相同的条件多次查询的结果都是相同。Innodb存储引擎通过多版本并发控制解决了幻读的问题。

可串行化：将事物放到一个队列中按照顺序一个一个的执行，可以闭上三个异常问题，但是牺牲了并发的高性能。

修改隔离级别

有两种方法可以改变当前会话的隔离级别

SET session TRANSACTION ISOLATION LEVEL Serializable;

SET @@tx_isolation='read-committed';

参数可以为：

Read uncommitted

Read committed

Repeatable Read

Serializable

查看当前会话的隔离级别

select @@tx_isolation;

image.png

这是Mysql的默认级别

设置当前会话的级别为最低的读未提交

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET @@tx_isolation='read-committed';

使用上面的命令查看下是否已经修改了。

模拟三种异常的问题

创建一个实例用的表

CREATE TABLE `heros_tmp` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(30) NOT NULL DEFAULT '',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `heros_tmp` (`id`, `name`) VALUES (1, '张飞');

模拟脏读

开启两个Mysql的链接会话，命名为A和B。

A会话开启事物

begin;

select * from hrefos_tmp;

insert into heros_tmp values(NULL,'关羽');

image.png

B会话

查看heros_tmp表。

select * from hreos_tmp;

image.png

可以看到会话A添加了一条关羽的数据，并没有提交，但是在会话B中是可以看到这条关羽的数据。这就是脏读的问题。

升级隔离级别，解决脏读问题

在上面的表中可以看到，把隔离级别设置到读已提交以上的等级是可以解决脏读的问题。

接下里设置两个会话A、B为读已提交

SET @@tx_isolation='read-committed';

在会话A中开启事物，并插入一条数据

begin;

select * from heros_tmp;

insert into heros_tmp values(NULL,"刘备");

select * from heros_tmp;

image.png

可以看到会话A中的查询结果多了一条刘备的数据。

在会话B中查询heros_tmp表中的数据，看看是否可以看到刘备这条数据

select * from heros_tmp;

image.png

可以看到会话B，并没有读到会话A中事物未提交添加的数据。

不可重复度

在A会话中开启事物，查看id=1的数据

begin;

select name from heros_tmp where id=1;

image.png

B会话开启事物对id=1的数据进行修改

begin;

update heros_tmp set name='张翼德' where id=1;

commit;

在A会话中再次查看id=1的数据，

image.png

升级隔离级别，解决不可重复读问题

修改两个会话的隔离级别为可重复读

SET session TRANSACTION ISOLATION LEVEL Repeatable Read;

会话A中，开启事物查询id=1的数据

begin;

select name from heros_tmp where id=1;

image.png

会话B中，开启事物对id=1的数据进行修改后提交事物

begin;

update heros_tmp set name='张飞' where id=1;

commit;

select name from heros_tmp where id=1;

image.png

已经修改成功了

最后会话A中，再次查询id=1的数据，看结果是不是变了。

image.png

会话A的查询结果和上一次查询一致，没有出现不可重复读的问题。

幻读

在会话A中查看heros_tmp表中的id>1的数据。

image.png

在会话B中插入一个数据

begin;

insert into heros_tmp values(NULL,'吕布');

commit;

在会话A中再次查看表中的数据，会发现多了一个吕布的数据

image.png

前后多行数据的两次查询结果不同，出现了幻读问题

升级隔离级别，解决幻读问题

因为Innodb在可重复读级别使用了多版本并发解决了 幻读的问题，所以在Innodb引擎中不用把级别设置为可串行化就可以解决问题。

设置级别为可重复读

SET session TRANSACTION ISOLATION LEVEL Repeatable Read;

在会话A中开启事物，查询表中所有的数据。

begin;

select name from heros_tmp where id > 1;

image.png

在会话B中开启事物，添加一条新数据并提交事物。

begin;

insert into heros_tmp values(NULL,'孙权');

commit;

select name from heros_tmp where id>1;

image.png

新数据写入成功

在会话A中查询，看是否可以读取到新加入的数据。

image.png

两次查询多行数据的结果一致，没有出现幻读的问题。