These 4 rules keep your data safe — no half-finished updates, no broken values, and no weird side effects.
What is ACID?
ACID stands for:
- Atomicity
- Consistency
- Isolation
- Durability
Let’s walk through each one with real-world table examples — before and after.
Atomicity – “All or Nothing”
If a transaction has multiple steps, either all succeed or none do. If something breaks in the middle, it’s like it never happened.
Example: Transferring $100 from Alice to Bob
Before
| account_id | name | balance |
|---|---|---|
| 1 | Alice | 500 |
| 2 | Bob | 300 |
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 99; -- Oops, invalid!
ROLLBACK;
After ROLLBACK
| account_id | name | balance |
|---|---|---|
| 1 | Alice | 500 |
| 2 | Bob | 300 |
Atomicity: Alice’s balance didn’t drop. The system saved nothing because the full transaction didn’t succeed.
Consistency – “Data Must Stay Valid”
Data must follow all rules: constraints, types, relationships.
Example: Prevent negative prices with CHECK (price > 0)
Before
| product_id | name | price |
|---|---|---|
| 10 | Mouse | 25 |
UPDATE products SET price = -10 WHERE product_id = 10;
After (unchanged)
| product_id | name | price |
|---|---|---|
| 10 | Mouse | 25 |
Consistency: SQL blocks changes that break rules.
Isolation – “One Transaction at a Time”
Even if multiple users change data at once, each transaction behaves like it’s the only one.
Example: Two people update stock at once
Before
| item_id | item_name | quantity |
|---|---|---|
| 5 | Headphones | 10 |
-- Transaction A
BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE item_id = 5;
-- WAITING TO COMMIT
-- Transaction B
BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE item_id = 5;
-- BLOCKED UNTIL A COMMITS
After Transaction A commits
| item_id | item_name | quantity |
|---|---|---|
| 5 | Headphones | 9 |
Isolation: No conflicting edits — users don’t step on each other’s toes.
Durability – “Saved Means Saved”
Once you COMMIT, your changes are safe — even if the server crashes immediately after.
Example: Insert a new order
Before
| order_id | customer | total |
|---|---|---|
| 1 | Maria | 120 |
BEGIN;
INSERT INTO orders (order_id, customer, total) VALUES (2, 'Jake', 250);
COMMIT;
-- system crashes now
After Reboot
| order_id | customer | total |
|---|---|---|
| 1 | Maria | 120 |
| 2 | Jake | 250 |
Durability: Committed data survives system failure.
ACID Recap Table
| Property | What It Means | Example |
|---|---|---|
| Atomicity | All steps succeed or none do | Bank transfer with rollback on failure |
| Consistency | Keeps data valid | Blocks invalid price update |
| Isolation | No interference between transactions | Two users update same row safely |
| Durability | Once saved, it stays saved | Order insert survives crash |
Final Thoughts
ACID is the backbone of data safety. Use it to keep your app fast, secure, and bug-proof — especially when handling money, users, or inventory.