ACID Testing

ACID stands for :

Atomicity
Consistency
Isolation
Durability

For OLTP databases and when databases deal with mission-critical business transactions and critical information ACID features are much need for reliability & stability. For customers that demand high quality of databases to maintain confidentiality of their information, ACID features is what you need.

Atomicity:-
Atomicity describes database operations that are defined within a single transactions of nature “all or nothing”. If any single operation fails then the whole transaction fails. This ensures that the database is in a valid state at all times.

For instance, a transaction to transfer money from one bank account to another involves withdrawal & deposit to two different account. If deposit fails, withdrawal should also be reversed, else money will be lost. So these operations should be grouped into single atomic transaction to ensure data integrity. Databases provide atomicity in two ways. 1. Rollback feature, 2. Two phase commit protocol.

Consistency:-
Consistency of data is maintained by allowing only valid transactions that comply to preset database constraints. Some constraints are:
1. Unique constraint on a column - For eg: Only one SSN can be allowed per person record. if a duplicate SSN appears then that transaction should be rejected.
2. Rules: Some rules check if data is in specified format. Eg: Salary should always be numeric and alphabets should be rejected.

Many other features of database help ensure data consistency.

Isolation:-
This concurrent transactions don't overlap each other. Every transaction occurs before or after all other transactions. If huge number of concurrent transactions are expected, then a robust database system is needed which has guaranteed isolation. Databases use Locking mechanism to implement Isolation. The row, table or entire database can be locked when a particular transaction takes place. Although this ensures isolation it poses issues with response times.

Durability:-
A committed or completed transaction should persist regardless of failures of any kind. Eg : Hard disk crash, network unavailability should not affect the saved data in database. Durability is often achieved via
1. Database replication or
2. Load balanced servers where two servers simultaneously run on separate machines. Backups alone cannot provide "durability" of all transactions.