MySQL——幻读的解决

MVCC下的幻读现象

两个事务下的幻读示例

-- 事务A
start transaction;
select * from t_user where id = 2;	-- 此时查询不到id = 2的数据
-- 此时开启事务B，在事务B中插入一条id = 2的记录
start transaction;
insert into t_user values(2, "pp");
commit;
-- 再次在事务A中执行select操作
select * from t_user where id = 2;	-- 保证RR下的可重复读，还是查询不到数据
-- 事务A再次执行以下操作，发现可以修改成功
update t_user set username = "kk" where id = 2;
-- 此时再在事务A中执行select操作
select * from t_user where id = 2;	-- 发现可以查询到

  在RR隔离级别下，事务A第一次执行快照select语句生成了一个ReadView，之后事务B向t_user表中新插入一条记录并提交。ReadView并不能阻止事务A执行update或者delete语句来改动新插入的记录（由于事务B已经提交，因此改动该记录并不会造成阻塞），这样以来，这条新记录的trx_id隐藏列的值就变成了事务A的事务id。之后事务A再用快照select查询这条记录就可以看到这条记录了。

举例说明（包含RR级别和RC级别）

假设原始行为：

field	DB_ROW_ID	DB_TRX_ID	DB_ROLL_PTR
0	10	1	0x1234

证明up_limit_id为已提交的最大事务ID + 1是错误的

  MVCC在特定条件下能够有效地避免幻读现象，但这仅限于使用快照读的情况

• 当前读：读取的数据都是记录的最新版本。有哪些操作会触发当前读？update、delete、insert、select lock in share model、select for update
• 快照读：读取的数据是历史版本的数据。普通select会触发快照读。

InnoDB中幻读的解决

  由上面可知，MVCC能够在一定程度上减轻幻读问题的影响，但它不能彻底杜绝幻读现象的发生。在第三级别RR隔离级别下，MVCC有效缓解大多数快照读场景下的幻读问题；而在一些实际业务场景中，特别像银行业务等对实时性要求很高的场景，快照读无法满足需求，必须采用当前读操作确保获取到到的是数据库中的实时状态。

  面对这一问题，一种有效的解决方案是在并发环境下采用加锁技术来实现对当前数据的精确读取。在MySQL数据库系统中，为解决幻读问题特别引入了Next-Key Locks机制。该机制涵盖了两个关键组成部分：记录锁（Record Locks）和间隙锁（Gap Locks）。

• 记录锁：加在索引上的锁，不包括记录本身，即使该表上没有任何索引，那么InnoDB会在后台创建一个隐藏的聚集主键索引，那么锁住的是这就是这个隐藏的聚集主键索引

  • 记录锁分为两种类型：共享锁（S锁）、排他锁（X锁）。当一个事务获取了一条记录的S型记录锁后，意味着该事务正在对该记录进行读取操作，在此状态下，其他事务也可以继续获取该记录的S型记录锁，进而实现并发读取，但这些事务不能获取该记录的X型排他锁，因为排他锁会限制其他事务对记录进行任何写入操作；其次，一旦一个事务成功获取了一条记录的X型排他锁，这就意味着该事务获得了对这条记录的独占访问权，不仅能够进行读取，还可以进行修改或删除等写入操作。此时，其他任何事务均无法再获取该记录的共享锁（S锁）或排他锁（X锁），从而确保了在事务执行期间，该记录的完整性得以妥善保护，不会受到其他并发事务的影响。

• 间隙锁：针对索引记录之间的空隙进行锁定，不对索引本身上锁，前开后开，不锁记录。

  -- 事务A
  start transaction;
  -- 该条语句会对number=3的左边两边上锁，假设表中有有两个相同的数据值，那么该数据值两边均会添加间隙锁
  -- 下面语句改成id = 5，也是同样效果
  select * from t_user where number = 3 for update;
  -- 表t_user中有4条记录，分别是
  -- id = 1, number = 1
  -- id = 5, number = 3
  -- id = 7, number = 8
  -- id = 11, number = 12
  -- 事务B
  start transaction;
  insert into t_user values(2,2);
  -- 此时会出现错误：ERROR1205: Lock wait timeout exceeded; try restarting transaction
  -- 因为（1，1）和（5，3）之间，（5，3）和（7，8）之间有间隙锁，不允许插入。
  -- 如果事务B执行如下语句，则可以成功
  insert into t_user values(8, 9);
  

  • 对于没有索引的列，当前读操作时，会加全表间隙锁。

• Next-Key Locks
    Next-Key Locks实质上是记录锁与间隙锁的有机结合体，其锁定范围不仅涵盖了记录实体本身，而且采用了前开后闭原则，即锁定区间包含了指定记录及其右侧的间隙（相对而言锁的范围较大，会影响并发度）。Next-Key Locks在间隙锁的基础上额外包含了紧邻间隙右侧的记录行，以此来增强并发控制力度，减少潜在的幻读风险，但与此同时可能会对系统的并发性能产生一定影响。