ACID Properties
ACID stands for Atomicity, Consistency, Isolation, and Durability — the four properties that guarantee reliable database transactions. Every relational database engine (PostgreSQL, MySQL InnoDB, SQL Server) implements ACID.
ACID Explained
- A — Atomicity: all or nothing. Every statement in a transaction succeeds or ALL are rolled back.
- C — Consistency: transactions take the database from one valid state to another. Constraints (FK, CHECK, UNIQUE) are maintained.
- I — Isolation: concurrent transactions don't interfere with each other — each transaction sees a consistent snapshot.
- D — Durability: committed transactions persist even after crashes. Data written to disk (WAL log).
- All four are guaranteed by InnoDB (MySQL), PostgreSQL, and SQL Server by default.
- NoSQL databases often sacrifice ACID for performance/scale (eventual consistency).
ACID In Practice
-- Atomicity: all or nothing
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If step 2 fails, step 1 is automatically rolled back
-- Balance never disappears
COMMIT;
-- Consistency: constraints are always maintained
BEGIN;
INSERT INTO orders (user_id, total_amount, status)
VALUES (999, 100.00, 'pending');
-- If user 999 doesn't exist: FK violated → ROLLBACK
-- Database stays consistent (no orphaned orders)
COMMIT;
-- Isolation: transaction A doesn't see uncommitted B
-- Session A:
BEGIN;
UPDATE products SET stock = 0 WHERE id = 1;
-- Session B (at the same time):
SELECT stock FROM products WHERE id = 1;
-- Session B reads old stock (50), not Session A's uncommitted 0
-- (at default isolation levels)
-- Durability: committed data survives crashes
-- WAL (Write-Ahead Log) ensures committed data is flushed to disk
-- Even if server crashes right after COMMIT, data is safe
-- PostgreSQL: pg_wal directory
-- MySQL: innodb_log_filesQuick Quiz
Tip
Tip
Practice ACID Properties in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Transactions guarantee all-or-nothing. ACID = reliability.
Common Mistake
Warning
A common mistake with ACID Properties is skipping edge case testing — empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task — (1) Write a working example of ACID Properties from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- ACID stands for Atomicity, Consistency, Isolation, and Durability — the four properties that guarantee reliable database transactions.
- A — Atomicity: all or nothing. Every statement in a transaction succeeds or ALL are rolled back.
- C — Consistency: transactions take the database from one valid state to another. Constraints (FK, CHECK, UNIQUE) are maintained.
- I — Isolation: concurrent transactions don't interfere with each other — each transaction sees a consistent snapshot.