Exploring ACID Properties in SQL with Practical Examples

Ensuring that data remains accurate, reliable, and consistent is at the heart of database systems. This reliability comes from the ACID properties — Atomicity, Consistency, Isolation, and Durability.

In this post, we’ll explore each property using a simple bank Accounts table.

Step 1: Creating the Accounts Table

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

The CHECK constraint ensures no account can have a negative balance, maintaining data consistency.

Step 2: Inserting 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 a transaction is all or nothing.

START TRANSACTION;

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

ROLLBACK;

After rollback, balances return to their original state. ✅ No partial updates occur.

Step 4: Consistency
Consistency ensures all data follows predefined rules.

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

❌ This fails because negative balances are not allowed — maintaining data integrity.

Step 5: Isolation
Isolation prevents concurrent transactions from interfering with each other.

Session 1:

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

Session 2:

SELECT * FROM Accounts WHERE acc_no = 3;

Depending on your isolation level, uncommitted changes may or may not be visible.

Step 6: Durability
Durability ensures that once committed, changes persist, even after system failures.

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

✅ After restarting the database, Charlie’s updated balance remains intact.

ACID properties in action — keeping your data safe, reliable, and consistent.

🧩Summary :

Databases rely on ACID properties — Atomicity, Consistency, Isolation, and Durability — to ensure data remains accurate, reliable, and consistent.

Using a simple bank Accounts table, we can see these principles in action:

Atomicity: Transactions are all-or-nothing, preventing partial updates.

Consistency: Data always adheres to predefined rules.

Isolation: Concurrent transactions don’t interfere with each other.

Durability: Committed changes are permanent, even after system failures.

Understanding ACID is essential for building robust and reliable SQL databases.