The safety guarantees provided by transactions are often described by the well-known acronym ACID, which stands for atomicity, consistency, isolation, and durability. The term was coined in 1983 by Theo Härder and Andreas Reuter, in an effort to establish precise terminology for fault-tolerance mechanisms in databases.
In practice, however, one database’s implementation of ACID does not equal another’s. For example, as we shall see, there is a lot of ambiguity around the meaning of isolation. The high-level idea is sound, but the devil is in the details. Today, when a system claims to be “ACID compliant,” it’s unclear what guarantees you can actually expect. “ACID” has unfortunately become mostly a marketing term.
Note
Systems that do not meet the ACID criteria are sometimes called BASE, which stands for basically available, soft state, and eventual consistency. This is even more vague than the definition of ACID. It seems that the only sensible definition of BASE is “not ACID” (i.e., it can mean almost anything you want).
Let’s dig into the definitions of atomicity, consistency, isolation, and durability, as this will let us refine our idea of transactions.
Atomicity
In general, atomic refers to something that cannot be broken into smaller parts. The word means similar but subtly different things in different branches of computing. For example, in multithreaded programming, if one thread executes an atomic operation, that means there is no way that another thread could see the half-finished result of the operation. The system can be only in the state it was before the operation or after the operation, not something in between.
By contrast, in the context of ACID, atomicity is not about concurrency. It does not describe what happens if several processes try to access the same data at the same time, because that is covered under the letter I, for isolation (see “Isolation”).
Rather, ACID atomicity describes what happens if a client wants to make several writes, but a fault occurs after some of the writes have been processed—for example, a process crashes, a network connection is interrupted, a disk becomes full, or an integrity constraint is violated. If the writes are grouped together into an atomic transaction, and the transaction cannot be completed (committed) because of a fault, then the transaction is aborted and the database must discard or undo any writes it has made so far in that transaction.
Without atomicity, if an error occurs partway through making multiple changes, it’s difficult to know which changes have taken effect and which haven’t. The application could try again, but that risks making some changes twice, leading to duplicate or incorrect data. Atomicity simplifies this problem: if a transaction was aborted, the application can be sure that it didn’t change anything, so it can safely be retried.
The ability to abort a transaction on error and have all writes from that transaction discarded is the defining feature of ACID atomicity. Perhaps abortability would have been a better term than atomicity, but we will stick with atomicity since that’s the usual word.
Consistency
The word consistency is terribly overloaded:
-
In the related discussion, we discussed replica consistency and the issue of eventual consistency that arises in asynchronously replicated systems (see “Problems with Replication Lag”).
-
A consistent snapshot of a database, such as for a backup, is a snapshot of the entire database as it existed at one moment in time. More precisely, a consistent snapshot is consistent with the happens-before relation (see “The happens-before relation and concurrency”): if the snapshot contains a value that was written at a particular time, that snapshot also reflects all the writes that happened before that value was written.
-
Consistent hashing is an approach to sharding that some systems use for rebalancing (see “Consistent hashing”).
-
In the CAP theorem, the word consistency is used to mean linearizability (see “Linearizability”).
-
In the context of ACID, consistency refers to an application-specific notion of the database being in a “good state.”
It’s unfortunate that the same word has at least five meanings.
The idea of ACID consistency is that you have certain statements about your data (invariants) that must always be true—for example, in an accounting system, credits and debits across all accounts must always be balanced. If a transaction starts with a database that is valid according to these invariants, and any writes during the transaction preserve the validity, then you can be sure that the invariants are always satisfied. (An invariant may be temporarily violated during transaction execution, but it should be satisfied again at transaction commit.)
If you want the database to enforce your invariants, you need to declare them as constraints as part of the schema. For example, foreign-key constraints, uniqueness constraints, and check constraints (which restrict the values that can appear in an individual row) are often used to model specific types of invariants. More complex consistency requirements can sometimes be modeled using triggers or materialized views.
However, complex invariants can be difficult or impossible to model using the constraints that databases usually provide. In that case, it’s the application’s responsibility to define its transactions correctly so that they preserve consistency. If you write bad data that violates your invariants, but you haven’t declared those invariants, the database can’t stop you. As such, the C in ACID often depends on how the application uses the database and is not a property of the database alone.
Isolation
Most databases are accessed by several clients at the same time. That’s no problem if they are reading and writing different parts of the database, but if they are accessing the same database records, you can run into concurrency problems (race conditions).
Figure 8-1 is a simple example of this kind of problem. Say you have two clients simultaneously incrementing a counter that is stored in a database. Each client needs to read the current value, add 1, and write the new value back (assuming that no increment operation is built into the database). In Figure 8-1, the counter should have increased from 42 to 44, because two increments happened, but it actually went to only 43 because of the race condition.
Diagram illustrating a race condition where two clients concurrently read and increment a database counter, leading to an incorrect final value of 43 instead of 44.
Figure 8-1. A race condition between two clients concurrently incrementing a counter
Isolation in the sense of ACID means that concurrently executing transactions are isolated from each other; they cannot step on each other’s toes. The classic database textbooks formalize isolation as serializability, which means that each transaction can pretend that it is the only transaction running on the entire database. The database ensures that when the transactions have committed, the result is the same as if they had run serially (one after another), even though in reality they may have run concurrently.
However, serializability has a performance cost. In practice, many databases use forms of isolation that are weaker than serializability—that is, they allow concurrent transactions to interfere with each other in limited ways. Some popular databases, such as Oracle, don’t even implement it (Oracle has an isolation level called “serializable,” but it actually implements snapshot isolation, which is a weaker guarantee than serializability). This means that some kinds of race conditions can still occur. We will explore snapshot isolation and other forms of isolation in “Weak Isolation Levels”.
Durability
The purpose of a database system is to provide a safe place where data can be stored without fear of losing it. Durability is the promise that after a transaction has committed successfully, any data it has written will not be forgotten, even if there is a hardware fault or the database crashes.
In a single-node database, durability typically means that the data has been written to nonvolatile storage such as a hard drive or SSD. Regular file writes are usually buffered in memory before being sent to the disk sometime later, which means they may be lost if there is a sudden power failure; many databases therefore use the fsync system call to ensure that the data really has been written to disk. Databases usually also have a write-ahead log or similar feature (see “Making B-trees reliable”), which allows them to recover in the event that a crash occurs partway through a write. Many databases (such as MySQL, MongoDB, and PostgreSQL) store their data with a checksum, which allows them to detect corrupted or incomplete log entries and thus helps restore the database to a consistent snapshot after a crash.
In a replicated database, durability may mean that the data has been successfully copied to a certain number of nodes. To provide a durability guarantee, a database must wait until these writes or replications are complete before reporting a transaction as successfully committed. However, as discussed in “Reliability and Fault Tolerance”, perfect durability does not exist; if all your hard disks and all your backups are destroyed at the same time, there’s obviously nothing your database can do to save you.
Replication and Durability
Historically, durability meant writing to an archive tape. Then it was understood as writing to a disk or SSD. More recently, it has been adapted to mean replication. Which implementation is better?
The truth is, nothing is perfect:
-
If you write to disk and the machine dies, even though your data isn’t lost, it is inaccessible until you either fix the machine or transfer the disk to another machine. Replicated systems can remain available.
-
A correlated fault—say, a power outage, or a bug that crashes every node on a particular input—can knock out all replicas at once (see “Reliability and Fault Tolerance”), causing any data that is only in memory to be lost. Writing to disk is therefore still relevant for replicated databases.
-
In an asynchronously replicated system, recent writes may be lost when the leader becomes unavailable (see “Handling Node Outages”).
-
When the power is suddenly cut, SSDs in particular have been shown to sometimes violate the guarantees they are supposed to provide; even fsync isn’t guaranteed to work correctly. Disk firmware can have bugs, just like any other kind of software—for example, causing drives to fail after exactly 32,768 hours of operation. And fsync is hard to use; even PostgreSQL used it incorrectly for over 20 years.
-
Subtle interactions between the storage engine and the filesystem implementation can lead to bugs that are hard to track down and may cause files on disk to be corrupted after a crash. Filesystem errors on one replica can sometimes spread to other replicas as well.
-
Data on disk can gradually become corrupted without this being detected. If data has been corrupted for some time, replicas and recent backups may also be corrupted. In this case, you will need to try to restore the data from a historical backup.
-
One study of SSDs found that 30% to 80% of drives develop at least one bad block during the first four years of operation, and only some of these can be corrected by the firmware. Magnetic hard drives have a lower rate of bad sectors but a higher rate of complete failure than SSDs.
-
When a worn-out SSD (which has gone through many write/erase cycles) is disconnected from power, it can start losing data within a timescale of weeks to months, depending on the temperature. This is less of a problem for drives with lower wear levels.
In practice, no one technique can provide absolute guarantees. There are only various risk-reduction techniques—including writing to disk, replicating to remote machines, and backups—and they can and should be used together. As always, it’s wise to take any theoretical “guarantees” with a healthy grain of salt.