Fun MySQL fact of the day: inconsistent consistency
In being consistency inconsistent, the
ACID and the
CAP both stand for Consistency, but don't describe the same properties. In fact, the
CAP actually stands for Atomic Consistency (or Linearizability), and, oh holy crap!, the Atomic part of Atomic Consistency isn't the same property as the
A for Atomic in
ACID. More, the
CAP encompasses both the
Take a breath.
Let's break a few things down, starting first with the CAP theorem (or Brewer's theorem). In simple terms, the CAP theorem states that, in the presence of a network partition (
P), a system can remain either Atomically Consistent (
C) or Available (
A). We can say that MySQL is a
CP system, foregoing
A: in the presence of a network partition, the database will not be available to any/all nodes that have been partitioned off, but for any non-partitioned nodes (even if it's just the database itself), each read will see the most recent write. That's pretty much all there is to it.
Now we can move on to the
C (Consistency) in
ACID, which is the property that guarantees that within a transaction, data may only change in permitted ways. For example, an
UPDATE cannot result in a duplicate value in a
UNIQUE index, nor can it violate a
FOREIGN KEY constraint or write a string into a numeric field. If any of those things could happen, the data would become inconsistent. Thankfully, MySQL does enforce consistency... until it doesn't, so stay tuned for tomorrow's Fun MySQL fact of the day.