ACID Transactions: The Cornerstone of Database Integrity

Learn the Who, What, Why, Where, and How of ACID Transactions

ACID transactions guarantee the highest levels of data integrity in a database and were a significant development when introduced in the 1980s as part of monolithic SQL databases like Oracle and IBM DB2.

However, when NoSQL databases, like Apache Cassandra and MongoDB, entered the market, they initially focused on “big data” use cases that didn’t require ACID transactional guarantees. These databases prioritized horizontal scalability for newer cloud native applications at the expense of consistent data.

Despite this, ACID transactions remain a fundamental building block when developing business-critical, user-facing transactional applications. They govern the complex process of ensuring data integrity while supporting highly concurrent operations. Classic examples include financial ledgers, hotel and airline bookings, or eCommerce inventory, where it’s critical that every user sees accurate, consistent data.

While monolithic SQL/relational databases are ACID compliant, NoSQL/non-relational databases are not or may only support a highly restrictive single-row ACID (see below). They sacrifice ACID transactions to provide higher performance at scale (i.e. low latency and high throughput) than legacy relational databases can deliver.

Types of ACID Transactions

There are three ways to classify ACID transactions in the context of distributed databases:

  1. Single-row ACID transactions occur when all operations impact only a single row (aka key). As the data for a single row typically doesn’t cross over to other nodes (in most distributed databases), single-row ACID is easier to achieve in a distributed database. However, many NoSQL databases cannot even support this type of ACID transaction as their eventually consistent storage engines have no inherent guarantees on the correctness of the data read.
  2. Single-shard ACID transactions are a marginal improvement on single-row ACID. Single-shard ACID occurs when all the rows involved in the transaction’s operations are located in a single shard of a distributed database. Since a single shard is always located inside a single node (or server), single-shard ACID doesn’t involve coordinating transaction operations across multiple nodes. Therefore, it is easier to implement in a distributed database.
  3. Distributed ACID transactions are transactions that impact a set of rows distributed across shards on multiple nodes distributed across a data center, region, or the world. Shards are spread across nodes when the cluster is created in auto-sharded distributed databases like YugabyteDB and Google Cloud Spanner. Implementing distributed ACID transactions in a scale-out database requires a transaction manager to coordinate the various operations and then commit/roll back the transaction as required. Popular NoSQL databases are not built on a distributed storage engine and therefore avoid these distributed ACID transactions for fear of compromising performance (i.e. increased write latency and decreased linear scalability). However, you can improve application developer agility through a modern distributed database that offers both distributed ACID transactions and high performance.

What are Distributed ACID Transactions?

Distributed ACID transactions are ACID-compliant transactions that modify multiple rows in more than one shard—usually distributed across multiple nodes. A transaction is a unit of work performed within a database, often composed of multiple operations.

Like all forms of ACID, a distributed ACID transaction has four key properties:

  • Atomicity — All operations in a transaction are treated as a single atomic unit . All are performed or none are performed.
  • Consistency — The database is always in a consistent, internal state.
  • Isolation — Determines how and when changes made by one transaction become visible to others.
  • Durability — Ensures all transaction results permanently remain in the system. Any modifications must persist even in case of power loss or system failures.

Are ACID Transactions in a Distributed Database Always Distributed?

There are different types of distributed databases. The most widely-used distributed databases include common NoSQL databases such as Apache Cassandra, and distributed SQL databases, like YugabyteDB.

Some distributed databases support ACID transactions in a limited fashion, while others fully support distributed ACID transactions. ACID transactions can be classified into three types (see above):

  1. Single-row ACID
  2. Single-shard ACID
  3. Distributed ACID transactions (see above)

Only distributed ACID transactions are fully distributed and are the default transaction for distributed SQL databases, like YugabyteDB.

YugabyteDB is a high-performance distributed SQL database that supports fully distributed ACID transactions across multiple rows, multiple shards, and multiple nodes at any scale and across zones, regions, and countries.

What Every Database Needs to Support ACID Transactions

Four foundational aspects must be designed and developed to support ACID transactions in a monolithic or distributed database.

  1. Provisional updates are required for atomicity. Transactions involve multiple operations across multiple rows. Since these operations must be treated as a single unit, a provisional update (in a temporary space) is needed first, followed by a commit.
  2. A strongly consistent core (for consistency) is the foundation for achieving ACID guarantees in a transaction involving a single operation on a single row. Additional data integrity constraints are built on top of this core to achieve full ACID compliance (with multiple operations across multiple rows).
  3. Transaction ordering is necessary for isolation. For a database to support the strictest serializable isolation level, a mechanism such as globally ordered timestamps is required to sequentially arrange all transactions. However, the snapshot isolation level relies on partial ordering, where sub-operations in one transaction may interleave with those from other transactions. This results in lower latency and higher throughput than the serializable level, while still detecting write-write conflicts.
  4. Persistent storage (for durability) is easy to achieve with modern storage technology. A storage engine stores data in an underlying persistent device, such as an HDD or SSD. To understand how different types of storage engines work and their workload optimization patterns, check out A Busy Developer’s Guide to Database Storage Engines.

The Benefits of ACID Transactions

  1. Absolute Data Integrity and Safety
    A major concern for application developers is ensuring data integrity. ACID transactions provide a straightforward solution to this challenge. By leveraging the consistency offered by ACID transactions, developers can easily enforce application-specific constraints, and prevent lost updates, dirty reads, and stale reads. This is essential if you are building user-facing applications where accurate, timely data is essential, like for the financial services, retail, and SaaS industries.
  2. Simplified Concurrency Control
    Concurrent access to shared resources such as retail inventory, bank balances, and gaming leaderboards is inevitable and can cause issues for application developers. Isolation in ACID transactions helps them manage this. For example, suppose the database guarantees transactions with serializable isolation. In this case, developers can treat each transaction as if it was executed sequentially, eliminating the need to program logic to handle conflicts or errors between operations from separate transactions.
  3. Intuitive Data Access Logic
    ACID-compliant databases typically support complex schema modeling and natively support multi-step data manipulation operations, such as consistent secondary indexes. Therefore, business logic can be more directly represented in the application code.
  4. Future-Proofing Database Needs
    Durability is a non-negotiable feature for any database that needs stable persistence. Therefore, “in-memory” only systems are not considered databases. Developers may be tempted to compromise atomicity, consistency, isolation, or some combination of these features in exchange for better performance, but the loss of flexibility long term can be costly. The ability to enhance applications quickly gives businesses a competitive edge. For example, an internal-facing, non-transactional app with a dashboard-only view can be quickly transformed into a customer-facing transactional app if the original database is fully ACID-compliant.

ACID Transactions FAQ:

  • What are the types of ACID transactions?

    ACID transactions can vary between different distributed databases and can be classified as single-row, single-shard, or distributed. Single-row transactions are simple but not universally supported. Single-shard transactions are easier to implement but have limitations, while distributed transactions need coordination from a more complex architecture but offer much greater agility and performance.

  • What are distributed ACID transactions?

    Distributed ACID transactions are ACID-compliant transactions that modify multiple rows in more than one shard—usually distributed across multiple nodes. A transaction is a unit of work performed within a database, often composed of multiple operations.

  • What are the benefits of ACID transactions?

    ACID transactions guarantee data integrity, simplify concurrency control, and offer intuitive data logic, which is essential for finance, retail, and SaaS companies. Supporting complex schemas, it ensures flexibility and a competitive edge by transforming non-transactional apps into transactional ones.

  • Does YugabyteDB support fully distributed ACID transactions?

    Yes, YugabyteDB is a high-performance distributed SQL database that supports fully distributed ACID transactions across multiple rows, multiple shards, and multiple nodes at any scale.