理解：事务的隔离级别

一、数据库事务的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

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

友情链接更多精彩内容