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 it means 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 are treated 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 like bank 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.
- Consistency guarantees 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 was before 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). A
properly 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 each other 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 different mode, 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 deleted, a second user
updates one of those invoices. In a blocking transaction scenario, the second
user would have to wait for the first user's deletions to complete before
issuing the update. The second user would then find out that the customer had
been deleted, which is much better than losing changes without knowing about it.
- Durability guarantees that the database will keep track of pending
changes in such a way that the server can recover from an abnormal termination.
Hence, even if the database server is unplugged in the middle of a transaction,
it will return to a consistent state when it's restarted. The database handles
this by storing uncommitted transactions in a transaction log. By virtue of
consistency (explained above), a partially completed transaction won't be
written to the database in the event of an abnormal termination. However, when
the database is restarted after such a termination, it examines the transaction
log for completed transactions that had not been committed, and applies them.