ACID Properties

ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved.

Atomicity: Atomicity is an all-or-none proposition, states that database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails.

When an update occurs to a database, either all or none of the update becomes available to anyone beyond the user or application performing the update. This update to the database is called a transaction and it either commits or aborts. This means that only a fragment of the update cannot be placed into the database, should a problem occur with either the hardware or the software involved. Features to consider for atomicity:

• a transaction is a unit of operation - either all the transaction's actions are completed or none are
• atomicity is maintained in the presence of deadlocks
• atomicity is maintained in the presence of database software failures
• atomicity is maintained in the presence of application software failures
• atomicity is maintained in the presence of CPU failures
• atomicity is maintained in the presence of disk failures
• atomicity can be turned off at the system level
• atomicity can be turned off at the session level

Consistency: Consistency guarantees that a transaction never leaves your database in a half-finished state means A transaction either creates a new and valid state of data, or, if any failure occurs, returns all data to its state before the transaction was started.

Isolation: Isolation keeps transactions separated from each other until they’re finished means that multiple transactions occurring at the same time not impact each other’s execution. For example, if ‘A’ issues a transaction against a database at the same time that ‘B’ issues a different transaction, both transactions should operate on the database in an isolated manner. The database should either perform ‘A’’s entire transaction before executing ‘B’’s or vice-versa. This prevents ‘A’’s transaction from reading intermediate data produced as a side effect of part of ‘B’’s transaction that will not eventually be committed to the database. Note that the isolation property does not ensure which transaction will execute first, merely that they will not interfere with each other.

Durability: Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination means committed data is saved by the system such that, even in the event of a failure and system restart, the data is available in its correct state.

Maintaining updates of committed transactions is critical. These updates must never be lost. The ACID property of durability addresses this need. Durability refers to the ability of the system to recover committed transaction updates if either the system or the storage media fails. Features to consider for durability:

• recovery to the most recent successful commit after a database software failure
• recovery to the most recent successful commit after an application software failure
• recovery to the most recent successful commit after a CPU failure
• recovery to the most recent successful backup after a disk failure
• recovery to the most recent successful commit after a data disk failure 

Above four rules are very important for any developers dealing with databases.