SQL vs. NoSQL

What Is CAP?

The CAP Theorem, also called Brewer's Theorem, first was proposed by Eric Brewer in a July 2000 keynote at the ACM Symposium on the Principles of Distributed Computing. It was formally proved in 2002 by Seth Gilbert and Nancy Lynch of MIT. The CAP Theorem states that it is impossible for any shared-data system to guarantee simultaneously all of the following three properties: consistency, availability and partition tolerance.

Consistency in CAP is not the same as consistency in ACID (that would be too easy). According to CAP, consistency in a database means that whenever data is written, everyone who reads from the database will always see the latest version of the data. A database without strong consistency means that when the data is written, not everyone who reads from the database will see the new data right away; this is usually called eventual-consistency or weak consistency.

Availability in a database according to CAP means you always can expect the database to be there and respond whenever you query it for information. High availability usually is accomplished through large numbers of physical servers acting as a single database through sharing (splitting the data between various database nodes) and replication (storing multiple copies of each piece of data on different nodes).

Partition tolerance in a database means that the database still can be read from and written to when parts of it are completely inaccessible. Situations that would cause this include things like when the network link between a significant number of database nodes is interrupted. Partition tolerance can be achieved through some sort of mechanism whereby writes destined for unreachable nodes are sent to nodes that are still accessible. Then, when the failed nodes come back, they receive the writes they missed. In Cassandra, this is called hinted handoff. A database with good partition tolerance can span multiple data centers, whereas one with weak partition tolerance basically is bound to a single data center.

What Is ACID?

ACID is the classic measure of determining whether your database is good. A transaction in a database is a single logical operation. An example would be inserting an address or updating a phone number in an employee database. Every database provides methods to do operations like those, but ACID formalizes the process.

Atomicity means that the transaction either succeeds or fails. If the transaction fails, it should fail completely, and the database should be left in the state it was in before the transaction started.

Consistency means that the database is in a known good state both before and after the transaction.

Isolation means that transactions are independent of one another, and if two transactions are trying to modify the same data, one of them must wait for the other to finish before it can begin.

Durability means that once the transaction has completed, the changes made by the transaction will persist, even if there is a system failure. A transaction log of some sort usually is used for this purpose. In MariaDB and MySQL, this is called the binary log.

So, what is the opposite of ACID? BASE (Basically Available, Soft-state, Eventual consistency), of course. BASE is a retronymn coined by Dan Pritchett in an article in the ACM Queue magazine for describing a database that does not implement the full ACID model, with the main difference being that it is eventually consistent. The idea is that if you give up some consistency, you can gain more availability and greatly improve the scalability of your database.