幻读 phantom read
本页讨论：
- 幻读（phantom read）是什么？
- 如何避免幻读？
幻读（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