You are here

What is the relation between SQL, NoSQL, the CAP theorem and ACID?

The CAP Theorem

Published by Eric Brewer in 2000, the theorem is a set of basic requirements that describe any distributed system (not just storage/database systems).

Let's imagine a distributed database system with multiple servers being used by an outside person requesting information. Here's how the CAP theorem applies:

  • Consistency - All the servers in the system will have the same data so anyone using the system will get the same copy regardless of which server answers their request.
  • Availability - The system will always respond to a request (even if it's not the latest data or consistent across the system or just a message saying the system isn't working).
  • Partition Tolerance - The system continues to operate as a whole even if individual servers fail or can't be reached.


It's theoretically impossible to have all 3 requirements met, so a combination of 2 must be chosen and this is usually the deciding factor in what technology is used.

ACID

ACID is a set of properties that apply specifically to database transactions, defined as follows:

  • Atomicity - Everything in a transaction must happen successfully or none of the changes are committed. This avoids a transaction that changes multiple pieces of data from failing halfway and only making a few changes.
  • Consistency - The data will only be committed if it passes all the rules in place in the database (ie: data types, triggers, constraints, etc).
  • Isolation - Transactions won't affect other transactions by changing data that another operation is counting on; and other users won't see partial results of a transaction in progress (depending on isolation mode).
  • Durability - Once data is committed, it is durably stored and safe against errors, crashes or any other (software) malfunctions within the database.

 

SQL / Relational DB

ACID is commonly provided by most classic relational databases like MySQL, Microsoft SQL ServerOracle and others. These databases are known for storing data in spreadsheet-like tables that have their columns and data types strictly defined. The tables can have relationships between each other and the data is queried with SQL (Structured Query Language), which is a standardized language for working with databases.

NoSQL

With the massive amounts of data being created by modern companies, alternative databases have been developed to deal with the scaling and performance issues of existing systems as well as be a better fit for the kind of data created. NoSQL databases are what these alternatives are called because many do not support SQL as a way to query the data.

These NoSQL alternatives have matured now and while some do provide SQL abilities, they have come to be known more for their emphasis on scalable storage of a much higher magnitude of data (ie: terabytes and petabytes) by dropping direct support for database joins, storing data differently and using several distributed servers together as one.

Tying it all together

So in overview, CAP provides the basic requirements that a distributed storage system has to follow and ACID is a set of rules that a database can choose to follow that guarantees how it handles transactions and keeps data safe.

NoSQL databases are alternatives to classic relational databases for storing lots more data or different kinds of data and they often use a distributed set of servers working together. This system has to fit 2 of the 3 requirements of the CAP theorem (depends on the software used and the needs of the application).

When it comes to how safe the committed data is, any ACID compliant system can be considered reliable but most NoSQL databases don't implement ACID and vary in how durable they are with stored data.

By the way, SQL vs NoSQL terms aren't useful in practice and it's far better to just reference the type of database itself, here are the major types:

  • Relational
  • Document
  • Key/Value
  • Wide-Column (different from relational db with columnar storage)
  • Graph
  • Search (optimized for storing and searching against text)

 

https://www.quora.com/What-is-the-relation-between-SQL-NoSQL-the-CAP-theorem-and-ACID


More info:

Blog: