亲宝软件园·资讯

展开

MySQL脏读幻读

向着百万年薪努力的小赵 人气:0

前言

上一篇文章讲解了MySQL的事务的相关概念MySQL的事务特性概念梳理总结

文章末尾提出了事务因并发出现的问题有哪些？
本篇将着重讲述这个问题的前因后果及解决方式。

事务因并发出现的问题有哪些 脏读

概念：一个事务读取到其他事务未提交的数据。
用一个图来讲解，在并发环境下，多个事务操作同一对象带来的问题：

不可重复读

概念：一个事务在一个时间段内 前后读取的数据不一致，或者出现了修改/删除。

幻读

概念：事务A 按照查询条件读取某个范围的记录，其他事务又在该范围内出入了满足条件的新记录，当事务A再次读取数据到时候我们发现多了满足记录的条数（幻行）

建议大家把幻读记作幻行，以免和不可重复读记混淆

不可重复读与幻读的区别

前提：两者都是读取到已经提交的数据

不可重复读：重点是在于修改，在一个事务中，同样的条件，第一次读取的数据与第二次【数据不一样】（因为中间有其他事务对这个数据进行了修改）
幻读：重点在于新增或者删除，在一个事务中，同样的条件（范围），第一次读取和第二读取【记录条数不一样】（因为中间有其他事务在这个范围里插入、删除了的数据）

我们现在已经知道，原来事务并发会出现，脏读，不可重复读，幻读的问题。
那这些问题我们都是需要去解决的，怎么解决呢？
有兴趣可以看看官网是怎么解释的
链接: 官网地址

事务并发的三大问题其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

事务的四个隔离级别

我们通过事务的隔离级别来解决不同的问题，那么，不同的隔离级别解决了什么问题呢？

其实sql标准92版 官方都有定义出来

另外，sql标准不是数据库厂商定义出来的，大家不要以为sql语言是什么mysql，sqlserver搞出来的，我们会发现每个数据库语句的sql语句都是差不多的。sql是独立于厂商的！！SQL是Structured Query Language的缩写，本来就属于一种查询语言！！

官网支持四种隔离级别：

# 修改当前会话的隔离级别
# 读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
# 读已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
# 可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
# 串行化
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

我们也可以通过SQL去查询当前的隔离级别

SHOW GLOBAL VARIABLES LIKE '%isolation%'; //全局隔离级别
SHOW SESSION VARIABLES LIKE '%isolation%';
set SESSION autocommit=0; //关闭自动提交

InnoDB默认的隔离级别是RR

事务隔离级别越高，多个事务在并发访问数据库时互相产生数据干扰的可能性越低，但是并发访问的性能就越差。（相当于牺牲了一定的性能去保证数据的安全性）

Read UnCommited 读未提交 RU

多个事务同时修改一条记录，A事务对其的改动在A事务还没提交时，在B事务中就可以看到A事务对其的改动。

结论：没有解决任何问题，存在脏读，因为他就是读取最新的数据。

Read Commited 读已提交 RC

多个事务同时修改一条记录，A事务对其的改动在A事务提交之后，在B事务中可以看到A事务对其的改动。

结论：我就读取你已经提交的事务就完事，解决脏读。

Repeatable Read 可重复读 RR

多个事务同时修改一条记录，这条记录在A事务执行期间是不变的（别的事务对这条记录的修改不被A事务感知）。

结论：RR级别解决了脏读、不可重复读、幻读的问题。

Serializable 串行化

多个事务同时访问一条记录（CRUD），读加读锁，写加写锁，完全退化成了串行的访问，自然不会收到任何其他事务的干扰，性能最低。

结论：加锁排队读取，性能最低。

可以看出，RU与串行化都没啥实用意义，主要还是看RC和RR，那么Mysql是怎么实现这两种隔离级别的呢？
我们要先学习Mysql的两种机制，undo 版本链机制以及read view快照读机制，读已提交和可重复读隔离级别的实现都是建立在这两个核心机制之上。

undo 版本链

undo 版本链就是指undo log的存储在逻辑上的表现形式，它被用于事务当中的回滚操作以及实现MVCC，这里介绍一下undo log之所以能实现回滚记录的原理。

对于每一行记录，会有两个隐藏字段：row_trx_id和roll_pointer
row_trx_id表示更新（改动）本条记录的全局事务id （每个事务创建都会分配id，全局递增，因此事务id区别对某条记录的修改是由哪个事务作出的）
roll_pointer是回滚指针，指向当前记录的前一个undo log版本，如果是第一个版本则roll_pointer指向null，这样如果有多个事务对同一条记录进行了多次改动，则会在undo log中以链的形式存储改动过程。

在上图中，最下方的undo log中记录了当前行的最新版本，而该条记录之前的版本则以版本链的形式可追溯，这也是事务回滚所做的事。那undo log版本链和事务的隔离性有什么关系呢？那就要引入另一个核心机制：read view。

read view

read view表示读视图，这个快照读会记录四个关键的属性：

当一个事务读取某条记录时会追溯undo log版本链，找到第一个可以访问的版本，而该记录的某一个版本是否能被这个事务读取到遵循如下规则：

（这个规则永远成立，这个需要好好理解，对后面讲解可重复读和读已提交两个级别的实现密切相关）

RR中 Read View是事务第一次查询的时候建立的。RC的Read View是事务每次查询的时候建立的。

Oracle、Postgres等等其他数据库都有MVCC的实现。

需要注意，在InnoDB中，MVCC和锁是协同使用的，这两种方案并不是互斥的。

配合使用read view和undo log版本链就能实现事务之间并发访问相同记录时，可以根据事务id不同，获取同一行的不同undo log版本（多版本并发控制）。

MVCC（Multi-Version Concurrent Control ）多版本并发控制

多版本并发控制，是什么意思呢？版本控制，我们在进行查询的时候是有版本的，后续在同一个事务里查询的时候，我们都是使用我们当初创建的快照版本。
比如说嘛，快照，你10岁20岁30岁40岁去照相，你只能看到你之前照相的模样，但是不能看到你未来的模样。

MVCC怎么去实现？
每个事务都有一个事务ID，并且是递增，我们后续MVCC的原理都是基于它去完成。
效果：建立一个快照，同一个事务无论查询多少次都是相同的数据。

一个事务能看见的版本：

一个事务不能看见的版本：

下面通过模拟并发访问的两个事务操作，介绍MVCC的实现（具体来说就是可重复读和读已提交两个隔离级别的实现）

可重复读实现

下面模拟两个并发访问同一条记录的事务AB的行为，假设这条记录初始时id=1，a=0，该记录两个隐藏字段row_trx_id = 100，roll_pointer = null
注意：在可重复读隔离级别下，当事务sql执行的时候，会生成一个read view快照，且在本事务周期内一直使用这个read view，下面给出了并发访问同一条记录的两个事务AB的具体执行过程，并解释可重复读是如何实现的（解决了脏读和不可重复读）。

事务A的read view：

create_trx_id = 101｜ m_idx = [101, 102]｜min_trx_id = 101｜max_trx_id = 103

事务B的read view：

create_trx_id = 102｜ m_idx = [101, 102]｜min_trx_id = 101｜max_trx_id = 103

（ps. 这里因为AB事务是并发执行，因此两个事务创建的read view的max_trx_id = 103）

这里要注意的是，每次对一条记录发生修改，就会记录一个undo log的版本，则在A事务中第二次查询id=1的记录的a的值的时候，B事务对该记录的修改已经添加到版本链上了，此时这个undo log的trx_id = 102，在A事务的read view的m_idx数组中且不等于A事务的trx_id = 101，因此无法访问到，需要在向前回溯，这里找到trx_id = 100的记录版本（小于A事务read view的min_trx_id属性，因此可以访问到），故A事务第二次查询依旧得到a = 0，而不是B事务修改的a = 1。

你可能有疑问，在A事务第二次查询的时候，B事务已经完成提交了，那么A事务的read view的m_idx数组应该移除102才对啊，它存的不是当前活跃的事务的id吗？·

注意：在可重复读隔离级别下，当事务sql执行的时候，会生成一个read view快照，且在本事务周期内一直使用这个read view，虽然102确实应该从A事务的read view中移除，但是因为read view在可重复读隔离级别下只会在第一条SQL执行时创建一次，并始终保持不变直到事务结束。

那么也就明白了，在可重复读隔离级别下，因为read view只在第一条SQL执行时创建，因此并发访问的其他事务提交前改动的脏数据、以及并发访问的其他事务提交的改动数据都对当前事务是透明的（尽管确实是记录在了undo log版本链中） ，这就解决了脏读和不可重复读（即使其他事务提交的修改，对A事务来说前后查询结果相同）的问题！

读已提交实现

还是借助上面事务处理的例子，所有的事务处理流程不变，只是将隔离级别调整为读已提交，读已提交依旧遵守read view和undo log版本链机制，它和可重复读级别的区别在于，每次执行sql，都会创建一个read view，获取最新的事务快照。 而因为这个区别，读已提交产生了不可重复读的问题，下面来分析一下原因：

事务A第一次查询创建的read view：

create_trx_id = 101｜ m_idx = [101, 102]｜min_trx_id = 101｜max_trx_id = 103

事务B的read view：

create_trx_id = 102｜ m_idx = [101, 102]｜min_trx_id = 101｜max_trx_id = 103

事务A第二次查询创建的read view：

create_trx_id = 101｜ m_idx = [101]｜min_trx_id = 101｜max_trx_id = 103

（ps. 这里因为AB事务是并发执行，因此两个事务创建的read view的max_trx_id = 103）

这里重点观察A事务的第二次查询，之前你可能就意识到了，在事务B完成提交后，当前系统中活跃的事务id应该移除102，但是因为在可重复读隔离级别下，A事务的read view只会在第一个SQL执行时创建，而在读已提交隔离级别下，每次执行SQL都会创建最新的read view，且此时 m_idx数组中移除了102，那么事务A在追溯undo log版本链的时候，最新版本记录的trx_id = 102，102不在A事务的m_idx数组中，且101 = min_trx_id <= 102 < max_trx_id = 103，因此可以访问到B事务的提交结果。

那么对A事务来说，在事务过程中读取同一条记录第一次得到a=0，第二次得到a=1，所以出现了不可重复读的问题（这里B不提交的话A如果就进行了第二次查询，则102不会从A事务的read view移除，则A事务依旧访问不到B事务未提交的修改，因此脏读还是可以避免的！）

MVCC多版本并发控制的实现可以理解成读已提交、可重复读两种隔离级别的实现，通过控制read view的创建时机（其访问机制是不变的），配合undo log版本链可以实现事务之间对同一条记录的并发访问，并获得不同的结果。

但是，大家有没有想过，刚才的一切都是对A提供便利，对B呢？
而且，MVCC 是适合用于处查询的时候使用，能提供很高的性能，我们的事务不仅仅
是只有读，我们还有写情况，刚才介绍的情况，B的事务是不是会被直接覆盖掉？这不就造成了事务丢失了嘛
针对写的情况，Mysql还有另一种基于锁的机制

LBCC

锁的作用是什么?它跟Java里面的锁是一样的，是为了解决资源竞争的问题，Java里面的资源是对象，数据库的资源就是数据表或者数据行。

基于锁的方式起始比较简单，就是一个事务在进行数据查询时，不允许其他事务修改。也就是说，基于锁的机制就使得数据库无法支持并发事务的读写操作，这种方案在一定程度上影响了操作数据的效率。

本文着重讲InnoDB引擎

在之前讲MySQL存储引擎的时候，我们知道了 InnoDB和MylSAM支持的锁 的类型是不同的。InnoDB同时支持表锁和行锁，而MylSAM只支持表锁，用lock table的语法加锁。

lock tables xxx read;
lock tables xxx write;
unlock tables ;

为什么支持行锁会成为InnoDB的优势?表锁和行锁的区别到底在哪?

锁的类型

我们可以看到，官网把锁分成了8类。我们把前面的两个行级别的锁(Shared andExclusive Locks)，和两个表级别的锁(Intention Locks）称为锁的基本模式。

show variables like 'innodb_autoinc_lock_mode';
--0: traditonal(每次都会产生表锁)
--1: consecutive(会产生一个轻量锁,simple insert 会获得批量的锁，保证连续插入，默认值)
--2: interleaved(不会锁表，来一个处理一个，并发最高)

空间索引的谓词锁：Predicate Locks for Spatial Indexes是5.7版本里面新增的空间索引的谓词锁。

共享锁

第一个行级别的锁就是我们在官网看到的Shared Locks(共享锁)，我们获取了一行数据的读锁以后，可以用来读取数据，所以它也叫做读锁，注意不要在加上了读锁以后去写数据，不然的话可能会出现死锁的情况。而且多个事务可以共享一把读锁。

共享锁的作用：因为共享锁会阻塞其他事务的修改，所以可以用在不允许其他事务修改数据的情况。
那怎么给一行数据加上读锁呢?
我们可以用select… lock in share mode;的方式手工加上一把读锁。
释放锁有两种方式，只要事务结束，锁就会自动事务，包括提交事务和结束事务。

排它锁

第二个行级别的锁叫做Exclusive Locks(排它锁)，它是用来操作数据的，所以又叫做写锁。只要一个事务获取了一行数据的排它锁，其他的事务就不能再获取这一行数据的共享锁和排它锁。

排它锁的加锁方式有两种
第一种是自动加排他锁，可能是同学们没有注意到的:我们在操作数据的时候，包括增删改，都会默认加上一个排它锁。
第二种是手工加锁，我们用一个FOR UPDATE给一行数据加上一个排它锁，这个无论是在我们的代码里面还是操作数据的工具里面，都比较常用。
释放锁的方式跟前面是一样的。

这个是两个行锁，接下来就是两个表锁。

意向锁

意向锁是什么呢?我们好像从来没有听过，也从来没有使用过，其实他们是由数据库自己维护的。

也就是说：

反过来:

意向锁跟意向锁是不冲突的，意向锁跟行锁也不冲突。

那么这两个表级别的锁存在的意义是什么呢?

如果说没有意向锁的话，当我们准备给一张表加上表锁的时候，我们首先要做什么?是不是必须先要去判断有没其他的事务锁定了其中了某些行?如果有的话，肯定不能加上表锁。那么这个时候我们就要去扫描整张表才能确定能不能成功加上一个表锁，如果数据量特别大，比如有上千万的数据的时候，加表锁的效率是不是很低?
但是我们引入了意向锁之后就不一样了。我只要判断这张表上面有没有意向锁，如果有，就直接返回失败。如果没有，就可以加锁成功。所以InnoDB里面的表锁，我们可以把它理解成一个标志。就像火车上卫生间有没有人使用的灯，让你不用去推门，是用来提高加锁的效率的。

所以锁是用来解决事务对数据的并发访问的问题的。那么，锁到底锁住了什么呢?
当一个事务锁住了一行数据的时候，其他的事务不能操作这一行数据，那它到底是锁住了这一行数据，还是锁住了这一个字段，还是锁住了别的什么东西呢?

行锁的原理

没有索引的表

首先我们有三张表，一张没有索引的t1，一张有主键索引的t2，一张有唯一索引的t3。
我们先假设 InnoDB的行锁 锁住的是一行数据或者一条记录。
我们假设t1的表结构，它有两个字段, int类型的id和varchar类型的name。里面有4条数据，1、2、3、4。

我们在两个会话里面手工开启两个事务。
在第一个事务里面，我们通过 where id =1锁住第一行数据。
在第二个事务里面，我们尝试给id=3的这一行数据加锁，能成功吗?

很遗憾，我们看到红灯亮起，这个加锁的操作被阻塞了。这就有点奇怪了，第一个事务锁住了id=1的这行数据，为什么我不能操作id=3的数据呢?
我们再来操作一条不存在的数据，插入 id=5。它也被阻塞了。实际上这里整张表都被锁住了。所以，我们的第一个猜想被推翻了，InnoDB的行锁锁住的应该不是Record。
那为什么在没有索引或者没有用到索引的情况下，会锁住整张表?这个问题我们先留在这里。

有主键索引的表

我们假设t2的表结构。字段和t1是一样的，不同的地方是id上创建了一个主键索引。里面的数据是1、4、7、10。

第一种情况，使用相同的id值去加锁，冲突;使用不同的id 加锁，可以加锁成功。那么，既然不是锁定一行数据，有没有可能是锁住了id 的这个字段呢?

有唯一索引的表（上面假设锁住了字段）

我们假设t3的表结构字段还是一样的, id上创建了一个主键索引,name 上创建了一个唯一索引。里面的数据是1、4、7、10。

在第一个事务里面，我们通过name字段去锁定值是4的这行数据。
在第二个事务里面，尝试获取一样的排它锁，肯定是失败的，这个不用怀疑。
在这里我们怀疑InnoDB的行锁锁住的是字段，所以这次我换一个字段，用id=4去给这行数据加锁，能成功吗?

很遗憾，又被阻塞了，说明行锁锁住的是字段的这个推测也是错的，否则就不会出现第一个事务锁住了name，第二个字段锁住id失败的情况。

既然锁住的不是record，也不是column,，行列都没锁，那InnoDB的行锁锁住的到底是什么呢?在这三个案例里面，我们要去分析一下他们的差异在哪里，也就是这三张表的结构，是什么区别导致了加锁的行为的差异?其实答案就是索引。InnoDB的行锁，就是通过锁住索引来实现的。

那么我们还有两个问题没有解决:

1、为什么表里面没有索引的时候，锁住一行数据会导致锁表?或者说，如果锁住的是索引，一张表没有索引怎么办?

所以，一张表有没有可能没有索引?

所以，为什么锁表，是因为查询没有使用索引，会进行全表扫描，然后把每一个隐藏的聚集索引都锁住了。

2、为什么通过唯一索引给数据行加锁，主键索引也会被锁住?

大家还记得在InnoDB里面，当我们使用辅助索引（二级索引）的时候，它是怎么检索数据的吗?辅助索引的叶子节点存储的是什么内容?
在辅助索引里面，索引存储的是二级索引和主键的值。比如name=4,存储的是name的索引和主键id 的值4。
而主键索引里面除了索引之外，还存储了完整的数据。所以我们通过辅助索引锁定一行数据的时候，它跟我们检索数据的步骤是一样的，会通过主键值找到主键索引，然后也锁定。

本质上是因为锁定的是同一行数据，是相互冲突的。

InnoDB中LBCC要解决的问题

问题1-幻读问题(InnoDB)

范围查询的时候，多次查询结果的数据行数一致

select * from table where id >=1 and id<=4 //锁定2，3 [解决幻读问题]

问题二， for update 实现了排他锁(行锁)

--transaction1 
select * from table where id=1 for update; //查询主键id=1 （行 锁，只锁定行）
--transaction2
 update table set name='111' where id=1; //阻塞 
 update table set name='222' where name =''; //阻塞

基于索引来决定的，如果where是索引，那么这个时候，直接加行锁.

问题三， 锁定整个表

select * from table for update; //表锁
update table set name='111' where id=1; //阻塞

锁的算法

我们先来看一下我们测试用的表,t2，这张表有一个主键索引,前面我们已经见过了。我们插入了4行数据，主键id分别是1、4、7、10。
为了让大家真正理解这三种行锁算法的区别，我也来花一点时间给大家普及一下这三种范围的概念。
因为我们用主键索引加锁，我们这里的划分标准就是主键索引的值。


这些数据库里面存在的主键值，我们把它叫做Record（记录），那么这里我们就有4个Record。
根据主键，这些存在的Record隔开的数据不存在的区间，我们把它叫做Gap（间隙），它是一个左开右开的区间。
假设我们有N个Record，那么所有的数据会被划分成多少个Gap 区间?答案是N+1，就像我们把一条绳子砍N刀，它最后肯定是变成N+1段。
最后一个，间隙(Gap）连同它左边的记录(Record)，我们把它叫做临键的区间，它是一个左开右闭的区间。再重复一次，是左开右闭。
整型的主键索引，它是可以排序，所以才有这种区间。如果我的主键索引不是整形，是字符怎么办呢?

任何一个字符集，都有相应的排序规则:

在这里插入图片描述

Record Lock (记录锁) [锁定的是索引]

第一种情况，当我们对于唯一性的索引(包括唯一索引和主键索引)使用等值查询，精准匹配到一条记录的时候，这个时候使用的就是记录锁。

顾名思义，记录锁就是为某行记录加锁，它封锁该行的索引记录，并不是真正的数据记录，锁的是索引的键值对。

-- 记录锁：id 列为主键列或唯一索引列 
SELECT * FROM user WHERE id = 1 FOR UPDATE;
--意味着id=1的这条记录会被锁住

Gap Lock（间隙锁 锁定索引区间，不包括record lock）

第二种情况，当我们查询的记录不存在，没有命中任何一个record，无论是用等值查询还是范围查询的时候，它使用的都是间隙锁。
还有个情况，假如我们只命中间隙的一边，另一边无法命中怎么办？
这种情况下，会锁住另一边的无限空间

顾名思义 锁间隙，不锁记录。
重复一遍，当查询的记录不存在的时候，使用间隙锁。
注意，间隙锁主要是阻塞插入insert。相同的间隙锁之间不冲突。
间隙锁是基于非唯一索引，它锁定一段范围内的索引记录，比如下面这个查询

 SELECT * FROM user WHERE id BETWEN 1 AND 4 FOR UPDATE;

那么意味着所有在(1,4)区间内的记录行都会被锁住，它是一个左右开区间的范围，意味着在这种情况下， 会锁住id为2，3的索引，但是1、4不会被锁定

next Key Lock（临键锁 锁定索引区间，包括record lock）

第三种情况，当我们使用了范围查询，不仅仅命中了Record记录，还包含了Gap间隙，在这种情况下我们使用的就是临键锁，它是MySQL里面默认的行锁算法，相当于记录锁加上间隙锁。

唯一性索引，等值查询匹配到一条记录的时候，退化成记录锁。
没有匹配到任何记录的时候，退化成间隙锁。

next Key Lock 可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法，每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。

为什么要锁住下一个左开右闭的区间?——就是为了解决幻读的问题。

小结

所以，我们再回过头来看下这张图片，为什么InnoDB的RR级别能够解决幻读的问题，就是用临键锁实现的。
我们再回过头来看下这张图片,这个就是MySQL InnoDB里面事务隔离级别的实现。

最后我们来总结一下四个事务隔离级别:

Read Uncommited
RU隔离级别:不加锁。Serializable
Serializable 所有的select语句都会被隐式的转化为select … in share mode，会和update、delete互斥。

这两个很好理解，一般也不用，主要是RR和RC的区别?

Repeatable Read：RR隔离级别下，普通的select使用快照读(snapshot read)，底层使用MVCC来实
现。
加锁的select(select … in share mode / select … for update)以及更新操作update, delete等语句使用当前读(current read)，底层使用记录锁、或者间隙锁、临键锁。

Read Commited：RC隔离级别下，普通的select 都是快照读，使用MVCC 实现。加锁的select都使用记录锁，因为没有Gap Lock。

除了两种特殊情况——外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会使用间隙锁封锁区间。
所以RC会出现幻读的问题。

事务隔离级别怎么选？

RU和Serializable肯定不能用

RC和RR主要有几个区别：

在RC中，一个update语句，如果读到一行已经加锁的记录，此时 InnoDB返回记录最近提交的版本，由MySQL上层判断此版本是否满足update的where 条件。若满足(需要更新)，则MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)。

实际上，如果能够正确地使用锁(避免不使用索引去枷锁),只锁定需要的数据，用默认的RR级别就可以了
在我们使用锁的时候，有一个问题是需要注意和避免的，我们知道，排它锁有互斥的特性。一个事务或者说一个线程持有锁的时候，会阻止其他的线程获取锁，这个时候会造成阻塞等待，如果循环等待，会有可能造成死锁。
死锁的相关信息，可以看我的下一篇博客，MySQL死锁的解析
链接: MySQL死锁使用详解及检测和避免方法

加载全部内容
相关教程
猜你喜欢
用户评论