The Mount Rushmore of Guaranteed Data Validity
For many developers, our vocabulary for communicating with databases is fairly limited. This is, of course, by design; straightforward declarative queries allow us to abstract away the intricacies of database transactions. But in certain situations (like the high stakes of financial transactions), abstractions can feel like a liability, creating a dangerous gap between our own understanding and the concrete mechanics of the technology.
We're going to attempt to narrow that gap by exploring—in a series of visual dictionary definitions—what it means for a database to be ACID-compliant. We'll begin by clarifying what we mean by a database transaction, then define the four ACID properties—atomicity, consistency, isolation, and durability—before taking a peek at some of the related concepts that support them. And we'll do so with an eye towards expanding our vocabulary: by defining terms in pictures, we can make the conceptual more real.
Once we can see atomicity, consistency, isolation, and durability, we can properly enshrine them as a database transaction's sacred rules, thereby carving their four faces into our Mount Rushmore of Guaranteed Data Validity.
PART 1: DEFINING A DATABASE TRANSACTION
A database transaction is a logical unit of work on a database. For example, an account transfer may involve multiple steps (debiting from one account and crediting to another), but they can be properly thought of as comprising a single event.
Whacking the nail is a step. Driving it in is a transaction.
PART 2: DEFINING ACID PROPERTIES
Remember how we used to think that atoms were the fundamental building blocks of matter and couldn’t be broken down into smaller particles? This property is the same idea: though a database transaction may consist of multiple statements, it must be treated as a single, unitary operation that either occurs in whole or not at all. Were we not to treat it as such, a database transaction that did not successfully complete (due to some logical or syntax error, or even a hardware failure like an interruption in power supply) could easily yield invalid data.
You either buy the whole pack, or none at all.
Database transactions must abide by all rules and constraints (for example, the type or range of data may need to be validated). If a transaction contradicts them (resulting in invalid data), the transaction is rolled back, restoring the database to a state consistent with those rules.
The calf has violated the constraints of the corral; the roper is in the process of rolling it back, thereby restoring the corral to a state consistent with its rules.
Concurrent database transactions are processed without interference from each other. This ensures the state of the database remains consistent by preventing one transaction from accessing intermediate (and therefore, inconsistent) data belonging to another transaction.
This Dudley Do-Right is preventing collisions among concurrent transactions.
Probably the most straightforward ACID property: once a database transaction is committed, it must not be lost, even in the case of system failure.
Rest assured: the database transactions on this young man’s face have been committed and are now, for all intents and purposes, permanent.
PART 3: DEFINING RELATED CONCEPTS
The six concepts that follow represent a sliver of an introduction to the mechanics of ACID compliance.
Consider them a jumping-off point:
A set of theories and practices for ensuring that database transactions performed concurrently do not violate data integrity… in other words, for ensuring isolation.
The individuals inside the Porta Potties are performing their transactions concurrently. The replication and locking of Porta Potties ensure they do so in isolation.
The prevention of simultaneous access to data for the purpose of concurrency control. For example, the updating of an account would lock its corresponding row in a database table, preventing simultaneous transactions from modifying that row until the updating transaction completed and the lock was released.
A requirement of concurrency control that multiple threads of execution do not access a shared resource at the same time (i.e. trying to write data to the same address in memory).
A violation of said requirement.
Concurrent transactions demonstrate serializability if their outcome is the same as if they were executed sequentially.
Whether you watched Game of Thrones over 8 years or all at once, it pretty much turns out the same.
Aborted transactions do not harm the integrity of committed transactions. The committed transactions can “recover” after having read data from those aborted transactions. Recoverability is a prerequisite for consistency; without a way to roll back invalid data, it’s impossible to restore a database to its previously valid state.
You can't restore an ACID-compliant database without unbreaking a few eggs.
Maintaining coherence among multiple copies of a dataset. This is what you are striving for when you decide to back up your files on an external hard drive more frequently. On a grander scale, caching is another helpful demonstration of the necessity of synchronization; since a cache is a copy of underlying data, it is critical that it remains synchronized with its database so as to avoid inconsistencies.
No one feels they have to be an individual here.
Information that is invalid is often more dangerous than no information at all. Which is to say, databases are generally useful only insofar as they can be trusted. It should be mentioned, however, that in circumstances where data integrity is NOT critical, speed or scaling requirements may dictate the use of databases that are not ACID-compliant. But when data integrity is critical, without principles to ensure trust, without a recognized standard for guaranteeing data validity, any systems that rely upon data being accurate are like buildings without foundations, liable to topple at the slightest disturbance.
So hopefully, this visual dictionary has made database transactions a bit less abstract. And if it’s been an introduction to a few of the theories and practices that make ACID-compliance possible, all the better. But if, after looking at all these pictures, you’ve decided that you prefer words, here are a few places where you can find more of them: