ACID

Explanation

Atomicity: a transaction is treated as a single unit. All operations in the transaction succeed, or none do. If a crash occurs mid-transaction, the database is rolled back to its state before the transaction started. This is why banking transfers don't cause money to disappear when a server crashes mid-operation. Consistency: a transaction can only bring the database from one valid state to another. All data integrity rules (constraints, triggers, cascades) are enforced. A transaction that would violate a NOT NULL constraint or a UNIQUE constraint is rejected, leaving the database unchanged. Consistency is partly the database's responsibility (constraint enforcement) and partly the application's (enforcing business rules within transactions). Isolation: concurrent transactions execute independently — they don't see each other's intermediate states. The degree of isolation is configurable via isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE). Perfect isolation (Serializable) makes transactions execute as if they ran one after another, even when they ran concurrently. This prevents anomalies like dirty reads (seeing uncommitted data) and phantom reads (a second read in the same transaction seeing different rows due to another transaction's committed inserts). Durability: once a transaction is committed, it will survive system failures (power loss, crashes). The database writes the committed transaction to a write-ahead log (WAL) before acknowledging the commit. On restart after a crash, the database replays the WAL to restore committed state. Durability doesn't guarantee the data is backed up — that requires separate backup procedures.

Code Example

-- ACID in action: demonstrating each property

-- ATOMICITY: partial failure rolls back everything
BEGIN;
INSERT INTO orders (user_id, total) VALUES (42, 5000);  -- succeeds
INSERT INTO order_items (order_id, product_id) VALUES (currval('orders_id_seq'), 999);
-- product_id 999 doesn't exist → FK violation → entire transaction rolls back
COMMIT;
-- Result: neither the order NOR the items exist

-- CONSISTENCY: constraint violations rejected
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
-- If this makes balance negative and CHECK(balance >= 0) exists:
-- → ROLLBACK, constraint violation
COMMIT;

-- ISOLATION: READ COMMITTED level
-- Transaction A reads user 42's balance: $100
-- Transaction B deducts $100 and commits
-- Transaction A reads again: sees $0 (READ COMMITTED sees committed changes)
-- With REPEATABLE READ: Transaction A always sees $100 for the duration of the transaction

-- DURABILITY: write-ahead log
-- 1. Application: COMMIT
-- 2. Database writes to WAL (disk) → acknowledges commit
-- 3. Server crashes
-- 4. Database restarts, replays WAL → committed data restored

Why It Matters for Engineers

ACID properties are the reason relational databases are trusted with financial systems, medical records, and any application where data correctness is non-negotiable. Understanding ACID tells you what guarantees you have and when you can rely on the database to protect your data vs when you need to write defensive application code. ACID also explains why NoSQL databases (which often sacrifice some ACID properties for scalability) are not always appropriate for critical data. Knowing the trade-offs between ACID-compliant databases and eventually-consistent NoSQL systems lets you make informed architectural choices.