To recap, in ACID, atomicity and isolation describe what the database should do if a client makes several writes within the same transaction:
Atomicity
If an error occurs halfway through a sequence of writes, the transaction should be aborted, and the writes made up to that point should be discarded. In other words, the database saves you from having to worry about partial failure by giving an all-or-nothing guarantee.
Isolation
Concurrently running transactions shouldn’t interfere with each other. For example, if one transaction makes several writes, then another transaction should see either all or none of those writes, but not a subset.
These definitions assume that you want to modify several objects (rows, documents, records) at once. Such multi-object transactions are often needed if several pieces of data need to be kept in sync. Figure 8-2 shows an example from an email application. To display the number of unread messages for a user, you could query something like this:
SELECT COUNT(*) FROM emails WHERE recipient_id = 2 AND unread_flag = true
However, you might find this query to be too slow if there are many emails and decide to store the number of unread messages in a separate field (a kind of denormalization, which we discuss in “Normalization, Denormalization, and Joins”). Now, whenever a new message comes in, you have to increment the unread counter as well, and whenever a message is marked as read, you also have to decrement the unread counter.
In Figure 8-2, user 2 experiences an anomaly: the mailbox listing shows an unread message, but the counter shows zero unread messages because the counter increment has not yet happened. (If an incorrect counter in an email application seems too insignificant, think of a customer account balance instead of an unread counter and a payment transaction instead of an email.) Isolation would have prevented this issue by ensuring that user 2 sees either both the inserted email and the updated counter, or neither, but not an inconsistent halfway point.
Diagram illustrating a "dirty read" scenario where User 2 reads uncommitted data inserted and updated by User 1 in a database transaction.
Figure 8-2. Violating isolation: one transaction reads another transaction’s uncommitted writes (a “dirty read”)
Figure 8-3 illustrates the need for atomicity: if an error occurs somewhere over the course of the transaction, the contents of the mailbox and the unread counter might become out of sync. In an atomic transaction, if the update to the counter fails, the transaction is aborted and the email insertion is rolled back.
Diagram illustrating the need for atomicity in transactions, showing a database crash during a transaction leading to an inconsistent state in which an email has been inserted into a table, but the corresponding unread counter has not been updated.
Figure 8-3. Atomicity ensures that if an error occurs, any prior writes from that transaction are undone, to avoid an inconsistent state.
Multi-object transactions require some way of determining which read and write operations belong to the same transaction. In relational databases, that is typically done based on the client’s TCP connection to the database server. On any particular connection, everything between a BEGIN TRANSACTION and a COMMIT statement is considered to be part of the same transaction. If the TCP connection is interrupted, the transaction must be aborted.
On the other hand, many nonrelational databases don’t have such a way of grouping operations together. Even if there is a multi-object API (e.g., a key-value store may have a multi-put operation that updates several keys in one operation), that doesn’t necessarily mean it has transaction semantics: the command may succeed for some keys and fail for others, leaving the database in a partially updated state.
Single-object writes
Atomicity and isolation also apply when a single object is being changed. For example, imagine you are writing a 20 kB JSON document to a database:
-
If the network connection is interrupted after the first 10 kB have been sent, does the database store that unparseable 10 kB fragment of JSON?
-
If the power fails while the database is in the middle of overwriting the previous value on disk, do you end up with the old and new values spliced together?
-
If another client reads that document while the write is in progress, will it see a partially updated value?
Each of those outcomes would be incredibly confusing, so storage engines almost universally aim to provide atomicity and isolation on the level of a single object (such as a key-value pair) on one node. Atomicity can be implemented using a log for crash recovery (see “Making B-trees reliable”), and isolation can be implemented using a lock on each object (allowing only one thread to access an object at any one time).
Some databases also provide more complex atomic operations, such as an increment operation, which removes the need for a read-modify-write cycle like that in Figure 8-1. Similarly popular is a conditional write operation, which allows a write to happen only if the value has not been concurrently changed by someone else (see “Conditional writes (compare-and-set)”), similarly to a compare-and-set or compare-and-swap (CAS) operation in shared-memory concurrency.
Note
Strictly speaking, the term atomic increment uses the word atomic in the sense of multithreaded programming. In the context of ACID, it should be called an isolated or serializable increment, but that’s not the usual term.
These single-object operations are useful, as they can prevent lost updates when several clients try to write to the same object concurrently (see “Preventing Lost Updates”). However, they are not transactions in the usual sense of the word. For example, the Aerospike’s “strong consistency” mode and “lightweight transactions” feature of Cassandra and ScyllaDB offer linearizable (see “Linearizability”) reads and conditional writes on a single object, but no guarantees across multiple objects.
The need for multi-object transactions
Do we need multi-object transactions at all? Would it be possible to implement any application with only a key-value data model and single-object operations?
In some use cases, single-object inserts, updates, and deletes are sufficient. However, in many other cases, writes to several objects need to be coordinated:
-
In a relational data model, a row in one table often has a foreign-key reference to a row in another table. Similarly, in a graph-like data model, a vertex has edges to other vertices. Multi-object transactions allow you to ensure that these references remain valid; when inserting several records that refer to one another, the foreign keys have to be correct and up-to-date, or the data becomes nonsensical.
-
In a document data model, the fields that need to be updated together are often within the same document, which is treated as a single object; no multi-object transactions are needed when updating a single document. However, document databases lacking join functionality also encourage denormalization (see “When to Use Which Model”). When denormalized information needs to be updated, as in the example of Figure 8-2, you need to update several documents in one go. Transactions are very useful in this situation to prevent denormalized data from going out of sync.
-
In databases with secondary indexes (almost everything except pure key-value stores), the indexes also need to be updated every time you change a value. These indexes are different database objects from a transaction point of view—for example, without transaction isolation, it’s possible for a record to appear in one index but not another because the update to the second index hasn’t happened yet (see “Sharding and Secondary Indexes”).
Such applications can still be implemented without transactions. However, error handling becomes much more complicated without atomicity, and the lack of isolation can cause concurrency problems. We will discuss those problems in “Weak Isolation Levels” and explore alternative approaches in the related discussion.
Handling errors and aborts
A key feature of a transaction is that it can be aborted and safely retried if an error occurs. ACID databases are based on this philosophy: if the database is in danger of violating its guarantee of atomicity, isolation, or durability, it would rather abandon the transaction entirely than allow it to remain half-finished.
Not all systems follow that philosophy, though. In particular, datastores with leaderless replication (see “Leaderless Replication”) work on more of a “best effort” basis, which can be summarized as “the database will do as much as it can, and if it runs into an error, it won’t undo something it has already done”—so it’s the application’s responsibility to recover from errors.
Errors will inevitably happen, but many software developers prefer to think only about the happy path rather than the intricacies of error handling. For example, popular object-relational mapping (ORM) frameworks such as Rails ActiveRecord and Django don’t retry aborted transactions—the error usually results in an exception bubbling up the stack, so any user input is thrown away, and the user gets an error message. This is a shame, because the whole point of rolling back transactions is to enable safe retries.
Although retrying an aborted transaction is a simple and effective error-handling mechanism, it isn’t perfect:
-
If the transaction actually succeeded, but the network was interrupted while the server tried to acknowledge the successful commit to the client (so it timed out from the client’s point of view), then retrying the transaction causes it to be performed twice unless you have an additional application-level deduplication mechanism in place.
-
If the error is due to overload or high contention between concurrent transactions, retrying the transaction will make the problem worse, not better. To avoid such feedback cycles, you can limit the number of retries, use exponential backoff, and handle overload-related errors differently from other errors (see “When an Overloaded System Won’t Recover”).
-
It is worth retrying only after transient errors (e.g., due to deadlock, isolation violation, temporary network interruptions, or failover). After a permanent error (e.g., constraint violation), a retry would be pointless.
-
If the transaction also has side effects outside of the database, those side effects may happen even if the transaction is aborted. For example, if you’re sending an email, you wouldn’t want to send the email again every time you retry the transaction. If you want to make sure that several systems either commit or abort together, two-phase commit can help (we will discuss this in “Two-Phase Commit”).
-
If the client process crashes while retrying, any data it was trying to write to the database is lost.