ACID properties

A transaction is understood as a unit of program execution that accesses and possibly updates various data items. A transaction can be considered as an action or a series of actions carried out by a single user or an application program which reads or updates the contents of a database. A transaction must see a consistent database. During the execution of the transaction, the database may be temporarily inconsistent. When a transaction completes successfully, the database must be consistent. After a transaction commits the changes it has made to the database persist even if there are system failures. Multiple transactions can execute in parallel. There are two main issues to deal with and they are:

• Various kinds of failures such as hardware failures and system crash.
• The concurrent execution of multiple transactions.

ACID properties

To preserve the data integrity, database system must ensure:

• Atomicity: In atomicity, either all operations of the transaction are properly reflected in the database or none are. The transaction is indivisible as it completes the action entirely or not at all despite the failures.
  
  
• Consistency: Execution of a transaction in isolation preserves the consistency of the database. Consistency transfers the database from one consistent state to another consistent state.
  
  
• Isolation: Although multiple transactions may execute concurrently, each transaction must not be aware of other concurrently executing transactions. The intermediate transaction results must be hidden from other concurrently executed transactions. That is, for every pair of transactions T_i and T_j, it appears to T_iin a way that either T_j finished execution before T_i started or T_j started execution after T_i finished. However, the effects of a transaction are not visible to other transactions until it has completed.
  
  
• Durability: After a transaction completes successfully the changes it has made to the database persist even if there are system failures.

It is the responsibility of the applications and the database management system to maintain the constraints. Transaction to transfer 50 from account A to account B is given below:

1. read(A)
2. A:= A - 50
3. write(A)
4. read(B)
5. B:= B + 50
6. write(B)

Atomicity requirement: If the transaction fails after the third step and before the sixth step then the system should ensure that its updates are not reflected in the database otherwise an inconsistency will result.

Consistency requirement: The sum of A and B is unchanged by the execution of the transaction.

Isolation requirement: If between third step and sixth step, another transaction is allowed to access the partially updated database, it will see an inconsistent database that is the sum of A+B will be less than it should be. Isolation can be ensured with the little effort by running transactions serially which means one after the other. However executing multiple transactions concurrently has significant benefits.

Durability requirement: Once the user has been notified that the transaction has completed that is the transfer of the 50 has taken place, the updates to the database by the transaction must persist despite failures.

Transaction State

Active - It is the initial state. The transaction stays in this state while it is executing.

Partially committed - It is the state after the final statement has been executed.

Failed - After the discovery that normal execution can no longer proceed.

Aborted - It is the state after the transaction has been rolled back and the database has been restored to its state prior to the start of the transaction. Two options after it has been aborted are:

• Restart the transaction and it can be done only if there is no internal logical error.
• Kill the transaction

Committed - It is the state after successful completion.

References:

1. H.F.Korth and A. Silberschatz,"Database system concepts",McGraw Hill,2010
2. A.K.Majumdar and p, Bhatt acharya,"Database Management Systems",Tata McGraw Hill,India,2004
3. F.Korth, Henry. Database System Concepts. 6th edition.