MySQL的四种事务隔离级别

小鱼儿 2023-07-18 14:27 144阅读 0赞

### mysql事物隔离级别分为四种： ###

*  READ-UNCOMMITTED            0 --> 读未提交
 *  READ-COMMITTED                 1 --> 读已提交
 *  REPEATABLE-READ                2 --> 可重复读
 *  SERIALIZABLE                         3 --> 序列化

mysql中查看事务的隔离级别用的是tx\_isolation，5.7.20版本之后，用的是transaction\_isolation

查看隔离级别命令：

SHOW [GLOBAL] VARIABLES LIKE 'tx_isolation';  #mysql5.7版本以前的使用方法
    
    SHOW [GLOBAL] VARIABLES LIKE 'transaction_isolation';  #mysql5.7版本后的使用方法
    
    或者
    
    select @@tx_isolation; 
    
    select @@transaction_isolation;

设置隔离级别的方法是：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    
    SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    
    或
    
    SET [SESSION|GLOBAL] tx_isolation=[0|1|2|3];              #mysql5.7以前
    
    SET [SESSION|GLOBAL] transaction_isolation=[0|1|2|3];     #mysql5.7以后

默认mysql的隔离级别为 REPEATABLE-READ(可重复读),查看方法如下：

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

### **READ-UNCOMMITTED ** ###

--------------------

> 该事物隔离级别可读取其它事物未提交的结果
> 
> 出现的问题:脏读，不可重复读

1、通过mysql -u username -p password 开启两个mysql连接, 分别为A连接， B连接。  
2、输入set tx\_isolation = 'read-uncommitted';或者set tx\_isolation=0;将A连接的事务隔离级别设置为READ-UNCOMMITTED  
3、在A,B连接的窗口分别查询test1表的数据， 表数据一致， 如下图所示

![20200329132104751.png][] 连接A和连接B的数据结果一致

4、在B连接的命令窗口输入`start transaction;`，并更新相应记录, 但不提交，如下图所示

> mysql> start transaction;  
> Query OK, 0 rows affected (0.00 sec)
> 
> mysql> update user set user\_name='test1' where id=4;  
> Query OK, 1 row affected (0.00 sec)  
> Rows matched: 1  Changed: 1  Warnings: 0

![20200329141218524.png][]

5、在A连接的命令窗口查看test1表， 结果如下图所示

通过结果发现。B连接，开启事物，修改数据，并未提交，A连接在read-uncommitted的隔离模式下仍然可以看到已更新的数据。

![20200329141402212.png][]

6、B连接窗口输入rollback回滚数据，此时A,B连接所查询的数据，和初始时的数据是一样的。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70][]

![20200329141657815.png][]

A连接可以读取到B连接已提交的数据

### **READ-COMMITTED** ###

--------------------

> 该事物隔离级别只会读取已提交的数据，在事务中已更改但未读取的数据不会读取
> 
> 解决的问题：可以解决脏读问题
> 
> 产生的问题：不可重复读

1、同上A,B 两个连接

2、输入`set tx_isolation = 'read-committed';`

`3、`将A连接的事务隔离级别修改B连接窗口开启事务，更新数据，但不提交，如图  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 1][]

**如结果所示，B未提交的数据，A读取不到 **

4、此时B提交事物， A继续查询，结果 与上一步不一致，即产生了**不可重复读**的问题 ，如下图所示

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 2][]

A连接可以读取到B连接已提交的数据

### **REPEATABLE-READ** ###

--------------------

> 该事务隔离级别只会读取已提交的结果，与READ-COMMITTED不同的是，repeatable-read在开启事务的情况下，同一条件的查询返回的结果永远是一致的，无论其它事物是否提交了新的数据
> 
> 解决的问题：不可重复读
> 
> 产生的问题：幻读
> 
> **幻读在可重复读的模式下才会出现，其他隔离级别中不会出现**

1、和READ-COMMITTED的实验环境差不多，此时将A的隔离级别改为 repeatable-read，查询结果如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 3][]

2、B还是一样开启事物，更新数据， 此事暂时不提交。但A开启事物查询数据。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 4][]

如结果所示，B未提交的数据，A读取不到

2、此时B提交事物， A继续查询会查询到B更改的结果如下图所示

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 5][]

### 由上图可以看出，A连接端在B连接端提交事务前和后，查询的数据结果并没有改变 ###

### **SERIALIZABLE ** ###

--------------------

> 这种隔离级别和repeatable-read类似，只会读取其它事物已提交的内容，有一点不同的地方在于，如果autocommit为false，那么每一条select语句会自动被转化为select ... lock in share mode.这样出现一些阻塞情况
> 
> 解决的问题：脏读、不可重复读，幻读

1、设置事务级别为序列化

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 6][]

2、B开启事务， 并更新数据，但不提交

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 7][]

3、A 隔离级别改为serializable， 并且开启事务或者将autocommit设置为0， 并查询被修改数据，如图

如果此时A连接端也开启事务修改数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 8][]

B连接端查询时会出现阻塞

![2020032922442746.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 9][]

从上图可以看出

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

在MySQL中，实现了这四种隔离级别，分别有可能产生问题如下所示：

![format_png][]

[20200329132104751.png]: /images/20230528/31f9564b08734c629b413c01289b242d.png
[20200329141218524.png]: /images/20230528/b4deace3f0c3493b86823c09e05f0c78.png
[20200329141402212.png]: /images/20230528/5f61f29dbadb40aeb78a410bd1ffdad8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70]: /images/20230528/d8162aebda0a433dba8d21710bbec5e0.png
[20200329141657815.png]: /images/20230528/80836011ae2e44789c6ccd79b2c06fcf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 1]: /images/20230528/7350f6e28e0a4e72a9db37e0080d9e40.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 2]: /images/20230528/33b8d6bf4f37446e8489419a296468c8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 3]: /images/20230528/85c5cb5bf44a4ff3bf9786ce04c1f1ce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 4]: /images/20230528/e8b350e8297f42e987bdb74f852b4624.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 5]: /images/20230528/20d07b77a93347dd871ef4f4cd5388f3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 6]: /images/20230528/4f01a7b4c37545ea881aa1e0fbc55e8a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 7]: /images/20230528/0bea33024b8440609f58ba66ede3ebbf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 8]: /images/20230528/bc4c01fc1f5549489a7a548be4c73761.png
[2020032922442746.png]: /images/20230528/4024512ad15340ab95d762003cee850d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5Mzk5OTY2_size_16_color_FFFFFF_t_70 9]: /images/20230528/6ca736b5a7df40edaace862db128cfce.png
[format_png]: /images/20230528/7dc68a1e20fb424b88e07822a7b80b26.png