MySQL下的事务隔离级别及可能出现的问题

2021-01-30

事务

事务就是一组原子性的SQL查询，或者说一个独立的工作单元

事务的ACID

A Atomicity 原子性

一个事务必须被视为一个不可分割的最小工作单位，整个事务中所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中一部分

C Consistency 一致性

事务总是从一个一致性状态转移到另一个一致性状态

例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不会变的

I Isolation 隔离性

一个事务所做的修改在最终提交之前，对其他事务是不可见的

D Durability 持久性

一旦事务提交，则其所做的修改就会永远保存到数据库中，即使数据库发生故障也不会对其有任何影响

并发事务可能带来的问题

脏读

事务A在访问数据并对数据做了修改后，但是还没提交事务，此时事务B也在访问这个数据，并使用了事务A修改后但未提交的数据

丢失修改

不可重复读

事务A多次读同一个数据时，在这个事务未结束前，事务B也读了该数据，如果事务B对数据进行了修改，就会导致事务A两次读到的数据不一致

幻读

事务A读取了几行数据，事务B插入了一些数据，随后的查询中，事务A发现多了一些原本不存在的数据，就像产生了幻觉一样

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的，因此，幻读在“当前读”下才会出现

即使把所有的记录都加上锁，还是阻止不了新插入的记录，这也是为什么“幻读”会被单独拿出来解决的原因。

事务隔离机制

事务的隔离级别

Level 1 Read Uncommited（读未提交）

事务A修改了数据，但未提交前，事务B可以读取到事务A修改的数据

Level 2 Read Commited（读提交）

事务A修改了数据，但未提交前，事务B不可以读取到事务A修改的数据，只有在事务A提交后，事务B才可以读取到修改的数据

Level 3 Repeatable Read / RR（可重复读取）

事务A修改了数据，无论是否已经提交，事务B整个过程中都无法读取到事务A修改的数据，只有重新开一个事务才可以读取到，事务B可以读取到自己修改的数据

Level 4 Serializable（可串行化）

要求事务串行执行，这样事务之间就完全不能产生干扰

事务的隔离级别的问题

隔离级别	脏读	不可重复读	幻读
Read Uncommited	✔️	✔️	✔️
Read Commited	❌	✔️	✔️
Repeatable Read	❌	❌	✔️
Serializable	❌	❌	❌

业务场景问题

Case1

问题回顾

在此前MySQL无法获取最新数据的解决方法文章中，我们利用Flask + Websocket的长连接做了一个消息推送服务，同时数据库会不断更新数据，结果发现一旦连接上后，即使修改了数据库的数据，返回的数据始终不变

然而无论我手动登录MySQL，或者另外写个程序去读取MySQL，数据是可以看到更新的

问题排查

之前参考了CSDN上的解决方法，通过查找事务隔离级别

mysql> show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

可以看到，事务隔离级别是REPEATABLE-READ，也就是可重复读，根据前面的介绍

事务A修改了数据，无论是否已经提交，事务B整个过程中都无法读取到事务A修改的数据，只有重新开一个事务才可以读取到，事务B可以读取到自己修改的数据

实际就是，事务A负责搜集数据并修改数据库，虽然事务A已经进行了事务提交，但是由于之前Websocket里的写法是通过同一个事务B轮询去查找数据库，检查是否有数据更新，因此每次取到的数据都是一样的

问题处理

此前处理方法，实际是将事务隔离级别修改到了Read Commited

执行

1 2	mysql> set global TRANSACTION ISOLATION LEVEL READ COMMITTED ; Query OK, 0 rows affected (0.00 sec)

此时再去查找

mysql> show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
  
mysql> show global variables like '%iso%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

在Read Commited下，事务A修改数据并提交后，事务B是可以读取到数据的

另一种方法，可以尝试重新获取事务B，然后读取数据，但这样的持续开启事务会有一定的开销，其它方案待探索

其他问题

为什么Repeatable Read中可能出现幻读？

幻读指的是在同一个事务中，两次执行相同的查询时，结果集中包含的记录数不同。例如，第一次查询时，符合条件的记录有10条，另一个事务在此期间插入了新记录并提交了事务。当第一次事务再次查询时，可能会发现结果集中多了那条新插入的记录。

在Reapeatable Read中，有两种读取，一种是快照读，也就是普通的select，这种是不会看到其它事务在此期间所CUD的数据，但还有种是当前读，如select for update或者update或者delete，InnoDB会执行当前读，这时读的就是最新数据，而不是最开始的快照数据了，就会导致幻读的产生

但MySQL的InnoDB引入了一种称之为间隙锁（Gap Lock）的机制来避免幻读，间隙锁会锁住查询范围内的空隙，防止其他事务在这些空隙中插入新记录，从而避免了幻读。

而其他引擎或者数据库可能会出现幻读问题。