## What is ACID?
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.
## The Four Properties Explained
### Atomicity (All or Nothing)
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:
1. Subtract $100 from Account A
2. Add $100 to Account B
If step 2 fails, step 1 must be rolled back. You cannot have money disappear. Atomicity guarantees this.
```sql
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
```
### Consistency (Valid State Always)
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.
### Isolation (Transactions Do Not Interfere)
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:
- Person 1 reads balance: $500
- Person 2 reads balance: $500
- Person 1 withdraws $300 (balance should be $200)
- Person 2 withdraws $300 (balance should be -$100, but they read $500!)
- Final balance: $200 or -$100 depending on order
With isolation:
- Transactions execute sequentially or with proper locking
- Final balance: Correct based on actual order
### Durability (Permanent Once Committed)
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.
## Why ACID Matters
**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.
## ACID in Action
```sql
-- 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:
- **Atomicity**: Both updates happen or neither does
- **Consistency**: Balance constraints are checked
- **Isolation**: Other transactions do not see intermediate state
- **Durability**: Committed changes survive crashes
## ACID vs BASE
**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.
## Databases That Provide ACID
**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.
## Isolation Levels
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.
```sql
-- Set isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- Your queries here
COMMIT;
```
## The Cost of ACID
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.
## Transactions in Code
Most ORMs and database libraries handle transactions:
```javascript
// 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.
## Common Transaction Mistakes
**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.
## Real-World Example
Online ticket sales for a concert:
```sql
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.
## ACID and Microservices
Microservices make ACID harder. Each service has its own database, so cross-service transactions are challenging.
Solutions:
- **Saga Pattern**: Coordinate transactions across services with compensating actions
- **Two-Phase Commit**: Distributed transaction protocol (complex, slow)
- **Event Sourcing**: Store events instead of state, rebuild from events
Or design services to avoid needing distributed transactions.
## When You Need ACID
**Always use ACID for**:
- Financial transactions
- Inventory management
- User authentication
- Order processing
- Medical records
- Legal documents
**Can relax for**:
- Analytics data
- Logs
- Caching
- Social media interactions
- View counts
## Testing ACID Properties
Test your transactions:
- Simulate crashes mid-transaction
- Run concurrent transactions
- Verify rollback behavior
- Check constraint enforcement
- Test recovery from failures
## The CAP Theorem Connection
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.
## Monitoring Transactions
Watch for:
- Long-running transactions (block others)
- High rollback rates (indicates problems)
- Deadlocks (transactions waiting on each other)
- Lock contention (multiple transactions fighting for same resources)
Good monitoring prevents transaction-related performance issues.
## The Bottom Line
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.
