In general, an ACID-compliant DBMS is greatly preferred to a non-complaint DBMS. In applications where the availability and integrity of the stored data are critical, an ACID-compliant database is required; non-compliant systems should be automatically rejected.
The ACID test alone does not guarantee reliability. Other factors such as the reliability of the host environment (both hardware and software components), a strictly observed backup policy, etc. are also crucial in maintaining any DBMS.
The four ACID properties are atomicity, consistency, isolation and durability.
For example, suppose that a particular transaction is supposed to update a record consisting of ten fields of data (name, gender, age, etc.) into a customer database. Further suppose that an unexpected software failure occurs halfway through the transaction. If the DBMS is not atomic, when the database comes back online, the record will be in an unknown state: All, some or none of the fields in the record may have been updated. Therefore, a future transaction that depends on the record may be relying upon incorrect information. In contrast, an atomic DBMS in the same situation would void the already completed parts of the transaction and return to the state before the transaction was attempted.
The mostly widely used mechanism for providing atomicity is the transactional commit/rollback mechanism. A group of write operations in a transactions are attempted. If all of the writes succeed, then the writes are committed to the database; that is, the writes are made permanent. If any of the writes failed, then the database is rolled back to the point before the transaction was started.
For example, suppose a student checks out a 2N222 transistor. The 2N2222 must be charged against the student's account, the number of 2N2222 transistors available must be decreased by one and the number of 2N2222 transistors in use must be increased by one. If all of these changes do not occur, the database is in an inconsistent state.
An ACID-complaint DBMS provides the tools to enforce consistency, usually in the form of rules checking. However, it is up to the designer to implement consistency enforcement.
Isolation is primarily accomplished through locking. To lock a table or record prevents other transactions from reading or writing the data in that table or record until the current transaction is finished. This process ensures that no transaction reads data which is no longer valid.
Locking a record is preferable to locking an entire table. Pending transactions on a locked table must wait for the entire table to be unlocked, even if only one record in the table is being updated. In contrast, with record-level locking, only transactions that depend upon the locked record must wait; other transactions can proceed without waiting.
This durability is required even if the failure causes the operating system to crash or the server to shut down. The only exception is a hard disk failure, at which point the database is vaild up to the last successful backup made before the failure.
All durable database management systems are atomic, but not all atomic database management systems are durable.