ACID Transactions

ACID transactions guarantees 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 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.

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 properties 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, Google Cloud Spanner, and Azure Cosmos DB. Implementing distributed ACID transactions in a scale-out database requires a transaction manager to coordinate the various operations and then commit/rollback 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. 

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, 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.