MySQL之事务隔离级别案例理解

一、事务的ACID特性

正如我们所知，事务有着ACID四个特性，分别为：
1. 原子性（atomicity）
一个事务被视为是一个不可分割的最小工作单元，事务中的语句，要么全部提交成功，要么全部失败回滚。
2. 一致性（consistency）
数据库总是从一个一致性状态转换成另外一个一致性的状态。事务没有提交，事务中做出的修改就不会保存到数据库中。
3. 隔离性（isolation）
通常在一个事务所做的修改在最终提交之前，对其它事务不可见。
4. 持久性（durability）
事务一旦提交，其做出的修改会保存到数据库中，即使系统崩溃，修改的数据也不会丢失。

---

二、隔离级别

隔离级别规定了一个事务中所做的修改，在其他事务内、事务间的可见性。

设置事务隔离级别（注：修改global事务隔离级别，重新登陆数据库才能生效。）

set global tx_isolation='REPEATABLE-READ'
set session tx_isolation='REPEATABLE-READ'

查询事务隔离级别

select @@tx_isolation;
select @@global.tx_isolation;
select @@session.tx_isolation;

不同的数据库产品默认的事务隔离级别是不同的，大多数系统的默认级别为提交读（RC），MySQL的默认级别为可重复读（RR）。

1. 未提交读（Read Uncommitted）

未提交读：事务中的修改，即使没有提交，对于其他事务也都是可见的。

事务读取到未提交的数据，即为脏读（dirty read）。

案例分析

（1）首先准备数据

use test;
create table test(
    id int(5) not null
) engine=innodb default charset=utf8;
insert test(id) values(100),(200),(300),(400);

（2）设置数据库的隔离级别为RU。

mysql> set global tx_isolation='READ-UNCOMMITTED';
mysql> select @@tx_isolation;
Name          |Value           |
--------------|----------------|
@@tx_isolation|READ-UNCOMMITTED|

（3）事务操作

session A操作–开始事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+-----+
| id  |
+-----+
| 100 |
| 200 |
| 300 |
| 400 |
+-----+
4 rows in set (0.00 sec)

session B操作–开始事务，并修改

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(500);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+-----+
| id  |
+-----+
| 100 |
| 200 |
| 300 |
| 400 |
| 500 |
+-----+
5 rows in set (0.00 sec)

session A操作

mysql> select * from test;
+-----+
| id  |
+-----+
| 100 |
| 200 |
| 300 |
| 400 |
| 500 |
+-----+
5 rows in set (0.00 sec)

从上面的案例中，session B并没有对事务进行提交（commit），session A依然可以看到新的记录。

2. 提交读（Read Committed）

提交读：事务从开始到提交之前，其做出的修改对于其他事务不可见。

克服了脏读，但是无法避免不可重复读和幻读。

不可重复读，即两次执行同样的查询，得到的结果可能不一致。

案例分析
（1）设置数据库隔离级别为RC

mysql> set global tx_isolation='READ-COMMITTED';
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+

（2）事务操作

session A操作–开始事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+-----+
| id  |
+-----+
| 100 |
| 200 |
| 300 |
| 400 |
+-----+
4 rows in set (0.00 sec)

session B操作–开始事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+-----+
| id  |
+-----+
| 100 |
| 200 |
| 300 |
| 400 |
+-----+
4 rows in set (0.00 sec)

mysql> update test set id=999 where id=600;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+-----+
| id  |
+-----+
| 100 |
| 200 |
| 300 |
| 600 |
+-----+
4 rows in set (0.00 sec)

session A操作

mysql> select * from test;
+-----+
| id  |
+-----+
| 100 |
| 200 |
| 300 |
| 400 |
+-----+
4 rows in set (0.09 sec)

session B操作–提交事务

mysql> commit;
Query OK, 0 rows affected (0.11 sec)

session A操作

mysql> select * from test;
+-----+
| id  |
+-----+
| 100 |
| 200 |
| 300 |
| 600 |
+-----+
4 rows in set (0.00 sec)

案例中，session B事务未提交之前，session A看不到B的修改。提交之后，可以读取到。

3. 可重复读（Repeatable Read）

可重复读可以保证同一个事务中多次读取同样记录的结果是一致的。

克服了脏读和不可重复读，可能出现幻读。（MVCC解决了部分幻读问题）

幻读，即当某个事务在读取某个范围记录时，其他事务又在范围内插入了新的数据，当前之前的事务再次读取该范围记录时，产生幻行。

案例分析
（1）设置事务隔离级别

mysql> set global tx_isolation='REPEATABLE-READ';
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

（2）事务操作

session A操作–开始事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+-----+
| id  |
+-----+
| 100 |
| 200 |
| 300 |
| 400 |
| 500 |
+-----+
5 rows in set (0.00 sec)

session B操作–修改并提交事务

mysql> insert into test values(600);
Query OK, 1 row affected (0.36 sec)

session A操作

mysql> select * from test;
+-----+
| id  |
+-----+
| 100 |
| 200 |
| 300 |
| 400 |
| 500 |
+-----+
5 rows in set (0.00 sec)

session A操作–提交事务

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+-----+
| id  |
+-----+
| 100 |
| 200 |
| 300 |
| 400 |
| 500 |
| 600 |
+-----+
6 rows in set (0.00 sec)

小结：可重复读级别只能读取已经提交的数据，并且在一个事务中，读取事务开始时的数据。

汇总

隔离级别	脏读	不可重复读	幻读	加锁读
未提交读	是	是	是	否
提交读	否	是	是	否
可重复读	否	否	是	否
可序列化	否	否	否	是

4. 可序列化（Serializable）

最高的隔离级别。在读取的每一行数据上加锁，强制事务串行执行，使之不可能冲突，从而解决幻读问题。因此，会导致大量的超时和锁争用，不利于并发。

---

三、不可重复读和幻读的个人理解

• 脏读–针对未提交的数据。

• 不可重复读–针对事务内多次读取数据，数据本身的对比（侧重update）。

• 幻读–针对事务内多次读取同一范围的数据，数据条数的对比（侧重insert、delete）。