ACID

Acronym for "Atomicity, Consistency, Isolation, Durability", as understood in data-management theory (which underlies engineering of database management systems DBMSs).

Accessible introductions to the value of ACID include ones from

As Donal Fellows said in comp.lang.tcl:

 The Holy Four Properties of Transactions, which are (IIRC):
  Atomic - it happens or it doesn't
  Consistent - the story doesn't change underneath your feet
  Isolated - other people can't mess you up
  Durable - once you've finished commit, it doesn't go away

 It's a shame there's not three of them, or we could make religious
 jokes.  :^)

DGP: That's not really a good definition of Consistent, even informally. Better might be: "you can't $#^&#$&^ it up." Important thing is that invalid transactions are not permitted. CL adds: the issue with consistency most often is that a particular datamanager doesn't support consistency semantically; it's not particularly that extant datamanagers are wont to behave erratically (at least in this regard). I repeat: SQL-less datamanagers often are not able to express "consistency" as powerfully as SQL can.

PS: To quote directly from Philip Greenspun's great explanation from BOOK Database Backed Web Sites:

Atomicity Results of a transaction's execution are either all committed or all rolled back. All changes take effect, or none do. Suppose that a user is editing a comment. A Web script tells the database to "copy the old comment value to an audit table and update the live table with the new text". If the hard drive fills up after the copy but before the update, the audit table insertion will be rolled back.

Consistency The database is transformed from one valid state to another valid state. A transaction is legal only if it obeys user-defined integrity constraints. Illegal transactions aren't allowed and, if an integrity constraint can't be satisfied the transaction is rolled back. For example, suppose that you define a rule that postings in a discussion forum table must be tied to a valid user ID. Then you hire Joe Novice to write some admin pages. Joe writes a delete-user page that doesn't bother to check whether or not the deletion will result in an orphaned discussion forum posting. Oracle will check, though, and abort any transaction that would result in you having a discussion forum posting by a deleted user.

Isolation The results of a transaction are invisible to other transactions until the transaction is complete. For example, suppose you have a page to show new users and their photographs. This page is coded in reliance on the publisher's directive that there will be a mugshot for every user and will present a broken image if there is not. Jane Newuser is registering at your site at the same time that Bill Olduser is viewing the new user page. The script processing Jane's registration does inserts into several tables: users, mugshots, users_demographics. This may take some time if Jane's mugshot is large. If Bill's query starts before Jane's transaction commits, Bill won't see Jane at all on his new-users page, even if Jane's insertion into some of the tables is complete.

Durability Once committed (completed), the results of a transaction are permanent and survive future system and media failures. Suppose your ecommerce system inserts an order from a customer into a database table and then instructs CyberCash to bill the customer $500. A millisecond later, before your server has heard back from CyberCash, someone trips over the machine's power cord. Oracle will not have forgotten about the new order. Furthermore, if a programmer spills coffee into a disk drive, it will be possible to install a new disk and recover the transactions up to the coffee spill, showing that you tried to bill someone for $500 and still aren't sure what happened over at CyberCash.

[Explain why and how MetaKit and MySQL don't respect ACIDity.]


PS: For an explanation about MySQL: [L3 ].

TV (19 jun 03) I remember 'atomicity' from operating system design, where it means that what happens is according to the translation of the Greek indivisible. You'd have a piece of code which claims a resource, like disc access channels, does its thing with it, like append to a file, and frees the access channel to the resource. And all of that at once, uninterruptible (literally) by other processes or routines, which could ask for a seek or other write or a read in the same time interval.

You'd probably have a two valued semaphore (Edsger Dijkstra) to 'guard' the resource access permission, for which contention may happen, and therefore deadlock can occur when circular conflicts aren't resolved.

In a standard database example like in a flight reservation system atomicity wouldn't be primary about rolling back, which is more advanced, but about indivisibility: you'll not end up without reservation but with a financial record or vice versa.

DKF: DBs are a lot more subtle in their handling of locking. One fairly simple scheme is that the DB has one write lock and multiple read locks (after all, it's not a problem to have many people reading the DB at once!) so that updates can only happen when everyone else is locked out. As strategies go, this works well if updates are rare. When updates are more frequent, more elaborate locking schemes are needed (e.g. record-level locks).