MYSQL隔离级别及测试包括脏读，幻读

最新推荐文章于 2023-08-04 18:06:35 发布

公众号【架构师也是人】

最新推荐文章于 2023-08-04 18:06:35 发布

阅读量5.8k

点赞数 3

分类专栏： Mysql 文章标签： mysql隔离级别

本文链接：https://blog.csdn.net/johnstrive/article/details/46724315

版权

Mysql 专栏收录该内容

36 篇文章 0 订阅

订阅专栏

概述

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。
1. Read Uncommitted（读取未提交内容）
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。
2. Read Committed（读取提交内容）
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
3. Repeatable Read（可重读）
这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。
4. Serializable（可串行化）
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。
这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：
脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。
在MySQL中，实现了这四种隔离级别，分别有可能产生问题如下所示：

隔离级别	脏读	不可重复读	幻读
Read Uncommitted	√	√	√
Read Committed	×	√	√
Repeatable Read	×	×	√
Serializable	×	×	×

测试

测试前准备

下面，将利用MySQL的客户端程序，分别测试几种隔离级别。测试数据库为test，表为t1；表结构：
通过mysql -uxxxx -p 打开客户端窗口MYSQL_A

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.05 sec)

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

你可以用下列语句查询全局和会话事务隔离级别：
SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

##设置事务级别为未提交读
mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-UNCOMMITTED      |
+-----------------------+
1 row in set (0.00 sec)
mysql> select @@tx_isolation;
+-----------------------+
| @@tx_isolation        |
+-----------------------+
| READ-UNCOMMITTED      |
+-----------------------+
1 row in set (0.00 sec)

修改事务自动提交

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set autocommit = off;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

修改完隔离级别及手动事务后。打开另一个mysql窗口，称为MYSQL_B

开始测试

测试Read Uncommitted（未提交读）

MYSQL_A执行：

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

mysql> insert into t1 values(1,'aa');
Query OK, 1 row affected (0.00 sec)

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

MYSQL_B执行：

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

MYSQL_A执行：

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(2,'bb');
Query OK, 1 row affected (0.01 sec)

MYSQL_B执行：

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | aa   |
|    2 | bb   |
+------+------+
2 rows in set (0.00 sec)

MYSQL_A执行：

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

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

MYSQL_B执行：

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

经过上面的实验可以得出结论，事务MYSQL_A插入了一条记录，但是没有提交，此时MYSQL_B可以查询出未提交记录。造成脏读现象。未提交读是最低的隔离级别。

注意不论是【MYSQL_A】还是【MYSQL_B】每次开启事务，CRUD操作结束后要commit。否则影响其他事务操作。

测试Read Committed（已提交读）

MYSQL_A执行：

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+
1 row in set (0.00 sec)

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------------+
| @@tx_isolation        |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+
1 row in set (0.00 sec)

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

mysql> select * from test.t1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | ccFromMB5 |
|    2 | bb        |
|    3 | cc        |
|    4 | dd        |
+------+-----------+
4 rows in set (0.00 sec)

MYSQL_B执行：

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+
1 row in set (0.00 sec)

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------------+
| @@tx_isolation        |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+
1 row in set (0.00 sec)

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

mysql> insert into test.t1 values(5,'ee');
Query OK, 1 row affected (0.00 sec)

MYSQL_A执行：依然是4条记录。

mysql> select * from test.t1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | ccFromMB5 |
|    2 | bb        |
|    3 | cc        |
|    4 | dd        |
+------+-----------+
4 rows in set (0.00 sec)

MYSQL_B执行：commit

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

MYSQL_A执行：

mysql> select * from test.t1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | ccFromMB5 |
|    2 | bb        |
|    3 | cc        |
|    4 | dd        |
|    5 | ee        |
+------+-----------+
5 rows in set (0.00 sec)
#因为开始开启了手动事务，结束后别忘了commit
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

经过上面的实验可以得出结论，已提交读隔离级别解决了脏读的问题，但是出现了不可重复读的问题，即事务A在B commit前后两次查询的数据不一致，因为在两次查询之间事务B插入了一条数据。已提交读只允许读取已提交的记录，但不要求可重复读。

测试Repeatable Read（可重读）

MYSQL_A 和 MYSQL_B执行：

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

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

MYSQL_A执行：

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

mysql> select * from test.t1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | ccFromMB5 |
|    2 | bb        |
|    3 | cc        |
|    4 | dd        |
+------+-----------+
4 rows in set (0.00 sec)

MYSQL_B执行：

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

mysql> insert into test.t1 values(6,'fff');
Query OK, 1 row affected (0.00 sec)

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

mysql> update test.t1 set name = 'rere' where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> select * from test.t1;
+------+------+
| id   | name |
+------+------+
|    1 | rere |
|    2 | bb   |
|    3 | cc   |
|    4 | dd   |
|    5 | ee   |
|    6 | fff  |
+------+------+
6 rows in set (0.00 sec)

MYSQL_A执行：

#未执行commit
mysql> select * from test.t1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | ccFromMB5 |
|    2 | bb        |
|    3 | cc        |
|    4 | dd        |
|    5 | ee        |
+------+-----------+
5 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
#执行commit后，查询出最新数据
mysql> select * from test.t1;
+------+------+
| id   | name |
+------+------+
|    1 | rere |
|    2 | bb   |
|    3 | cc   |
|    4 | dd   |
|    5 | ee   |
|    6 | fff  |
+------+------+
6 rows in set (0.00 sec)

测试传说中的Repeatable Read（可重读）下的幻读

为了让幻读重现，我们修改t1表的id字段为主键
MYSQL_A执行：

mysql> alter table test.t1 add primary key(id);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test.t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | 0       |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

OK，我们开始测试幻读
MYSQL_A执行：

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

mysql> select * from test.t1;
+----+------+
| id | name |
+----+------+
|  1 | rere |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
|  5 | ee   |
|  6 | fff  |
+----+------+
6 rows in set (0.00 sec)

MYSQL_B执行：

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

mysql> insert into test.t1 values(7,'ggg');
Query OK, 1 row affected (0.01 sec)

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

mysql> select * from test.t1;
+----+------+
| id | name |
+----+------+
|  1 | rere |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
|  5 | ee   |
|  6 | fff  |
|  7 | ggg  |
+----+------+
7 rows in set (0.01 sec)

MYSQL_A执行：

mysql> select * from test.t1;
+----+------+
| id | name |
+----+------+
|  1 | rere |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
|  5 | ee   |
|  6 | fff  |
+----+------+
6 rows in set (0.00 sec)

mysql> insert into test.t1 values (7,'vvv');
ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY'
在未提交当前事务时，查出来就6条，但是事务B已经提交了id为7的记录，这个时候，事务A会莫名的感到无助（提示：ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY'），当提交A事务后，再查询就会发现id为7的记录已经存在。这就是Repeatable Read（可重读）下的幻读
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test.t1;
+----+------+
| id | name |
+----+------+
|  1 | rere |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
|  5 | ee   |
|  6 | fff  |
|  7 | ggg  |
+----+------+
7 rows in set (0.00 sec)

测试Serializable（串行化）

MYSQL_A 执行：

mysql> set global transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| SERIALIZABLE          |
+-----------------------+
1 row in set (0.00 sec)

MYSQL_B依然是可重复读隔离级别。
MYSQL_A 执行：

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

mysql> select * from test.t1;
+----+------+
| id | name |
+----+------+
|  1 | rere |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
|  5 | ee   |
|  6 | fff  |
|  7 | ggg  |
|  8 | hhh  |
+----+------+
8 rows in set (0.00 sec)

MYSQL_B 执行：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

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

mysql> insert into test.t1 values(9,'iii');

这时，客户端会一直保持等待状态，不会打印出执行结果，直至A事务提交。
MYSQL_A 执行：

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

MYSQL_B 自动结束等待
一下这一句是A端执行commit后打印出的，可以看下执行时间。我等了23秒才手动在A端执行commit。

#这个是刚才insert等待的执行结果
Query OK, 1 row affected (23.41 sec)
#B端commit。
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

MYSQL_A 执行：

mysql> select * from test.t1;
+----+------+
| id | name |
+----+------+
|  1 | rere |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
|  5 | ee   |
|  6 | fff  |
|  7 | ggg  |
|  8 | hhh  |
|  9 | iii  |
+----+------+
9 rows in set (6.09 sec)

这里需要注意，如果B端insert后一直没有commit。那么A端同样会进入等待状态，直至B端提交事务。可以看下执行时间，我是先在A端执行查询，进入等待。然后在B端执行commit。随后A端结束等待查出B端插入的id为9的数据。可见，此隔离级别是最高的，也是最消耗资源、性能最差的级别。
serializable完全锁定字段，若一个事务来查询同一份数据就必须等待，直到前一个事务完成并解除锁定为止。是完整的隔离级别，会锁定对应的数据表格，因而会有效率的问题。

Serializable可能造成B端插入的时候等待超时报错，如：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

公众号【架构师也是人】

关注

3
点赞
踩
11

收藏

觉得还不错? 一键收藏
2
评论
MYSQL隔离级别及测试包括脏读，幻读

概述SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。 1. Read Uncommitted（读取未提交内容）在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Di
复制链接

扫一扫