mysql 如何解决幻像问题（Phantom Problem）

在默认的事务隔离级别下，即REPEATABLEREAD下，InnoDB存储引擎采用Next-KeyLocking机制来避免PhantomProblem（幻像问题）。

这点可能不同于与其他的数据库，如Oracle数据库，因为其可能需要在SERIALIZABLE的事务隔离级别下才能解决PhantomProblem。

PhantomProblem是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。

下面将演示这个例子，

表t create table (id ) 表t由1、2、5这三个值组成，

 CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int,
  PRIMARY KEY (`id`),
  key(a)
) ;
insert into t(a) values(1),(2),(5);

若这时事务T1执行如下的SQL语句：

SELECT* FROM t WHERE a＞2 FOR UPDATE;

注意这时事务T1并没有进行提交操作，上述应该返回5这个结果。

若与此同时，另一个事务T2插入了4这个值，并且数据库允许该操作，那么事务T1再次执行上述SQL语句会得到结果4和5。这与第一次得到的结果不同，违反了事务的隔离性，即当前事务性，即当前事务能够看到其他事务的结果。

时间	会话A	会话B
1	set sessi tx_isolation='read-committed';
2	begin;
3	select * from t where a>2 for update; 结果为a:5
4		begin;
5		insert into t(a) select 4;
6		commit
7	select * from t where a>2 for update; 结果为 a:4 和 a:5

InnoDB存储引擎采用Next-KeyLocking的算法避免PhantomProblem。对于上述的SQL语句

SELECT * FROM t WHERE a＞2 FOR UPDATE，

其锁住的不是5这单个值，而是对（2，+∞）这个范围加了X锁。因此任何对于这个范围的插入都是不被允许的，从而避免PhantomProblem。InnoDB存储引擎默认的事务隔离级别REPEATABLE READ，在该隔离级别下，其采用Next-KeyLocking的方式来加锁。而在事务隔离级别READCOMMITTED下，其仅采用RecordLock，因此在上述的示例中，会话A需要将事务的隔离级别设置为READCOMMITTED。此外，用户可以通过InnoDB存储引擎的Next-KeyLocking机制在应用层面实现唯一性的检查。例如：

SELECT* FROM table
WHERE col= xxx LOCK IN SHARE MODE；
#如果没有返回值 ，则证明该值并不存在，是可以插入的
INSERT INTO table VALUES(...);

如果用户通过索引查询一个值，并对该行加上一个SLock，那么即使查询的值不在，其锁定的也是一个范围，因此若没有返回任何行，那么新插入的值一定是唯一的。也许有读者会有疑问，如果在进行第一步SELECT…LOCKINSHAREMODE操作时，有多个事务并发操作，那么这种唯一性检查机制是否存在问题。其实并不会，因为这时会导致死锁，只有一个事务的插入操作会成功，而其余的事务会抛出死锁的错误。