Feeling ACIDic
Posted by Daniel Lyons Thu, 06 Sep 2007 02:38:00 GMT
I have a number of rants in me that I haven’t had time to get out. But let’s talk about one of them for right now. Let’s talk about what it means to be ACID compliant.
ACID stands for Atomic, Consistent, Isolated and Durable. It refers to transaction support in some kind of data storage mechanism. According to my twisted view of open source databases, I think it became a big deal because for many years it was something PostgreSQL had that MySQL did not. Of course nowadays MySQL does have transactions and ACID compliance, assuming you put the correct non-SQL-92 conforming magic runes at the end of your table definitions, your copy of MySQL was compiled to support it, and you don’t mind losing the highly-touted screaming fast performance characteristics of MySQL’s stupider table “type.”
Each of these terms means something in particular referring to the way your system deals with transactions.
Atomic means that everything you do in your transaction either applies or it doesn’t. To the user, it means if part of what you’re doing fails, the whole thing fails. But it also means that if the whole thing succeeds and the database crashes, it either has all of the changes or none of them. All of the work in the transaction is one little bundle to the database, even if something goes wrong.
Consistent means, once you begin a transaction, your view of the data in the database will not change, even if other people are making changes at the same time. If you read the list of users at the beginning of the transaction, wait 900 years and read them again, you’ll see the same list of users, even if someone else has come along and deleted them, or the usual creation/deletion churning has occurred in the intervening time. Your view is completely static until you commit the transaction. Other people’s changes do not leak in.
Isolated means that your changes to the database cannot be perceived by other users until your whole transaction is complete. If you start a transaction and empty the users table, wait 900 years and then commit, in the intervening time, everybody else has still been able to log in and use their accounts. Your changes don’t leak out.
Durability means once you commit, your changes are permanent. Permanent in the sense of mortality. Permanent as in only a hardware failure can muddle them, and even then it cannot undo them.
There are a number of interesting things about ACID compliance. One that comes to mind is ACID compliance is not automatically inherited by systems built on ACID compliant systems. Suppose you have a database for keeping personnel data. Suppose Joe wants to give the programmers a raise and some extra vacation time. Suppose Joe goes down the list manually editing each programmer’s record in the payroll database. If the system is dumb, it will treat each change as its own transaction. If the check writing program comes along and starts running while Joe is going down the list of programmers, some of them will get checks with the raise and some will not. The check writing program is getting an inconsistent view of the data because the application wasn’t written to use the database in a way that guaranteed isolation.
