mysql事务隔离级别与IO的关系 10

最新推荐文章于 2022-08-15 13:36:22 发布

哒哒-blog

最新推荐文章于 2022-08-15 13:36:22 发布

阅读量278

点赞数

分类专栏： mysql 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_44102152/article/details/109442671

版权

mysql 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

mysql事务隔离级别与IO的关系

一、事务隔离级别类型介绍
- 1. 事务隔离级别级别基本概念
- 2. 事务隔离级别基本操作
二、读取未提交与读取已提交
- 1. 读取未提交-脏读问题
- 2. 读取已提交 - 不可重复读问题
三、可重复读取与串行化
- 1. 可重复读取 - 幻读问题
- 2. 串行化
四、事务隔离级别的实现
五、事务与IO的关系

一、事务隔离级别类型介绍

1. 事务隔离级别级别基本概念

以下几个概念是事务隔离级别要实际解决的问题，所以需要搞清楚都是什么意思。

脏读 : 脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。

可重复读 : 可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据更新（UPDATE）操作。

不可重复读 : 对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操作。

幻读 : 幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

事务隔离级别
SQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

读取未提交（READ UNCOMMITTED）
读取已提交（READ COMMITTED）
可重复读（REPEATABLE READ）
串行化（SERIALIZABLE）

从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是 MySQL 的默认级别。事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题，下面展示了 4 种隔离级别对这三个问题的解决程度。

隔离级别	脏读	不可重复读	幻读
读取未提交	可能	可能	可能
读取已提交	不可能	可能	可能
可重复读	不可能	不可能	可能
串行化	不可能	不可能	不可能

只有串行化的隔离级别解决了全部这 3 个问题，其他的 3 个隔离级别都有缺陷。

2. 事务隔离级别基本操作

如何设置隔离级别

我们可以通过以下语句查看当前数据库的隔离级别，通过下面语句可以看出我使用的 MySQL 的隔离级别是 REPEATABLE-READ，也就是可重复读，这也是 MySQL 的默认级别。

mysql> show variables like "transaction_isolation";  #注意：mysql8 以下 tx_isolation
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.08 sec)

稍后，我们要修改数据库的隔离级别，所以先了解一下具体的修改方式。

修改隔离级别的语句是：

set [作用域] transaction_isolation level [事务隔离级别]，
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}。

其中作用于可以是 SESSION 或者 GLOBAL，GLOBAL 是全局的，而 SESSION 只针对当前回话窗口。隔离级别是 {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} 这四种，不区分大小写。

比如下面这个语句的意思是设置全局隔离级别为读提交级别。

set global transaction isolation level read committed;--修改全局隔离级别为读取已提交
set session transaction isolation level read committed;--修改当前会话隔离级别为读取已提交

show global variables like "transaction_isolation"; --查询全局隔离级别
show session variables like "transaction_isolation"; --查询当前会话隔离级别

以下是修改测试：

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like "transaction_isolation";
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.02 sec)

测试表数据如下：

mysql> select * from user;
+----+----------+------+
| id | username | age  |
+----+----------+------+
|  1 | shine    |   20 |
|  2 | starsky  |   20 |
|  4 | will     |   10 |
|  5 | harry    |   10 |
|  7 | cara     |   30 |
|  8 | jace     |   40 |
+----+----------+------+
6 rows in set (0.01 sec)

二、读取未提交与读取已提交

1. 读取未提交-脏读问题

MySQL 事务隔离其实是依靠锁来实现的，加锁自然会带来性能的损失。而读未提交隔离级别是不加锁的，所以它的性能是最好的，没有加锁、解锁带来的性能开销。但有利就有弊，这基本上就相当于裸奔啊，所以它连脏读的问题都没办法解决。任何事务对数据的修改都会第一时间暴露给其他事务，即使事务还没有提交。

下面来做个简单实验验证一下，首先设置全局隔离级别为读未提交。

mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like "transaction_isolation";
+-----------------------+------------------+
| Variable_name         | Value            |
+-----------------------+------------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.01 sec)

设置完成后，只对之后新起的 session 才起作用，对已经启动 session 无效。如果用 shell 客户端那就要重新连接 MySQL，如果用 Navicat 那就要创建新的查询窗口。

启动两个事务，分别为事务A和事务B，在事务A中使用 update 语句，修改 age 的值为20，初始是10 ，在执行完 update 语句之后，在事务B中查询 user 表，会看到 age 的值已经是 20 了，这时候事务A还没有提交，而此时事务B有可能拿着已经修改过的 age=20 去进行其他操作了。在事务B进行操作的过程中，很有可能事务A由于某些原因，进行了事务回滚操作，那其实事务B得到的就是脏数据了，拿着脏数据去进行其他的计算，那结果肯定也是有问题的。

在这里插入图片描述

读未提交，其实就是可以读到其他事务未提交的数据，但没有办法保证你读到的数据最终一定是提交后的数据，如果中间发生回滚，那就会出现脏数据问题，读未提交没办法解决脏数据问题。更别提可重复读和幻读了，想都不要想。

2. 读取已提交 - 不可重复读问题

既然读未提交没办法解决脏数据问题，那么就有了读提交。读提交就是一个事务只能读到其他事务已经提交过的数据，也就是其他事务调用 commit 命令之后的数据。那脏数据问题迎刃而解了。

读提交事务隔离级别是大多数流行数据库的默认事务隔离界别，比如 Oracle，但是不是 MySQL 的默认隔离界别。

我们继续来做一下验证，首先把事务隔离级别改为读提交级别。

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like "transaction_isolation";
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.01 sec)

之后需要重新打开新的 session 窗口，也就是新的 shell 窗口才可以。

同样开启事务A和事务B两个事务，在事务A中使用 update 语句将 id=1 的记录行 age 字段改为 30。此时，在事务B中使用 select 语句进行查询，我们发现在事务A提交之前，事务B中查询到的记录 age 一直是20，直到事务A提交，此时在事务B中 select 查询，发现 age 的值已经是 30 了。

这就出现了一个问题，在同一事务中(本例中的事务B)，事务的不同时刻同样的查询条件，查询出来的记录内容是不一样的，事务A的提交影响了事务B的查询结果，这就是不可重复读，也就是读取已提交隔离级别。

在这里插入图片描述
每个 select 语句都有自己的一份快照，而不是一个事务一份，所以在不同的时刻，查询出来的数据可能是不一致的。读提交解决了脏读的问题，但是无法做到可重复读，也没办法解决幻读。

三、可重复读取与串行化

1. 可重复读取 - 幻读问题

可重复是对比不可重复而言的，上面说不可重复读是指同一事物不同时刻读到的数据值可能不一致。而可重复读是指，事务不会读到其他事务对已有数据的修改，及时其他事务已提交，也就是说，事务开始时读到的已有数据是什么，在事务提交前的任意时刻，这些数据的值都是一样的。但是，对于其他事务新插入的数据是可以读到的，这也就引发了幻读问题。

同样的，需改全局隔离级别为可重复读级别。

mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like "transaction_isolation";
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

在这个隔离级别下，启动两个事务，两个事务同时开启。

首先看一下可重复读的效果，事务A启动后修改了数据，并且在事务B之前提交，事务B在事务开始和事务A提交之后两个时间节点都读取的数据相同，已经可以看出可重复读的效果。

在这里插入图片描述

2. 串行化

串行化是4种事务隔离级别中隔离效果最好的，解决了脏读、可重复读、幻读的问题，但是效果最差，它将事务的执行变为顺序执行，与其他三个隔离级别相比，它就相当于单线程，后一个事务的执行必须等待前一个事务结束。

四、事务隔离级别的实现

首先说读未提交，它是性能最好，也可以说它是最野蛮的方式，因为它压根儿就不加锁，所以根本谈不上什么隔离效果，可以理解为没有隔离。

再来说串行化。读的时候加共享锁，也就是其他事务可以并发读，但是不能写。写的时候加排它锁，其他事务不能并发写也不能并发读。

最后说读提交和可重复读。这两种隔离级别是比较复杂的，既要允许一定的并发，又想要兼顾的解决问题

实现可重复读取

为了解决不可重复读，或者为了实现可重复读，MySQL 采用了 MVVC (多版本并发控制) 的方式。

我们在数据库表中看到的一行记录可能实际上有多个版本，每个版本的记录除了有数据本身外，还要有一个表示版本的字段，记为 row trx_id，而这个字段就是使其产生的事务的 id，事务 ID 记为 transaction id，它在事务开始的时候向事务系统申请，按时间先后顺序递增。

按照上面这张图理解，一行记录现在有 3 个版本，每一个版本都记录这使其产生的事务 ID，比如事务A的transaction id 是100，那么版本1的row trx_id 就是 100，同理版本2和版本3。

在这里插入图片描述

在上面介绍读提交和可重复读的时候都提到了一个词，叫做快照，学名叫做一致性视图，这也是可重复读和不可重复读的关键，可重复读是在事务开始的时候生成一个当前事务全局性的快照，而读提交则是每次执行语句的时候都重新生成一次快照。

对于一个快照来说，它能够读到那些版本数据，要遵循以下规则：

当前事务内的更新，可以读到；
版本未提交，不能读到；
版本已提交，但是却在快照创建后提交的，不能读到；
版本已提交，且是在快照创建前提交的，可以读到；

利用上面的规则，再返回去套用到读提交和可重复读的那两张图上就很清晰了。还是要强调，两者主要的区别就是在快照的创建上，可重复读仅在事务开始是创建一次，而读提交每次执行语句的时候都要重新创建一次。

五、事务与IO的关系

在这里插入图片描述

innodb_flush_log_at_trx_commit=0,innodb中的log thread每隔一秒钟将会log buffer中的数据写入文件，同时还会通知文件系统进行与文件同步的flush操作，保证数据确实已经写入磁盘。但是，每次事务的结束（commit或者rollback）并不会触发log thread将log buffer中的数据写入文件。所以当设置为0时候，在mysql crash或者oscrash或者主机断电的情况，最极端的情况是丢失一秒的数据变更。
innodb_flush_log_at_trx_commit=1,这也是innodb默认设置，每次事务的结束都会触发log thread将log buffer中的数据写入文件，并通知文件系统同步文件。这个设置是最安全的，能够保证不论是mysql crash，os crash还是主机断电都不会丢失任何已经提交的事务。
innodb_flush_log_at_trx_commit=2,log thread会在每次事务结束后将数据写入事务日志，但是仅仅是调用了文件系统的写入操作，而文件系统都是有缓存的，所以log thread的写入并不能保证将文件系统中缓存写入到物理磁盘进行永久固化。文件系统什么时候将缓存中的数据同步到物理磁盘，log thread 并不知道。所以当设置为2的时候，mysql 的吵嚷声并不会造成数据的丢失，但是os crash或者主机断电可能造成事务日志的丢失，各种文件系统对文件缓存的刷新机制各不相同。

总结：

从以上的分析得出，当innodb_flush_log_at_trx_commit设置为1时是最安全的，但由于所作的io同步操作最多，性能也是三种当中最差的；如果设置为0，则每秒同步一次，性能相对高些，如果设置为2，性能可能是这三种中最好的，但也有可能会出现故障后丢失的数据最多的。至于具体应该如何设置，一般来说，如果不能完全接受数据的丢失，那可以通过牺牲一定的性能来换取数据的安全性，选择设置为1，如果允许丢失少量的数据（比如说1秒内），那么设置为0，当然如果操作系统够稳定，主机的硬件设备足够好的话，而且主机的供电系统也足够安全的话，那么可以将innodb_flush_log_at_trx_commit=2,保证系统的高性能。

下一篇：mysql sql分析 11
上一篇：mysql事务与锁的关系 09