本页讨论:
  • 幻读(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 SessionSession 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 SessionSession B | | START TRANSACTION;  START TRANSACTION; | | SELECT * FROM t_bitfly; | +------+-------+ | | id  | value | | +------+-------+ | |  1 || | +------+-------+ |  INSERT INTO t_bitfly |  VALUES (2, 'b'); | | SELECT * FROM t_bitfly; | +------+-------+ | | id  | value | | +------+-------+ | |  1 || | +------+-------+ |  COMMIT; | | SELECT * FROM t_bitfly; | +------+-------+ | | id  | value | | +------+-------+ | |  1 || | +------+-------+ | | UPDATE t_bitfly SET value='z'; | Rows matched: 2  Changed: 2  Warnings: 0 | (怎么多出来一行) | | SELECT * FROM t_bitfly; | +------+-------+ | | id  | value | | +------+-------+ | |  1 || | |  2 || | +------+-------+ | v

如何避免幻读?

如何保证两次当前读返回一致的记录,那就需要在第一次读与第二次读之间,其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能,innodb Next-Key Lock锁应运而生。

InnoDB中有三种行锁技术:
  1. Record Lock:单个行记录上的锁,我们通常讲的行锁,它的实质是通过对索引的加锁实现只有通过索引条件检索数据InnoDB才使用行级锁,否则,InnoDB将使用表锁。在事务隔离级别为读已提交下,仅采用Record Lock。
  2. Gap Lock:间隙锁,锁定一个范围,但不包含记录本身;
  3. 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 SessionSession 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 SessionSession B | | START TRANSACTION;  START TRANSACTION; | | SELECT * FROM t_bitfly; | +----+-------+ | | id | value | | +----+-------+ | |  1 || | +----+-------+ |  INSERT INTO t_bitfly |  VALUES (2, 'b'); |  COMMIT; | | SELECT * FROM t_bitfly; | +----+-------+ | | id | value | | +----+-------+ | |  1 || | +----+-------+ | | SELECT * FROM t_bitfly LOCK IN SHARE MODE; | +----+-------+ | | id | value | | +----+-------+ | |  1 || | |  2 || | +----+-------+ | | SELECT * FROM t_bitfly FOR UPDATE; | +----+-------+ | | id | value | | +----+-------+ | |  1 || | |  2 || | +----+-------+ | | SELECT * FROM t_bitfly; | +----+-------+ | | id | value | | +----+-------+ | |  1 || | +----+-------+ v


上一篇:锁定读
下一篇:死锁

成为你想看到的世界变革力量

创建者:万乐荣
最后更新时间 : 2018年9月9日 11:30

评论