Mysql事务-隔离级别

时间:2023-01-25 09:33:22

MYSQL事务-隔离级别

事务是什么?

事务简言之就是一组SQL执行要么全部成功,要么全部失败。MYSQL的事务在存储引擎层实现。

事务都有ACID特性:

  • 原子性(Atomicity):一个事务必须被视为一个不可分割的单元;

  • 一致性(Consistency):数据库总是从一种状态切换到另一种状态;

  • 隔离性(Isolation):通常来说,事务在提交前对于其他事务不可见;

  • 持久性(Durablity):一旦事务提交,所做修改永久保存数据库;

事务最常用的例子就是银行转账。假设polo需给lynn转账1000元,如下步骤:

  • 确认polo账户余额高于1000元;

  • 从polo的账户余额减去1000元;

  • 将lynn的账户余额增加1000元;

SQL语句如下:

mysql> BEGIN;
mysql> SELECT balance FROM bank_account WHERE uid=10001;
mysql> UPDATE bank_account SET balance=balance-1000 WHERE uid=10001;
mysql> UPDATE bank_account SET balance=balance+1000 WHERE uid=10002;
mysql> COMMIT;

注:mysql启动事务可使用BEGIN或者START TRANSACTION;

上述三个步骤执行在一个事务中就能够保证数据的完整性,要么全部成功,要么全部失败。

MYSQL提供两种事务型引擎:Innodb和NDBCluster。默认采用自动提交模式,执行一条语句自动COMMIT。通过AUTOCOMMIT变量可启用或者禁用自动提交模式:

mysql> SHOW VARIABLES LIKE "AUTOCOMMIT";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec) mysql> SET AUTOCOMMIT=1

AUTOCOMMIT=1表示开启默认提交,0表示关闭默认提交需要手动提交。

事务隔离级别

事务隔离性的解释:通常情况下,事务在提交之前对于其他事务不可见。

数据库有四种隔离级别,当然MYSQL也是如此。分别为:

  • READ UNCOMMITED(未提交读)

  • READ COMMITED(已提交读)

  • EPEATABLE READ(可重复读)

  • SEAIALIZABLE(可串行化)

本人理解 : 隔离级别就是决定一个事务的修改另一个事务什么情况下可见。

书本解释 : 隔离级别都规定了一个事务中所做修改,哪些在事务内和事务间是可见的。

上面两段理解的区别在于是否存在事务内可见性的规定。我在各个级别似乎没有看到

下面开始说明MYSQL的四种隔离级别,先准备一张学生表:

mysql> CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

只有id(主键自增)与name字段

  • READ UNCOMMITTED(未提交读)

    事务中修改没有提交对其他事务也是可见的,俗称脏读。非常不建议使用。

    实例演示

    客户端A和B设置隔离级别为未提交读

    mysql> SET SESSION TX_ISOLATION='READ-UNCOMMITTED';

    客户端A与B开启事务并查询student

    mysql> BEGIN;
    mysql> SELECT * FROM student;
    Empty set (0.00 sec)

    客户端A和B都是空数据

    客服端B插入一条新的数据

    mysql> INSERT INTO student(name) VALUES("polo");
    Query OK, 1 row affected (0.00 sec)

    此时事务未提交,客服端A查看student表

    $ SELECT * FROM student;
    mysql> SELECT * FROM student;
    +----+------+
    | id | name |
    +----+------+
    |  1 | polo |
    +----+------+
    1 row in set (0.00 sec)

    客户端A看到B未提交的修改

    客户端B执行回滚操作

    mysql> ROLLBACK

    成功之后,客户端A查看student表

    mysql> SELECT * FROM student;
    Empty set (0.00 sec)

    客户端A查看数据为空

    以上可以看出未提交读隔离级别的危险性,对于一个没有提交事务所做修改对另一个事务是可见状态,容易造成脏读。非特殊情况不得使用此级别

  • READ COMMITTED(提交读)
    多数数据库系统默认为此级别(MYSQL不是)。已提交读级别即为一个事务只能已提交事务所做的修改,也就解决了未提交读的问题,即脏读的问题。

    实例演示

    客户端A和B设置隔离级别为已提交读

    mysql> SET SESSION TX_ISOLATION='READ-COMMITTED';

    客户端A与B开启事务并查询student

    mysql> BEGIN;
    mysql> SELECT * FROM student;
    Empty set (0.00 sec)

    客户端A和B都为空

    客服端B插入一条新的数据,不提交

    mysql> INSERT INTO student (name) VALUES('polo');

    客户端A查看student

    mysql> SELECT * FROM student;
    Empty set (0.00 sec)

    注意这里与上面不同了,在客户端B没有提交事务情况下无数据

    下面客户端B提交事务

    mysql> COMMIT;

    客户端A再次查看student表。

    mysql> SELECT * FROM student;
    +----+------+
    | id | name |
    +----+------+
    |  1 | polo |
    +----+------+
    1 row in set (0.00 sec)

    成功读取到客户

    从上面的示例可以看出,提交读没有了未提交读的问题,但我们可以看到在客户端A的一个事务中执行两次同样的SELECT语句得到不同结果,因此已提交读又被称为不可重复读。同样筛选条件可能得到不同的结果。

  • REPEATABLE READ(可重复读)

    如其名也,解决已提交读不可重复读取的问题。

    示例演示

    客户端A和B设置隔离级别为可重复读

    mysql> SET SESSION tx_isolation='REPEATABLE-READ'

    客户端A与B开启事务并查看

    mysql> BEGIN;
    mysql> SELECT * FROM student;
    +----+------+
    | id | name |
    +----+------+
    |  1 | polo |
    +----+------+
    1 rows in set (0.00 sec)

    客服端B更新polo为jeff,并提交事务

    mysql> UPDATE student SET name='jeff' WHERE id=1;
    mysql> COMMIT

    客户端A查看student表

    mysql> SELECT * FROM student;
    +----+------+
    | id | name |
    +----+------+
    |  1 | jeff |
    +----+------+
    1 rows in set (0.00 sec)

    注意客户端A查看数据未变,没有不可重复读问题

    客户端A提交事务,并查看student表

    mysql> COMMIT;
    mysql> SELECT * FROM student;
    +----+------+
    | id | name |
    +----+------+
    |  1 | polo |
    +----+------+
    1 rows in set (0.00 sec)

    上面实例可知,可重复读两次读取内容一样。数据库这级别并没有解决幻读的问题。但是MYSQL在可重复读基础上增加了MVCC机制解决了此问题,实例无法演示幻读效果。

    那什么是幻读?首先,可重复读锁定范围为当前查询到的内容,如执行

    mysql> SELECT * FROM student WHERE id>=1

    锁定的即id>=1查到的行,为行级锁。如另一事务执行并默认提交以下语句

    mysql> INSERT INTO student (name) VALUES ('peter');

    新增的这行并没有被锁定,此时读取student

    mysql> SELECT * FROM student WHERE id>=1;
    +----+---------+
    | id | name    |
    +----+---------+
    |  1 | polo    |
    |  2 | peter   |
    +----+---------+
    2 rows in set (0.00 sec)

    便出现了幻读

    除了使用MYSQL的MVCC机制,还可以使用可串行化隔离级别解决此问题。

  • SEAIALIZABLE(可串行化)

    可串行化是最高隔离级别,强制事务串行执行。执行串行了也就解决了一切的问题,这个级别只有在对数据一致性要求非常严格且没用并发的情况下使用

    实例演示

    客户端A和B设置隔离级别为可串行化

    mysql> SET SESSION tx_isolation='SERIALIZABLE';

    客户端A执行查询

    mysql> SELECT * FROM student WHERE id<4;
    +----+---------+
    | id | name    |
    +----+---------+
    |  1 | polo    |
    |  2 | peter   |
    +----+---------+
    2 rows in set (0.00 sec)

    客户端B执行新增

    mysql> INSERT INTO student (name) VALUES('tiger');

    好的!效果出现了,此时我们会发现INSERT语句被阻塞执行,原因就是A执行了查询表student同时满足id<4,已被锁定。如果查询表student条件为id<3,则新增语句可正常执行。

隔离级别对照图

Mysql事务-隔离级别

Mysql事务-隔离级别的更多相关文章

  1. &lbrack;51CTO&rsqb;新说MySQL事务隔离级别!

    新说MySQL事务隔离级别! 事务隔离级别这个问题,无论是校招还是社招,面试官都爱问!然而目前网上很多文章,说句实在话啊,我看了后我都怀疑作者弄懂没!本文所讲大部分内容,皆有官网作为佐证,因此对本文内 ...

  2. 查询mysql事务隔离级别

    查询mysql事务隔离级别 查询mysql事务隔离级别 分类: DB2011-11-26 13:12 2517人阅读 评论(0) 收藏 举报 mysqlsessionjava   1.查看当前会话隔离 ...

  3. MySQL事务隔离级别测试实例

    https://www.cnblogs.com/huanongying/p/7021555.html MySQL事务隔离级别 事务隔离级别 脏读 不可重复读 幻读 读未提交(read-uncommit ...

  4. MySQL事务隔离级别 解决并发问题

    MySQL事务隔离级别 1. 脏读: 骗钱的手段, 两个窗口或线程分别调用数据库转账表,转账后未提交,对方查看到账后,rollback,实际钱没转. 演示方法: mysql默认的事务隔离级别为repe ...

  5. mysql事务隔离级别、脏读、幻读

    Mysql事务隔离级别本身很重要,再加上可能是因为各大公司面试必问的缘故,在博客中出现的概率非常高,但不幸的是,中国的技术博客要么是转载,要么是照抄,质量参差不齐,好多结论都是错的,对于心怀好奇之心想 ...

  6. mysql事务隔离级别与设置

    mysql数据库,当且仅当引擎是InnoDB,才支持事务: 1.隔离级别 事务的隔离级别分为:未提交读(read uncommitted).已提交读(read committed).可重复读(repe ...

  7. MySQL事务隔离级别(二)

    搞清楚MySQL事务隔离级别 首先创建一个表 account.创建表的过程略过(由于 InnoDB 存储引擎支持事务,所以将表的存储引擎设置为 InnoDB).表的结构如下: 为了说明问题,我们打开两 ...

  8. MySQL事务隔离级别(一)

    本文实验的测试环境:Windows 10+cmd+MySQL5.6.36+InnoDB 一.事务的基本要素(ACID) 1.原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做 ...

  9. MySQL事务隔离级别详解

    原文地址:http://xm-king.iteye.com/blog/770721 SQL标准定义了4类隔离级别,包括了一些具体规则,用来限定事务内外的哪些改变是可见的,哪些是不可见的.低级别的隔离级 ...

随机推荐

  1. 题目:利用条件运算符的嵌套来完成此题:学习成绩&gt&semi;&equals;90分的同学用A表示,60-89分之间的用B表示,60分以下的用C表示。

    public class Five_05 { public static void main(String[] args) { Scanner input=new Scanner(System.in) ...

  2. eval解析JSON字符串的一个小问题

    之前写过一篇 关于 JSON 的介绍文章,里面谈到了 JSON 的解析.我们都知道,高级浏览器可以用 JSON.parse() API 将一个 JSON 字符串解析成 JSON 数据,稍微欠妥点的做法 ...

  3. 重置样式 - Eric Meyer的原版

    重置样式就是一组CSS声明,用来覆盖不同浏览器渲染HTML元素时的各种默认样式.重置样式一般会被加入到主样式文件的开头,用来将各个浏览器的自有默认样式重置成统一表现,确保样式表中后续追加的样式在不同浏 ...

  4. RAMOS和SSD对比

    http://bbs.pcbeta.com/forum.php?mod=redirect&goto=findpost&ptid=1786284&pid=48341400RAMO ...

  5. css第三天

    三 1.标准模式与怪异模式(针对盒子模型)与边框,填充,边界,有关 标准模式(默认):元素的总宽度 = 盒子的宽度 + 左右填充宽度 + 左右边框宽度元素的总高度 = 盒子的高度 + 上下填充高度 + ...

  6. Docker代理设置方法

    1.注意Docker版本(此处版本为docker-ce-18.06.1) docker version 2.编辑Docker服务配置文件 vim /usr/lib/systemd/system/doc ...

  7. oracle批量去掉字段值空格

    --查询验证 select ltrim(col1) from t1; --去掉字段值左边空格update t1 set col1=ltrim(col1); --去掉字段值右边空格update t1 s ...

  8. &lbrack;NOI2010&rsqb;超级钢琴(RMQ&plus;堆)

    小Z是一个小有名气的钢琴家,最近C博士送给了小Z一架超级钢琴,小Z希望能够用这架钢琴创作出世界上最美妙的音乐. 这架超级钢琴可以弹奏出n个音符,编号为1至n.第i个音符的美妙度为Ai,其中Ai可正可负 ...

  9. js中使用0 &OpenCurlyDoubleQuote;” null undefined &lbrace;&rcub;需要注意

    注意:在js中0为空(false) ,代表空的还有“”,null ,undefined: 如果做判断if(!上面的四种值):返回均为false console.log(!null);// true c ...

  10. hive sql 效率提升

    转 :  http://www.cnblogs.com/xd502djj/p/3799432.html hive的查询注意事项以及优化总结 . Hive是将符合SQL语法的字符串解析生成可以在Hado ...