MySQL中四种隔离级别的演示

转载 努力努力再努力D 2019/9/30 21:37:32

事务的隔离是并发操作中需要理解清楚的问题y中共有种不同的隔离级别,这种隔离级别分别是:可以看出,不同的隔离级别有不同的多事务执行结果。y中的存储引擎的默认的隔离级别是可重复读()本文使用演示了上表中所列出的种隔离级别(基于y

事务的隔离是并发操作中需要理解清楚的问题.MySQL中共有4种不同的隔离级别,这4种隔离级别分别是:

隔离级别类型 影响结果
READ UNCOMMITTED(未提交读) 事务将会读取到未提交的数据,可能会造成脏读、可重复读和幻读的现象,是一种较低的隔离级别,在实际中较少使用
READ COMMITTED(提交读) 该种隔离级别在事务1没有提交或回滚时,事务2可避免脏读,但是在事务1提交或回滚之后,事务2出现了可重复读和幻读的情况
REPEATABLE READ(可重复读) 可重复读是MySQL默认的隔离级别,可以有效避免脏读和可重复读的情况,但是不能避免幻读
SERIABLIZABLE(串行化)  可以同时解决脏读、可重复读和幻读的情况,但是由于会出现阻塞的情况,所以实际中也较少使用

 

可以看出,不同的隔离级别有不同的多事务执行结果。MySQL中的InnoDB存储引擎的默认的隔离级别是可重复读(REPEATABLE READ).

本文使用演示了上表中所列出的4种隔离级别(基于MySQL5.7).使用的表为test,test中的字段分别是id、name.

mysql> select * from test;
+------+-------+
| id   | name  |
+------+-------+
|  100 | zhangsan |
|  200 | lisi  |
+------+-------+

由于MySQL中默认开启的是隐式事务,所以为了操作的效果不受影响,需要将事务开启成显式的.同时启动了两个会话窗口,并将启动的两个会话窗口都需要如此操作.在第一个窗口执行:

mysql> set autocommit=0;

1、READ UNCOMMITTED

由下面的查询结果可以看出,MySQL中默认的隔离级别为REPEATABLE READ:

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

因此需要将默认的隔离级别设置为READ UNCOMMITED:

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

(1)在第一个窗口执行:

mysql> UPDATE test SET name='wangwu' WHERE id=100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(2)在第二个窗口中查询test表: 

mysql> SELECT * FROM test;
+------+--------+
| id   | name   |
+------+--------+
|  100 | wangwu |
|  200 | lisi   |
+------+--------+
2 rows in set (0.00 sec)

从上面的输出结果中可以看出,id=100对应的name为wangwu.

(3)接着在第一个窗口中执行ROLLBACK操作,再次在第二个窗口查询时,id=100对应的name为zhangsan,说明了READ UNCOMMITTED隔离级别不可避免脏读.这里没有测试可重读读和幻读的情况了,因为如果脏读没有避免,那么也会出现可重复读和幻读的情况.

mysql> select * from test;
+------+----------+
| id   | name     |
+------+----------+
|  100 | zhangsan |
|  200 | lisi     |
+------+----------+
2 rows in set (0.00 sec)

  

2、READ COMMITTED

将第一个和第二个窗口的隔离级别都设置为:

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

在第一个窗口重新开启一个事务:

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

接着在第一个窗口执行(此时没有进行commit或rollback操作):

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

  

在第二个窗口中查询test:

mysql> select * from test;
+------+----------+
| id   | name     |
+------+----------+
|  100 | zhangsan |
|  200 | lisi     |
+------+----------+
2 rows in set (0.00 sec)

  

通过上面的输出结果可知,使用了READ COMMITTED隔离级别后,没有出现脏读的情况。但是当在窗口一中执行commit时,从窗口二中查询得到的结果已经发生改变:

mysql> select * from test;
+------+--------+
| id   | name   |
+------+--------+
|  100 | wangwu |
|  200 | lisi   |
+------+--------+
2 rows in set (0.00 sec)

  

从而可知,提交前和提交后得到的两种情况不相同,从而说明了READ COMMITTED不能避免可重复读。

3、REPEATABLE READ

开始前的数据:

mysql> select * from test;
+------+----------+
| id   | name     |
+------+----------+
|  100 | zhangsan |
|  200 | lisi     |
+------+----------+
2 rows in set (0.00 sec)

同样的,将第一个和第二个窗口的隔离级别都设置为:

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

第一个窗口执行:

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

在第一个窗口更新test中的name:

mysql> UPDATE test set name='xiaoming' where id=100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

在第二个窗口查询test:

mysql> select * from test;
+------+----------+
| id   | name     |
+------+----------+
|  100 | zhangsan |
|  200 | lisi     |
+------+----------+
2 rows in set (0.00 sec)

此时发现,在第二个窗口中查询的数据并没有发生改变,REPEATABLE READ可以避免脏读。

同时,当第一个窗口提交更新后,在第二个窗口中执行查询时,数据没有发生变化,说明此时也避免了可重复读的情况。

mysql> select * from test;
+------+----------+
| id   | name     |
+------+----------+
|  100 | zhangsan |
|  200 | lisi     |
+------+----------+
2 rows in set (0.00 sec)

在第二个窗口中无论读取多少次,读取到的数据都不会是第一个窗口中更新的数据,只有当第二个窗口也提交时,此时的第二个窗口才会更新数据。

mysql> select * from test;
+------+----------+
| id   | name     |
+------+----------+
|  100 | xiaoming |
|  200 | lisi     |
+------+----------+
2 rows in set (0.00 sec)

最后,在第二个窗口插入一条数据:

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

在第一个窗口更新数据,按道理来说,应该会有两行数据受影响,但是此时发现有三行数据受影响了,说明了出现了幻读的现象:

mysql> update test set name='zhaoliu';
Query OK, 3 rows affected (6.36 sec)
Rows matched: 3  Changed: 3  Warnings: 0

 

4、 SERIALIZABLE

将第一个和第二个窗口的隔离级别都设置为:

mysql> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query OK, 0 rows affected (0.00 sec)

开始操作前的数据:

+------+---------+
| id   | name    |
+------+---------+
|  100 | zhaoliu |
|  200 | zhaoliu |
|  300 | zhaoliu |
+------+---------+
3 rows in set (0.00 sec)  

当在窗口2中执行插入操作时,会发生阻塞现象,因为此时窗口1没有提交(即没有释放锁),只有当窗口1执行提交操作时,窗口2中的数据更插入成功。

 

本文通过实验的方式对MySQL中的四种隔离级别进行验证。可以有效加深对这四种隔离级别的理解。

如有错误,请指正。谢谢

 

上一篇:分库分表(1) --- 理论

下一篇:mysql执行过程以及顺序

赞(0)

共有 条评论 网友评论

验证码: 看不清楚?
    扫一扫关注最新编程教程