Fun MySQL fact of the day: necessarily coarse locks

If, like me, you're curious why, in yesterday's example, InnoDB acquires a next-key lock before and a gap lock after the records it is updating with equality in a secondary index when using REPEATABLE READ (what a mouthful!), then today's Fun MySQL fact is for you! I found myself particularly confused by this question since the InnoDB documentation only suggests a next-key lock would be used for an UPDATE, affecting only the gap before the records being updated. The MySQL bug tracker has numerous bugs reported about this, but they're all closed as "works as expected". And it does work as expected, but only if you know these three fun facts (that's right, not one but two bonus facts!):

• InnoDB does not "remember" which rows it has previously seen in a transaction,
• in an InnoDB index, records are sorted in ascending lexicographical order, and
• the primary key is always the last (hidden) field in a secondary index

So, if two index records are otherwise equal, they are sorted by primary key. This also means (a,b) is inserted after (a,a), and this is important: inserts to a non-unique secondary index can happen before or after an existing record. This is where the first fact comes in: because InnoDB doesn't "remember" which rows its seen in a transaction, to prevent phantom reads, InnoDB simply must prohibit inserts from being made in the gap before the first record it encountered and the gap after the last record it saw. This is because we can't always guarantee that new index records will always be "greater than" the last one, so InnoDB must lock the gaps before and after the matching records.

If you want to see this in action, go back to yesterday's example and running the following to transactions:

account 5> BEGIN; UPDATE list_of_things SET modified = now() WHERE account_id = 5;
account 9> BEGIN;
    INSERT INTO list_of_things VALUES(100, 9, now(), '9s oldest thing');
    INSERT INTO list_of_things VALUES(-100, 9, now(), '9s newest thing');

You'll see the first insert succeeds while the second one hangs. That's pretty much it.