What's the actual difference between MySQL InnoDB implementations of Repeatable Read and Serializable

Question

According to the SQL Standard, Repeatable Read should prevent fuzzy reads and dirty reads, while Serializable should also prevent phantom reads.

According to the 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”).

So if Repeatable Read can prevent phantom reads too, what does Serializable offers in return?

Is it that Serializable protects against write skew or read skew and Repeatable Read does not?

Answer 1

The answer can also be found in mysql documentation, quote:

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.

A serializable transaction schedule, when implemented with two-phase locking, prevents read and write skew. That's how it works on SQL Server using locking or on PostgreSQL using their Serializable Snapshot Isolation.

If a shared lock is acquired on the any resource that's being read, then read skew and write skew are prevented as well.