Fun MySQL fact of the day: REPEATABLE READ isn't REPEATABLE WRITE

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 INSERTs, UPDATEs, or 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.