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

概述

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

©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页