[Linux] 面试题-如何解决幻读

CREATE TABLE `test_20` ( `id` int(11) NOT NULL,
 `c` int(11) DEFAULT NULL,
 `d` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `c` (`c`)
) ENGINE=InnoDB;

insert into test_20 values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

begin;
select * from t where d=5 for update;
commit;

• 
  
  
  
  • 
    
  
  
• 在READ COMMITTED级别下可执行
  
• 在REPEATABLE READ级别下，session B会阻塞
  
  
  
  • 
    
  
  

• 
  
  
  
  • 
    
  
  
• 1）Q1 只返回 id=5 这一行；
  
• 2）在 T2 时刻，session B 把 id=0 这一行的 d 值改成了 5，因此 T3 时刻 Q2 查出来的是id=0 和 id=5 这两行；
  
• 3）在 T4 时刻，session C 又插入一行（1,1,5），因此 T5 时刻 Q3 查出来的是 id=0、id=1和 id=5 的这三行。
  
  
  
  • 
    
  
  

• 
  
  
  
  • 
    
  
  
• 1）在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
  
• 2）上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。
  
  
  
  • 
    
  
  

• 
  
  
  
  • 
    
  
  
• 1） T2 时刻，session B 事务提交，写入了两条语句；
  
• 2）T4 时刻，session C 事务提交，写入了两条语句；
  
• 3）T6 时刻，session A 事务提交，写入了 update t set d=100 where d=5 这条语句。
  
  
  
  • 
    
  
  

update t set d=5 where id=0; /*(0,0,5)*/update t set c=5 where id=0; /*(0,5,5)*/
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/
update t set d=100 where d=5;/* 所有 d=5 的行，d 改成 100*/

• 这是我们假设“select * from t where d=5 for update 这条语句只给d=5 这一行，也就是 id=5 的这一行加锁”导致的。
  

• 把扫描过程中碰到的行，也都加上写锁，再来看看执行效果
  

insert into t values(1,1,5); /*(1,1,5)*/update t set c=5 where id=1; /*(1,5,5)*/
update t set d=100 where d=5;/* 所有 d=5 的行，d 改成 100*/
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

• 
  
  
  
  • 
    
  
  
• 1）session A 执行 select ... for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10);
  
• 2）session B 执行 select ... for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
  
• 3）session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；
  
• 4）session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。
  
  
  
  • 
    
  
  

• 间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。这，也是现在不少公司使用的配置组合。