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.

Summary

Property Description Example

Atomicity All or nothing execution of a transaction. Rollback test
Consistency Data remains valid before and after a transaction. Negative balance rejection
Isolation Transactions are executed independently. Two-session example
Durability Once committed, data persists permanently. Commit and restart DB

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.

💬 Try running these queries yourself and observe how your database ensures reliability step-by-step!