背景

RR隔离级别是否能够阻止幻读

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)
复制

mysql> desc test_table;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | bigint      | NO   | PRI | NULL    |       |
| user_name | varchar(50) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
复制

实验A：

执行结果A

实验B：

执行结果B

MySQL InnoDB如何定义幻读

The so-called phantom problem occurs within a transaction when the same query produces 
different sets of rows at different times. 
For example, if a SELECT is executed twice, but returns a row the second time 
that was not returned the first time, the row is a “phantom” row.
复制

By default, InnoDB operates in REPEATABLE READ transaction isolation level. 
In this case, InnoDB uses next-key locks for searches and index scans, 
which prevents phantom rows (see Section 15.7.4, “Phantom Rows”).
复制

• 在快照读情况下，MySQL通过MVCC来避免幻读。
• 在当前读情况下，MySQL通过next-key来避免幻读。

“读”与“读”的区别

快照读：就是select
select * from table ….;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert;
update ;
delete;

MySQL InnoDB的Next-Key Locks

A next-key lock is a combination of a record lock on the index record 
and a gap lock on the gap before the index record.

InnoDB performs row-level locking in such a way that when it searches or scans a table index, 
it sets shared or exclusive locks on the index records it encounters. 

Thus, the row-level locks are actually index-record locks. 
A next-key lock on an index record also affects the “gap” before that index record. 

That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record.

If one session has a shared or exclusive lock on record R in an index, 
another session cannot insert a new index record in the gap immediately before R in the index order.
复制

SELECT * FROM child WHERE id > 100 FOR UPDATE;

实验C：

执行结果C

If you want to see the “freshest” state of the database, 
use either the READ COMMITTED isolation level or a locking read.
复制

SELECT * FROM t FOR SHARE;

实验D：

执行结果D

执行结果D

结论