Understanding ACID Properties in SQL with Practical Examples

🧠 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!