2019-03-21 10:17 UTC

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 ... read more

2019-03-20 08:33 UTC

Fun MySQL fact of the day: the phantom record menace

Yesterday, we created an index to prevent a full table scan from locking the entire list_of_things table during a single UPDATE. I mentioned it was an improvement to the problem, but not a complete fix. Today, we'll see why.  ... read more

2019-03-19 08:32 UTC

Fun MySQL fact of the day: tenant lock-out

A couple weeks ago, we very briefly touched on the subject about MySQL/InnoDB locking only single records. And since we're on the topic of multi-tenant schemas, I think it's a good time to focus on this subject. For now, we'll focus on using MySQL's default isolation, REPEATABLE READ. ... read more

2019-03-12 20:00 UTC

Fun MySQL fact of the day: too much history

Yesterday, we discussed that InnoDB stores "old" record versions in an undo log, but we didn't discuss where undo logs are stored. While it's amusing to think it's turtles all the way down, it's actually a lot simpler: undo logs are stored inside InnoDB (and in redo logs, ... read more

2019-03-04 23:15 UTC

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 ... read more

2019-03-01 21:18 UTC

Fun MySQL fact of the day: InnoDB read-only optimisation

The I in ACID stands for Isolation, which is a property that determines whether or not a transaction can see the progress of other transactions. Transaction isolation is a complex system generally defined by the allowance of certain "read phenomena". ... read more

2019-02-27 21:55 UTC

Fun MySQL fact of the day: no nested transactions

Nested transactions allow a client to begin and end multiple transactions within an outer transaction (within an outer transaction (within an outer transaction (within an outer transaction ...))). When would you use it? This type of mechanism is necessary to accomplish a pure Component Based Architecture. However, excepting BerkleyDB, nearly ... read more