Tagged with "innodb"

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-18 10:32 UTC

Fun MySQL fact of the day: `REPLACE` your expectations

So, if we agree that we should avoid using INSERT ... ON DUPLICATE KEY UPDATE and INSERT IGNORE, surely we can use REPLACE INTO to upsert a row, right?! And yes, you can, if you want a DoS ~~bug~~ feature or account/identity takeover vulnerability in your multi-tenant system. Yep, ... read more

2019-03-14 11:33 UTC

Fun MySQL fact of the day: `ON DUPLICATE KEY LEAK DATA`

As developers, part of our job is ensuring system stability long after we are finished writing code. It is our responsibility to ensure the next developer in our place doesn't inherit a system littered with landmines and that our customers' data integrity and privacy is always a top priority. Today's ... read more

2019-03-13 19:06 UTC

Fun MySQL fact of the day: implicit rollbacks

Last Thursday, I suggested that you may not always want your transactions automatically rolled-back on an ACID violation, and, on Monday, I hinted that transaction rollbacks are nearly the most expensive thing you can do in MySQL/InnoDB. And, well, it's because of the very same "undo logs" about ... 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-11 23:38 UTC

Fun MySQL fact of the day: nothing isn't free

Nothing isn't free in InnoDB. When a transaction rolls-back in MySQL/InnoDB, the "or-nothing" part of "all-or-nothing" is close to being the most expensive thing you can do in MySQL. While we can dig deep into the internals to find out why, we've already gone over all the building blocks, and ... read more

2019-03-08 12:01 UTC

Fun MySQL fact of the day: leveraging `autocommit`

By default, MySQL uses autocommit for all new connections, and, as noted yesterday, autocommit can be remarkably powerful when used carefully for two separate reasons: ... read more

2019-03-07 13:26 UTC

Fun MySQL fact of the day: all-or-something?

Finally, A. The A in ACID stands for Atomicity, which is the property that guarantees that a series of database operations performed in a transaction all occur or nothing occurs. You might be thinking, "if Atomicity is the first letter in ACID, why did you save it ... read more

« Previous 1 2 3 4 Next »