MySQL 隔离级别及其脏读不可重复读幻读

2019-11-07 约 2333 字预计阅读 5 分钟

MySQL 中，InnoDB 所提供的事务符合 ACID 的要求，而事务通过事务日志中的 redo log 和 undo log 满足了原子性、一致性、持久性，事务还会通过锁机制满足隔离性，在 InnoDB 存储引擎中，有不同的隔离级别，它们有着不同的隔离性。

事务的特性

原子性（Atomicity - [ˌætəˈmɪsəti] ）：一个事务（Transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即指处于同一个事务中的多条语句是不可分割的
一致性（Consistency - [kənˈsɪstənsi]）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。即事务必须使数据库从一个一致性状态变换到另外一个一致性状态
隔离性（Isolation - [ˌaɪsəˈleɪʃn]）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。即指多线程环境下，一个线程中的事务不能被其他线程中的事务打扰
持久性（Durability - [dərəˈbɪlɪti]）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。即事务一旦提交，就应该被永久保存起来

事务的组成部分

顺便提一下 DQL（数据查询语言：Data Query Language）。

一个或多个 DML（Data Manipulation Language）语句，比如：INSERT、UPDATE、SELECT、DELETE 等
一个 DDL（数据定义语言：Data Definition Language）语句，比如：CREATE、ALTER、DROP 等
一个 DCL（数据控制语言：Data Control Language）语句，比如：GRANT、DENY、REVOKE 等

不隔离事务带来的问题

脏读（Drity Read）：一个事务读取到另一个事务未提交的数据，侧重点在 事务未提交
不可重复读（Non-Repeatable Read）：一个事务对同一行数据重复读取两次，但得到的结果不同，侧重点在 更新修改
幻读或虚读（Phantom Read）：一个事务执行两次查询，第二次查询时，莫名的多出了一些之前不存在数据，或者莫名的不见了一些数据，侧重点在 新增删除
丢失更新（Lost Update）：两个事务同时更新一行数据，后提交（或撤销）的事务将之前事务提交的数据覆盖了

丢失更新可分为两类，分别是第一类丢失更新和第二类丢失更新：

第一类丢失更新：指两个事务同时操作同一个数据时，当第一个事务撤销时，把已经提交的第二个事务的更新数据覆盖了，第二个事务就造成了数据丢失
第二类丢失更新：指当两个事务同时操作同一个数据时，第一个事务将修改结果成功提交后，对第二个事务已经提交的修改结果进行了覆盖，对第二个事务造成了数据丢失

事务的隔离级别

读未提交（Read Uncommitted）：一个事务在执行过程中，既可以访问其他事务未提交的新插入的数据，又可以访问未提交的修改数据。如果一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读此行数据，即一个事务读取到另一个事务中未提交的数据。此隔离级别可防止丢失更新
读已提交（Read Committed）：一个事务在执行过程中，既可以访问其他事务成功提交的新插入的数据，又可以访问成功修改的数据。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行，即一个事务读取到另一个事务中已提交的数据。此隔离级别可有效防止脏读
可重复读（Repeatable Read）：一个事务在执行过程中，可以访问其他事务成功提交的新插入的数据，但不可以访问成功修改的数据。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务，即一个事务可以重复读，每次读取的结果集都相同，而不管其他事务有没有提交。此隔离级别可有效防止不可重复读和脏读
可串行化（Serializable）：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。此隔离级别可有效防止脏读、不可重复读和幻读。但这个级别可能导致大量的超时现象和锁竞争，在实际应用中很少使用

隔离级别表（级别越高，数据越安全，但性能越低）：

隔离级别	脏读	不可重复读	幻读
读未提交	有可能	有可能	有可能
读已提交	不可能	有可能	有可能
可重复读	不可能	不可能	有可能
可串行化	不可能	不可能	不可能

事务处理的方式

显示局部设置

1
2
3
4
5
6
7
8
# 开启一个事务
BEGIN

# 事务回滚
ROLLBACK

# 提交事务
COMMIT

隐式全局设置

1
2
3
4
5
# 禁止自动提交
SET AUTOCOMMIT=0

# 开启自动提交
SET AUTOCOMMIT=1

隔离级别操作

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
// 查看事务隔离级别
mysql root@localhost:mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set
Time: 0.017s

// 设置当前会话隔离级别为读未提交
mysql root@localhost:mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected
Time: 0.043s

// 设置当前会话隔离级别为读已提交
mysql root@localhost:mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected
Time: 0.043s

// 设置当前会话隔离级别为可重复读
mysql root@localhost:mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected
Time: 0.043s

// 设置当前会话隔离级别为可串行化
mysql root@localhost:mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected
Time: 0.043s

// 查看当前 Session 的事务隔离级别
mysql root@localhost:mysql> show variables like '%transaction_isolation%';
+-----------------------+------------------+
| Variable_name         | Value            |
+-----------------------+------------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set
Time: 0.067s

// 查看全局的事务隔离级别
mysql root@localhost:mysql> show global variables like '%transaction_isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set
Time: 0.016s

实验环节待续中。。。

目录