The following examples further illustrate the ACID properties. In these examples, the database table has two columns, A and B. 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 Atomicity is the guarantee that series of database operations in an atomic transaction will either all occur (a successful operation), or none will occur (an unsuccessful operation). The series of operations cannot be separated with only some of them being executed, which makes the series of operations "indivisible". A guarantee of atomicity prevents updates to the database from occurring only partially, which can cause greater problems than rejecting the whole series outright. In other words, atomicity means indivisibility and irreducibility. Alternatively, we may say that a logical transaction may be composed of several physical transactions. Unless and until all component physical transactions are executed, the logical transaction will not have occurred. An example of an atomic transaction is a monetary transfer from bank account A to account B. It consists of two operations, withdrawing the money from account A and depositing it to account B. We would not want to see the amount removed from account A before we are sure it has also been transferred into account B. Performing these operations in an atomic transaction ensures that the database remains in a
consistent state, that is, money is neither debited nor credited if either of those two operations fails.
Consistency failure Consistency is a very general term, which demands that the data must meet all validation rules. In the previous example, the validation is a requirement that . All validation rules must be checked to ensure consistency. Assume that a transaction attempts to subtract 10 from without altering . Because consistency is checked after each transaction, it is known that before the transaction begins. If the transaction removes 10 from successfully, atomicity will be achieved. However, a validation check will show that , which is inconsistent with the rules of the database. The entire transaction must be canceled and the affected rows rolled back to their pre-transaction state. If there had been other constraints, triggers, or cascades, every single change operation would have been checked in the same way as above before the transaction was committed. Similar issues may arise with other constraints. We may have required the data types of both and to be integers. If we were then to enter, say, the value 13.5 for , the transaction will be canceled, or the system may give rise to an alert in the form of a trigger (if/when the trigger has been written to this effect). Another example would be integrity constraints, which would not allow us to delete a row in one table whose primary key is referred to by at least one
foreign key in other tables.
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: • T1 transfers 10 from A to B. • T2 transfers 20 from B to A. Combined, there are four actions: • T1 subtracts 10 from A. • T1 adds 10 to B. • T2 subtracts 20 from B. • T2 adds 20 to A. If these operations are performed in order, isolation is maintained, although T2 must wait. Consider what happens if T1 fails halfway through. The database eliminates T1's effects, and T2 sees only valid data. By interleaving the transactions, the actual order of actions might be: • T1 subtracts 10 from A. • T2 subtracts 20 from B. • T2 adds 20 to A. • T1 adds 10 to B. Again, consider what happens if T1 fails while modifying B in Step 4. By the time T1 fails, T2 has already modified A; it cannot be restored to the value it had before T1 without leaving an invalid database. This is known as a
write-write contention, because two transactions attempted to write to the same data field. In a typical system, the problem would be resolved by reverting to the last known good state, canceling the failed transaction T1, and restarting the interrupted transaction T2 from the good state.
Durability failure Consider a transaction that transfers 10 from A to B. First, it removes 10 from A, then it adds 10 to B. At this point, the user is told the transaction was a success. However, the changes are still queued in the
disk buffer waiting to be committed to disk. Power fails and the changes are lost, but the user assumes (understandably) that the changes persist. == Implementation ==