Database: Transactions: Acid Compliance

The following is taken from PostgreSQL vs. MySQL [webtechniques.com, Sept 2001]. I've reproduced only the section on ACID, leaving out the comparison of PostgreSQL and MySQL, which is somewhat dated.ACID (an acronymn for Atomicity Consistency Isolation Durability) is a 'keyword' that business professionals generally look for when evaluating databases. Here is a quick description of what itmeans to be ACID compliant:

Atomicity is an all-or-none proposition. Suppose you define a transaction that contains an UPDATE, an INSERT, and a DELETE statement. With atomicity, these statements aretreated as a single unit, and thanks to consistency (the C in ACID) there are only two possible outcomes: either they all change the database or none of them do. This is important in situations likebank transactions where transferring money between accounts could result in disaster if the server were to go down after a DELETE statement but before the corresponding INSERT statement.

Consistencyguarantees that a transaction never leaves your database in a half-finished state. If one part of the transaction fails, all of the pending changes are rolled back, leaving the database as it wasbefore you initiated the transaction. For instance, when you delete a customer record, you should also delete all of that customer's records from associated tables (such as invoices and line items). Aproperly configured database wouldn't let you delete the customer record, if that meant leaving its invoices, and other associated records stranded.

Isolation keeps transactions separated from eachother until they're finished. Transaction isolation is generally configurable in a variety of modes. For example, in one mode, a transaction blocks until the other transaction finishes. In a differentmode, a transaction sees obsolete data (from the state the database was in before the previous transaction started). Suppose a user deletes a customer, and before the customer's invoices are...