ACID PROPERTY IN DBMS

#database #tutorial #beginners #sql

you're learning database fundamentals, you've probably heard of ACID—Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable transactions in relational databases.

Let’s explore each one with real SQL examples using an Accounts table.

Step 1: Create the Table and Insert Sample Data
sql
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT CHECK (balance >= 0) -- Enforces Consistency
);

-- Insert sample rows
INSERT INTO Accounts VALUES (101, 'Aarav', 50000);
INSERT INTO Accounts VALUES (102, 'Meera', 30000);
INSERT INTO Accounts VALUES (103, 'Ravi', 70000);
Exploring ACID Properties

Atomicity: All or Nothing Let’s simulate a money transfer from Aarav to Meera. We’ll start a transaction and rollback midway to ensure no partial updates remain.

sql
BEGIN
UPDATE Accounts SET balance = balance - 10000 WHERE acc_no = 101; -- Aarav
UPDATE Accounts SET balance = balance + 10000 WHERE acc_no = 102; -- Meera

-- Something goes wrong, rollback!
ROLLBACK;
END;

Consistency: Data Integrity Rules Try inserting a record with a negative balance:

sql
INSERT INTO Accounts VALUES (104, 'Kiran', -5000);

Isolation: Transactions Don’t Clash Open Session A and run:

sql
BEGIN
UPDATE Accounts SET balance = balance + 5000 WHERE acc_no = 103;
sql
SELECT balance FROM Accounts WHERE acc_no = 103;
Result: Session B sees the old balance until Session A commits. That’s Isolation—transactions don’t interfere with each other.

4. Durability: Data Persists After Commit
sql
BEGIN
UPDATE Accounts SET balance = balance + 10000 WHERE acc_no = 102;
COMMIT;
END;
Now, restart your database or session and run: