MySQL事务隔离级别详解

最新推荐文章于 2023-12-25 08:55:03 发布

boomballa

最新推荐文章于 2023-12-25 08:55:03 发布

阅读量706

点赞数 1

分类专栏： Mysql 文章标签：数据库 mysql 事务

本文链接：https://blog.csdn.net/u011865772/article/details/40393861

版权

Mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL事务隔离级别详解

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

下面介绍一下这4种隔离级别：

READ UNCOMMITTED 读未提交。一个事务中，可以读取到其他事务未提交的变更。该级别允许脏读发生。
READ COMMITTED 读已提交。一个事务中，可以读取到其他事务已经提交的变更。该级别允许幻读、不可重复读发生。
REPEATABLE READ 可重复读。一个事务中，直到事务结束前，都可以反复读取到事务一开始看到的数据，不会发生变化。该级别可保证事务一致性。
SERIALIZABLE 串行。串行化读，即便每次读都需要获得表级共享锁，每次写都加表级排它锁，两个会话间的读写会相互阻塞。该级别会导致InnoDB表的并发特性丧失，变成和MyISAM一样。

在MySQL中，实现了这四种隔离级别，随着并发事务会带来一些问题：

也就是：

① Dirty read （脏读）

② Unrepeatable read （不可重复读）

③ Phantom read （幻读）

下面把会出现的这几个问题解释一下：

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

Unrepeatable read（不可重复读）：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。

Phantom read（幻读）：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。

下面我运用Mysql参照（http://xm-king.iteye.com/blog/770721）这篇博客做了几个实验，运用A、B端两个事务分别测试几种隔离级别。测试数据库为test，表为t_test；表结构：

[test]>show create table t_test;
+--------+-------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                      |
+--------+-------------------------------------------------------------------------------------------------------------------+
| t_test | CREATE TABLE `t_test` (
  `id` int(11) NOT NULL,
  `no` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(一)、将A的隔离级别设置为Read Uncommitted(未提交读)

事务A端:

[(none)]>SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

[(none)]>SELECT @@TX_ISOLATION;      
+------------------+
| @@TX_ISOLATION   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

在B端未更新数据之前：
事务A端：

[test]>SELECT @@TX_ISOLATION;        
+------------------+
| @@TX_ISOLATION   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

[test]>start transaction;
Query OK, 0 rows affected (0.00 sec)

[test]>select * from t_test;
+----+------+
| id | no   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

[test]>

B端更新数据：
事务B端：

[test]>SET AUTOCOMMIT=0;  

[test]>update t_test set no=888 where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[test]>select * from t_test;         
+----+------+
| id | no   |
+----+------+
|  1 |  888 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

[test]>rollback;
Query OK, 0 rows affected (0.06 sec)

[test]>select * from t_test;
+----+------+
| id | no   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

[test]>

事务A端：

[test]>start transaction;
Query OK, 0 rows affected (0.00 sec)

[test]>select * from t_test;
+----+------+
| id | no   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

[test]>select * from t_test;
+----+------+
| id | no   |
+----+------+
|  1 |  888 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

[test]>select * from t_test;
+----+------+
| id | no   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

[test]>

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

(二)、将事务A端的事务隔离级别设置为Read Committed(已提交读)

在B端未更新数据之前：
事务A端：

[test]>SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

[test]>SELECT @@TX_ISOLATION;        
+----------------+
| @@TX_ISOLATION |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

[test]>start transaction;
Query OK, 0 rows affected (0.00 sec)

[test]>select * from t_test;
+----+------+
| id | no   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

B端更新数据：
事务B端：

[test]>SET AUTOCOMMIT=0;  

[test]>update t_test set no=888 where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[test]>select * from t_test;
+----+------+
| id | no   |
+----+------+
|  1 |  888 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

[test]>commit;
Query OK, 0 rows affected (0.23 sec)

[test]>

事务A端：

[test]>start transaction;
Query OK, 0 rows affected (0.00 sec)

[test]>select * from t_test;
+----+------+
| id | no   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

[test]>select * from t_test;
+----+------+
| id | no   |
+----+------+
|  1 |  888 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

[test]>

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

(三)、将A的隔离级别设置为Repeatable Read(可重复读)

在B端未更新数据之前：
事务A端:

[test]>SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
[test]>SELECT @@TX_ISOLATION;        
 +-----------------+
| @@TX_ISOLATION  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

[test]>start transaction;            
Query OK, 0 rows affected (0.05 sec)

[test]>select * from t_test;
+----+------+
| id | no   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

B端更新数据：
事务B端：

[test]>SET AUTOCOMMIT=0;

[test]>start transaction;
Query OK, 0 rows affected (0.00 sec)

[test]>update t_test set no=888 where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[test]>select * from t_test;         
+----+------+
| id | no   |
+----+------+
|  1 |  888 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

[test]>commit;                
Query OK, 0 rows affected (0.06 sec)

事务 A端：

[test]>start transaction;            
Query OK, 0 rows affected (0.05 sec)

[test]>select * from t_test;
+----+------+
| id | no   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

[test]>select * from t_test;
+----+------+
| id | no   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

[test]>select * from t_test;
+----+------+
| id | no   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

B端插入数据：
事务 B端：

[test]>insert into t_test(no)value(999);
Query OK, 1 row affected, 1 warning (0.00 sec)

[test]>select * from t_test;
+----+------+
| id | no   |
+----+------+
|  1 |  888 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  0 |  999 |
+----+------+
5 rows in set (0.00 sec)

[test]>select * from t_test;
+----+------+
| id | no   |
+----+------+
|  1 |  888 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  0 |  999 |
+----+------+
5 rows in set (0.00 sec)

[test]>

事务A端：

[test]>select * from t_test;
+----+------+
| id | no   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

[test]>commit;
Query OK, 0 rows affected (0.00 sec)

[test]>select * from t_test;
+----+------+
| id | no   |
+----+------+
|  1 |  888 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

[test]>

由以上的实验可以得出结论，可重复读隔离级别只允许读取已提交记录，而且在一个事务两次读取一个记录期间，其他事务部的更新该记录。但该事务不要求与其他事务可串行化。例如，当一个事务可以找到由一个已提交事务更新的记录，但是可能产生幻读问题(注意是可能，因为数据库对隔离级别的实现有所差别)。像以上的实验，就没有出现数据幻读的问题。

(四)、将A端的隔离级别设置为 Serializable (可串行化)

A端打开事务，B端插入一条记录
A端：

[test]>SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

[test]>SELECT @@TX_ISOLATION;     
+----------------+
| @@TX_ISOLATION |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)

[test]>select * from t_test;      
+----+------+
| id | no   |
+----+------+
|  1 |  888 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

[test]>

事务 B 端:

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

[test]>SELECT @@TX_ISOLATION;        
+-----------------+
| @@TX_ISOLATION  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

[test]>insert into t_test(no)value(999);

因为此时事务A的隔离级别设置为serializable，开始事务后，并没有提交，所以事务B只能等待。

A端提交事务：
A端:

[test]>commit;
Query OK, 0 rows affected (0.00 sec)

事务 B端:

[test]>insert into t_test(no)value(999);
Query OK, 0 rows affected (0.00 sec)

Serializable完全锁定字段，若一个事务来查询同一份数据就必须等待，直到前一个事务完成并解除锁定为止。是完整的隔离级别，会锁定对应的数据表格，因而会有效率的问题。

boomballa

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL事务隔离级别详解

MySQL事务隔离级别详解
复制链接

扫一扫

专栏目录

MySQL事务隔离级别详解

MySQL事务隔离级别详解

“相关推荐”对你有帮助么？