ACID is a set of properties that guarantee reliable processing of transactions in a relational database.
| Property | Meaning |
|---|---|
| A | Atomicity – All or nothing |
| C | Consistency – Valid state transitions |
| I | Isolation – Transactions don’t interfere |
| D | Durability – Once committed, it’s saved |
🧨 Atomicity – “All or Nothing”
A transaction must either complete fully or not at all. No partial updates.
✅ Example:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- crash happens here
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;If the system crashes before the second update, the entire transaction is rolled back — Account A won’t lose $100.
📏 Consistency – “Valid State Transitions”
A transaction must move the database from one valid state to another, respecting all rules (e.g., constraints, triggers, data types).
✅ Example:
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
balance INT CHECK (balance >= 0)
);
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
If this update violates the CHECK (balance >= 0) constraint, the DBMS prevents the commit, ensuring consistency.
🧵 Isolation – “Transactions Don’t Interfere”
Transactions should appear to run independently, even when executed concurrently.
There are different Levels of Isolation.
This prevents issues like:
• Dirty reads (reading uncommitted data)
• Non-repeatable reads (data changes mid-transaction)
• Phantom reads (rows appear/disappear mid-query)
✅ Example:
Two users transfer money at the same time:
• Transaction A deducts $100
• Transaction B reads the balance in the middle of A
With proper isolation, Transaction B must wait or read a consistent snapshot.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
💾 Durability – “It Sticks After Commit”
Once a transaction is committed, its changes persist, even if the system crashes immediately afterward.
✅ Example:
You place an order and get a “success” message.
The DB commits:
INSERT INTO orders (user_id, item_id) VALUES (1, 101);
COMMIT;
Even if the power goes out right after, the order is not lost. The DB uses mechanisms like:
• Write-ahead logs
• Disk flushes
• Journaling
🧠 Summary
| Property | Guarantees |
|---|---|
| Atomicity | No partial updates – all steps succeed or none |
| Consistency | The data remains valid and rules are enforced |
| Isolation | Concurrent transactions don’t interfere |
| Durability | Once saved, it won’t be lost after a crash |
Together, these properties make sure your transactions are safe, reliable, and correct, even in the face of crashes or concurrency.