MySQL事务隔离级别解密

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: READ UNCOMMITTED 未提交读  在READ UNCOMMITTED级别,事务中的修改,即使没有提交,对其他事务也都是可见的。
  1. READ UNCOMMITTED 未提交读 
    在READ UNCOMMITTED级别,事务中的修改,即使没有提交,对其他事务也都是可见的。事务可以读取未提交的数据,这也被称作脏读。这个级别会导致很多问题,从性能上来说,READ UNCOMMITTED不会比其他的级别好太多,但确缺乏其他级别的很多好处,除非真的有必要的理由,在实际应用中一般很少使用。
  2. READ COMMITED 提交读 
    大多数数据库系统的默认隔离级别是 READ COMMITED(但mysql不是)。READ COMMITED满足前面提到的隔离性简单定义:一个事务开始时,只能看见已经提交的事务所做的修改。换句话说,一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读,因为两次执行同样的查询,可能会得到不一样的结果。
  3. REPEATABLE READ 可重复写 
    MySQL的默认事务隔离级别。 
    REPEATABLE READ解决了脏读的问题。该级别保证了在同一个事务中多次读取同样的记录的结果是一致的。但是理论上,可重复读隔离级别还是无法解决另一个幻读的问题。所谓幻读,指的是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行。InnoDB存储引擎通过多版本并发控制(MVCC)解决了幻读的问题。
  4. SERIALIZABLE 可串行化 
    SERIALIZABLE 是最高的隔离级别。它通过强事务串行执行,避免了前面说的幻读的问题。简单来说,SERIALIZABLE会在曲度的每一行数据都加上锁,所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别,只有在非常需要确保数据一致性而且可以接受没有并发的情况下,才考虑使用该级别。

实验

上面是摘自《高性能MySQL》一书,光这么看,可能也理解不到多少东西,不如直接做实验亲身体验一下,加深理解。

我们先简单创建一个表 users,结构如下:

+------------+------------------+------+-----+-------------------+----------------+
| Field      | Type             | Null | Key | Default           | Extra          |
+------------+------------------+------+-----+-------------------+----------------+
| id         | int(11) unsigned | NO   | PRI | <null>            | auto_increment |
| name       | varchar(50)      | NO   |     |                   |                |
| created_at | datetime         | NO   |     | CURRENT_TIMESTAMP |                |
| updated_at | datetime         | NO   |     | CURRENT_TIMESTAMP |                |
| deleted_at | datetime         | YES  |     | <null>            |                |
+------------+------------------+------+-----+-------------------+----------------+
AI 代码解读

实现插入三条数据:

+----+-------------+---------------------+---------------------+------------+
| id | name        | created_at          | updated_at          | deleted_at |
+----+-------------+---------------------+---------------------+------------+
| 1  | coolcao2018 | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom         | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili        | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+-------------+---------------------+---------------------+------------+
AI 代码解读

然后打开两个终端,A和B分别表示两个用户同时在操作。

READ UNCOMMITTED

对于用户A,操作:

mysql root@localhost:test> set session transaction isolation level read uncommitted;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> select * from users;
+----+-------------+---------------------+---------------------+------------+
| id | name        | created_at          | updated_at          | deleted_at |
+----+-------------+---------------------+---------------------+------------+
| 1  | coolcao2018 | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom         | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili        | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+-------------+---------------------+---------------------+------------+
AI 代码解读

然后,用户B操作,

mysql root@localhost:test> set session transaction isolation level read uncommitted;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> update users set name='coolcao' where id=1;
AI 代码解读

此时,用户B并未提交事务,用户A进行查询操作看看:

mysql root@localhost:test> select * from users;
+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom     | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili    | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+---------+---------------------+---------------------+------------+
AI 代码解读

从整个过程来看,用户B还并未提交事务,但是A却已经能够直接读到B的更新。

从上面实验结果来看,不难理解上面对于 READ UNCOMMITTED级别的描述: 在READ UNCOMMITTED级别,事务中的修改,即使没有提交,对其他事务也都是可见的。

READ COMMITTED

同时将A,B两个终端的事务级别设置为 read committed:

// 在A,B两个终端都执行
set session transaction isolation level read committed;
AI 代码解读

对于A,我们开启一个事务,然后更新一下数据,但并不提交事务:

mysql root@localhost:test>  set session transaction isolation level read committed;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> select * from users;
+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom     | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili    | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+---------+---------------------+---------------------+------------+
mysql root@localhost:test> update users set name='coolcao222' where id=1;
mysql root@localhost:test> select * from users;
+----+------------+---------------------+---------------------+------------+
| id | name       | created_at          | updated_at          | deleted_at |
+----+------------+---------------------+---------------------+------------+
| 1  | coolcao222 | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom        | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili       | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+------------+---------------------+---------------------+------------+
AI 代码解读

然后,在B终端,开启另外一个事务,进行数据查询:

mysql root@localhost:test> set session transaction isolation level read committed;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> select * from users;
+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom     | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili    | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+---------+---------------------+---------------------+------------+
AI 代码解读

然后,将A事务提交:

commit;
AI 代码解读

这时,再在B查询 :

mysql root@localhost:test> select * from users;
+----+------------+---------------------+---------------------+------------+
| id | name       | created_at          | updated_at          | deleted_at |
+----+------------+---------------------+---------------------+------------+
| 1  | coolcao222 | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom        | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili       | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+------------+---------------------+---------------------+------------+
AI 代码解读

从结果来看,也不难理解 read committed级别,对于一个事务,只能读取到当前事务的数据和其他已经提交的事务的数据,对于其他未提交事务的数据,读不到。

而且,从上面的实验结果中,我们也看到了,会话B在会话A提交事务前后查询的结果并不一致,这也就是上面所说的,不可重复读。

REPEATABLE READ 可重复读

我们将会话A设置为REPEATABLE READ :

mysql root@localhost:test> set session transaction isolation level repeatable read;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> select * from users;
+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-08-10 15:21:02 | 2018-08-10 15:21:02 | <null>     |
| 2  | tom     | 2018-08-10 15:21:07 | 2018-08-10 15:21:07 | <null>     |
| 3  | lili    | 2018-08-10 15:21:11 | 2018-08-10 15:21:11 | <null>     |
+----+---------+---------------------+---------------------+------------+
AI 代码解读

此时,我们在B终端插入一条数据:

mysql root@localhost:test> insert into users (id,name) values (4,'coco');
mysql root@localhost:test> commit;
mysql root@localhost:test> select * from users;
+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-08-10 15:21:02 | 2018-08-10 15:21:02 | <null>     |
| 2  | tom     | 2018-08-10 15:21:07 | 2018-08-10 15:21:07 | <null>     |
| 3  | lili    | 2018-08-10 15:21:11 | 2018-08-10 15:21:11 | <null>     |
| 4  | coco    | 2018-08-10 15:23:50 | 2018-08-10 15:23:50 | <null>     |
+----+---------+---------------------+---------------------+------------+
AI 代码解读

在终端B中,插入一条记录,并提交,这时id=4的用户已经被插入到数据库。

此时,再回到终端A,查询:

mysql root@localhost:test> select * from users;
+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-08-10 15:21:02 | 2018-08-10 15:21:02 | <null>     |
| 2  | tom     | 2018-08-10 15:21:07 | 2018-08-10 15:21:07 | <null>     |
| 3  | lili    | 2018-08-10 15:21:11 | 2018-08-10 15:21:11 | <null>     |
+----+---------+---------------------+---------------------+------------+
AI 代码解读

哎,查询的结果中,没有B刚插入的id=4的用户,这也就是说该级别的事务隔离,保证了在同一个事务中多次读取同样的记录的结果是一致的。这时,我们在A中插入一条记录:

mysql root@localhost:test> insert into users (id,name) values (4,'coco');
(1062, u"Duplicate entry '4' for key 'PRIMARY'")
AI 代码解读

哎,这个时候,数据库报错了,提示主键重复。明明我在这个事务中,查询的数据只有1,2,3,为什么插入4的时候提示主键冲突呢?是发生幻觉了么?是的,发生“幻读”了。由于REPEATABLE READ级别的隔离,在一个事务中,多次读取同样记录的结果是一致的,在这多次读取之间,被别的事务插入了新的数据,这时前事务再插入数据,必然会导致错误。

SERIALIZABLE 可串行化

我们将A,B同时设置为SERIALIZABLE, 然后在A开启是个事务,做一个简单查询:

mysql root@localhost:test> set session transaction isolation level serializable;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> select * from users where id<10;
+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-08-10 15:21:02 | 2018-08-10 15:21:02 | <null>     |
| 2  | tom     | 2018-08-10 15:21:07 | 2018-08-10 15:21:07 | <null>     |
| 3  | lili    | 2018-08-10 15:21:11 | 2018-08-10 15:21:11 | <null>     |
| 4  | coco    | 2018-08-10 15:23:50 | 2018-08-10 15:23:50 | <null>     |
| 5  | juli    | 2018-08-10 15:31:32 | 2018-08-10 15:31:32 | <null>     |
+----+---------+---------------------+---------------------+------------+
AI 代码解读

此时,A事务并未提交,然后在B再开启一个事务,进行插入操作:

mysql root@localhost:test> set session transaction isolation level serializable;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> insert into users (id,name) values (6,'kate');
(1205, u'Lock wait timeout exceeded; try restarting transaction')
AI 代码解读

你会发现,哎我去,B事务被挂住了,然后过了一段时间,提示了错误 (1205, u'Lock wait timeout exceeded; try restarting transaction') ,说等待锁超时。

是的,在串行化级别,会在读取的每一行数据都加上锁,也就是说,上面A事务在读取时,已经加了锁,此时B事务在插入操作时,得等待锁的放开,时间一长,A锁未放开,B就报错了。

从实验中可以看出,可串行化级别,由于要保证避免幻读而加了锁导致效率以及可能会触发的等待锁超时等错误,实际应用中,该级别的事务隔离也很少使用。

对照着实验结果,来理解上面四个隔离级别,就容易理解了。

1、具有1-5工作经验的,面对目前流行的技术不知从何下手,需要突破技术瓶颈的可以加群。

2、在公司待久了,过得很安逸,但跳槽时面试碰壁。需要在短时间内进修、跳槽拿高薪的可以加群。

3、如果没有工作经验,但基础非常扎实,对java工作机制,常用设计思想,常用java开发框架掌握熟练的,可以加群。

4、觉得自己很牛B,一般需求都能搞定。但是所学的知识点没有系统化,很难在技术领域继续突破的可以加群。

5.群号 468947140,点击链接加入群聊【Java-BATJ企业级资深架构】:https://jq.qq.com/?_wv=1027&k=57VIGuh

6.阿里Java高级大牛直播讲解知识点,分享知识,知识点都是各位老师多年工作经验的梳理和总结,带着大家全面、科学地建立自己的技术体系和技术认知!

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
32
分享
相关文章
MySQL底层概述—9.ACID与事务
本文介绍了数据库事务的ACID特性(原子性、一致性、隔离性、持久性),以及事务控制的演进过程,包括排队、排它锁、读写锁和MVCC(多版本并发控制)。文章详细解释了每个特性的含义及其在MySQL中的实现方式,并探讨了事务隔离级别的类型及其实现机制。重点内容包括:ACID特性(原子性、持久性、隔离性和一致性的定义及其实现方式)、事务控制演进(从简单的全局排队到复杂的MVCC,逐步提升并发性能)、MVCC机制(通过undo log多版本链和Read View实现高效并发控制)、事务隔离级别(析了四种隔离级别(读未提交、读已提交、可重复读、可串行化)的特点及适用场景)、隔离级别与锁的关系。
京东面试:MySQL MVCC是如何实现的?如何通过MVCC实现读已提交、可重复读隔离级别的?
1.请解释什么是MVCC,它在数据库中的作用是什么? 2.在MySQL中,MVCC是如何实现的?请简述其工作原理。 3.MVCC是如何解决读-写和写-写冲突的? 4.在并发环境中,当多个事务同时读取同一行数据时,MVCC是如何保证每个事务看到的数据版本是一致的? 5.MVCC如何帮助提高数据库的并发性能?
京东面试:MySQL MVCC是如何实现的?如何通过MVCC实现读已提交、可重复读隔离级别的?
MySQL事务日志-Undo Log工作原理分析
事务的持久性是交由Redo Log来保证,原子性则是交由Undo Log来保证。如果事务中的SQL执行到一半出现错误,需要把前面已经执行过的SQL撤销以达到原子性的目的,这个过程也叫做"回滚",所以Undo Log也叫回滚日志。
155 7
MySQL事务日志-Undo Log工作原理分析
【MySQL基础篇】事务(事务操作、事务四大特性、并发事务问题、事务隔离级别)
事务是MySQL中一组不可分割的操作集合,确保所有操作要么全部成功,要么全部失败。本文利用SQL演示并总结了事务操作、事务四大特性、并发事务问题、事务隔离级别。
1730 2
【MySQL基础篇】事务(事务操作、事务四大特性、并发事务问题、事务隔离级别)
MySQL进阶突击系列(04)事务隔离级别、AICD、CAP、BASE原则一直搞不懂? | 看这篇就够了
本文详细介绍了数据库事务的四大特性(AICD原则),包括原子性、隔离性、一致性和持久性,并深入探讨了事务并发问题与隔离级别。同时,文章还讲解了分布式系统中的CAP理论及其不可能三角关系,以及BASE原则在分布式系统设计中的应用。通过具体案例和图解,帮助读者理解事务处理的核心概念和最佳实践,为应对相关技术面试提供了全面的知识准备。
MySQL的事务隔离级别
【10月更文挑战第17天】MySQL的事务隔离级别
181 43
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
1987 14
MySQL事务日志-Redo Log工作原理分析
mysql事务特性
原子性:一个事务内的操作统一成功或失败 一致性:事务前后的数据总量不变 隔离性:事务与事务之间相互不影响 持久性:事务一旦提交发生的改变不可逆
MySQL事务隔离级别及默认隔离级别的设置
在数据库系统中,事务隔离级别是一个关键的概念,它决定了事务在并发执行时如何相互隔离。MySQL提供了四种事务隔离级别,每种级别都解决了不同的并发问题。本文将详细介绍这些隔离级别以及MySQL的默认隔离级别。
Oracle和MySQL有哪些区别?从基本特性、技术选型、字段类型、事务、语句等角度详细对比Oracle和MySQL
从基本特性、技术选型、字段类型、事务提交方式、SQL语句、分页方法等方面对比Oracle和MySQL的区别。
1300 18

热门文章

最新文章

AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等