To demonstrate the ACID properties (Atomicity, Consistency, Isolation, and Durability) using SQL transactions.

🧩 Step 1: Create Table and Insert Sample Data
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ACCOUNTS CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

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

INSERT INTO ACCOUNTS VALUES (101, 'Kiran', 5000);
INSERT INTO ACCOUNTS VALUES (102, 'Asha', 7000);
INSERT INTO ACCOUNTS VALUES (103, 'Vikram', 9000);
COMMIT;

-- ✅ View initial data
SELECT * FROM ACCOUNTS;

Step 2: Atomicity

Atomicity means “all or nothing.”
If a transaction fails, no partial updates remain.

🧪 Example: Transfer ₹1000 from Kiran to Asha, but rollback halfway
-- Start transaction
SAVEPOINT start_transfer;

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

-- Suppose error happens — rollback
ROLLBACK TO start_transfer;

-- ✅ Check balances after rollback
SELECT * FROM ACCOUNTS;
✅ Output
No change! Balances are the same as before → proves Atomicity.

Step 3: Consistency

Consistency means the database always stays valid — rules and constraints are never broken.

🧪 Example: Try inserting an invalid record
-- This should FAIL because of the CHECK constraint
INSERT INTO ACCOUNTS VALUES (104, 'Sneha', -2000);

🧱 Expected Result:

ORA-02290: check constraint (BALANCE >= 0) violated

✅ Database rejected invalid data → proves Consistency.
Step 4: Isolation

Isolation means one transaction’s changes are not visible to others until committed.

We can simulate this conceptually (since Oracle Live SQL runs one session per user):

Session 1 (Transaction A):

-- Begin Transaction A
UPDATE ACCOUNTS SET balance = balance - 500 WHERE acc_no = 101;

-- Don’t commit yet

Session 2 (Transaction B):

-- Try reading while Transaction A is open
SELECT * FROM ACCOUNTS WHERE acc_no = 101;

✅ Observation:
Transaction B still sees the old balance (not updated)
→ changes are isolated until commit.

Then, if Session 1 commits:

COMMIT;

Now Session 2 reads:

SELECT * FROM ACCOUNTS WHERE acc_no = 101;

✅ Now it sees the new value → proves Isolation.

🔒 Step 5: Durability

Durability means once a transaction is committed, it survives system failure.

🧪 Example:
UPDATE ACCOUNTS SET balance = balance + 500 WHERE acc_no = 103;
COMMIT;

✅ Even if the database restarts or session closes, the committed change stays stored permanently.
If you re-run:

SELECT * FROM ACCOUNTS WHERE acc_no = 103;