MySQL如何解决幻读？

 

• SERIALIZABLE 串行化
• MVCC + Next-Key Lock

 

幻读：

　　幻读指事务T1在进行一次查询之后发现某个记录不存在，然后会根据这个结果进行下一步操作，此时如果事务T2成功插入了该记录，那么对于事务T1而言，其进行下一步操作（比如插入该记录）的时候很可能会报错。从事务使用的角度来看，在检查一条记录不存在之后，其进行插入应该完全没问题的，但是这里却抛出主键冲突的异常。

简单来说：事务T1的两次读之间有其他事务写操作，比如事务T1统计年龄 > 30，当T1两次读数据之间其他事务新添加了记录，所以事务T1第二次读取到的数据突然多了一个，仿佛出现了幻觉一般，这就是一种幻读

 

串行化：

• 事务在读操作时，先加共享锁，直到事务结束才释放
• 事务在写操作时，先加排它锁，直到事务结束才释放

串行化隔离级别 是 MySQL 中最高级别的隔离模式，它确保每个事务按顺序执行，就像在一个接一个的单线程环境中一样。

这意味着在同一时间只有一个事务可以访问特定的数据集，从而彻底消除并发带来的问题，如脏读、不可重复读和幻读。

隐式锁机制

在串行化隔离级别下，InnoDB 会自动为某些查询施加隐式的 Next-Key Locks，即使这些查询没有显式地使用 FOR UPDATE 或 LOCK IN SHARE MODE。这种隐式锁机制确保事务按顺序执行，并防止并发插入和更新操作。

串行化使用间隙锁(A>10,A是主键，则A<10的数据可以插入，A是一般索引，按B+树叶子节点排列加间隙锁)、行锁（等值的读写时）、表锁（当间隙锁锁住表所有记录，插入间隙锁范围外的数据插入失败），幻读不存在的！！！ 

 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

多版本并发MVCC：

InnoDB 的 MVCC， 是 通过 在 每 行 记录 后面 保存 两个 隐藏 的 列 来 实现 的。 这 两个 列， 一个 保存 了 行的 创建 时间， 一个 保存 行的 过期 时间（ 或 删除 时间）。 当然 存储 的 并不是 实际 的 时间 值， 而是 系统 版 本号（ system version number）。 每 开始 一个 新的 事务， 系统 版本 号 都会 自动 递增。 事务 开始时 刻 的 系统 版 本号 会 作为 事务 的 版 本号， 用来 和 查询 到 的 每 行 记录 的 版本 号 进行 比较。

MVCC 只在 REPEATABLE READ 和 READ COMMITTED 两个 隔离 级别 下 工作。

MVCC快照读需满足条件：

1. InnoDB 只 查找 版本 早于 当前 事务 版本 的 数据 行（ 也就是， 行的 系统 版本 号 小于 或 等于 事务 的 系统 版 本号）， 这样 可以 确保 事务 读 取的 行， 要么 是在 事务 开始 前 已经 存在 的， 要么 是 事务 自身 插入 或者 修 改过 的。
2. 行的 删除 版本 要么 未定义， 要么 大于 当前 事务 版 本号。 这可 以 确保 事务 读取 到 的 行， 在 事务 开始 之前 未被 删除。

MVCC解决了基于快照读下的幻读，事务 读 取的 行， 要么 是在 事务 开始 前 已经 存在 的， 要么 是 事务 自身 插入 或者 修 改过 的。

并不会读到其他事务的写操作 ！！！

 

MVCC无法解决当前读下的幻读。

 在 REPEATABLE READ 可重复读的隔离级别下进行当前读：

1. 事务T1 INSERT id= 10的记录，事务T2 在事务T1未commit时进行INSERT id=10的记录会阻塞，

对已COMMIT进行INSERT 会主键索引冲突 Duplicate entry '10' for key 'PRIMARY'。

2. 可以显式的加意向排它锁、意向共享锁避免大部分幻读

select * from tb where ? lock in share mode;
       select * from tb where ? for update;

for update：IX锁(意向排它锁)，即在符合条件的rows上都加了排它锁

lock in share mode：是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁

排它锁：X锁、 写锁，事务A对一个资源加了X锁后只有A本身能对该资源进行读和写操作，其他事务对该资源的读和写操作都将被阻塞，直到A释放锁为止  

共享锁：S锁、 读锁， 事务A锁定的数据其他事务可以共享读该资源，但不能写，直到事务A释放

 

FOR UPDATE 和 LOCK IN SHARE MODE 在可重复读隔离级别下会查询到其他事务已提交的数据。

 事务T1 

start TRANSACTION;

INSERT into orders (id,amount)  VALUES (2,100);

COMMIT;

事务T2

start TRANSACTION;

SELECT * FROM orders;

SELECT * FROM orders FOR UPDATE;

  SELECT * FROM orders LOCK IN SHARE MODE;

 

事务T1插入id=2的记录后COMMIT成功，此时事务T2执行 SELECT * FROM orders; 在可重复读的事务隔离级别下查询不到id=2的数据记录。

但执行 SELECT * FROM orders FOR UPDATE; 或 SELECT * FROM orders LOCK IN SHARE MODE;  可以查询到id=2的记录

 

若先执行事务T2的  SELECT * FROM orders FOR UPDATE; 获取INNDB快照，且加间隙锁。

事务T1的DML语句会阻塞报错。这样就不会出现幻读

INSERT into orders (id,amount) VALUES (3,50);

耗时：50006.079 ms执行结果：Error 1205 (HY000): Lock wait timeout exceeded; try restarting transaction [6aca72a3-5e65-466c-acea-1140658ffa94]

-------------------------------------------------------------------------------------------------------------------------------------------------------

间隙锁Gap Lock：

事务T1 锁定 orders 表 amount 大于1的所有记录

start TRANSACTION;

UPDATE orders SET amount = 200 WHERE amount > 1; 

 

事务T2 执行INSERT 语句报错 Lock wait timeout exceeded，等待事务T1进行事务COMMIT释放锁

start TRANSACTION;

INSERT into orders (id,amount)  VALUES (2,100);

耗时：50008.956 ms执行结果：Error 1205 (HY000): Lock wait timeout exceeded; try restarting transaction [523d8451-a0ce-4e94-b7b7-3ad0f786ccb4]

 

Next-Key Lock是Gap Lock（间隙锁）和Record Lock（行锁）的结合版，都属于Innodb的锁机制，主要应用于可重复读隔离级别，但也可能出现在更高隔离级别的事务中。

在某些情况下，即使使用 Next-Key Lock，也可能出现幻读，特别是在非唯一索引上。

select * from tb where id>100 for update;

1. 主键索引 id 会给 id=100 的记录加上 record行锁
2. 索引 id 上会加上 gap 锁，锁住 id(100,+无穷大）这个范围

其他事务对  id>100 范围的记录读和写操作都将被阻塞

插入 id=1000的记录时候会命中索引上加的锁会报出事务异常；

Next-Key Lock会确定一段范围，然后对这个范围加锁，保证A在where的条件下读到的数据是一致的，因为在where这个范围其他事务根本插不了也删不了数据，都被Next-Key Lock锁堵在一边阻塞掉了。

 

施瓦茨(Baron Schwartz); 扎伊采夫(Peter Zaitsev); 特卡琴科(Vadim Tkachenko). 高性能MySQL(第3版)