MySQL的InnoDB的幻读问题

一些文章写到InnoDB的可重复读避免了“幻读”(phantom read),这个说法并不准确。

做个试验:(以下所有试验要注意存储引擎和隔离级别)

在这里插入图片描述

试验一:
在这里插入图片描述
如此就出现了幻读,以为表里没有数据,其实数据已经存在了,傻乎乎的提交后,才发现数据冲突了。

试验二:
在这里插入图片描述
在这里插入图片描述
本事务中第一次读取出一行,做了一次更新后,另一个事务里提交的数据就出现了。也可以看做是一种幻读。


那么,InnoDB指出的可以避免幻读是怎么回事呢?

http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html

By default, InnoDB operates in REPEATABLE READ transaction isolation
level and with the innodb_locks_unsafe_for_binlog system variable
disabled. In this case, InnoDB uses next-key locks for searches and
index scans, which prevents phantom rows (see Section 13.6.8.5,
“Avoiding the Phantom Problem Using Next-Key Locking”).

准备的理解是,当隔离级别是可重复读,且禁用innodb_locks_unsafe_for_binlog的情况下,在搜索和扫描index的时候使用的next-key locks可以避免幻读。

关键点在于,是InnoDB默认对一个普通的查询也会加next-key locks,还是说需要应用自己来加锁呢?如果单看这一句,可能会以为InnoDB对普通的查询也加了锁,如果是,那和序列化(SERIALIZABLE)的区别又在哪里呢?

MySQL manual里还有一段:

13.2.8.5. Avoiding the Phantom Problem Using Next-Key Locking (http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html)

To prevent phantoms, InnoDB uses an algorithm called next-key locking
that combines index-row locking with gap locking.

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.

我的理解是说,InnoDB提供了next-key locks,但需要应用程序自己去加锁。manual里提供一个例子:
在这里插入图片描述
这样,InnoDB会给id大于100的行(假如child表里有一行id为102),以及100-102,102+的gap都加上锁。

可以使用show innodb status来查看是否给表加上了锁。

再看一个实验,要注意,表t_bitfly里的id为主键字段。实验三:
在这里插入图片描述
在这里插入图片描述
可以看到,用id<=1加的锁,只锁住了id<=1的范围,可以成功添加id为2的记录,添加id为0的记录时就会等待锁的释放。

MySQL manual里对可重复读里的锁的详细解释:

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_repeatable-read

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE
MODE),UPDATE, and DELETE statements, locking depends on whether the
statement uses a unique index with a unique search condition, or a
range-type search condition. For a unique index with a unique search
condition, InnoDB locks only the index record found, not the gap
before it. For other search conditions, InnoDB locks the index range
scanned, using gap locks or next-key (gap plus index-record) locks to
block insertions by other sessions into the gaps covered by the range.


一致性读和提交读,先看实验,实验四:
在这里插入图片描述
在这里插入图片描述
如果使用普通的读,会得到一致性的结果,如果使用了加锁的读,就会读到“最新的”“提交”读的结果。

本身,可重复读和提交读是矛盾的。在同一个事务里,如果保证了可重复读,就会看不到其他事务的提交,违背了提交读;如果保证了提交读,就会导致前后两次读到的结果不一致,违背了可重复读。

可以这么讲,InnoDB提供了这样的机制,在默认的可重复读的隔离级别里,可以使用加锁读去查询最新的数据

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

If you want to see the “freshest” state of the database, you should
use either the READ COMMITTED isolation level or a locking read:
SELECT * FROM t_bitfly LOCK IN SHARE MODE;


结论:MySQL InnoDB的可重复读并不保证避免幻读,需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-key locks。
==================== 结尾 ====================

作者: bitfly. 转载请注明来源或包含本信息. 谢谢
链接: http://blog.bitfly.cn/post/mysql-innodb-phantom-read/

posted @ 2019-10-06 16:06  肥宅快乐码  阅读(352)  评论(0编辑  收藏  举报