Before discussing about ACID properties, I would like to explain about transaction
What is Transaction?
“A transaction is a logical unit of work, that contains one or more SQL statements”
A database transaction must be Atomic, Consistent, Isolated and Durable
Above four properties of transaction acronym as ACID
ACID Properties:
- Atomicity: When a Database processes a transaction, either its fully completed or not executed completely
- Consistency: Any data written to Database must be valid according to rules defined including constraints
- Isolation: Modifications of data performed by a transaction must be secure and independent of another transaction. But, it does not ensure the order of transactions.
For example:
user A withdraws Rs.500 and user B withdraws Rs.400 from user C’s account, which has a balance of Rs.1500. Since both A and B draw from C’s account, one of the users is required to wait until the other user transaction is completed, avoiding inconsistent data.
If B is required to wait, then B must wait until A’s transaction is completed, and C’s account balance changes to Rs.1000. Now, B can withdraw Rs.400 from this Rs.1000 balance
4. Durability: The database should be durable enough to hold all its latest updates even if the system fails or restarts.
In the above example, user B may withdraw Rs.400 only after user A’s transaction is completed and is updated in the database. If the system fails before A’s transaction is logged in the database,
A cannot withdraw any money and C’s account returns to its previous consistent state (Rs. 1500).
Online Transaction Processing(OLTP) supports ACID properties.
Online Analytical Processing(OLAP) does not support ACID properties.
Interview Questions:
- What is a database transaction?
- What are ACID properties?
Next, We’re going to discuss about “Normalization” [ https://medium.com/@pk0411/what-is-normalization-a4d2401ffd9d ]