mysql幻读及锁的实验

1. 什么是幻读

参考mysql官方文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-next-key-locking.html

上面的定义说的是在一个事务中，两次查询返回的结果不一致，重点是第二次查询是返回了更多的记录（其他事务插入的记录），这种现象被称为幻读。然后举的例子实际是一种加锁的case（select * from ... for udpate），mysql使用next-key方案解决该类幻读问题，即行锁+间隙锁。

-- 另外两类问题，脏读是指读到其他事务未提交的内容；不可重复读和幻读类似，区别是其他事务更新或删除记录的影响。

2. 普通的查询，是否有幻读？

如果是普通的查询，如select * from tableA; 是否有幻读的问题？这个问题需要区分隔离级别，如果是RU和RC级别，自然是能读到其他事务插入的记录的。如果是RR级别，普通读都是一致性快照读，InnoDB通过MVVC机制（RC级别已使用该机制）保证普通读不会读到其他事务已插入并提交的记录。

// 另一种更准确定义，幻读是指当前读（变更操作）能读到其他事务插入并提交的记录。如果认可这种定义，普通读就不用管是否有幻读了，只需要理解一致性快照读和当前读的概念就可以。

具体点，InnoDB的每行记录有一个隐藏列：“所属事务ID”，然后加上undo log（sql变更的逆操作列表）。一致性快照读，会根据快照视图，读取“历史”数据，快照视图记录了创建快照时处于活跃状态的事务ID列表，基于这个视图，InnoDB能够找到记录的合适版本（根据记录上的所属事务ID判断是否符合快照版本，不符合则根据undo log追溯）。

下面我们来做下实验，看看是不是这么回事。

3. 各种测试

创建测试表。

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t1` int(11) NOT NULL,
  `t2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_t1` (`t1`),
  KEY `idx_t2` (`t2`)
) ENGINE=InnoDB

加入几条测试数据：

mysql测试版本：5.7.18

3.1 事务1插入id=4的记录但未提交 -> 事务2执行普通查询，result: 事务2读不到id=4的记录。// 事务1提交之后事务2再次查询也是看不见的。

事务1 vs 事务2，这个实验说明一致性快照读，当然在同一个事务内对记录的变更对查询是可见的。

3.2 事务2执行加锁的sql语句

select * from test for update;   // 发生当前读，且尝试对所有最新版本的记录加X锁，事务1对id=4的记录持有X锁，索冲突，最终获取锁等待超时报错。

select * from test where id >3 for update;  // 仍然是获取行锁超时

事务1执行"delete from test where id>5;" -> 事务2执行"insert into test values (6,6,'6')"； // 结果事务2获取行锁失败，lock_data标识了锁的是伪记录。

事务1执行delete from test; -> 事务2执行insert into test valus(4,4,'4'); // 加锁失败，出现GAP锁了。

上面这个测试如果事务2执行的是insert into test valus(6,6,'6'); 也会加锁失败，但不会出现GAP，而是在lock_data标识行锁对应的是伪记录。若执行delete from test where id >5; 则执行成功，说明事务1加了记录锁和(3,5)的间隙锁，对于>5的区间并没有显式地持有间隙锁，但是其他事务在>5的区间进行插入操作时，会优先对伪记录持有行锁，导致其他事务插入操作需要竞争锁。

4. 总结

幻读是指一个事务的先后两次当前读操作返回结果不一样，后面的读取操作读到了更多的数据（其他事务插入的）。

读取操作分为一致性快照读，和当前读。

一致性快照读由MVCC方案实现，读取的快照版本。

当前读，依赖行锁+间隙锁，不存在幻读。特别的，对于唯一索引键两侧的无限区间，如果存在冲突的加锁sql语句，先执行的事务可能会立即持有伪记录（其他事务插入）的行锁。对于非唯一索引，会对扫描的范围持有行锁和间隙锁。

 

// 补充对于非主键索引查询的加锁实验

##事务1和事务2先后执行update test set t2='xx' where t1=2;  // 对检索的唯一索引加行锁。

##事务1和事务2先后执行update test set t2='xx' where t2='4';  // 对检索的普通索引加行锁。lock_data包括普通索引及对应的主键。

##事务1执行update test set t2='xx' where t2='5';事务2执行update test set t2='xx' where t1=5;//当冲突的sql语句没有使用同一个索引时，会升级为对主键索引加行锁。