What is ACID?

Intro

It's a harmful substance, of course? No. We are not talking about that here. Here we want to take a look at the acronym ACID which stands for:

• Atomicity - all or no operations in a transaction are executed.
• Consistency - e.g. read operations reflect changes after a write operation.
• Isolation - changes are reflected only after transactions are finished.
• Durability - data can survive system failure, power outages et al.

By reading some of the words like transaction, read, write you already know that the topic is related to databases.

Some databases are modelled based on the ACID transaction model so we can be confident that by using a particular database, working and storing our data will be reliable over time.

Databases Supporting ACID

Because of their reliability (ACID) and RDBMS features, some of the most used databases are:

• MySQL
• PostgreSQL
• MsSQL
• Oracle

The Problem

ACID sounds really good, so why are not all databases implementing it?

The problem comes, as it often does, with scale. When things get big enough and distributed it becomes much harder to keep ACID guarantees.

For example how to keep reads consistent and reflected across a distributed system immediately after a transaction? How do you keep transactions atomic across a distributed system?

Maybe BASE can help us?

What is BASE?

The "solution", abandon some of the ACID guarantees for the sake of scaling, easy.

Base stands for:

• Basically available - high availability through distribution and replication
• Soft state - state can be different based on time and location
• Eventually consistent - state becomes eventually consistent

With this transaction model we can scale easier but need to do more work on the application side to deal with inconsistencies.

BASE / NoSQL Databases

Base databases often come in the flavor of NoSQL instead of RDBMS. Here is a list of some:

• Cassandra
• Elasticsearch
• AWS DynamoDB
• MongoDB
• Couchbase

But I Want To Have Both!

If you want to have both ACID compliance and good old SQL features, there might be an options.

• TiKV / TiDB - TiKV provides both raw and ACID-compliant transactional key-value API. TiDB is an open-source MySQL compatible NewSQL database that supports Hybrid Transactional and Analytical Processing (HTAP) workloads.

Other Interesting Databases

• SurrealDB - Tables, documents, and graph. Store and model your data in any way. Realtime API layer, and security permissions all-in-one. Advanced inter-document relations and analysis. No JOINs. No pain and more.
• Xata - Serverless, relational, advanced search capabilities, analytics engine, branching capabilities.