全面解析MySQL中的隔离级别
数据库并发的对同一批数据进行增删改,就可能会出现我们所说的脏写、脏读、不可重复读、幻读等一系列问题。MySQL提供了一系列机制来解决事务并发问题,比如事务隔离、锁机制、MVCC多版本并发控制机制。今天来探究一下事务隔离机制。
事务是一组SQL组成的逻辑处理单元,先来看下事务的ACID特性:
- 原子性(Atomicity) :事务是一个原子操作单元,对数据进行修改,要么全执行要么全不执行。是从执行层面上来描述的。
- 一致性(Consistent) :在事务开始和完成时,数据都必须保持一致状态。是从执行结果层面上来描述的。
- 隔离性(Isolation) :数据库系统提供一定的隔离机制,保证事务执行过程中对外部不可见,独立运行,不受外部影响。
- 持久性(Durable) :事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
并发事务的影响:
- 脏写(更新丢失:Lost Update):多个事务选择了同一行,彼此不知道对方存在,会覆盖之前事务的数据操作。
- 脏读(Dirty Reads):A事务读取了B事务未提交的数据,B事务回滚,A提交,最终结果不符合一致性原则
- 不可重读(Non-Repeatable Reads):同一个事务,相同的查询语句,执行多次结果不一致,可能是外部事务修改导致的,不符合隔离性。
- 幻读(Phantom Reads):事务A读取到了事务B提交的新增数据,不符合隔离性
事务隔离级别:
隔离级别 | 脏读(Dirty Read) | 不可重复读(NonRepeatable Read) | 幻读(Phantom Read) |
读未提交(Read uncommitted) | 可能 | 可能 | 可能 |
读已提交(Read committed) | 不可能 | 可能 | 可能 |
可重复读(Repeatable Read) | 不可能 | 不可能 | 可能 |
串行化(Serializable) | 不可能 | 不可能 | 不可能 |
MySQL提供了上面四种隔离级别,隔离越严格,可能出现的问题就越少,但付出的性能代价就越大,默认的隔离级别是可重复读。下面使用客户端进行操作进行验证。
先加创建一张表和数据
CREATE TABLE `account` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `balance` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `account` (`id`, `balance`) VALUES (1, 500), (2, 600), (3, 200);
连接客户端,查看隔离级别,可以看到是可重复读:
MySQL [test]> show variables like 'tx_isolation'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+
读未提交测试:
AB客户端都执行set tx_isolation='read-uncommitted';设置隔离级别为读未提交。
A客户端开启事务:start transaction;查询数据:select * from account;
B客户端开启事务:start transaction;更新数据:update account set balance = balance - 100 where id = 1;此时事务未提交
A客户端再次查询数据:select * from account; 此时看到两次查询的数据已经不一样了
在B没提交前A就读到了B更新的数据,此时如果B回滚,那么A那边就是脏数据。这种情况就是读未提交造成的脏读。用读已提交隔离级别可以解决。
使用commit命令把AB客户端的事务提交。
读已提交测试:
AB客户端都执行 set tx_isolation='read-committed'; 设置隔离级别为读已提交。
A客户端开启事务:start transaction;查询数据:select * from account;
B客户端开启事务:start transaction;更新数据:update account set balance = balance - 100 where id = 1;此时事务未提交
A客户端再次查询数据:select * from account; 此时看到A客户端两次查询数据一致,未出现脏读情况
此时B客户端事务提交:commit;
A客户端再次查询数据:select * from account; 此时看到A客户端查询数据已经发生了变化,这就是不可重复读。
可重复读测试:
AB客户端都执行 set tx_isolation='repeatable-read'; 设置隔离级别为可重复读。
A客户端开启事务:start transaction;查询数据:select * from account;
B客户端开启事务:start transaction;更新数据:update account set balance = balance - 100 where id = 1; commit提交事务
A客户端再次查询数据:select * from account; 此时看到A客户端两次查询数据一致,重复读取数据一致。
A客户端执行更新语句:update account set balance = balance - 50 where id = 1;
A客户端再次查询数据:select * from account; 此时看到id=1的这条数据是B客户端更新之后的数据-50,数据的一致性没有被破坏
B客户端重新开启事务,插入一条数据:insert into account(id,balance) values (4,1000); commit提交事务;
A客户端查询,和上次结果一致
A客户端执行:update account set balance = balance - 100 where id = 4; 更新B客户端新插入的数据,能执行成功,再次查询所有数据,能插到id=4的数据,出现幻读。
# A客户端执行过程:# 设置隔离级别可重复度MySQL [test]> set tx_isolation='repeatable-read'; Query OK, 0 rows affected, 1 warning (0.00 sec) # 开启事务 MySQL [test]> start transaction; Query OK, 0 rows affected (0.00 sec) # 查询所有数据 MySQL [test]> select * from account; +----+---------+ | id | balance | +----+---------+ | 1 | 300 | | 2 | 600 | | 3 | 200 | +----+---------+ 3 rows in set (0.00 sec) # 再次查询验证两次结果是否一致 MySQL [test]> select * from account; +----+---------+ | id | balance | +----+---------+ | 1 | 300 | | 2 | 600 | | 3 | 200 | +----+---------+ 3 rows in set (0.00 sec) # 在B客户端插入数据之后,此次A客户端不能查询到 MySQL [test]> select * from account; +----+---------+ | id | balance | +----+---------+ | 1 | 150 | | 2 | 600 | | 3 | 200 | +----+---------+ 3 rows in set (0.00 sec) # A客户端更新B客户端插入的数据,发现可以更新成功 MySQL [test]> update account set balance = balance + 1000 where id = 4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # 再次查询,能查询到数据,出现幻读 MySQL [test]> select * from account; +----+---------+ | id | balance | +----+---------+ | 1 | 400 | | 2 | 600 | | 3 | 200 | | 4 | 2000 | +----+---------+ 4 rows in set (0.00 sec) # 提交事务 MySQL [test]> commit; Query OK, 0 rows affected (0.01 sec)
# B客户端执行过程:设置隔离级别可重复读 MySQL [test]> set tx_isolation='repeatable-read'; Query OK, 0 rows affected, 1 warning (0.00 sec) # 开启事务 MySQL [test]> start transaction; Query OK, 0 rows affected (0.00 sec) # 更新数据,直接提交 MySQL [test]> update account set balance = balance - 100 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MySQL [test]> commit; Query OK, 0 rows affected (0.01 sec) # 再次开启事务 MySQL [test]> start transaction; Query OK, 0 rows affected (0.00 sec) # 插入一条数据 MySQL [test]> insert into account(id,balance) values (4,1000); Query OK, 1 row affected (0.01 sec) MySQL [test]> commit; Query OK, 0 rows affected (0.00 sec)
最后一种串行化:set tx_isolation='serializable';可自行验证,能解决上面所有问题,但是一般不会用到的,保证一致性的同时带来的是性能大幅度下降,并发性极低,默认是可重复读。
通过隔离级别在一定程度上能处理事务并发的问题,除此之外还有其他的手段,后续会再次探究。
以上就是全面解析MySQL中的隔离级别的详细内容,更多关于MySQL 隔离级别的资料请关注猪先飞其它相关文章!
相关文章
- 这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
- 新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
深入研究mysql中的varchar和limit(容易被忽略的知识)
为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15- 这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
- 一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
- 我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
- 这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15
- 宿主机使用网线的时候,客户机在Bridged Adapter模式下,使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候,客户机在Bridged Adapter模式下,使用可选项里唯一一个WIFI选项,Microsoft Virtual Wifi Miniport Adapter也无法上网,故弃之。...2013-09-19
- 首先要声明一点,大部分情况下,修改MySQL密码是需要有mysql里的root权限的...2013-09-11
- MySQL命令行导出数据库: 1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录 如我输入的命令行:cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26
- 一、连接Mysql格式: mysql -h主机地址 -u用户名 -p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口,然后进入目录mysql/bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密...2015-11-08
- Navicat for MySQL注册码用来激活 Navicat for MySQL 软件,只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23
- 这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14
- 这篇文章主要介绍了基于PostgreSQL和mysql数据类型对比兼容,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-25
- 一、准备编译环境,安装所需依赖包yum groupinstall 'Development' -y yum install openssl openssl-devel zlib zlib-devel -y yum install readline-devel pcre-devel ncurses-devel bison-devel cmake -y二、编译安...2015-10-21
Mysql中 show table status 获取表信息的方法
这篇文章主要介绍了Mysql中 show table status 获取表信息的方法的相关资料,需要的朋友可以参考下...2016-03-12- 这篇文章主要为大家分享了20分钟MySQL基础入门教程,快速掌握MySQL基础知识,真正了解MySQL,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2016-12-02
- 这篇文章主要介绍了node.js如何操作MySQL数据库,帮助大家更好的进行web开发,感兴趣的朋友可以了解下...2020-10-29
- 传统的关系数据库一般由数据库(database)、表(table)、记录(record)三个层次概念组成,MongoDB是由数据库(database)、集合(collection)、文档对象(document)三个层次组成。MongoDB对于关系型数据库里的表,但是集合中没有列、行和关...2013-09-11
- 这篇文章主要介绍了Delphi远程连接Mysql的实现方法,需要的朋友可以参考下...2020-06-30