mysql事务隔离级别探险

最新推荐文章于 2024-06-30 15:59:01 发布

是guava不是瓜娃啊

最新推荐文章于 2024-06-30 15:59:01 发布

阅读量756

点赞数 2

分类专栏： mysql 文章标签： mysql 事务隔离级别互联网企业选择RC作为隔离级别 mysql选RR做默认隔离级别

本文链接：https://blog.csdn.net/eumenides_/article/details/103635767

版权

mysql 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

阅读完本章你能学到什么

• 了解到mysql中的几种日志和它的作用
• 加深对mysql事务隔离级别的理解
• 现代互联网企业会选择什么样的隔离级别

先来思考几个问题

mysql的4种隔离级别都有什么问题？
mysql的默认隔离级别是哪一种？为什么和其他主流数据库入：oracle和sqlserver不一样？
现代互联网企业到底应该选择哪一种隔离级别？

关系型数据库（RDBMS）中事务的ACID

在mysql中，innodb所提供的事务符合ACID的要求，而事务通过事务日志中的redolog和undolog满足了
原子性(A)、一致性©、持久性(D)，事务还会通过锁机制满足隔离性(I)，在innodb存储引擎中，有不同的隔离级别，它们对事务之间有着不同的隔离性。

mysql中的几种日志

• redolog
mysql在执行事务提交之前会把所有涉及设计库操作的sql记录到redolog里，然后在一条条的同步到数据库文件中，即使此刻断点宕机，那么也可以从redolog中恢复。redolog是物理日志，记录的是数据库对页的操作。
• undolog
他是数据被修改前的备份，如果事务执行一半出错，那么可以根据undolog恢复之前的状态。undolog是逻辑日志。
• binlog
二进制日志。逻辑日志，用来记录的是所有修改数据库的sql(言外之意就是除了select以外的语句)，主要用于备份恢复和主从复制。包含statement、row、mixed3种模式，通过binlog_format配置，生产一般用row，这样虽然IO压力大，性能消耗大，尤其是alter table或者不加where时的update，但是记录的全。因为我们不是专业DBA，因此在这里我们只需要知道生产一般用row或mixed，不用statement就可以了。
binlog是数据库层面的，redolog和undolog是基于innodb引擎的。binlog在事务提交时一次性写入，redolog是在事务执行过程中不断写入文件的。

mysql中事务的隔离级别

事务隔离级别中存在的问题

在讨论事务隔离级别时我们下来看3个知识点，这3个知识点会或多或少的出现在不同的隔离级别中。

脏读

读到了别的事务未提交的数据，脏数据，所以叫脏读

不可重复读

事务中查询相同条件的语句，查询的结果发生了变化。侧重于更新，值发生了变化。

幻读

事务中查询相同条件的语句，查询的结果发生了变化。侧重于新增和删除，数据的条数发生了变化变多或变少。

事务的四种隔离级别

在四种隔离级别，这里我们主要演示已提交读和可重复读两种隔离级别对应的不可重复读和幻读的情况，
由于未提交读对应的脏读比较容易理解，故此处不做演示。

未提交读(read uncommited)

允许：脏读、不可重复读、幻读
概念：事务中的修改，即使没有提交，对其他事务也都是可见的。
结论：不可用，逻辑上就存在问题。

可重复读(reapeatable read)

允许：幻读
概念：在一个事务内读不到别的事务更新的数据，哪怕别的事务提交了也读不到，只有当前事务提交后才能读到。
结论：可用。
解析：

首先我们先开两个session窗口，查看下当前session的事务隔离级别，然后在找一张初始化过一些数据的表来查看一下。
我们发现在mysql的隔离级别是默认是可重复读(reapeatable read)，然后我们分别为两个sesson开启事务，并且验证可重复读。
然后我们在验证一次幻读，上面我们提到了幻读主要侧重于数据的新增或删除，此处我们演示下新增，删除同样道理故此处不演示。
以上我们详细演示了可重复读(reapeatable read)隔离级别下解决的不可重复读问题和还存在的幻读问题，我们不禁会想这个可重复读是如何实现的？为什么别的事务改变了数据并且提交了事务，我当前事务依然感知不到，那这个可重复读的数据是读哪里的呢？答案是：我当前事务内读的是快照数据，所以在我当前事务未提交之前读的一直都是固化在快照里的数据，因此无法感知别的事务对数据产生的变化。
接下来我们在来看个有意思的事。

根据上面的学习我们知道此时session-2应该会去读快照数据会出现有可重复读，不应该在自己提交事务之前感知到别的事务对数据的修改，但这里又是为什么？答案是：快照的生成时间。在上面的学习过程中我们发现在 session-2开启事务后我们会先进行一次查询，快照就是在此时生成的，因此快照合适生成取决于你开启事务后的第一条查询语句何时执行，如果你开启事务后在执行第一条查询前被别的事务"抢先"修改了数据并且提交了事务，那么就会出现这种意想不到的结果。但是有同学心里就想了难道每次开启事务我都要先做一次累赘的查询？当然不是，除了这种查询生成快照之外还可以在开启事务的时候指定快照的创建来避免这种"抢先"的情况。
一开始的时候我们提到过事务的隔离机制是通过锁实现，那为什么这里有整出来个快照？原因是这个锁是排他锁，在多个事务中第一个事务还未提交之前，别的事务因为排他锁的存在想要读取数据只能干等，引入快照则可以提高数据库的并发能力。这在innodb引擎中有个专业的名称叫：一致性读。
一致性读：如果当前行被加了排他锁，那么当读取该行数据时，不是等到行上的锁释放而且去读一个快照数据。

上图展示了innodb中一致性非锁定读的过程。之所以称其为非锁定读，是因为它不需要等待被访问的行上的排他锁的释放。而上图中的快照的实现是由事务日志所对应的undo段来完成，其实快照就是该行所对应的之前的版本的数据，即历史数据，一行记录可能有不止一个快照数据。
并不是所有隔离级别都使用了一致性读，在"可重复读"和"已提交读"的隔离级别下，innodb存储引擎使用了一致性读，但是在这两个隔离级别中，对于快照数据的定义也不相同：
在"可复重读"隔离级别下：第一次SELECT语句执行时生成，只有在本事务中对数据进行更改才会更新快照，因此，只有第一次SELECT之前其它已提交事务所作的更改你可以看到，但是如果已执行了SELECT，那么其它事务 commit数据，你SELECT是看不到的。
在"已提交读"的隔离级别下：每次读取都会重新生成一个快照，所以每次快照都是最新的，也因此事务中每次 SELECT也可以看到其它已commit事务所作的更改。
那么问题又来了，"快照"到底是啥？是对当前数据的全量拷贝吗？难道每开启一个事务，都要把当前数据库的数据拷贝一份出来？以后我们可以聊聊可重复读的实现原理——MVCC(多版本并发控制)。

已提交读(read commited)

允许：不可重复读、幻读
概念：一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。
一旦提交了，那么其他事务中就可以看到数据发生的变化。
结论：可用。
解析：

首先我们先把当前session的隔离级别设置成已提交读(read commited)
我们来复现一下已提交读(read commited)下的不可重复读，幻读和可重复读一样这里不做演示。

可串行化(serializable)

概念：它通过强制事务串行执行,是最高的隔离级别，该级别下数据库丧失并发能力，性能低下。
结论：不可用，性能低下

以上用到的sql语句:

# 查看当前session的事务隔离级别
SELECT @@tx_isolation;
# 设置当前session的事务隔离级别为RC
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
# 开启事务
START TRANSACTION;
# 提交事务
COMMIT;
# 开启事务并且固化快照
START TRANSACTION WITH CONSISTENT SNAPSHOT;

每种隔离级别以及所存在的问题：
在这里插入图片描述

为什么mysql将RR作为默认的隔离级别

不知道大家还记不记得上面讲到mysql中的几种日志时说到binlog的作用是来恢复数据和主从复制，binlog有3中模式statement、row、mixed。在mysql5.1之前binlog只支持statement模式，这种模式是语句级别的，他会将sql语句的执行顺序串行化记录在日志中。
假如说我们的隔离级别选择RC的话：
在这里插入图片描述
经过这一波操作后此时表中是什么样子呢？我们都知道此时表中还剩id为2和3的两条记录。但是statement模式下的binlog会把sql语句按照执行顺序串行化记录下来，等到从机复制。我们都知道binlog中语句的顺序以commit为序，那么先会执行session-2，在执行session-1，那么这张表里只会剩下一条id为3的记录。这样肯定不行啊，这就会主从不一致。
那该怎么办？有两个方法：

选择RC时指定binlog为row或mixed模式，但是刚才说了mysql5.1之前只有statement。
选择RR作为默认的隔离级别。
有同学问如果选择RR作为默认的隔离级别，binlog还是statement这样就这能解决了？好，我们演示一波：

这时候我们通过演示发现session-2想新增一条id为2的数据时迟迟不成功会被一直阻塞住，直到session-1提交事务，这样sql的执行顺序就变了，因此就算binlog选择的是statement模式，主从复制也不会存在问题。
这个阻塞被称之为RR的间隙锁。

对于已提交读(RC)和可重复读(RR)我们究竟该怎么选

先说结论：现代互联网企业项目并发量比较大，一般选择**已提交读(RC)**作为数据库的隔离级别。
理由有3点：

可重复读(RR)存在间隙锁，出现死锁的几率要大于已提交读(RC)。
可重复读(RR)隔离级别下，条件未命中索引会锁表(如果命中索引则锁行)，已提交读(RC)隔离级别下只会锁行。

然后我们验证下可重复读(RR)下命中索引会不会从锁表变锁行：

我们再来看下已提交读(RC)未命中索引会不会是行级锁:
在RC隔离级别下，半一致性读特性增加了update操作的并发性。
半一致性读：它是已提交读和一致性读的结合，一个update语句，如果读到一行已经加锁的记录，此InnoDB返回记录最近提交的版本，由MySQL上层判断此版本是否满足 update的where条件。若满足(需要更新)，则 MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)。
上面的含义解释比较晦涩，通俗的来说主要体现下面两点：
• 减少了更新同一行记录时的冲突，减少锁等待。
有并发冲突，读事务最新的commit版本，不加锁，无需锁等待。
• 可以提前放锁，进一步减少并发冲突概率。(这一点我个人感觉很像上面第二点)
对于不满足update更新条件的记录，可以提前放锁，减少并发冲突的概率。
首先我们先来回顾一下RR更新数据未命中索引会锁表，自身事务不提交导致其他事务阻塞的情况

然后咱们再看看RC隔离级别下更新不满足条件半一致性读是如何提高数据库并发的