mysql事务及隔离级别分析

最新推荐文章于 2023-09-24 17:17:27 发布

Duktig丶

最新推荐文章于 2023-09-24 17:17:27 发布

阅读量608

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库事务事务隔离级别

本文链接：https://blog.csdn.net/qq_42937522/article/details/119375657

版权

mysql 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

背景

原文地址：https://duktig.cn/archives/73/

事务

什么是事务？

数据库事务指的是一组sql语句组成的数据库逻辑处理单元，在这组的sql操作中，要么全部执行成功，要么全部执行失败。

例子：转账。用户A要转账给用户B，要经历如下过程：用户A转账扣钱->用户B收账加钱，为了保证数据的一致性，要采用事务。两步操作都要成功才能成功，只要有一步出错，全都执行失败，即回滚。

事务的特性（ACID）

原子性（Atomicity）：事务的原子性操作，数据操作要么全部成功，要么全部失败。
- 基于日志的Redo/Undo机制
一致性（Consistent）：事务执行前后的状态要一致，可理解为数据一致性。
隔离性（Isalotion）：事务之前相互隔离，不受影响，与事务的隔离级别密切相关。
- 数据库系统提供-定的隔离机制，事务处理过程中的中间状态对外部是不可见的，保证事务在不受外部并发操作影响的“独立”环境执行。
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的（持久化到数据库），即使出现系统故障也能够保持。

原子性、隔离性、持久性都是为了保障一致性而存在的，一致性也是最终的目的。

什么是`Redo/Undo`机制？

Redo log用来记录某数据块被修改后的值，可以用来恢复事务已提交但还未持久化到数据库的数据；Undo log是用来记录数据更新前的值，保证数据更新失败能够回滚。

场景：假如某个时刻数据库崩溃，在崩溃之前有事务A和事务B在执行，事务A已经提交，而事务B还未提交。当数据库重启进行 crash-recovery 时，就会通过Redo log将已经提交事务的更改写到数据文件，而还没有提交的就通过Undo log进行roll back。

什么是脏读？幻读？不可重复读？（事务可能导致的问题）

脏读

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

幻读

幻读是针对数据**插入（INSERT）**操作来说的。假设事务A修改了某些行的数据，但未提交，此时事务B插入了与事务A更改前记录相同的记录行，并先于事务A提交。那么在事务A查询时，会发现好像刚才更改对某些数据未起作用，但其实是事务B刚刚插入进来的，感觉除了幻觉，称之为幻读。

不可重复读

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

可重复读（正常情况）

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

事务隔离级别

隔离级别解决的问题

事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。

隔离级别	脏读	不可重复读	幻读
读未提交（READ UNCOMMITTED）	可能	可能	可能
读提交（READ COMMITTED）	不可能	可能	可能
可重复读（REPEATABLE READ）	不可能	不可能	可能
串行化（SERIALIZABLE）	不可能	不可能	不可能

从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是 MySQL 的默认级别。

如何设置隔离级别

查看当前数据库的隔离级别：

# 查看事务隔离级别 5.7.20 之后
show variables like 'transaction_isolation';
SELECT @@transaction_isolation;

# 5.7.20 之前
SELECT @@tx_isolation;
show variables like 'tx_isolation';

#结果：
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

查询当前有多少事务正在运行：

select * from information_schema.innodb_trx;

修改数据库的隔离级别：

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

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

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

set global transaction isolation level read committed;

隔离级别分析

建立一张表用来测试

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4

初始只有一条记录：

初始user表数据

读未提交

MySQL 事务隔离其实是依靠锁来实现的，加锁自然会带来性能的损失。而读未提交隔离级别是不加锁的，所以它的性能是最好的，没有加锁、解锁带来的性能开销。但有利就有弊，这基本上就相当于裸奔啊，所以它连脏读的问题都没办法解决。

任何事务对数据的修改都会第一时间暴露给其他事务，即使事务还没有提交。

做一个实验，先将全局隔离级别设置为读未提交：

set global transaction isolation level read uncommitted;

设置完成后，只对之后新起的 session 才起作用，对已经启动 session 无效。如果用 shell 客户端那就要重新连接 MySQL，如果用 Navicat 那就要创建新的查询窗口。这时候再重新启动两个黑窗口进行模拟。

Mysql中开启事务有两种方式begin/start transaction，最后提交事务执行commit，或者回滚事务rollback。在执行begin/start transaction命令，它们并不是一个事务的起点，在执行完它们后的第一个sql语句，才表示事务真正的启动。

分析执行流程：

1、在第一个黑窗口（事务A）中，执行begin；后；将id=1的数据行改为name='duktig666'。

begin;
UPDATE user SET name='duktig666' WHERE id = 2;

2、在第二个黑窗口（事务B）中，执行执行begin；后；执行查询，观察数据。

begin;
SELECT * FROM user;

3、在第一个黑窗口（事务A）中将事务回滚，在第二个黑窗口（事务B）中再次执行查询，观察数据。

rollback;

SELECT * FROM user;
commit;

读未提交分析

总结：

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

读提交

既然读未提交没办法解决脏数据问题，那么就有了读提交。

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

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

继续验证，将事务隔离级别设置为读提交，然后重新打开两个mysql黑窗口。

set global transaction isolation level read committed;

分析执行流程：

1、事务A开启事务，执行修改操作修改id=2的name：duktig->duktig666。

2、此时事务A未提交，事务B开启事务，执行查询操作，数据为duktig。

3、事务A提交，事务B再次执行查询操作，数据为duktig666。

具体代码参看“读未提交都差不多”。

读提交分析

在不同的时刻，查询出来的数据可能是不一致的，可能会受到其他事务的影响。

总结：

读提交解决了脏读的问题，但是无法做到可重复读，也没办法解决幻读。

可重复读

可重复是对比不可重复而言的，上面说不可重复读是指同一事务不同时刻读到的数据值可能不一致。

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

继续验证，需改全局隔离级别为可重复读级别，将name重置为duktig，并重新打开两个黑窗口。

set global transaction isolation level repeatable read;

可对读提交的流程再执行一次，发现修改操作不会出现可重复读，即解决了可重复读（上述操作不在重复验证）。但是更新操作又引起了数据不一致（幻读）。

分析验证流程：

1、开启事务A，执行修改操作修改id=2的name：duktig->duktig666。

2、开启事务B，在事务A执行完update后，执行insert操作，插入记录“name='duktig' age=23”（这条数据和事务A修改前的name和age的值相同）。

INSERT INTO user (name,age) VALUES ('duktig',23);

3、事务B提交后，事务A执行select操作，查询age=23的数据，这时出现了多一行的数据，这是事务B刚刚插入的，即幻读。

可重复读分析

看到有文章提到，在Mysql中，默认的不可重复读个隔离级别也解决了幻读的问题。但是我这确实出现了幻读问题，这需要再分析分析。

串行化

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

MySQL 中是如何实现事务隔离的？

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

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

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

实现可重复读

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

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

可重复读实现原理图

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

快照，学名叫做一致性视图。可重复读是在事务开始的时候生成一个当前事务全局性的快照，而读提交则是每次执行语句的时候都重新生成一次快照。

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

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

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

大致理解为：读提交只有在事务Acommit后事务B才能独到其数据，所以解决了脏读问题。可重复读只能在事务开始时才能读到数据，所以无论在事务的那个阶段，读到的数据都是一致的。

并发写问题

存在这的情况，两个事务，对同一条数据做修改。最后结果应该是哪个事务的结果呢，肯定要是时间靠后的那个对不对。并且更新之前要先读数据，这里所说的读和上面说到的读不一样，更新之前的读叫做“当前读”，总是当前版本的数据，也就是多版本中最新一次提交的那版。

假设事务A执行 update 操作， update 的时候要对所修改的行加行锁，这个行锁会在提交之后才释放。而在事务A提交之前，事务B也想 update 这行数据，于是申请行锁，但是由于已经被事务A占有，事务B是申请不到的，此时，事务B就会一直处于等待状态，直到事务A提交，事务B才能继续执行，如果事务A的时间太长，那么事务B很有可能出现超时异常。

加锁的过程要分有索引和无索引两种情况，比如下面这条语句

update user set age=11 where id = 1

id 是这张表的主键，是有索引的情况，那么 MySQL 直接就在索引数中找到了这行数据，然后干净利落的加上行锁就可以了。

而下面这条语句

update user set age=11 where age=10

表中并没有为 age 字段设置索引，所以， MySQL 无法直接定位到这行数据。那怎么办呢，当然也不是加表锁了。MySQL 会为这张表中所有行加行锁，没错，是所有行。但是呢，在加上行锁后，MySQL 会进行一遍过滤，发现不满足的行就释放锁，最终只留下符合条件的行。虽然最终只为符合条件的行加了锁，但是这一锁一释放的过程对性能也是影响极大的。所以，如果是大表的话，建议合理设计索引，如果真的出现这种情况，那很难保证并发度。

幻读问题

上面介绍可重复读的时候，那张图里标示着出现幻读的地方实际上在 MySQL 中并不会出现，MySQL 已经在可重复读隔离级别下解决了幻读的问题。

前面刚说了并发写问题的解决方式就是行锁，而解决幻读用的也是锁，叫做间隙锁，MySQL 把行锁和间隙锁合并在一起，解决了并发写和幻读的问题，这个锁叫做 Next-Key锁。

假设现在表中有两条记录，并且 age 字段已经添加了索引，两条记录 age 的值分别为 10 和 30。

此时，在数据库中会为索引维护一套B+树，用来快速定位行记录。B+索引树是有序的，所以会把这张表的索引分割成几个区间。

幻读问题解决

如图所示，分成了3 个区间，(负无穷,10]、(10,30]、(30,正无穷]，在这3个区间是可以加间隙锁的。

在事务A提交之前，事务B的插入操作只能等待，这就是间隙锁起得作用。在事务A执行更新操作时，

update user set name='风筝2号’ where age = 10; 的时候，由于条件 where age = 10 ，数据库不仅在 age =10 的行上添加了行锁，而且在这条记录的两边，也就是(负无穷,10]、(10,30]这两个区间加了间隙锁，从而导致事务B插入操作无法完成，只能等待事务A提交。不仅插入 age = 10 的记录需要等待事务A提交，age<10、10<age<30 的记录页无法完成，而大于等于30的记录则不受影响，这足以解决幻读问题了。

这是有索引的情况，如果 age 不是索引列，那么数据库会为整个表加上间隙锁。所以，如果是没有索引的话，不管 age 是否大于等于30，都要等待事务A提交才可以成功插入。

参考：