Fun MySQL fact of the day: inconsistent consistency

In being consistency inconsistent, the C 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 same property as the A for Atomic in ACID. More, the C in CAP encompasses both the A and C of ACID! Ahhhh!

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 INSERT or 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.