Know What ACID means For a SQL Database

In most of the interviews, candidates are unable to explain what an ACID property means w.r.t a SQL database like mysql, oracle, postgresql etc and SQL being one the game changing technology which is still popular and very much used, it is utmost important to this feature.

Every Relational Database available on market must or should have this feature. When compared with NoSQL Databases these feature distinguishes them. NoSQL databases like MongoDB, Cassandra etc have BASE instead os ACID. To know more on that click the link here.

In a very simple and easy terms let us dissect each one of those.

• Atomicity - For every transaction made to a database directly or via an application should either get successful (commit) or failure (abort), there is no middle state.

For example, say User A wants to transfer some amount to User B. In a successful situation, amount gets debited from A and credited to B. So the corresponding table should have the amount deducted from A's balance and added to B's balance.

In a failure situation, the amount should not get debited from A's account and is not credit to B's account.

If the above condition where not in place (no atomicity), then in a real world User A and User B would be unhappy and they would file a case against the bank as the data got debited from A's account and did not get credited to B's account.

• Consistency - The data after and before transaction should be consistent (make sense).

For example, say in the above case A's account had 200 balance and B's account had 200 balance. Now A makes a transaction of 100, so amount 100 must be debited from A and credit to B.

This means that the overall data is consistent before transaction together it was 200 + 200 = 400 and after transaction it is 100 + 300 = 400. This make sense!

• Isolation - Each transaction to a database must be independent of each other and should only be visible once the transaction is either committed or rollbacked.

For example, say in the above case when User A makes a transaction 100 to B and goes and checks his balance he should be seeing 200. Once the first transaction is complete then user A should being seeing 100 as his balance. So both transaction are independent, and each one can see other only after data is persisted or rollbacked.

• Durability - The data that gets persisted to database must be always available even during system failure situation.

This can be achieved in various ways few of which can be, data is saved to a NAS (Network attached storage) or data in disk replicated to multiple disk or regular backup in specified interval etc can help in achieving durability.