MySQL事务隔离级别详解（附带实例）(单元测试)

MySQL事务隔离级别详解（附带实例）(单元测试)

MySQL事务隔离级别详解（附带实例）

在《数据库事务》一节中介绍了 MySQL 事务的四大特性，其中事务的隔离性就是指当多个事务同时运行时，各事务之间相互隔离，不可互相干扰。如果事务没有隔离性，就容易出现脏读、不可重复读和幻读等情况。

为了保证并发时操作数据的正确性，数据库都会有事务隔离级别的概念。

1) 脏读

脏读是指一个事务正在访问数据，并且对数据进行了修改，但是这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

2) 不可重复读

不可重复读是指在一个事务内，多次读取同一个数据。在这个事务还没有结束时，另外一个事务也访问了该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

3) 幻读

幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。为了解决以上这些问题，标准 SQL 定义了 4 类事务隔离级别，用来指定事务中的哪些数据改变是可见的，哪些数据改变是不可见的。MySQL 包括的事务隔离级别如下：

• 读未提交（READ UNCOMITTED）
• 读提交（READ COMMITTED）
• 可重复读（REPEATABLE READ）
• 串行化（SERIALIZABLE）

MySQL 事务隔离级别可能产生的问题如下表所示：

隔离级别	脏读	不可重复读	幻读
READ UNCOMITTED	√	√	√
READ COMMITTED	×	√	√
REPEATABLE READ	×	×	√
SERIALIZABLE	×	×	×

MySQL 的事务的隔离级别由低到高分别为 READ UNCOMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。低级别的隔离级别可以支持更高的并发处理，同时占用的系统资源更少。下面根据实例来一一阐述它们的概念和联系。

1. 读未提交（READ UNCOMITTED，RU）

顾名思义，读未提交就是可以读到未提交的内容。如果一个事务读取到了另一个未提交事务修改过的数据，那么这种隔离级别就称之为读未提交。在该隔离级别下，所有事务都可以看到其它未提交事务的执行结果。因为它的性能与其他隔离级别相比没有高多少，所以一般情况下，该隔离级别在实际应用中很少使用。例 1 主要演示了在读未提交隔离级别中产生的脏读现象。

示例 1

1) 先在 test 数据库中创建 testnum 数据表，并插入数据。SQL 语句和执行结果如下：

mysql> CREATE TABLE testnum(
    -> num INT(4));
Query OK, 0 rows affected (0.57 sec)
mysql> INSERT INTO test.testnum (num) VALUES(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.09 sec)

2) 下面的语句需要在两个命令行窗口中执行。为了方便理解，我们分别称之为 A 窗口和 B 窗口。在 A 窗口中修改事务隔离级别，因为 A 窗口和 B 窗口的事务隔离级别需要保持一致，所以我们使用 SET GLOBAL TRANSACTION 修改全局变量。SQL 语句如下：

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.04 sec)
flush privileges;
Query OK, 0 rows affected (0.04 sec)

查询事务隔离级别，SQL 语句和运行结果如下：

mysql> show variables like &39;%tx_isolation%&39;\G
*************************** 1. row ***************************
Variable_name: tx_isolation
        Value: READ-UNCOMMITTED
1 row in set, 1 warning (0.00 sec)

结果显示，现在 MySQL 的事务隔离级别为 READ-UNCOMMITTED。3) 在 A 窗口中开启一个事务，并查询 testnum 数据表，SQL 语句和运行结果如下：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM testnum;
+------+
| num  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

4) 打开 B 窗口，查看当前 MySQL 的事务隔离级别，SQL 语句如下：

mysql> show variables like &39;%tx_isolation%&39;\G
*************************** 1. row ***************************
Variable_name: tx_isolation
        Value: READ-UNCOMMITTED
1 row in set, 1 warning (0.00 sec)

确定事务隔离级别是 READ-UNCOMMITTED 后，开启一个事务，并使用 UPDATE 语句更新 testnum 数据表，SQL 语句和运行结果如下：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE test.testnum SET num=num*2 WHERE num=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

5) 现在返回 A 窗口，再次查询 testnum 数据表，SQL 语句和运行结果如下：

mysql> SELECT * FROM testnum;
+------+
| num  |
+------+
|    1 |
|    4 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.02 sec)

由结果可以看出，A 窗口中的事务读取到了更新后的数据。6) 下面在 B 窗口中回滚事务，SQL 语句和运行结果如下：

mysql> ROLLBACK;
Query OK, 0 rows affected (0.09 sec)

7) 在 A 窗口中查询 testnum 数据表，SQL 语句和运行结果如下：

mysql> SELECT * FROM testnum;
+------+
| num  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

当 MySQL 的事务隔离级别为 READ UNCOMITTED 时，首先分别在 A 窗口和 B 窗口中开启事务，在 B 窗口中的事务更新但未提交之前， A 窗口中的事务就已经读取到了更新后的数据。但由于 B 窗口中的事务回滚了，所以 A 事务出现了脏读现象。使用读提交隔离级别可以解决实例中产生的脏读问题。

2. 读提交（READ COMMITTED，RC）工作时间英语

顾名思义，读提交就是只能读到已经提交了的内容。如果一个事务只能读取到另一个已提交事务修改过的数据，并且其它事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那么这种隔离级别就称之为读提交。该隔离级别满足了隔离的简单定义：一个事务从开始到提交前所做的任何改变都是不可见的，事务只能读取到已经提交的事务所做的改变。这是大多数数据库系统的默认事务隔离级别（例如 Oracle、SQL Server），但不是 MySQL 默认的。例 2 演示了在读提交隔离级别中产生的不可重复读问题。

示例 2

1) 使用 SET 语句将 MySQL 事务隔离级别修改为 READ COMMITTED，并查看。SQL 语句和运行结果如下：

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like &39;%tx_isolation%&39;\G
*************************** 1. row ***************************
Variable_name: tx_isolation
        Value: READ-COMMITTED
1 row in set, 1 warning (0.00 sec)

2) 确定当前事务隔离级别为 READ COMMITTED 后，开启一个事务，SQL 语句和运行结果如下：

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

3) 在 B 窗口中开启事务，并使用 UPDATE 语句更新 testnum 数据表，SQL 语句和运行结果如下：

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

mysql>  UPDATE test.testnum SET num=num*2 WHERE num=2;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

4) 在 A 窗口中查询 testnum 数据表，SQL 语句和运行结果如下：

mysql> SELECT * from test.testnum;
+------+
| num  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

5) 提交 B 窗口中的事务，SQL 语句和运行结果如下：

mysql> COMMIT;
Query OK, 0 rows affected (0.07 sec)

6) 在 A 窗口中查询 testnum 数据表，SQL 语句和运行结果如下：

mysql> SELECT * from test.testnum;
+------+
| num  |
+------+
|    1 |
|    4 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

当 MySQL 的事务隔离级别为 READ COMMITTED 时，首先分别在 A 窗口和 B 窗口中开启事务，在 B 窗口中的事务更新并提交后，A 窗口中的事务读取到了更新后的数据。在该过程中，A 窗口中的事务必须要等待 B 窗口中的事务提交后才能读取到更新后的数据，这样就解决了脏读问题。而处于 A 窗口中的事务出现了不同的查询结果，即不可重复读现象。使用可重复读隔离级别可以解决实例中产生的不可重复读问题。

3. 可重复读（REPEATABLE READ，RR）

顾名思义，可重复读是专门针对不可重复读这种情况而制定的隔离级别，可以有效的避免不可重复读。在一些场景中，一个事务只能读取到另一个已提交事务修改过的数据，但是第一次读过某条记录后，即使其它事务修改了该记录的值并且提交，之后该事务再读该条记录时，读到的仍是第一次读到的值，而不是每次都读到不同的数据。那么这种隔离级别就称之为可重复读。可重复读是 MySQL 的默认事务隔离级别，它能确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。在该隔离级别下，如果有事务正在读取数据，就不允许有其它事务进行修改操作，这样就解决了可重复读问题。例 3 演示了在可重复读隔离级别中产生的幻读问题。

示例 3

1) 在 test 数据库中创建 testuser 数据表，SQL 语句和执行结果如下：

mysql> CREATE TABLE testuser(
    -> id INT (4) PRIMARY KEY,
    -> name VARCHAR(20));
Query OK, 0 rows affected (0.29 sec)

2) 使用 SET 语句修改事务隔离级别，SQL 语句如下：

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)

3) 在 A 窗口中开启事务，并查询 testuser 数据表，SQL 语句和运行结果如下：

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

mysql> SELECT * FROM test.testuser where id=1;
Empty set (0.04 sec)

4) 在 B 窗口中开启一个事务，并向 testuser 表中插入一条数据，SQL 语句和运行结果如下：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql>  INSERT INTO test.testuser VALUES(1,&39;zhangsan&39;);
Query OK, 1 row affected (0.04 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.06 sec)

5) 现在返回 A 窗口，向 testnum 数据表中插入数据，SQL 语句和运行结果如下：

mysql> INSERT INTO test.testuser VALUES(1,&39;lisi&39;);
ERROR 1062 (23000): Duplicate entry &39;1&39; for key &39;PRIMARY&39;
mysql>  SELECT * FROM test.testuser where id=1;
Empty set (0.00 sec)

使用串行化隔离级别可以解决实例中产生的幻读问题。

4. 串行化（SERIALIZABLE）

如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来。那么这种隔离级别就称之为串行化。SERIALIZABLE 是最高的事务隔离级别，主要通过强制事务排序来解决幻读问题。简单来说，就是在每个读取的数据行上加上共享锁实现，这样就避免了脏读、不可重复读和幻读等问题。但是该事务隔离级别执行效率低下，且性能开销也最大，所以一般情况下不推荐使用。