Database ACID Properties

From WIKI..

In computer science, ACID (atomicity, consistency, isolation, durability) is a set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction. For example, a transfer of funds from one bank account to another, even though that might involve multiple changes (such as debiting one account and crediting another), is a single transaction.

Characteristics

Atomicity

Main article: Atomicity (database systems)

Atomicity requires that each transaction is "all or nothing": if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes.

Consistency

Main article: Consistency (database systems)

The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including but not limited to constraints, cascades, triggers, and any combination thereof.

Isolation

Main article: Isolation (database systems)

The isolation property ensures that the concurrent execution of transactions results in a system state that could have been obtained if transactions are executed serially, i.e. one after the other.^{[citation needed]}

Durability

Main article: Durability (database systems)

Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter).

Examples

The following examples are used to further explain the ACID properties. In these examples, the database has two fields, A and B, in two records. An integrity constraint requires that the value in A and the value in B must sum to 100. The following SQL code creates a table as described above:

CREATE TABLE acidtest (A INTEGER, B INTEGER CHECK (A + B = 100));

Atomicity failure

Assume that a transaction attempts to subtract 10 from A and add 10 to B. This is a valid transaction, since the data continue to satisfy the constraint after it has executed. However, assume that after removing 10 from A, the transaction is unable to modify B. If the database retained A's new value, atomicity and the constraint would both be violated. Atomicity requires that both parts of this transaction, or neither, be complete.

Consistency failure

Consistency is a very general term that demands the data meets all validation rules. In the previous example, the validation is a requirement that A + B = 100. Also, it may be inferred that both A and B must be integers. A valid range for A and B may also be inferred. All validation rules must be checked to ensure consistency.
Assume that a transaction attempts to subtract 10 from A without altering B. Because consistency is checked after each transaction, it is known that A + B = 100 before the transaction begins. If the transaction removes 10 from A successfully, atomicity will be achieved. However, a validation check will show that A + B = 90. That is not consistent according to the rules of the database. The entire transaction must be cancelled and the affected rows rolled back to their pre-transaction state.
In reality, this goes much further than simply A and B, as it implies every single cascade or trigger chain related to the events in the transaction, and thus every check on all of the indirectly impacted values as well. If there had been other constraints, triggers or cascades, every single change operation would have been checked in the same way as the above before the transaction was committed.

Isolation failure

To demonstrate isolation, we assume two transactions execute at the same time, each attempting to modify the same data. One of the two must wait until the other completes in order to maintain isolation.
Consider two transactions. T₁ transfers 10 from A to B. T₂ transfers 10 from B to A. Combined, there are four actions:

• subtract 10 from A
• add 10 to B.
• subtract 10 from B
• add 10 to A.

If these operations are performed in order, isolation is maintained, although T₂ must wait. Consider what happens if T₁ fails half-way through. The database eliminates T₁'s effects, and T₂ sees only valid data.
By interleaving the transactions, the actual order of actions might be: , , , . Again, consider what happens if T₁ fails. T₁ still subtracts 10 from A. Now, T₂ adds 10 to A restoring it to its initial value. Now T₁ fails. What should A's value be? T₂ has already changed it. Also, T₁ never changed B. T₂ subtracts 10 from it. If T₂ is allowed to complete, B's value will be 10 too low, and A's value will be unchanged, leaving an invalid database. This is known as a write-write failure, because two transactions attempted to write to the same data field.

Durability failure

Assume that a transaction transfers 10 from A to B. It removes 10 from A. It then adds 10 to B. At this point, a "success" message is sent to the user. However, the changes are still queued in the disk buffer waiting to be committed to the disk. Power fails and the changes are lost. The user assumes (validly) that the changes have been made