Notes
Fun MySQL fact of the day:
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 moreFun 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 moreFun 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 moreFun 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
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
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
Fun MySQL fact of the day: a CHECKered past
A Check Constraint is a table constraint, described in SQL-89, that allows a database index to remain consistent based on a specified search criteria or expression. For example, we could ADD CONSTRAINT is_even_ck CHECK(my_int_col % 2 = 0) to ensure that the value of the my_int_col column in every row ... read more
Fun MySQL fact of the day: inconsistent consistency
In being consistency inconsistent, theC in ACID and the C in CAP both stand for Consistency, but don't describe the same properties. In fact, the C in CAP actually stands for Atomic Consistency (or Linearizability), and, oh holy crap!, the Atomic part of Atomic Consistency isn't the ... read more
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
Fun MySQL fact of the day: InnoDB read-only optimisation
TheI 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