4

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?

user207421
  • 305,947
  • 44
  • 307
  • 483
Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911

1 Answers1

3

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.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
Shadow
  • 33,525
  • 10
  • 51
  • 64
  • This is correct (of course). My comment would be that RR and RC use MVCC, where by SELECT statements can fetch earlier but correct generations of each row. So from a concurrency perspective, it is not usually recommended to use serializable. – Morgan Tocker Oct 14 '15 at 13:38