Menu

MyRocks and Repeatable Read Isolation

Lu Pan | 18 May 2021 | 1 min read

tl;dr: Repeatable read isolation on MyRocks is effectively Snapshot isolation (unless it's affecting an unique index).

According to MySQL reference,

REPEATABLE READ

This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See Section 15.7.2.3, “Consistent Nonlocking Reads”.

For locking reads (SELECT with FOR UPDATE or FOR SHARE), 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 locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks, see Section 15.7.1, “InnoDB Locking”.

It basically means for non-locking SELECTs it provides effectively snapshot isolation. What if I do add locks for my SELECTs? If the index is not unique, well, too bad, because MyRocks doesn't have gap lock, it falls back to non-locking SELECTs. Hence snapshot isolation again.

According to MySQL reference,

With REPEATABLE READ isolation level, the snapshot is based on the time when the first read operation is performed. With READ COMMITTED isolation level, the snapshot is reset to the time of each consistent read operation.

If you think about it, it all makes sense due to the lack of gap lock.

Why should I care

To avoid the following