ACID property with SQL transactions

Create a table Accounts(acc_no INT PRIMARY KEY, name VARCHAR(50), balance INT). Insert 3 sample rows.

-- Creating Account table
CREATE TABLE Accounts (
  acc_no   INT PRIMARY KEY,
  name     VARCHAR2(50),
  balance  INT CHECK (balance >= 0)  -- Enforces Consistency
);

-- Inserting values
BEGIN
  INSERT INTO Accounts VALUES (101, 'Aarav', 50000);
  INSERT INTO Accounts VALUES (102, 'Meera', 60000);
  INSERT INTO Accounts VALUES (103, 'Ravi', 70000);
  COMMIT;
END;

SELECT * FROM Accounts;

Atomicity: Start a transaction that transfers money. Rollback midway → ensure no partial update remains.

BEGIN
  -- Atomic transaction: transfer money
  UPDATE Accounts SET balance = balance - 10000 WHERE acc_no = 101;
  UPDATE Accounts SET balance = balance + 10000 WHERE acc_no = 102;

  -- Show updated balances
  DECLARE
    v_bal_101 INT;
    v_bal_102 INT;
  BEGIN
    SELECT balance INTO v_bal_101 FROM Accounts WHERE acc_no = 101;
    SELECT balance INTO v_bal_102 FROM Accounts WHERE acc_no = 102;

    DBMS_OUTPUT.PUT_LINE('Account 101 balance: ' || v_bal_101);
    DBMS_OUTPUT.PUT_LINE('Account 102 balance: ' || v_bal_102);
  END;

  -- Rollback to test atomicity
  ROLLBACK;
END;

Consistency: Try inserting a record with negative balance → should be rejected.

BEGIN
  -- Try inserting invalid data
  INSERT INTO Accounts VALUES (104, 'Lakshmi', -5000);  -- Violates CHECK constraint
  COMMIT;
END;

Isolation: Run two sessions at once – one updating, the other reading → observe isolation.

-- Session A (Updater)
BEGIN
  UPDATE Accounts SET balance = balance + 5000 WHERE acc_no = 103;
  -- Do NOT commit yet
END;

-- Session B (Reader)
SELECT * FROM Accounts WHERE acc_no = 103;

Durability: Commit a transaction → restart DB → ensure data persists.

-- Commit and Restart
BEGIN
  UPDATE Accounts SET balance = balance + 10000 WHERE acc_no = 102;
  COMMIT;
END;

-- After DB restart
SELECT * FROM Accounts WHERE acc_no = 102;