Fun MySQL fact of the day:
REPEATABLE READ isn't
Since we're on the topic of transaction isolation, you might have previously had a mental model that transaction isolation levels are a layered model, each one stronger than the next. Unfortunately, this isn't quite true; and if that's not annoying enough, you might even be surprised to learn that while ANSI SQL-92 does list 8 different types of read phenomena, the list is not comprehensive nor is it even complete in terms of how each "level" should avoid them.
Since this is a Fun MySQL Fact and not a Fun Isolation Fact, let's take a sideways look at MySQL's default isolation level,
REPEATABLE READ, and see what happens when you look at it the wrong way. While I last explained that MySQL employs MVCC to implement lock-free transaction isolation, I didn't fully explain that the lock-free benefit was only for queries and not for
DELETEs. For these DML (Data Manipulation Language) commands, MySQL instead uses write locks on the current version of a record (to avoid specific read phenomena).
Now, let's consider: if using
REPEATABLE READ you queried a row,
X(id=1,version=1), you'll always get back
X(id=1,version=1) no matter how many times you query it in a transaction. It is, of course, aptly named a repeatable read. So, wouldn't it then surprise you if you tried to update this same row and
SET X (version=2) WHERE X(id=1,version=1) only to have MySQL tell you there were 0 matching rows? So, somebody else must have changed it. But, if you query it again, the row will still be
X(id=1,version=1). Outrageous! This is surely a bug!
Except not. In MySQL, DML always uses the current record version regardless of the version "pinned" inside your transaction's read view. I like to call this a write phenomenon, which is neither prohibited in any capacity nor even a bug. MySQL bug #57973 explains this behaviour as a "sort of WRITE COMMITTED version of REPEATABLE READ". While this is weird behaviour, it is the reason Optimistic Concurrency Control works in
REPEATABLE READ without deadlocks in InnoDB.