mysql的innodb通过nextkey lock解决了幻读，为什么还说默认隔离级别是可重复读?-千锋教育

mysql的innodb通过nextkey lock解决了幻读，为什么还说默认隔离级别是可重复读?

发布时间:2023-10-13 12:09:00

发布人:xqq

一、解决了幻读，为什么还说默认隔离级别是可重复读

MySQL的可重复读隔离级别下是有“bug”的Snapshot Isolation，可以避免非write skew style的幻读，但还会有write skew异常（所有Snapshot Isolation的数据库都存在），以及更新丢失异常（MySQL独有，由于MySQL的“bug”实现）。

一般意义上，“幻象（phantom）”可被定义为：对于相同的区间查询，插入和删除操作使得对相同的区间查询操作返回不同的结果。如果这么定义幻象异常，那么MVCC下的可重复读（RR）是可以避免幻象的。比如，PostgreSQL，在文档中就说，RR级别下可以避免幻读；MySQL也一样可以避免这种幻象（但我在官方文档中没找到）。

早期的数据库都是单版本的，这个定义没什么问题。但是，由于多版本的存在，情形就变得复杂了。RR隔离级别在MVCC实现的数据库中，一般会被实现成快照（SNAPSHOT），这就可能会产生另一种异常。由于事务会读到不同的版本，对于相同区间的查询，事务可能会错过某些满足该条件的并发地插入的记录，该事务只有在插入这条记录的事务提交后才能看到这条记录。进而产生的问题就是，事务本应该读到的数据，却没有被读到。

例如，assignments表有四列(eid, pid, workdate, hours)。assignments表示的是给employee(eid)分配project(pid)，并记录某个工作日(workdate)的工时(hours)。限制每个工作日工时不超过8小时。

assign表示分配工时的存储过程，假设eid为1的员工已有两个project, 工时分别为4，1。有两个并发的事务T1, T2, 同时执行assign。当T1,T2开始时，对于满足条件eid = 1 and workdate = ‘2019.7.11’ 的元组，拿到的是相同的快照，它们都判定插入一条工时为4的元组不会使当日工时大于8。

这个异常不满足上面对幻读的定义，然而这个事务调度却是不正确的。一些文献把这种异常也称为幻象(write skew style phantom)^[2]。

MVCC数据库无法避免这种异常。如果要避免这种异常，就必须要提高隔离级别到可串行化。可串行化的实现，在MySQL中是通过对读加锁（Gap Lock）；在PG中是使用SSI算法，通过验证连续的RW依赖检测是否事务是否可串行化^[3]。

第二，MySQL有一个比较特殊的情形，锁和快照读混用，这让它产生了另一种幻像。

如下面的例子，有两个事务，在RR隔离级别下， select是没有幻读的，但select for update却会产生幻读。因为select是读，通过时间戳读快照，事务2读不到事务1的写入。而select for update被认为是写，是可以更新已提交数据的，所以读到的是最新版本，事务2可以读到事务1的写入。PostgreSQL是没有这个现象的。

事务1 事务2

mysql> start transaction; mysql> start transaction;

Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;

Empty set (0.00 sec)

mysql> insert into t values(1);

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.01 sec)

// no phantom

mysql> select * from t;

Empty set (0.00 sec)