When working with relational databases, ACID properties ensure data is handled reliably, consistently, and safely.
ACID stands for:
- Atomicity 🧩 – All-or-nothing transactions
- Consistency ⚖️ – Database rules are preserved
- Isolation 🚧 – Transactions operate independently
- Durability 💪 – Committed transactions persist permanently
We’ll use an Accounts table to demonstrate each property step by step, with SQL examples you can try yourself.
⭐Step 1: Create the Accounts Table and Insert Sample Data
🧑💻CODE:
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT CHECK (balance >= 0));
INSERT INTO Accounts (acc_no, name, balance) VALUES
(101, 'Calindra', 5800),
(102, 'Thalorin', 4200),
(103, 'Veylith', 6900);
SELECT * FROM Accounts;
Explanation:
- acc_no is the primary key, ensuring each account is unique 🔑
- balance has a CHECK constraint to prevent negative values ❌
- Sample data gives three accounts to work with for transactions ✅
⭐Step 2: Atomicity 🧩 – All-or-Nothing Transactions
Goal: Ensure that if part of a transaction fails, no partial updates occur.
Scenario: Transfer 1500 from Calindra to Thalorin, but simulate an error midway.
🧑💻CODE:
START TRANSACTION;
UPDATE Accounts SET balance = balance - 1500 WHERE acc_no = 101;
UPDATE Accounts SET balance = balance + 1500 WHERE acc_no = 102;
ROLLBACK;
SELECT * FROM Accounts;
Observation:
- After ROLLBACK, all balances remain unchanged 🔄
- Prevents incomplete transactions, critical in banking systems 💳
⭐Step 3: Consistency ⚖️ – Enforcing Data Rules
Goal: Ensure the database always remains in a valid state.
Scenario: Try to insert a record with a negative balance:
🧑💻CODE:
INSERT INTO Accounts (acc_no, name, balance) VALUES (104, 'Elarion', -2000);
Result:
Explanation:
- Database constraints ❌ prevent invalid data
- Consistency ensures all business rules and constraints are preserved
Tip 💡: Use constraints, triggers, and validations to maintain consistent data, especially in critical systems like finance, healthcare, or inventory.
⭐Step 4: Isolation 🚧 – Transactions Don’t Interfere
Goal: Ensure simultaneous transactions don’t cause conflicts.
Scenario: Simulate two sessions:
🧑💻CODE:
START TRANSACTION;
UPDATE Accounts SET balance = balance - 700 WHERE acc_no = 101;
SELECT balance FROM Accounts WHERE acc_no = 101;
COMMIT;
Session 1: Update an account but do not commit yet:
Session 2: Read the same account’s balance:
Observation:
- Session 2 sees the original balance until Session 1 commits 🔒
Session 1 Commit:
- After commit, Session 2 sees the updated balance ✅
Tip 💡: Different isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) control how strict transaction isolation is.
⭐Step 5: Durability 💪 – Committed Transactions Persist
Goal: Ensure committed transactions are permanent, even after a crash or restart.
Scenario: Update Veylith’s balance and commit:
🧑💻CODE:
START TRANSACTION;
UPDATE Accounts SET balance = balance + 2500 WHERE acc_no = 103;
COMMIT;
SELECT acc_no, name, balance FROM Accounts WHERE acc_no = 103;
Observation:
- After restart, Veylith’s balance retains the updated value 💾
- Durability is typically ensured using write-ahead logs (WAL) and disk storage
💡 Closing Thoughts
In this guide, we explored ACID properties — Atomicity 🧩, Consistency ⚖️, Isolation 🚧, and Durability 💪 — using a simple Accounts table to illustrate real-world scenarios:
- Atomicity: Transactions are all-or-nothing — rollback ensures no partial updates.
- Consistency: Constraints like balance >= 0 keep data valid at all times.
- Isolation: Concurrent transactions operate independently, preventing conflicts.
- Durability: Committed changes survive database crashes and restarts.
By understanding and applying ACID principles, you can build robust, reliable, and secure database applications — whether in banking 💳, e-commerce 🛒, healthcare 🏥, or enterprise systems 🏢.
Mastering ACID not only ensures data integrity and safety but also gives you the confidence to handle real-world transactional challenges with SQL.
✅ Remember: ACID isn’t just a theory — it’s the backbone of trustworthy, dependable database systems.
Top comments (0)