系统城装机大师 - 固镇县祥瑞电脑科技销售部宣传站!

当前位置:首页 > 数据库 > Mysql > 详细页面

带你搞懂MySQL的事务隔离级别

时间:2023-10-29来源:系统城装机大师作者:佚名

一、并行事务会有什么问题?

在讲事务隔离级别之前,我们先想一下,如果有多个事务并行执行,MySQL数据最终会有什么问题?

可以说,事务的存在都是为了防止并发问题,我们的MySQL数据库可以同时接受多个client连接,即支持同时多个事务处理,当多个事务同时进行的时候,可能会出现以下等问题:

脏写(dirty write)

脏读(dirty read)

不可重复读(non-repeatable read)

幻读(phantom read)

1.1 脏写

脏写 *(dirty write) *,直白说就是两个事务同时更新一行数据,事务A回滚把事务B的值覆盖了,实质就是两个未提交的事务互相影响。

举个例子, 现在有一张表:

1
2
3
4
5
6
7
8
CREATE TABLE `bank_balance` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(45) NOT NULL COMMENT '用户名',
  `balance` int NOT NULL DEFAULT '0' COMMENT '余额,单位:人民币分,比如100表示人民币1元,默认是0',
  `wealth` tinyint NOT NULL DEFAULT '0' COMMENT '富有程度,0:贫穷,1:富有',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_bank_balance_user_name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

表中有一行id=3 且 user_name=Tom的记录:

1
2
3
4
5
6
7
mysql> select id,user_name,balance from bank_balance where user_name = 'Tom';
+----+-----------+---------+
| id | user_name | balance |
+----+-----------+---------+
| 3 | Tom       |     100 |
+----+-----------+---------+
1 row in set (0.00 sec)

现在有两个事务,事务A和事务B,事务A是给Tom账户余额加100,事务B是给Tom账户余额加200。

在①处,事务A得到的余额是200,事务B得到的余额是300,如果事务B是后更新,那么就覆盖了事务A的值。

在②处,事务 A 和事务B都没有提交的情况下,它们随时都有可能发生回滚,如上图这种情况事务 A 发生了回滚,然后事务B再提交,那么对于事务 B 看到的场景而言,就是自己明明更新了,结果值却还是旧值,这就是 脏写 。

1.2 脏读

*脏读(dirty read) **** ***指的是读到了其他事务未提交的数据,未提交意味着可能会回滚,也就是可能最终不会持久化到数据库中。其他事务读到了不会持久化的数据,这就是脏读。

比如下图,如果事务A在①处发生回滚,那么事务B在②处使用的Tom余额值200就是一个过期值,这种就是典型的 脏读现象

1.3 不可重复读

*不可重复读(non-repeatable read) *,指的是在同一事务内,相同数据在不同的时刻被读到了不一样的值,它和脏读不一样,脏读是指读取到了其他事务未提交的数据,而不可重复读表示读到了其他事务修改并提交后的值。

比如有两个事务,事务A和事务B,事务A查询Tom账户余额是100,事务B查询Tom账户余额也是100。

接下来,事务A把Tom账户余额更新为200,并提交事务。

当事务B继续读取Tom账户余额的时候,发现Tom账户余额是200了,和之前读取到的不一致,对于事务B而言,这种一个事务内多次读取得到不一样值的现象就称为不可重复读 现象

1.4 幻读

*幻读(phantom read) *,主要是是针对数据插入(INSERT)和删除(DELETE)操作来说的。

最经典的是插入的情况。假如现在有两个事务,事务A和事务B。事务A对某些行的内容作了更改,但是还未提交。

比如现在余额表中余额大于0的账户有2条,分别是小克和Tom,他们的富有程度都是贫穷:

1
2
3
4
5
6
7
mysql> select *from bank_balance where balance > 0;
+----+-----------+-----------+--------+
| id | user_name | balance   | wealth |
+----+-----------+-----------+--------+
|  2 | 小克      | 300000000 |      0 |
|  3 | Tom       |       100 |      0 |
+----+-----------+-----------+--------+

然后,接到上级命令,要把所有账户余额大于0的用户全部标识为富有,启动事务A完成这项任务,SQL如下:

1 update bank_balance set wealth = 1 where balance > 0;

SQL语句只是执行了,但是未提交。

紧接着,事务B插入了一条余额大于0的记录行(富有程度默认为贫穷),并且在事务A提交之前先提交了,SQL如下:

1 INSERT INTO `bank_balance` (`id`, `user_name`, `balance`) VALUES ('4', 'Eric', '500');

在这之后,如果事务A再发起相同条件的查询,会发现刚刚的更改对于某些数据未起作用(有些记录未被标识为富有),而且数据行比原来还多了!

这对于事务A而言,感觉出现了幻觉一样,这就是幻读现象

1.5 区别

读到这里,可能有些小伙伴就懵了,从脏读到幻读,感觉它们都一样的呀?其实,它们有实质性的区别:

1、脏读重在指一个事务读到了其他事务未提交的数据。

2、不可重复读主要在于一个事务中多次读到同一条数据,但前后读到的结果不一样,这是因为其他事务对数据进行修改并提交导致。

3、幻读则是因为被其他事务插入或者删除的数据影响,一个事务内同样条件的数据记录变多或者变少了。

二、事务隔离级别

前面已经讲完并行事务可能出现的问题,具体表象就是脏写,脏读,不可重复读,幻读。

针对这些问题,SQL定了一套标准,通过 隔离 来规避,且不同级别的隔离可以规避不同严重程度的事务问题,下面,我们一起看下SQL事务 隔离级别 都有哪些:

  • *读未提交(READ UNCOMMITTED) *,指一个事务还没提交,它做的修改就能被其他事务看到。

  • 读提 *交(READ COMMITTED) *,一个事务做的修改,只有提交之后,其他事务才能看到。

  • *可重复读(REPEATABLE READ) *,在整个事务过程中看到的数据,自始至终都是一致的。

  • *串行化(SERIALIZABLE) *,每个读写操作都会加锁,多个事务要访问同一条记录时,必须要进行排队,优先级低的事务必须等优先级高的事务完成以后才能进行。

从1到4,隔离级别依次变高,当然,性能也依次变差。那么这些隔离级别究竟都能防止哪些问题呢?来看一个表格:

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

但,MySQL InnoDB引擎默认的隔离级别是可重复读(RR) 。

为什么MySQL没有使用串行化这个级别?是不是意味着我们日常使用MySQL会有可能存在幻读的问题?

非也! 隔离级别越高代价也是越高的 ,且性能也越差。从性能上来说,当然是隔离级别越低越好。

至于隔离级别是RR(可重复读)下的MySQL怎么避免幻读问题,InnoDB引擎有它自己的想法,以后单独抽一讲来说啦~

我们再来看一张图,理解不同隔离级别下读取到的数据是怎么样的:

有两个事务,事务A和事务B,同时操作(查询或者给Tom余额加100),事务B在事务A提交前更新了Tom的余额,并且事务B在事务A前提交。

  • 读未提交隔离级别 下,事务 B 修改余额后,事务 A 能够马上看见,即使事务B还未提交,所以事务 A 中余额 R1 查询的值是 200,余额 R2、R3 也是 200.

  • 读提交隔离级别 下,事务 B 修改余额后,只有事务B提交后事务A才能看见,所以事务A中余额R1查询在提交前,查的值是100,余额R2和余额R3都是在事务B提交后,查询得到的值都是200。

  • 可重复读隔离级别 下,事务A在提交前自始至终查到的值都必须一样,所以,余额R1、R2都是100,当事务A提交后再查询(其实是新事务)就能查到新的值,所以R3是200。

  • 串行化隔离级别 下,MySQL会给记录行以及记录行之间的'空行'加锁,如果是A事务先获得锁,那么B事务必须等到A事务提交以后才能更新数据。

比如上图,如果事务A查询Tom余额的SQL条件是'where user_name = "Tom"', user_name有唯一索引,所以只会给Tom账户这一行数据加共享锁 。

当B事务要去更新Tom的账户余额时,是获取不到锁的, 必须等待直至事务A完全提交 。

所以以上R1、R2查询得到的值都是100(这个时候事务B在排队等待),事务A提交以后, 事务B就可以更新值并提交了,R3是在事务B提交之后查询,所以是200。

好啦,今天就先讲到这里啦。

到此这篇关于一文带你搞懂MySQL的事务隔离级别的文章就介绍到这了

分享到:

相关信息

  • MySQL的核心查询语句详解

    一、单表查询 1、排序 2、聚合函数 3、分组 4、limit 二、SQL约束 1、主键约束 2、非空约束 3、唯一约束 4、外键约束 5、默认值 三、多表查询 1、内连接 1)隐式内连接: 2)显式内连接: 2、外连接 1)左外连接 2)右外连接 四...

    2023-10-30

  • Mysql中如何删除表重复数据

    Mysql删除表重复数据 表里存在唯一主键 没有主键时删除重复数据 Mysql删除表中重复数据并保留一条 准备一张表 用的是mysql8 大家自行更改 创建表并添加四条相同的数据...

    2023-10-30

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载