MySQL事务隔离级别以及脏读、幻读、不可重复读

怼烎@ 2022-11-05 00:37 211阅读 0赞

**事务的隔离性**

MySQL是一个客户端／服务器架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称之为一个会话（Session）。每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是某个事务的一部分，也就是对于服务器来说可能同时处理多个事务。当数据库上有多个事务同时执行的时候，就可能出现脏读（Dirty Read）、不可重复读（Non-Repeatable Read）、幻读（Phantom Read）的问题，为了解决这些问题，就有了 “隔离级别” 的概念。

理论上在某个事务对某个数据进行访问时，其他事务应该进行排队，当该事务提交之后，其他事务才可以继续访问这个数据。但一般情况下隔离得越严实，效率就会越低。因此很多时候，我们都要在隔离性和效率二者之间寻找一个平衡点。

事务并发执行遇到的问题  
脏读（Dirty Read）： 脏读是指一个事务读到了另一个未提交事务修改过的数据。

如小王的账户中有100的余额，接下来有两个事务对小王的账户进行访问。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NoYXJwZW5fXw_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NoYXJwZW5fXw_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NoYXJwZW5fXw_size_16_color_FFFFFF_t_70 2]  
SQL标准制定的四种隔离级别  
ISO 和 ANIS SQL 标准制定了四种事务隔离级别的标准，分别为：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

我们先来看下这四种隔离级别的意思。

读未提交： 一个事务还没提交时，它做的变更就能被别的事务看到。  
读提交： 一个事务提交之后，它做的变更才会被其他事务看到。  
可重复读： 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交的变更对其他事务也是不可见的。  
串行化： 顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。  
SQL 标准中规定，针对不同的隔离级别，并发事务可以发生不同严重程度的问题，具体情况如下：  
（ √ 表示可以发生；× 表示不可以发生）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NoYXJwZW5fXw_size_16_color_FFFFFF_t_70 3]  
**MySQL对四种隔离级别的支持情况**  
MySQL InnoDB 存储引擎支持4种隔离级别，但与 SQL 标准中定义的不同的是，InnoDB 存储引擎在默认的可重复读（repeatable read）事务隔离级别下，使用 Next-Key Lock 锁的算法，避免了幻读的产生。也就是说 InnoDB 存储引擎在可重复读（repeatable read）的事务隔离级别下，已经可以完全保证事务的隔离性要求，即达到了 SQL 标准中的串行化（serializable ）隔离级别的要求。

如何设置事务的隔离级别  
在 InnoDB 存储引擎中，可以使用以下命令来设置全局或者当前会话的事务隔离级别：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL
    {
    	READ UNCOMMITTED
    	| READ COMMITTED
    	| REPEATABLE READ
    	| SERIALIZABLE
    }

如想设置当前会话的隔离级别为读提交，可以使用如下语句：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

如果想在 MySQL 数据库启动时就设置事务的默认隔离级别，那就需要修改配置文件中 transaction-isolation 的值，比方说，我们在启动前指定了 transaction-isolation = READ COMMITTED，那么事务的默认隔离级别就从原来的 REPEATABLE READ 变成了READ COMMITTED。

查看当前会话的事务隔离级别，可以用如下语句：

SELECT @@transaction_isolation;

查看全局的事务隔离级别，可以使用如下语句：

SELECT @@global.transaction_isolation;

注意：transaction\_isolation 是在 MySQL 5.7.20 的版本中引入来替换tx\_isolation的，如果你使用的是之前版本的 MySQL，请将上述用到的 transaction\_isolation 的地方替换为 tx\_isolation。

幻读补充：  
幻读示例中，假如表有个主键ID，查询语句改为：select id from xxx，如果会话A查询出来有两条数据（1，2）。期间会话B插入一条id为3的数据并且提交事务。会话A再次查询数据依然是（1，2），但是当会话A要插入一条id为3的数据时，是插入不了的。这个现象应该也属于幻读现象吧？在串行化隔离级别下不会发生这种情况。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NoYXJwZW5fXw_size_16_color_FFFFFF_t_70]: /images/20221023/32a2908706a741318df0140c99cf420a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NoYXJwZW5fXw_size_16_color_FFFFFF_t_70 1]: /images/20221023/647862e4bb534e33ad44af59de484fea.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NoYXJwZW5fXw_size_16_color_FFFFFF_t_70 2]: /images/20221023/12dd3ef4ced64da387fc4180eda8534a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NoYXJwZW5fXw_size_16_color_FFFFFF_t_70 3]: /images/20221023/aa2ab37d35d1498d8d5105c8e01a756d.png