ACID Transaction Guarantee

Introduction

ACID is an acronym for Atomicity, Consistency, Isolation, and Durability. These are the properties of a database transaction. Therefore, any database that satisfies the above properties while running read and write operations is ACID compliant.

ACID compliant databases favor consistency and integrity of data over persisting incorrect and partial data. Atomicity, isolation, and durability are database-defined guarantees, while consistency is an application-defined guarantee.

Atomicity

An atomicity guarantee ensures the database executes a sequence of operations as one single logical unit of work. Either it completes all the operations successfully or rolls back all the operations. The act of successfully executing all the database operations is a commit, and the act of reverting all the database operations on failure is a rollback.

Atomicity ensures no partial updates in the database, and the application developers can rely on the fact that the database is always in a clean state on failure. Furthermore, this guarantee ensures that the application developers can safely retry after failure without a doubt on duplicate data creation.

Consistency

Consistency guarantees ensure that the database moves from one valid state to another. During a transition phase, the database ensures it has satisfied all the rules and constraints.

Consistency is a user-controlled property. Therefore, the application must define all the valid rules and constraints that map to a consistent final state. An example of a consistency guarantee is that the balance of a bank account should always remain positive. This example is an application-defined guarantee, and the database plays no role in ensuring the correctness of this rule.

Isolation

In a typical database, there can be multiple database operations running concurrently. The isolation guarantee ensures that the concurrent transactions do not step onto each other's toes. If concurrently running transactions result in a state of the database that is the same as the state if the transactions run sequentially, then the database supports an isolation guarantee.

The isolation guarantee dictates how and what data should be visible for concurrently running transactions.

Durability

Durability guarantees ensure that the committed transactions remain persistent for a long time even if the system crashes or a power outage happens. While the definition of durability varies from one database to another, in most cases, durability implies persisting the data in nonvolatile storage like a disk. Durability ensures that the critical data remains safe and protected in the computer for later access.

Most relational databases, such as MySQL and Postgresql, are ACID compliant. However, NoSQL databases support a weaker set of guarantees and are not ACID-compliant.

Shortcomings of ACID

While ACID-compliant databases provide high consistency and integrity of data, they can fail under certain circumstances:

• When the data is too large to fit on a single machine, the database distributes the data across multiple physical nodes. In this case, ACID properties are not trivial to achieve.

• When the application's throughput increases beyond what a single machine can handle, we must create replicas and distribute the load across multiple nodes. In this case, ACID properties become difficult to achieve.

BASE

An alternative to the ACID model is the BASE model. BASE is an acronym for Basically Available, Soft State, and Eventual Consistency. Databases that do not support ACID properties follow the BASE model:

• Basically Available: Databases that favor the BASE model prefer high availability over immediate data consistency. They achieve this by replicating data over multiple nodes. If a single node fails, part of the data will be unavailable, but the database as a whole is operational.

• Soft State: Since the database replicates data over multiple nodes, immediate consistency is a distant reality. Hence, the data values change over time, so the interpretation of data consistency is on the application developers.

• Eventual Consistency: Since the database replicates data to multiple nodes, the database performs asynchronous replication to some nodes to achieve low latency. Hence, the data converges over time on all the nodes leading to eventual consistency.

NoSQL databases are designed for high availability and eventual consistency and follow the BASE model. For example, Cassandra and MongoDB adopt the BASE model by distributing data over replica nodes and having different notions of eventual consistency.

Databases that support the BASE model support high availability, but the high availability comes at the cost of immediate consistency. Data can be stale and divergent in multiple nodes. So, depending on the node the user queries, the data states can be different from one another.