Your Database on ACID: Set of Rules for Data Safety
Acid might screw up your brain, but it won’t hurt your database.
Not the drug, of course. Here we’re talking about atomicity, consistency, isolation and durability (ACID). It’s a key concept in database theory, and if you’ve worked with a database in an enterprise setting, there’s a good chance you’ve heard of it.
If not, no worries. It’s easy to grasp, and that’s good, because it’s something you’ll need if running a database (or merely the network that supports an enterprise-grade database) is part of your job.
ACID refers to the way a database processes transactions. A transaction is simply a logical operation, and it’s often one that’s composed of many steps.
For instance, a common transaction in an airline database would include a passenger’s flight change. First, the passenger will be removed from his old flight, then added to his new flight. Each step will be logged to complete the transaction.
In fact, it’s far more complex — even the simplest transactions can involve a dozen steps or more.
So, how does each element of ACID apply to our hapless passenger, who’s trying to switch his flight from New York to New Zealand? Let’s take a look.
Atomicity: Simply put, atomicity means “all or nothing.” If a part of a transaction fails, all of it fails. A database with perfect atomicity won’t keep the first part of a transaction intact if the second part fails because of drive corruption, a network brownout or, frankly, any other reason. Applied, our hapless passenger won’t be deleted from his flight to New York, unless he’s also added to a flight to New Zealand. If, for some reason, he’s been deleted from his New York flight but can’t be added to New Zealand (because the flight is full, perhaps), the New York deletion will be undone — “rolled back” in database lingo.
Consistency: Every database has its own rules. For instance, the airline database might have a rule that says no passenger can change flights unless he’s already paid for his first flight. In the ACID formula, consistency is just a nice way of saying no transaction can flout the rules of the database in which it takes place. If you try to book a passenger on a flight to New Zealand when he’s not yet paid for the flight to New York (say, because his credit card was declined), the transaction will fail.
Isolation: A good transaction knows little — or even nothing — about the transactions that come before and after it or even at the same time. This is the principle of isolation: No two transactions can interfere with each other. What’s more, a transaction can’t use data from a transaction that’s in progress. Rather, it can use data only from a transaction that’s complete. As one transaction alters data, another transaction can’t see — or use — that altered data to avoid complications. Hence, other New York/New Zealand transactions won’t see your passenger after he’s been deleted from his New York flight and before he’s been added to the New Zealand flight.
Durability: Changing a database won’t do much good if the change disappears. Thus, the notion of durability: Any successful transaction should be permanent. It can’t be undone unless the user specifies it, which would entail a new transaction. No hardware error, network brownout or act of God should keep a completed transaction from staying complete. So, if you reroute that passenger from New York to New Zealand, you’ll be confident he’ll end up in Auckland and not LaGuardia or JFK.
And it’s good thing too — New York gets chilly this time of year.
David Garrett is a Web designer and former IT director, as well as the author of “Herding Chickens: Innovative Techniques in Project Management.” He can be reached at editor (at) certmag (dot) com.