What is ACID Testing ?

One of the primary tests for reliability of a database management system (DBMS) is the ACID test. ACID-compliant systems possess certain properties that offer greater protection to stored data in the event of an unexpected hardware or software failure; even if the database is being read from or written to at the time the failure occurs.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.

Atomicity

When a transaction that updates the database occurs, either all of the update occurs, or none of the update occurs, even if a hardware or software failure occurs during the transaction.

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.

Consistency
Any change to the value of an instance is consistent with all other changes to other values of that instance.

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
Isolation prevents changes in concurrent transactions from conflicting with each other. Isolation also allows multiple users to each use the database as if he or she were the only user.

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.

Durability
When a hardware or software failure occurs, the information in the database must be accurate up to the last committed transaction before the failure.

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 valid 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.