Passing the ACID Test: A Visual Dictionary of Database Transactions - What do Atomicity, Consistency, Isolation, and Durability Actually Look Like?

a10d4a16955c61b8a8d255fc575f86f6The 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

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.

48a4ed749e2ce8c26c2389674bac3acbWhacking the nail is a step. Driving it in is a transaction.

PART 2: DEFINING ACID PROPERTIES

Atomicity

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.

Pez candy that reads Not Labeled for Individual SaleYou either buy the whole pack, or none at all.

Consistency

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.

Roper on horseback trying to lasso calfThe 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.

Isolation

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.

Policeman directing trafficThis Dudley Do-Right is preventing collisions among concurrent transactions.

Durability

Probably the most straightforward ACID property: once a database transaction is committed, it must not be lost, even in the case of system failure.

Post Malone smiling with face tattoosRest 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:

Concurrency Control

A set of theories and practices for ensuring that database transactions performed concurrently do not violate data integrity… in other words, for ensuring isolation.

Runners waiting in line to use Porta PottiesThe individuals inside the Porta Potties are performing their transactions concurrently. The replication and locking of Porta Potties ensure they do so in isolation.

Locking

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.

Screen Limit message on Netflix appShameless indeed.

Mutual Exclusion

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

Everyone grabbing slices of pizza at the same timeA violation of said requirement.

Serializability

Concurrent transactions demonstrate serializability if their outcome is the same as if they were executed sequentially.

Cersei Lannister from Game of ThronesWhether you watched Game of Thrones over 8 years or all at once, it pretty much turns out the same.

Recoverability

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.

Egg being cracked into a bowlYou can't restore an ACID-compliant database without unbreaking a few eggs.

Synchronization

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.

Lines of identical robots dancing in unisonNo one feels they have to be an individual here.

CONCLUSION

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:

Back to Blog

Related Articles

Codesmith: A Good Decision

“I needed options, and I needed them immediately.” Covid-19 had wreaked havoc in some form on so...

What are Hooks?

A “Hook” is one of those software engineering terms without a clear or precise definition. TechTalk...

Snapshots of Culture at Codesmith: A Behind the Scenes Look at the Daily Activities that Define Codesmith Culture

When I started Codesmith six weeks ago, I expected to learn to be a software engineer – but I’m...