🧠 Introduction
In relational databases, ACID stands for:
Atomicity
Consistency
Isolation
Durability
These four properties guarantee reliable transactions — ensuring that your data remains accurate even in case of errors or crashes.
In this tutorial, we’ll demonstrate each ACID property step by step using SQL commands.
🏗️ Step 1: Create a Sample Table
We’ll create an Accounts table and insert sample records.
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT CHECK (balance >= 0)
);
📸 Screenshot: Accounts table created successfully.
🧮 Insert Sample Data
INSERT INTO Accounts VALUES
(101, 'Arun Kumar', 50000),
(102, 'Meena Devi', 30000),
(103, 'Vishal', 70000);
📸 Screenshot: Data inserted successfully.
✅ Current Data:
acc_no name balance
101 Arun Kumar 50000
102 Meena Devi 30000
103 Vishal 70000
⚙️ A → Atomicity
A transaction is all or nothing.
Either all steps succeed, or none are applied.
🎯 Example: Money Transfer (with Rollback)
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 5000 WHERE acc_no = 101;
UPDATE Accounts SET balance = balance + 5000 WHERE acc_no = 102;
-- Simulate an error
ROLLBACK;
SELECT * FROM Accounts;
📘 Explanation:
We start a transaction.
We try to transfer ₹5000 from Arun to Meena.
Then we issue a ROLLBACK, which undoes both updates.
✅ Expected Result:
Balances remain unchanged.
🧩 C → Consistency
The database must always move from one valid state to another.
Invalid data (e.g., negative balances) should not be allowed.
🎯 Example: Insert Invalid Record
INSERT INTO Accounts VALUES (104, 'Karthik', -2000);
✅ Expected Output:
📘 Explanation:
The CHECK (balance >= 0) constraint ensures consistency of data.
You can never have an account with a negative balance.
🔒 I → Isolation
Multiple transactions running at the same time should not interfere with each other.
🎯 Example: Two Sessions
🪄 Session 1:
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 10000 WHERE acc_no = 103;
-- (Don’t commit yet)
🪄 Session 2:
SELECT * FROM Accounts WHERE acc_no = 103;
✅ Observation:
If your isolation level is READ COMMITTED,
Session 2 won’t see the updated balance until Session 1 commits.
Once Session 1 executes COMMIT;, Session 2 will see the new balance.
📘 Explanation:
This demonstrates Isolation — one transaction’s changes remain invisible to others until committed.
💽 D → Durability
Once a transaction is committed, it remains permanent — even if the system crashes.
🎯 Example: Commit and Restart
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance + 2000 WHERE acc_no = 101;
COMMIT;
✅ Now restart your database service, then run:
SELECT * FROM Accounts WHERE acc_no = 101;
📘 Expected Output:
The updated balance remains in place even after the restart.
✅ This demonstrates Durability — committed data survives crashes or restarts.
🚀 Conclusion
You’ve just implemented and tested all four ACID properties in SQL:
✅ Atomicity → Rollback works perfectly
✅ Consistency → Data validation enforced
✅ Isolation → Parallel sessions protected
✅ Durability → Changes survive restarts
With this, you’ve mastered one of the most important foundations of Database Reliability! 🧠💾
💬 Next Step:
Try experimenting with different isolation levels using:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
and observe how concurrent transactions behave differently!
Top comments (0)