ACID Properties with SQL Transactions

Databases need to be reliable, consistent, and crash-proof. This is where ACID properties — Atomicity, Consistency, Isolation, and Durability — come in.

In this post, we’ll demonstrate ACID properties using an Accounts table in Oracle SQL.
DROP TABLE Accounts PURGE;

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

INSERT INTO Accounts VALUES (101, 'Arjun', 5000);
INSERT INTO Accounts VALUES (102, 'Sneha', 3000);
INSERT INTO Accounts VALUES (103, 'Kiran', 7000);
COMMIT;

SELECT * FROM Accounts;

DROP TABLE Accounts PURGE;

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

INSERT INTO Accounts VALUES (101, 'Arjun', 5000);
INSERT INTO Accounts VALUES (102, 'Sneha', 3000);
INSERT INTO Accounts VALUES (103, 'Kiran', 7000);
COMMIT;

SELECT * FROM Accounts;

1.Atomicity
SET SERVEROUTPUT ON;

BEGIN
DBMS_OUTPUT.PUT_LINE('--- Atomicity Test ---');
SAVEPOINT start_trans;

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

ROLLBACK TO start_trans;

FOR rec IN (SELECT * FROM Accounts ORDER BY acc_no) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.acc_no || ' - ' || rec.name || ' : ' || rec.balance);
END LOOP;

END;

2.Consistency
BEGIN
DBMS_OUTPUT.PUT_LINE('--- Consistency Test ---');
INSERT INTO Accounts VALUES (104, 'Meena', -2000);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

3.Isolation

BEGIN;
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 103;

4.Durability
BEGIN
DBMS_OUTPUT.PUT_LINE('--- Durability Test ---');
UPDATE Accounts SET balance = balance + 200 WHERE acc_no = 102;
COMMIT;
END;
/

SELECT * FROM Accounts WHERE acc_no = 102;

Conclusion:

ACID properties ensure that database transactions are safe, reliable, and consistent, even in case of failures or concurrent operations.
They are the backbone of all transactional databases.

Thank you @santhoshnc sir for guiding me!!!!!