Wednesday, 28 March 2012

ACID property

In this article I am trying to describe the ACID property of the database in a short and simple manner. Hope you like it.
The AICD stands for
1.    Atomicity
2.    Consistency
3.    Isolation
4.    Durability

Let's describe each to understand it properly.

Atomicity

Modification of the data in the database, either failure or success. The begin of the such modifications starts with BEGIN TRANSACTION and ends with COMMIT TRANSACTION or ROLLBACK TRANSACTION.

BEGIN
  BEGIN TRY
        BEGIN TRANSACTION
        <..........>
        COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
        <..........>
        ROLLBACK TRANSACTION
  END CATCH
END

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

Only the valid data according to integrity constraints may be committed.

Isolation

One transaction does not interact with others.
  
Durability

When a transaction is committed the data will not be lost.

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 


Posted by: MR. JOYDEEP DAS


2 comments:

  1. The concept of transaction is based on ACID properties as have been nicely described here. However transactions have to conform to certain isolation levels and concurrency models and explanation of which may be found here : http://crazy4db.blogspot.in/2012/03/transaction-concurrency.html
    suggestion for those who would like to read it further.

    ReplyDelete
    Replies
    1. Thanks "Prashant",
      I definitely read your article by tonight, if I get some time.

      Delete