Exploring ACID Properties in SQL with Practical Queries

#beginners #tutorial #sql #database

Databases are designed to ensure that data remains accurate, reliable, and consistent even in the face of failures.
This reliability comes from the ACID properties — Atomicity, Consistency, Isolation, and Durability.

In this blog, let’s understand each property using a simple example: a bank Accounts table.

🧱 Step 1: Create the Accounts Table

We’ll begin by creating a table with three columns — account number, name, and balance.

CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT CHECK (balance >= 0) -- prevents negative balance
);

The CHECK constraint ensures that no record can have a negative balance — maintaining data consistency.

💡 Step 2: Insert Sample Records

INSERT INTO Accounts VALUES (1, "Alice", 1000);
INSERT INTO Accounts VALUES (2, "Bob", 1500);
INSERT INTO Accounts VALUES (3, "Charlie", 2000);

SELECT * FROM Accounts;

✅ Output:

acc_no name balance

1 Alice 1000
2 Bob 1500
3 Charlie 2000

⚙ Step 3: Atomicity

Atomicity ensures that a transaction is treated as a single unit — either all changes happen, or none do.

Let’s simulate a money transfer between two accounts, then roll it back to ensure no partial changes occur.

START TRANSACTION;

UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 2;

-- Cancel the transaction
ROLLBACK;

SELECT * FROM Accounts;

✅ After rollback, both balances return to their original state.
That’s Atomicity in action — preventing partial updates.

🧭 Step 4: Consistency

Now, let’s check if our table enforces consistency by rejecting invalid data.

INSERT INTO Accounts VALUES (104, 'David', -2000);

❌ This statement will fail because of the CHECK (balance >= 0) constraint.

This demonstrates Consistency, ensuring that all data adheres to predefined rules.

⚔ Step 5: Isolation

Isolation ensures that concurrent transactions don’t interfere with each other.

Try this in two different sessions:

Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 3;
-- Keep this transaction open

Session 2:

SELECT * FROM Accounts WHERE acc_no = 3;

Depending on your isolation level (e.g., READ COMMITTED, REPEATABLE READ), Session 2 may or may not see the uncommitted change.
That’s how Isolation controls visibility between transactions.

🔒 Step 6: Durability

Once a transaction is committed, its changes are permanent — even if the system crashes.

START TRANSACTION;
UPDATE Accounts SET balance = balance + 2000 WHERE acc_no = 3;
COMMIT;

SELECT acc_no, name, balance FROM Accounts WHERE acc_no = 3;

✅ After restarting your database, the updated balance for Charlie remains.
That’s Durability — ensuring committed data is never lost.

🏁 Final Thoughts

ACID properties form the foundation of reliable database systems.
By experimenting with simple SQL transactions, you can clearly see how Atomicity, Consistency, Isolation, and Durability maintain data integrity — even in complex systems.
Thank you @santhoshnc sir for guiding me.