理解：事务的隔离级别

一、数据库事务的ACID属性
二、事务的隔离级别
三、读写异常
四、事务隔离级别与读写异常的关系
五、MYSQL实践演示
六、MYSQL隔离级别与实现

一、数据库事务的ACID属性

  • 原子性：事务的所有操作要么全部执行，要么全部不执行；
  • 一致性：事务作为原子从一个一致的数据库状态开始独立的运行，则事务结束时事务也必须再次是一致的；
  • 隔离性：多个事务可能并发执行，但是系统保证，对于任意一对事务T1,T2，在T1看来，T2或者在T1开始之前已经完成执行，或者在T1完成之后开始执行。因此，每个事务都感觉不到系统中有其他事务在并发的执行。
  • 持久性：一个事务成功完成后，它对数据库的改变必须是永久的，即使出现系统故障。

二、事务的隔离级别

为了性能考虑，数据库会允许使用者选择牺牲隔离性来换取并发度。
SQL定义的四种隔离级别：

  • 读未提交（Read Uncommitted）：读取未提交的数据，即其他事务已经修改但是还未提交的数据，这是最低的隔离级别；
  • 读已提交（Read Committed）：读取已提交的数据，但不要求可重复读。
    例如，在事务两次读取一个数据项期间，另一个事务更新了该数据并提交；
  • 可重复读（Repeatable）：读取已提交的数据，保证可重复读。
    在一个事务中，对同一个数据项，确保前后读取的数据结果一样；
  • 可串行化（Serializable）：事务是可串行化执行的，就像一个事务执行的时候没有别的事务在同时执行，这是最高的隔离级别；

三、读写异常

事务隔离级别降低产生的并发问题：

  • 脏读（Dirty Reads）：一个事务读取了另外一个事务更新却还没有提交的数据；
    例如，事务A看到了被事务B回滚掉的数据。
  • 不可重复读（Non-Repeatable Reads）：一个事务对同一个数据项的多次读取可能会产生不同的结果，即：一个事务读取了另一个事务更新前后的数据；
    例如，事务A在T1时间读取数据项，事务B在T2时间修改数据项并提交事务，事务A在T3时间再次读取数据项时发现数据改变。
  • 幻读（Phantom Reads）：一个事务读到了另一个事务插进来的数据；
    一个事务用Where子句来检索一个表的数据，另一个事务插入一条新的记录，并且符合Where条件，这样，第一个事务用同一个where条件来检索数据后，就会多出一条记录。

不可重复读与幻读的区分：

  • 不可重复读的重点是数据修改
    同样的条件, 你读取过的数据,再次读取出来发现值不一样了
  • 幻读的重点在于新增或者删除
    同样的条件, 第 1 次和第 2 次读出来的记录数不一样

四、事务隔离级别与读写异常的关系

隔离解别 \ 读写异常 脏读 不可重复读 幻读
读未提交 Y Y Y
读已提交 N Y Y
可重复读 N N Y
可串行化 N N N

（Y表示会出现，N表示不会出现）

五、MYSQL实践演示

  • mysql版本号：

    mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.14-log |
+------------+

    8.0.11版本时以下操作中的tx_isolation全部改为transaction_isolation;

  • 查看InnoDB存储引擎 系统级的隔离级别 和 会话级的隔离级别

    mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+

    1. Mysql/InnoDB提供的默认隔离级别为可重复读（REPEATABLE-READ）;

    2. 用户可以使用SET TRANSACTION语句改变隔离级别：

      SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
      注意：
      (1)默认的行为（不带session和global）是为下一个（未开始）事务设置隔离级别。
      (2)如果你使用GLOBAL关键字，语句在全局对从那点开始创建的所有新连接（除了不存在的连接）设置默认事务级别。
      (3)使用SESSION 关键字为将来在当前连接上执行的事务设置默认事务级别。
      任何客户端都能自由改变会话隔离级别（甚至在事务的中间），或者为下一个事务设置隔离级别。

  • 关闭SQL语句的自动提交

    Session 1,Session 2:

mysql> set autocommit=0;
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+

  • 建立实验表

    mysql> create table student(id int(11),name varchar(20),primary key(id));
mysql> show create table student;
+---------+-------------------------------------------------------------
------------------------------------------------------------------------
| Table   | Create Table

+---------+-------------------------------------------------------------
------------------------------------------------------------------------
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------
------------------------------------------------------------------------
1）演示隔离级别：读未提交

更改系统级别：

Session 1:  
mysql> set session tx_isolation='read-uncommitted';
mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+------------------+
| @@global.tx_isolation | @@tx_isolation   |
+-----------------------+------------------+
| REPEATABLE-READ      | READ-UNCOMMITTED |
+-----------------------+------------------+

Session 2: 
mysql> set session tx_isolation='read-uncommitted';
mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ      | READ-UNCOMMITTED |
+-----------------------+-----------------+

演示脏读：事务A读取了事务B回滚的数据

Session 1:                              Session 2:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
                                        mysql> start transaction;
                                        Query OK, 0 rows affected (0.00 sec)
                                        mysql> insert into student values(1,"Tom");
                                        Query OK, 1 row affected (0.10 sec)

mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | Tom  |
+----+------+
1 row in set (0.00 sec)

                                        mysql> rollback;
                                        Query OK, 0 rows affected (0.12 sec)

mysql> select * from student;
Empty set (0.00 sec)

隔离级别为读未提交时，会出现脏读、不可重复读、幻读的情况；

2）演示隔离级别：读已提交

更改系统级别：

Session 1: 
mysql> set session tx_isolation='read-committed';
mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ        | READ-COMMITTED |
+-----------------------+-----------------+

Session 2:
mysql> set session tx_isolation='read-committed';
mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ        | READ-COMMITTED |
+-----------------------+-----------------+

脏读可以避免：只可以读取已提交的数据。

Session 1:                              Session 2:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
                                        mysql> start transaction;
                                        Query OK, 0 rows affected (0.00 sec)
                                        mysql> insert into student values(1,"Tom");
                                        Query OK, 1 row affected (0.10 sec)
                                        mysql> select * from student;
                                        +----+------+
                                        | id | name |
                                        +----+------+
                                        |  1 | Tom  |
                                        +----+------+
                                        1 row in set (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
                                        mysql> rollback;
                                        Query OK, 0 rows affected (0.12 sec)
mysql> select * from student;
Empty set (0.00 sec)

演示不可重复读：一个事务可以读取另一个事务更新前和更新后（提交）的数据；

Session 1:                              Session 2:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
                                        mysql> start transaction;
                                        Query OK, 0 rows affected (0.00 sec)
                                        mysql> insert into student values(1,"Tom");
                                        Query OK, 1 row affected (0.06 sec)
                                        mysql> commit;
                                        Query OK, 0 rows affected (0.08 sec)
mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | Tom  |
+----+------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

隔离级别为读已提交时，避免了脏读，会出现不可重复读、幻读的情况；

3）演示隔离级别：可重复读

更改系统级别：

Session 1: 
mysql> set session tx_isolation='repeatable-read';
mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ        | REPEATABLE-READ |
+-----------------------+-----------------+

Session 2:
mysql> set session tx_isolation='repeatable-read';
mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ        | REPEATABLE-READ |
+-----------------------+-----------------+

可重复读可以保证：一个事务中读取的数据始终一致。

Session 1:                              Session 2:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
                                        mysql> start transaction;
                                        Query OK, 0 rows affected (0.00 sec)
                                        mysql> insert into student values(1,"Tom");
                                        Query OK, 1 row affected (0.06 sec)
                                        mysql> commit;
                                        Query OK, 0 rows affected (0.08 sec)
mysql> select * from student;
Empty set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

演示幻读：

Session 1: 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
                                        mysql> start transaction;
                                        Query OK, 0 rows affected (0.00 sec)
                                        mysql> insert into student values(1,"Tom");
                                        Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
                                        mysql> commit;
                                        Query OK, 0 rows affected (0.09 sec)
mysql> select * from student;
Empty set (0.00 sec)
mysql> insert into student values(1,"Tom");
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

查询student为空表，插入数据时却发现已经有数据。

隔离级别为可重复读时，避免了脏读、不可重复读，会出现幻读的情况；

4）演示隔离级别：可串行化

隔离级别为可串行化时，可以避免脏读、不可重复读和幻读的情况；代价是并发度最低；

六、MYSQL隔离级别与实现

参考文章：

@梦工厂2018.3.14

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 205,386评论 6赞 479
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,939评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,851评论 0赞 341
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,953评论 1赞 278
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,971评论 5赞 369
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,784评论 1赞 283
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,126评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,765评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,148评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,744评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,858评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,479评论 4赞 322
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,080评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,053评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,278评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,245评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,590评论 2赞 343

推荐阅读更多精彩内容