ACID Properties in DBMS Explained with Simple Examples
When working with databases, we often perform operations like inserting, updating, deleting, or transferring data. These operations are called transactions. Imagine transferring ₹1,000 from one bank account to…
When working with databases, we often perform operations like inserting, updating, deleting, or transferring data. These operations are called transactions.
Imagine transferring ₹1,000 from one bank account to another. If the amount is deducted from the sender’s account but not credited to the receiver due to a system crash, the database becomes inconsistent.
To prevent such situations, databases follow the ACID properties.
ACID stands for:
- A – Atomicity
- C – Consistency
- I – Isolation
- D – Durability
These four properties ensure that transactions are reliable, accurate, and safe, even in the event of failures.
ACID = Atomicity + Consistency + Isolation + Durability
What is a Database Transaction?
A transaction is a group of one or more database operations treated as a single unit of work.
For example, a bank transfer involves two operations:
- Debit money from Account A.
- Credit money to Account B.
Both operations must succeed together. If one fails, the other should also fail.
1. Atomicity (All or Nothing)
Atomicity means a transaction is treated as a single indivisible unit.
Either all operations are completed successfully, or none of them are applied.
If any operation fails, the entire transaction is rolled back.
Example
Suppose Prakash transfers ₹1,000 from Account A to Account B.
Transaction Steps
Step 1: Debit ₹1,000 from Account A
Step 2: Credit ₹1,000 to Account B
Successful Transaction
Account A : ₹5,000 → ₹4,000
Account B : ₹3,000 → ₹4,000
Everything works correctly.
Failure Scenario
Debit completed ✔
Credit failed ❌
Without Atomicity:
Account A : ₹4,000
Account B : ₹3,000
₹1,000 disappears.
With Atomicity:
The database rolls back the debit operation.
Account A : ₹5,000
Account B : ₹3,000
No partial changes are saved.
Key Point
Either the entire transaction succeeds or nothing happens.
Keyword: Rollback
2. Consistency (Valid State)
Consistency ensures that every transaction follows all database rules, constraints, and business logic.
A transaction moves the database from one valid state to another valid state.
If any rule is violated, the transaction is rejected or rolled back.
Example 1: NOT NULL Constraint
Suppose we have the following table:
CREATE TABLE Employee (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
Now execute:
INSERT INTO Employee(id, name)
VALUES (101, NULL);
Since the name column cannot contain NULL, the transaction fails.
The database rejects the insert operation.
No invalid data is stored.
Example 2: Bank Transfer
Before Transaction
Account A = ₹5,000
Account B = ₹3,000
Total = ₹8,000
After Successful Transaction
Account A = ₹4,000
Account B = ₹4,000
Total = ₹8,000
The total amount remains the same.
The database stays valid.
If somehow the debit succeeds but the credit fails,
Account A = ₹4,000
Account B = ₹3,000
Total = ₹7,000
This is an invalid state.
The transaction is rolled back.
Key Point
Consistency ensures that every transaction follows database rules and never leaves the database in an invalid state.
Keyword: Data Integrity
3. Isolation (No Interference)
Isolation ensures that multiple transactions running at the same time do not interfere with each other.
Each transaction behaves as if it is running alone.
Users cannot see another transaction’s uncommitted changes.
Example
Suppose two users try to withdraw money simultaneously.
Initial Balance = ₹10,000
User 1
Withdraws ₹2,000.
User 2
Withdraws ₹5,000.
Without Isolation:
Both users may read the same balance simultaneously, leading to incorrect calculations or lost updates.
With Isolation:
The database controls concurrent access so that transactions execute safely without corrupting the data.
Key Point
One transaction should not affect another transaction until it is committed.
Keyword: Concurrency
4. Durability (Permanent Changes)
Durability guarantees that once a transaction is committed, the changes become permanent.
Even if the database server crashes immediately after committing the transaction, the committed data is not lost.
Example
Suppose you successfully pay your electricity bill online.
Payment Status = Successful
Immediately after payment, the server crashes.
When the database restarts,
Payment Status = Successful
The transaction is still available.
This is because the database permanently saves committed transactions.
Key Point
Once committed, data remains permanent even after crashes or power failures.
Keyword: Persistence
ACID Summary Table
| Property | Meaning | Example | Keyword |
|---|---|---|---|
| Atomicity | All operations succeed or none | Bank transfer rollback | Rollback |
| Consistency | Database always remains valid | Constraints are enforced | Data Integrity |
| Isolation | Concurrent transactions do not interfere | Two users updating the same record | Concurrency |
| Durability | Committed data is never lost | Server crash after payment | Persistence |
Easy Mnemonic
Remember ACID as:
- A → All or Nothing
- C → Correct Data
- I → Independent Transactions
- D → Data Stays Forever
Real-World Example
Consider transferring ₹1,000 from one bank account to another.
Atomicity
Both debit and credit happen together.
Consistency
The total amount of money remains unchanged.
Isolation
Other users cannot see intermediate transaction states.
Durability
Once the transfer is successful, it remains saved permanently—even if the server crashes immediately afterward.
Why Are ACID Properties Important?
Without ACID:
- Partial transactions could occur.
- Invalid data could be stored.
- Concurrent users could overwrite each other’s changes.
- Committed data could be lost after crashes.
With ACID:
- Transactions are reliable.
- Data integrity is maintained.
- Concurrent access is handled safely.
- Committed data is permanent.
Interview Definition
ACID stands for Atomicity, Consistency, Isolation, and Durability. These four properties ensure that database transactions are executed reliably, maintain data integrity, handle concurrent operations safely, and preserve committed data even in the event of system failures.
Conclusion
ACID properties form the foundation of every reliable relational database system. Whether you’re building a banking application, an e-commerce platform, or any enterprise application using Spring Boot and MySQL, understanding ACID is essential.
- Atomicity ensures all operations succeed together.
- Consistency keeps the database in a valid state.
- Isolation prevents concurrent transactions from interfering.
- Durability guarantees that committed data is never lost.
Mastering these concepts will not only help you design robust applications but also prepare you for technical interviews, where ACID is one of the most frequently asked database topics.
