ACID stands for Atomicity, Consistency, Isolation, Durability — ensuring that database transactions are processed completely, correctly, independently, and permanently, even in case of failures...
ACID is a set of properties that guarantee database transactions are processed reliably. The acronym stands for Atomicity, Consistency, Isolation, and Durability - four guarantees that prevent data corruption and ensure your database stays trustworthy.
When you transfer money between bank accounts, ACID properties ensure the money does not disappear or duplicate. Either the full transaction completes, or nothing happens at all.
A transaction either completes entirely or does not happen at all. No partial updates.
Example: Transfer $100 from Account A to Account B involves two operations:
If step 2 fails, step 1 must be rolled back. You cannot have money disappear. Atomicity guarantees this.
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = "A";
UPDATE accounts SET balance = balance + 100 WHERE id = "B";
COMMIT;
-- If anything fails, entire transaction rolls back
Master system design through bite-sized lessons built for early-career engineers. Build scalable, bulletproof systems with hands-on projects and real-world case studies that make complex concepts click.
Master the art of picking the right database for the job - because using the wrong one is like bringing a spoon to a knife fight!
World of Non-Relational Databases Understand the critical properties that make databases reliable enough to handle your money .
Relational Databases Discover the chaos that happens when multiple operations try to modify the same data at once, and how databases solve this problem.
Database Isolation Levels Database moves from one valid state to another. All rules (constraints, triggers, cascades) are enforced.
Example: You have a constraint that account balances cannot be negative. Even if a transaction tries to overdraw an account, consistency ensures the constraint is checked and the transaction is rejected.
Before transaction: All constraints satisfied After transaction: All constraints still satisfied
No transaction can leave the database in an invalid state.
Concurrent transactions do not see each other intermediate states. Each transaction executes as if it is the only one running.
Example: Two people withdraw from the same account simultaneously:
Without isolation:
With isolation:
Once a transaction commits, the changes are permanent. Even if the system crashes immediately after, the data persists.
Example: You buy something online, payment processes successfully, confirmation shows. Then the server crashes. When it restarts, your order still exists. Durability guarantees this.
Databases achieve this by writing to persistent storage (disk) and using transaction logs.
Banking: Money transfers must be reliable. ACID prevents funds from vanishing or duplicating.
E-commerce: Orders must be processed correctly. Inventory must update accurately.
Healthcare: Patient records must be consistent. Drug prescriptions cannot get corrupted.
Any Critical Data: Wherever data integrity matters, ACID protects you.
-- Money transfer with ACID guarantees
BEGIN TRANSACTION;
-- Check sufficient funds
SELECT balance FROM accounts WHERE id = "A" FOR UPDATE;
-- If balance >= 100, proceed
UPDATE accounts SET balance = balance - 100 WHERE id = "A";
UPDATE accounts SET balance = balance + 100 WHERE id = "B";
-- Everything succeeded
COMMIT;
-- If anything fails
ROLLBACK;
The database ensures:
ACID: Strong consistency, reliability. Used by traditional SQL databases (PostgreSQL, MySQL, Oracle).
BASE: Basically Available, Soft state, Eventually consistent. Used by some NoSQL databases (Cassandra, DynamoDB).
ACID: Your bank account balance is always accurate, right now.
BASE: Social media like counts might be slightly off for a few seconds but eventually correct.
Choose based on requirements. Financial systems need ACID. Social feeds can use BASE.
PostgreSQL: Full ACID compliance, rock-solid reliability.
MySQL/MariaDB: ACID with InnoDB storage engine.
Oracle: Enterprise-grade ACID guarantees.
SQL Server: Microsoft database with strong ACID support.
SQLite: Even lightweight SQLite provides ACID.
Most modern SQL databases are ACID-compliant by default.
ACID isolation has levels that trade correctness for performance:
Read Uncommitted: Dirty reads possible. Fastest, least safe.
Read Committed: Only see committed data. Prevents dirty reads.
Repeatable Read: Same query returns same results within transaction.
Serializable: Strongest isolation. Transactions appear sequential.
Default is usually Read Committed. Use Serializable for critical operations.
-- Set isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- Your queries here
COMMIT;
ACID is not free:
Performance: Locking and logging add overhead.
Scalability: Harder to scale horizontally across servers.
Complexity: Distributed ACID is challenging.
But for data that matters, the cost is worth it. Losing customer orders or corrupting account balances costs far more.
Most ORMs and database libraries handle transactions:
// Node.js with PostgreSQL
await db.transaction(async (trx) => {
await trx("accounts")
.where({ id: "A" })
.decrement("balance", 100)
await trx("accounts")
.where({ id: "B" })
.increment("balance", 100)
// Automatically commits on success
// Automatically rolls back on error
})
The framework handles BEGIN, COMMIT, and ROLLBACK.
Forgetting to Commit: Transaction hangs, locks resources.
Long-Running Transactions: Hold locks too long, block other operations.
Not Handling Rollback: Errors occur, but code does not roll back properly.
Nested Transactions: Most databases do not support true nested transactions.
Keep transactions short and handle errors properly.
Online ticket sales for a concert:
BEGIN TRANSACTION;
-- Check ticket availability
SELECT available FROM tickets WHERE seat = "A1" FOR UPDATE;
-- If available
UPDATE tickets SET available = false, user_id = 123 WHERE seat = "A1";
INSERT INTO orders (user_id, seat, price) VALUES (123, "A1", 150);
COMMIT;
Atomicity: Ticket marked sold and order created together, or neither happens.
Consistency: Cannot sell same seat twice (unique constraint).
Isolation: Two buyers cannot grab same seat simultaneously.
Durability: Once confirmed, your ticket is yours even if server crashes.
Microservices make ACID harder. Each service has its own database, so cross-service transactions are challenging.
Solutions:
Or design services to avoid needing distributed transactions.
Always use ACID for:
Can relax for:
Test your transactions:
CAP Theorem: You can have at most two of Consistency, Availability, Partition Tolerance.
Traditional ACID databases choose Consistency over Availability. In a network partition, they refuse requests rather than return potentially incorrect data.
NoSQL BASE systems often choose Availability over strict Consistency.
Watch for:
Good monitoring prevents transaction-related performance issues.
ACID properties are the foundation of reliable databases. They ensure your data stays correct even when things go wrong - and things always go wrong eventually.
For any application where data integrity matters, ACID compliance is not optional. The complexity and performance cost are small compared to the disaster of corrupted data.
Use ACID-compliant databases for important data. Use eventual consistency only when correctness can be relaxed. When in doubt, choose ACID.
Your users trust you with their data. ACID helps you keep that trust.