When working with relational databases, transactions are the building blocks that ensure reliability.
They follow the ACID properties:
Atomicity → All or nothing
Consistency → Valid state before & after
Isolation → Transactions don’t interfere
Durability → Changes survive crashes
In this blog, we’ll explore ACID with SQL scripts using an Accounts table.
Creating a Database in MySql
CREATE DATABASE acid_demo;
USE acid_demo;Step 1: Setup the Accounts Table
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT CHECK (balance >= 0)
) ENGINE=InnoDB;
Insert 3 sample rows.
INSERT INTO Accounts (acc_no, name, balance) VALUES
(1, 'Sarah', 5000),
(2, 'Jessie', 3000),
(3, 'Benson', 7000);
Run it.
Output:
3 rows insertedCheck the table:
SELECT * FROM Accounts;Output:
acc_no | name | balance
1 | Sarah| 5000
2 | Jessie | 3000
3 | Benson | 7000
Atomicity
Definition: A transaction is atomic, meaning either all operations succeed or none do.
Example: Transfer 500 from Sarah to Jessie, then rollback
ROLLBACK:
START TRANSACTION;
UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 2;
ROLLBACK;
SELECT * FROM Accounts;
Output:
balances remain unchanged (Sarah=5000, Jessie=3000).
This proves atomicity: either all updates happen, or none.
COMMIT:
START TRANSACTION;
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2;
COMMIT;
SELECT * FROM Accounts;
Output: Sarah=4500, Jessie=3500.
Committed → permanent update.
Consistency
Definition: A transaction must bring the database from one valid state to another.
Rules like constraints must never be violated.
Example: Try inserting negative balance
INSERT INTO Accounts (acc_no, name, balance) VALUES (4, 'David', -500);
Output: Error – CHECK constraint failed.
Database rejects invalid data → consistency is preserved.
Isolation
Definition: Transactions executing at the same time should not interfere with each other.
Example: Two sessions
Session 1 (updating):
START TRANSACTION;
UPDATE Accounts SET balance = balance - 2000 WHERE acc_no = 1;
Do not commit yet
SELECT balance FROM Accounts WHERE acc_no = 1;
Session 1 sees the reduced balance (2500).Session 2 (reading at same time):
SELECT balance FROM Accounts WHERE acc_no = 1
Durability
Definition: Once a transaction is committed, its changes persist even if the system crashes.
Example: Commit, restart DB, check again
START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 3;
COMMIT;Check:
SELECT * FROM Accounts WHERE acc_no = 3;
Benson’s balance increases (7500).
Now restart MySQL server
Reconnect, run again:
USE acid_demo;
SELECT * FROM Accounts WHERE acc_no = 3;
Balance is still 7500.
This proves durability: committed changes survive restarts.
🚀Wrap Up
We demonstrated the ACID properties using SQL:
🔹 Atomicity → Rollback prevents partial updates
🔹 Consistency → Constraints keep data valid
🔹 Isolation → Transactions run independently
🔹 Durability → Committed changes survive crashes
These principles ensure that databases remain reliable, safe, and trustworthy, even under concurrent workloads or unexpected failures.
Thanks to @santhoshnc sir for his guidance and support.
Top comments (0)