mysql - 可重复读取和可序列化的 MySQL InnoDB 实现之间的实际区别是什么

Question

按照SQL标准，Repeatable Read应该防止模糊读和脏读，而Serializable也应该防止幻读。

根据MySQL documentation :

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

那么，如果可重复读也可以防止幻读，那么可序列化会提供什么返回呢？

Serializable 是否可以防止写入偏斜或读取偏斜而 Repeatable Read 不能？

Answer 1

答案也可以在mysql documentation中找到, 引用:

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions.

可序列化的事务调度，在使用两阶段锁定实现时，可以防止读写倾斜。这就是它在 SQL Server 上使用锁定或在 PostgreSQL 上使用其可序列化快照隔离的工作方式。

如果在任何正在读取的资源上获取了共享锁，那么读偏斜和写偏斜也会被阻止。