💾 Understanding ACID Properties in Databases with SQL Examples

In this post, I’ll demonstrate ACID properties — Atomicity, Consistency, Isolation, and Durability — using a simple Accounts table and a few SQL commands.

Let’s dive in 👇

🧾 1. Table Creation

CREATE TABLE Accounts (
  acc_no INT PRIMARY KEY,
  name VARCHAR(50),
  balance INT CHECK (balance >= 0)
);

🧩 2. Insert Sample Data

INSERT INTO Accounts VALUES (101, 'Baviya', 3000);
INSERT INTO Accounts VALUES (102, 'Karthik', 4000);
INSERT INTO Accounts VALUES (103, 'Anu', 7000);

⚙️ 3. Atomicity Test (All or Nothing)

Goal: Start a transaction to transfer ₹1000 from Baviya to Karthik but rollback midway — no changes should remain.

-- Start Transaction
START TRANSACTION;

-- Subtract 1000 from Baviya
UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 101;

-- Add 1000 to Karthik
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 102;

-- Now ROLLBACK (cancel transaction)
ROLLBACK;

-- Check balances again
SELECT * FROM Accounts;

🧱 4. Consistency Test (Valid State → Valid State)

Goal: Try inserting invalid (negative) balance.

INSERT INTO Accounts VALUES (104, 'InvalidUser', -500);

🔒 5. Isolation Test (One transaction shouldn’t see another’s uncommitted changes)
Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 101;
-- Don’t commit yet!

Session 2:

SELECT * FROM Accounts WHERE acc_no = 101;
COMMIT;

💾 6. Durability Test (Data survives restart)

START TRANSACTION;
UPDATE Accounts SET balance = balance + 200 WHERE acc_no = 103;
COMMIT;

SELECT * FROM Accounts WHERE acc_no = 103;

✅ Conclusion

We successfully verified all ACID properties: