Demonstrating ACID Properties with SQL Transactions:-
Creating a Database in MySql
CREATE DATABASE acid_demo;
USE acid_demo;
Create a table Accounts(acc_no INT PRIMARY KEY, name VARCHAR(50), balance INT).
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, 'Alice', 5000),
(2, 'Bob', 3000),
(3, 'Charlie', 7000);
Run it.
👉 Output: 3 rows inserted.
Check the table:
SELECT * FROM Accounts;
Output:
acc_no | name | balance
1 | Alice | 5000
2 | Bob | 3000
3 | Charlie | 7000
Atomicity (Rollback vs Commit)
Atomicity: Start a transaction that transfers money. Rollback midway → ensure no partial update remains.
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 (Alice=5000, Bob=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: Alice=4500, Bob=3500.
Committed → permanent update.
Consistency
Try inserting a wrong record:
INSERT INTO Accounts (acc_no, name, balance) VALUES (4, 'David', -500);
Output: Error – CHECK constraint failed.
Database rejects invalid data → consistency is preserved.
Isolation (needs 2 query tabs)
Open two query tabs in Workbench:
Tab A = Session 1
Tab B = Session 2
In Tab A,
START TRANSACTION;
UPDATE Accounts SET balance = balance - 2000 WHERE acc_no = 1;
-- Do not commit yet
SELECT balance FROM Accounts WHERE acc_no = 1;
Tab A sees the reduced balance (2500).
In Tab B,
SELECT balance FROM Accounts WHERE acc_no = 1;
If using default isolation (REPEATABLE READ), Tab B still sees old committed value (4500), not Tab A’s uncommitted update.
Now, back in Tab A:
ROLLBACK;
Both sessions see Alice back to 4500.
This shows isolation.
Durability
START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 3;
COMMIT;
Check:
SELECT * FROM Accounts WHERE acc_no = 3;
Charlie’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.
Conclusion:
Atomicity: Transactions are “All or Nothing”.
Consistency: Database enforces valid data.
Isolation: Concurrent transactions do not interfere.
Durability: Committed changes are permanent.
Thanks @santhoshnc Sir for his guidance and support and for Everything
Top comments (0)