本页讨论：
- 幻读（phantom read）是什么？
- 如何避免幻读？
Read Uncommited（未提交读） | Read Commited （提交读） | Repeatable Read （可重复读） | Serializable （可串行化） | |
脏读 | Possible | Not Possible | Not Possible | Not Possible |
不可重复读 | Possible | Possible | Not Possible | Not Possible |
幻行 | Possible | Possible | Possible | Not Possible |
从上表得知，就算是Repeatable Read 隔离级别，也无法完全避免幻行的发生。那么，幻读是什么呢？
幻读是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。这行就是一个“ 幻影 ”行。
如下图所示：
Repeatable Read 隔离级别下，幻读的例子：
mysql> show create table t_bitfly\G; CREATE TABLE `t_bitfly` ( `id` bigint(20) NOT NULL default '0', `value` varchar(32) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk
mysql> select @@global.tx_isolation, @@tx_isolation; +-----------------------+-----------------+ | @@global.tx_isolation | @@tx_isolation | +-----------------------+-----------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+-----------------+
t Session A Session B | | START TRANSACTION; START TRANSACTION; | | SELECT * FROM t_bitfly; | empty set | INSERT INTO t_bitfly | VALUES (1, 'a'); | | SELECT * FROM t_bitfly; | empty set | COMMIT; | | SELECT * FROM t_bitfly; | empty set | | INSERT INTO t_bitfly VALUES (1, 'a'); | ERROR 1062 (23000): | Duplicate entry '1' for key 1 v (shit, 刚刚明明告诉我没有这条记录的)
例子二：
t Session A Session B | | START TRANSACTION; START TRANSACTION; | | SELECT * FROM t_bitfly; | +------+-------+ | | id | value | | +------+-------+ | | 1 | a | | +------+-------+ | INSERT INTO t_bitfly | VALUES (2, 'b'); | | SELECT * FROM t_bitfly; | +------+-------+ | | id | value | | +------+-------+ | | 1 | a | | +------+-------+ | COMMIT; | | SELECT * FROM t_bitfly; | +------+-------+ | | id | value | | +------+-------+ | | 1 | a | | +------+-------+ | | UPDATE t_bitfly SET value='z'; | Rows matched: 2 Changed: 2 Warnings: 0 | (怎么多出来一行) | | SELECT * FROM t_bitfly; | +------+-------+ | | id | value | | +------+-------+ | | 1 | z | | | 2 | z | | +------+-------+ | v
如何保证两次当前读返回一致的记录，那就需要在第一次读与第二次读之间，其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能，innodb Next-Key Lock锁应运而生。
InnoDB中有三种行锁技术：
- Record Lock：单个行记录上的锁，我们通常讲的行锁，它的实质是通过对索引的加锁实现；只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。在事务隔离级别为读已提交下，仅采用Record Lock。
- Gap Lock：间隙锁，锁定一个范围，但不包含记录本身；
- Next-Key Lock：Record Lock+Gap Lock，锁定一个范围，并且锁定记录本身
避免幻行的方式就是应用程序自己去加next-key locks锁，可重复读下默认采用next-key locks。
举例：
表的初始记录：
mysql> select * from child;
+----+-----+
| id | val |
+----+-----+
| 10 | a |
| 20 | b |
| 30 | a |
| 40 | b |
| 50 | c |
+----+-----+
SELECT * FROM child WHERE id > 35 FOR UPDATE;
这样，InnoDB会给id大于35的行，以及[30-35]，[35-40], 35+ 的gap都加上锁。
如下所示：
t Session A Session B | | START TRANSACTION; START TRANSACTION; |
| |
| SELECT * FROM child | WHERE id > 35 | FOR UPDATE;
|
| +----+-----+
| | id | val |
| +----+-----+
| | 40 | b |
| | 50 | c |
| +----+-----+
| 2 row in set (0.00 sec) | INSERT INTO child | VALUES (31, 'b'); | (waiting for lock ... then timeout)
| ERROR 1205 (HY000):
| Lock wait timeout exceeded;
|
|
| INSERT INTO child | VALUES (36, 'b'); | (waiting for lock ... then timeout)
| ERROR 1205 (HY000):
| Lock wait timeout exceeded;
|
|
| INSERT INTO child values (26,"c");
| Query OK, 1 row affected (0.00 sec)
| | | select * from child;
| +----+-----+
| | id | val |
| +----+-----+
| | 10 | a |
| | 20 | b |
| | 30 | a |
| | 40 | b |
| | 50 | c |
| +----+-----+ | COMMIT; |
| COMMIT;
| | select * from child;
| +----+-----+
| | id | val |
| +----+-----+
| | 10 | a |
| | 20 | b |
| | 26 | c |
| | 30 | a |
| | 40 | b |
| | 50 | c |
| +----+-----+ v
另外，在默认的可重复读的隔离级别里，可以使用加锁读去查询最新的数据。
t Session A Session B | | START TRANSACTION; START TRANSACTION; | | SELECT * FROM t_bitfly; | +----+-------+ | | id | value | | +----+-------+ | | 1 | a | | +----+-------+ | INSERT INTO t_bitfly | VALUES (2, 'b'); | COMMIT; | | SELECT * FROM t_bitfly; | +----+-------+ | | id | value | | +----+-------+ | | 1 | a | | +----+-------+ | | SELECT * FROM t_bitfly LOCK IN SHARE MODE; | +----+-------+ | | id | value | | +----+-------+ | | 1 | a | | | 2 | b | | +----+-------+ | | SELECT * FROM t_bitfly FOR UPDATE; | +----+-------+ | | id | value | | +----+-------+ | | 1 | a | | | 2 | b | | +----+-------+ | | SELECT * FROM t_bitfly; | +----+-------+ | | id | value | | +----+-------+ | | 1 | a | | +----+-------+ v