Understanding ACID Properties with SQL Transactions

#beginners #tutorial #sql #database

In this blog, we will learn about Atomicity, Consistency, Isolation, and Durability using a simple SQL example with screenshots.

We’ll use an Accounts table to demonstrate each property step by step.

CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT CHECK (balance >= 0) -- prevents negative balance
);
INSERT INTO Accounts VALUES (101, 'pranav', 5000);
INSERT INTO Accounts VALUES (102, 'sashmi', 3000);
INSERT INTO Accounts VALUES (103, 'harini', 7000);

SELECT * FROM Accounts;

Atomicity
Atomicity means all or nothing — if part of a transaction fails, the entire operation is rolled back.

START TRANSACTION;

UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 101;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 102;
-- Cancel the transaction
ROLLBACK;

SELECT * FROM Accounts;
The balances remain unchanged after rollback.

Consistency
Consistency ensures rules/constraints are never broken.
Since we defined CHECK (balance >= 0), inserting a negative balance will fail.

INSERT INTO Accounts VALUES (104, 'David', -2000);
we will see error like:
ERROR: CHECK constraint failed: Accounts
→ proves Consistency

Isolation
Isolation ensures transactions don’t interfere with each other.
Let’s simulate two sessions:
Session 1
START TRANSACTION;
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 101;
-- Don’t commit yet
Session 2
SELECT * FROM Accounts WHERE acc_no = 101;

If your DB uses READ COMMITTED (default in many systems), Session 2 still sees the old balance.
Once Session 1 does COMMIT;, Session 2 can see the updated value.

Durability
Durability guarantees that once a transaction is committed, it survives even after a crash/restart.

-- Commit a change
START TRANSACTION;
UPDATE Accounts SET balance = balance + 2000 WHERE acc_no = 103;
COMMIT;

-- Restart the DB server manually
-- Then check again
SELECT acc_no, name, balance FROM Accounts WHERE acc_no = 103;