Phantom Problem（幻读）问题

Myth丶恋晨 2022-12-27 08:41 104阅读 0赞

Phantom Problem是指同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。在默认的事务隔离级别下，即REPEATABLE READ下，InnoDB存储引擎采用Next-Key Locking机制来避免Phantom Problem（幻读问题）。

举个栗子：

创建测试表t：

CREATE TABLE t (a INT PRIMARY KEY);
    INSERT INTO t SELECT 1;
    INSERT INTO t SELECT 2;
    INSERT INTO t SELECT 5;

（事务隔离级别：READ COMMITED）事务T1先执行如下代码：

SELECT * FROM t WHERE a>2 FOR UPDATE;

这是事务T1并没有进行commit操作；应该返回a=5这个结果；假如与此同时，另一个事务T2插入了4这个值，并且数据库允许该操作，那么事务T1再次执行上述SQL语句会得到结果4和5。这与第一次得到的结果不同，违反了事务的隔离性，即当前事务能够看到其他事务的结果。其过程如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzcxMzIz_size_16_color_FFFFFF_t_70_pic_center]

InnoDB存储引擎采用Next-Key Locking的算法来避免Phantom Problem。对于上述的SQL语句SELECT \* FROM t WHERE a>2 FOR UPDATE，其锁住的不是5这个单值，而是对（2，+ ∞ ）这个单位加了X锁（排它锁）。因此任何对于这个范围的插入都是不允许的。从而避免了Phantom Problem。

InnoDB存储引擎默认的事务隔离级别是REPEATABLE READ，采用Next-Key Locking（锁定一个范围，并且锁定记录本身）的方式加锁。而在事务隔离级别READ-COMMITED下，仅采用Record Lock（单个行记录上的锁），因此在上述栗子中，会话A需要将事务的隔离级别设置为READ-COMMITED。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MzcxMzIz_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/9f2ae32468c34feeae0ef8f0cf8eb635.png