ACID property with SQL transactions

ACID stands for:

A – Atomicity: Ensures that a transaction is “all or nothing.”

C – Consistency: Ensures data follows rules and constraints.

I – Isolation: Ensures transactions run independently without interfering.

D – Durability: Ensures committed changes are saved even after system failure

Step-1 : Create Table and Insert Records

Step-2: Atomicity – Transaction Rollback
Simulate a transfer where you rollback midway:

Step-3: Consistency – Reject Invalid Data
Try inserting invalid data
❌ You’ll get an error because of the CHECK(balance >= 0) constraint.

Step-4: solation – Two Sessions

In Oracle Live SQL, open two sessions:
Session 1: Start a transaction and update balance but don’t commit.
Session 2: Try reading the same row.

Step-5: Durability – Commit and Restart

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

INSERT INTO Accounts VALUES (101, 'Ravi', 5000);
INSERT INTO Accounts VALUES (102, 'Meena', 3000);
INSERT INTO Accounts VALUES (103, 'Arun', 7000);

SELECT * FROM Accounts;
-- Start a transaction
SAVEPOINT start_tx;

UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 101;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 102;

-- Oops! Suppose error or cancellation occurs
ROLLBACK TO start_tx;

SELECT * FROM Accounts;
INSERT INTO Accounts VALUES (104, 'Kiran', -2000);
-- Session 1
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 101;
-- Don’t commit yet

-- Session 2
SELECT * FROM Accounts WHERE acc_no = 101;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 103;
COMMIT;