P99 CONF is the event on all things performance. Join us online Oct 23-24 — Registration is free

ACID Database

ACID Database Definition

What is ACID? ACID stands for atomicity, consistency, isolation, and durability. The ACID database acronym and its guarantees signal strong consistency:

  • Atomicity rolls back the entire operation if any part of the transaction fails.
  • Consistency ensures that with every transaction, the database remains structurally sound.
  • Isolation ensures that transactions are independent.
  • Durability permanently preserves all transaction results.

Compliance with ACID in a database guarantees that a READ will return the latest successful WRITE. ACID ensures that data remains in a valid, consistent state.

ACID compliant databases are typically very costly and challenging to scale. SQL databases share these downsides, since they are all ACID compliant to varying degrees. In fact, it can be very difficult and expensive to achieve SQL database deployments that are resilient and distributed. Thus, ACID compliant databases are often reserved for applications that require accurate records such as financial transactions.

ACID database diagram explaining that ACID stands for ACID stands for atomicity, consistency, isolation, and durability

ACID FAQs

ACID Database Principles?

The ACID database principles are: atomicity, consistency, isolation, and durability.

Atomicity is a transaction is an everything-or-nothing operation that either succeeds or fails. It may have multiple steps, but they are part of a single unit or operation. For example, either you make an online purchase, buy an item, and cause money to leave your account—or you don’t. If the database crashes mid-transaction, the entire transaction fails, and the money is still in your account.

Consistency requires that database systems keep data in a consistent state by updating it to respect the rules or constraints after multiple transactions. For example, money will leave your bank account via online transactions, withdrawals, checks, and other means, but integrity constraints ensure that the demands don’t exceed the balance.

Isolation ensures that data is managed between database operations so it is not used in transactions simultaneously. Instead, the first transaction will complete, and then the next will begin.

Durability means that committed transactions and database modifications are persistent, and are saved to permanent storage, not kept in volatile memory. This helps prevent data loss during system outages such as power failures.

What is ACID in database transactions?

In the context of data storage systems and databases, a transaction is any single unit operation that either completes entirely or not at all, leaving the storage system in a consistent state. The standard example of a transaction is withdrawing money from a bank account; either it leaves the bank account, or not.

ACID transactions ensure the greatest achievable data integrity and reliability. For example, ACID transactions ensure that a power failure does not cause data loss mid-use or trigger the need for time-consuming, costly recovery procedures.

Systems usually implement ACID transactions using locks. The system locks data so other transactions cannot access it until a transaction completes to ensure atomicity, isolation, and consistency. Write-ahead logs then guarantee durability, first storing into transaction logs, and only then implementing in the actual database.

Benefits of ACID transactions

ACID compliance offers various advantages:

Data integrity. ACID-compliant systems protect data and ensure it will be valid, remain accurate, and adhere to system constraints.

Simplified operational logic. Even complex update operations can be planned based on their mutual interaction mechanisms and do not need to be examined in advance. The DBMS system ensures consistency and isolation.

Reliable storage. This goes beyond in-memory storage, which fails often.

ACID transactions also have their shortcomings. The locking mechanism of systems that rely on ACID database design are usually slower at reading and writing operations, so high throughput, big data systems such as streaming services with distributed systems often perform more efficiently when they can process large amounts of data in parallel.

Alternatives to ACID usually include NoSQL databases such as ScyllaDB, Casandra, or MongoDB, and other distributed databases that replicate data across multiple nodes or servers. This kind of design permits more rapid data ingestion and reading because each node carries a copy of the overall data. The sacrifice is that each copy does not always update at the same time. In other words, this kind of system trades some consistency, accepting eventual consistency, for higher availability.

Eventual-consistency

Learn how the Raft consensus infrastructure enables radical new NoSQL capabilities: immediate consistentcy with zero overhead


Watch Video

ACID vs BASE Database

Relational databases follow the ACID database model, offering:

  • Atomicity: The entire operation rolls back if any part of the transaction fails.
  • Consistency: The database retains structural stability after each transaction.
  • Isolation: All transactions are independent.
  • Durability: All transaction results are preserved permanently.

ACID rules in databases generally make them costly and difficult to scale. SQL databases, which tend to comply with ACID rules to varying degrees, also tend to share the same flaws.

In contrast, NoSQL databases offer BASE guarantees. Here is a quick summary of ACID vs BASE properties in databases:

  • Basic Availability: Data is available most of the time, even during a partial system failure.
  • Soft state: Replicas are not always consistent.
  • Eventual consistency: Data will become consistent at some uncertain point in time.

NoSQL databases sacrifice some consistency to achieve higher availability and improved performance. Consistency in NoSQL databases is eventual rather than strong. Learn more from our educational page on SQL vs. NoSQL.

In sum, this difference between ACID vs BASE in databases means that the ACID model database is strongly consistent, easy to implement, and easy to scale vertically. They are simple to code yet more difficult to upgrade. The BASE model is eventually or weakly consistent and scales horizontally most readily. They make consistency tradeoffs to enable better performance and and high availability.

How do NoSQL Databases Support ACID Properties?

Do NoSQL databases support ACID (atomicity consistency isolation and durability)? Not exactly, although they support the same ideas in several ways.

The ACID properties of databases essentially provide the principles used for database transaction-oriented recovery. Adhering to ACID principles in case of failure ensures that data doesn’t become corrupt.

A transaction is one or many steps that make up a single logical operation. For example, transferring money between bank accounts from savings to checking is a transaction. A failure midway through before a transaction completes leaves the user without their money—an unacceptable outcome.

This signals a need for ACID principles to be applied to maintain data integrity.

Types of Failure

Transaction failure. This can occur due to a violation of consistency such as bad input or due to a deadlock or timeout in the DBMS.

System failure. This can be caused by an operating system fault, a bug in the DBMS code, or a hardware failure.

Media failure. This relatively rare type of failure which can be caused by bugs in the operating system or the media itself refers to not being able to read from or write to a storage device.

NoSQL Database ACID Compliance

ACID in relational databases is part of the normal landscape; all major relational DBMSs adhere to ACID principles and include features that ensure consistent data despite crashes and any failed transactions.

However, NoSQL databases are typically designed to ensure high availability across a cluster, sacrificing some durability or consistency while still allowing for some atomicity. And while most ACID database NoSQL options provide an eventually consistent solution, some vendors offer ACID compliant NoSQL database management systems.

Trending NoSQL Resources

ScyllaDB University Mascot

ScyllaDB University

Get started on your path to becoming a ScyllaDB expert.