ACID Properties
Advanced⏱️ 12 mins read
What You'll Learn
ACID guarantees reliable processing of database transactions. Atomicity: all operations succeed or all are rolled back — no partial state. Consistency: data moves from one valid state to another, all rules (constraints, triggers) always hold. Isolation: concurrent transactions execute as if sequential. Durability: once committed, data survives crashes (written to disk/WAL log).
Syntax
-- ACID is enforced by the database engine
-- Atomicity: BEGIN + COMMIT/ROLLBACK
-- Consistency: constraints, triggers, foreign keys
-- Isolation: transaction isolation levels
-- Durability: WAL (Write-Ahead Log), fsyncExample
-- Atomicity: debit AND credit must both succeed
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- only NOW are changes permanent
-- Consistency: foreign key constraint prevents orphans
INSERT INTO orders (customer_id, total)
VALUES (999, 50.00);
-- Fails if customer 999 doesn't exist in customers table
-- Isolation: READ COMMITTED default
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;Common Mistakes
Assuming NoSQL databases are ACID compliant — many prioritize availability over strict consistency (CAP theorem). Also: ACID doesn't mean 'no bugs' — application logic bugs still cause inconsistency.
Interview Tips
This is a classic senior-level question. Give a real-world banking example for each property. Isolation levels are frequently asked: explain dirty reads, non-repeatable reads, and phantom reads at each level.
Practice
Explain how a bank transfer (debit + credit) would violate each ACID property if transactions weren't used. Which property is violated first, and what's the real-world consequence?