Managing data might seem straightforward, but behind every secure and reliable database are rules that ensure nothing breaks.
Let’s dive into ACID properties and see how SQL transactions guarantee that our operations are safe, consistent, and permanent — demonstrated with a simple Accounts table.
Creating the Accounts Table
We start by creating a table to hold account information and inserting some sample data.
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT CHECK (balance >= 0)
) ENGINE=InnoDB;
INSERT INTO Accounts VALUES
(201, 'Arjun', 12000),
(202, 'Diya', 9500),
(203, 'Vikram', 7000);
Atomicity — All or Nothing
Definition:
A transaction must be atomic: either all operations succeed, or none do.
Example: Money Transfer with Rollback
Consistency — Maintain Data Rules
Definition:
Transactions must leave the database in a valid state, respecting all constraints.
Example: Prevent Negative Balance
Result: The database rejected the invalid insert, maintaining consistent data.
Isolation — Transactions Don’t Interfere
Definition:
Concurrent transactions should not affect each other’s operations.
Example: Simulate Two Sessions
Session 1 (User A)
START TRANSACTION;
UPDATE Accounts SET balance = balance - 2000 WHERE acc_no = 201;
SELECT * FROM Accounts WHERE acc_no = 201;
Session 2 (User B)
SELECT * FROM Accounts WHERE acc_no = 201;
Result: Session 2 doesn’t see the uncommitted changes from Session1.
Now User A commits and checks again:
Change visible only after commit → Isolation works.
Durability — Permanent Once Committed
Definition:
Once a transaction is committed, its changes persist even if the database restarts.
Example: Commit and Verify
START TRANSACTION;
UPDATE Accounts SET balance = balance + 1500 WHERE acc_no = 203; -- Vikram gains ₹1500
COMMIT;
Restart the database and run:
Summary
✅ Atomicity: No partial updates
✅ Consistency: Only valid data allowed
✅ Isolation: Transactions don’t interfere
✅ Durability: Changes survive system failures
ACID properties ensure databases are reliable, robust, and safe, making SQL transactions predictable and trustworthy.
Top comments (0)