所谓的幻影问题发生在一个事务中，当同一个查询在不同时间产生不同的行集合时。例如，如果一个SELECT 被执行两次，但第二次返回了第一次没有返回的一行，那么该行就是一行“幻影”行。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.

假设在child表的id列上有索引，并且你想读取并锁定该表中所有标识值大于 100 的行，目的是稍后更新所选行中的某些列：Suppose that there is an index on the id column of the child table and that you want to read and lock all rows from the table having an identifier value larger than 100, with the intention of updating some column in the selected rows later:

SELECT * FROM child WHERE id > 100 FOR UPDATE;

查询从索引中第一个 id 大于 100 的记录开始扫描。假设表中包含 id 值为 90 和 102 的行。如果在扫描范围内设置的索引记录锁没有锁住间隙中的插入（在本例中为 90 和 102 之间的间隙），另一个会话可以在表中插入一个 id 为 101 的新行。如果在同一事务中执行相同的 SELECT，你将会在查询返回的结果集中看到一个 id 为 101 的新行（一个 “幽灵”）。如果我们将一组行视为一个数据项，这个新的幽灵子行将违反事务的隔离原则，即一个事务应该能够在运行期间确保它读取的数据不会发生变化。The query scans the index starting from the first record where id is bigger than 100. Let the table contain rows having id values of 90 and 102. If the locks set on the index records in the scanned range do not lock out inserts made in the gaps (in this case, the gap between 90 and 102), another session can insert a new row into the table with an id of 101. If you were to execute the same SELECT within the same transaction, you would see a new row with an id of 101 (a “phantom”) in the result set returned by the query. If we regard a set of rows as a data item, the new phantom child would violate the isolation principle of transactions that a transaction should be able to run so that the data it has read does not change during the transaction.

为了防止幻读，InnoDB 使用一种称为 下一键锁定 的算法，它将索引行锁与间隙锁相结合。InnoDB 以行级锁的方式执行锁定，当它搜索或扫描表索引时，会对遇到的索引记录设置共享锁或排他锁。因此，行级锁实际上是索引记录锁。此外，索引记录上的下一键锁还会影响该索引记录之前的 “间隙”。也就是说，下一键锁是索引记录锁加上该索引记录之前间隙的间隙锁。如果一个会话对索引中的记录 R 拥有共享锁或排他锁，另一个会话就不能在索引顺序中紧接 R 前的间隙中插入新的索引记录。To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. 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. In addition, a next-key lock on an index record also affects the “gap” before the 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.

当 InnoDB 扫描索引时，它也可以锁定索引中最后一个记录之后的间隙。正如前面的例子所示：为了防止任何 id 大于 100 的记录插入表中，InnoDB 设置的锁包括对 id 值 102 之后的间隙的锁。When InnoDB scans an index, it can also lock the gap after the last record in the index. Just that happens in the preceding example: To prevent any insert into the table where id would be bigger than 100, the locks set by InnoDB include a lock on the gap following id value 102.

您可以使用下一个键锁定在您的应用程序中实现唯一性检查：如果您以共享模式读取数据，并且没有看到即将插入的行的重复值，那么您可以安全地插入您的行，并且知道在读取期间对您行的后继设置的下一个键锁防止了任何人在此期间插入重复行。因此，下一个键锁定使您能够 “锁定” 表中某些不存在的内容。You can use next-key locking to implement a uniqueness check in your application: If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the successor of your row during the read prevents anyone meanwhile inserting a duplicate for your row. Thus, the next-key locking enables you to “lock” the nonexistence of something in your table.

如 第 17.7.1 节，“InnoDB 锁定” 所述，可以禁用间隙锁定。这可能会导致幻影问题，因为当间隙锁定被禁用时，其他会话可以在间隙中插入新行。Gap locking can be disabled as discussed in Section 17.7.1, “InnoDB Locking”. This may cause phantom problems because other sessions can insert new rows into the gaps when gap locking is disabled.