Understanding ACID Properties in SQL with Examples

ACID properties — Atomicity, Consistency, Isolation, Durability — ensure that database transactions are reliable, safe, and predictable. Let’s explore them with a practical Accounts table.

1. Create Sample Accounts Table
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR2(50),
balance INT CHECK (balance >= 0) -- enforce positive balance
);

Insert sample data
INSERT INTO Accounts VALUES (101, 'Alice', 5000);
INSERT INTO Accounts VALUES (102, 'Bob', 3000);
INSERT INTO Accounts VALUES (103, 'Charlie', 7000);

COMMIT;

2. Atomicity

Definition: Either all operations in a transaction succeed, or none do.

Scenario: Transfer 1000 from Alice to Bob, but simulate a failure midway.

-- Start transaction
BEGIN
-- Deduct 1000 from Alice
UPDATE Accounts
SET balance = balance - 1000
WHERE acc_no = 101;

-- Simulate error (e.g., divide by zero)
-- This will cause the transaction to fail
-- Uncomment the following line to simulate failure
-- DECLARE x NUMBER := 1/0; END;

-- Add 1000 to Bob
UPDATE Accounts
SET balance = balance + 1000
WHERE acc_no = 102;

COMMIT; -- Will not reach here if error occurs

EXCEPTION
WHEN OTHERS THEN
ROLLBACK; -- Ensures no partial update
DBMS_OUTPUT.PUT_LINE('Transaction failed, rolled back.');
END;
/
✅ After rollback, balances remain unchanged — Atomicity is maintained.

3. Consistency

Definition: Transactions must leave the database in a valid state.

Scenario: Try inserting a negative balance.

-- This will fail due to the CHECK constraint
INSERT INTO Accounts VALUES (104, 'David', -500);

The database rejects this, ensuring Consistency.

4. Isolation

Definition: Concurrent transactions should not interfere with each other.

Scenario: Two sessions:

Session 1: Update Alice’s balance

UPDATE Accounts
SET balance = balance + 2000
WHERE acc_no = 101;
-- Do not commit yet

Session 2: Read Alice’s balance

SELECT balance FROM Accounts WHERE acc_no = 101;

Session 2 will not see the uncommitted change (depending on isolation level, default is READ COMMITTED).

This prevents dirty reads — demonstrating Isolation.

5. Durability

Definition: Once a transaction is committed, data persists even after a crash.

-- Commit a transaction
UPDATE Accounts
SET balance = balance + 1000
WHERE acc_no = 102;

COMMIT;

Restart the database.

Run:
SELECT * FROM Accounts;

Changes persist — Durability is guaranteed.

Summary Table
ACID Property Example Action
Atomicity Transfer money → rollback on error
Consistency CHECK constraint prevents negative balance
Isolation Concurrent reads/writes don’t interfere
Durability Committed changes persist after DB restart

💡Tip: Try changing isolation levels (READ COMMITTED, SERIALIZABLE) to observe how concurrent transactions behave differently.