What is ACID ?


Consider Following ATM Transaction Diagram


Identify steps of

• Transaction start
  
• Data Validation
  
• Data Verification
  
• Data Update
  
• Transaction end 
  
  
  
  Atomicity
  A transaction is totally complete or not complete. (committed or rolled back)
  Consider an ATM transaction. The Transaction is complete only when the customer receives money and the database is updated.
  

What is your solution to above problem.

Above transaction has two steps of Charging and Inventory update. When the second fails the system will have incorrect data. The database needs to handle this by rolling back, re-try, or canceling the transaction.


Consistency.
A transaction creates a new and valid state of data. In case of a failure, returns all data to its former state before the transaction. A record is updated only when data is complete and valid.

Consistency and Randomness

Isolation.
A transaction in process and not yet committed must remain isolated from any other transaction. A user accessing a bank account with two devices (2 logins) can do two transactions, but only one updates the balance at one time, other request stays in a que.

Durability.
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. If a data communications failure occurs during an ATM transaction, the data is rolled back to an earlier state.

TRANSACTION ISOLATION more ...

1. Serializable: The highest level of isolation. It locks data exclusively when read and write occurs. It acquires range locks so that phantom rows are not created.
   
   
2. Repeatable Read: Second highest level of isolation. Same as serializable except it does not acquire range locks so phantom rows may be created.
   
   
3. Read Committed: It allow shared locks and reads only committed data. That means never reading changed data that is in the middle of any transaction.
   
   
4. Read Un-Committed: It is the lowest level of Isolation. It allows dirty reading.