#9. ACID Transactions Explained - Consistency vs Performance
The One Thing to Remember
ACID ensures your database doesn't lie to you. Atomicity (all or nothing), Consistency (rules enforced), Isolation (concurrent users don't interfere), Durability (committed = permanent). The trade-off: stronger guarantees = lower performance.
Building on Article 8
In Article 8: Database Indexes, you learned how databases find data efficiently. But here's the question: How do databases guarantee that your data stays correct when multiple users are modifying it simultaneously?
Understanding ACID helps you understand why your database doesn't lose money during transfers, even when things go wrong.
← Previous: Article 8 - Database Indexes
Why This Matters (A True Horror Story)
I once debugged an e-commerce system that was overselling inventory. Two users would check stock simultaneously, both see "1 left", both buy it. Result: Negative inventory, angry customers. The fix? Wrap the check-and-update in a transaction with proper isolation. Two lines of code.
This isn't academic knowledge—it's the difference between:
-
Losing money vs keeping it safe
- Understanding ACID = you know transactions are atomic
- Not understanding = you think data is safe, but partial updates can happen
-
Debugging data corruption in hours vs days
- Understanding isolation = you know what anomalies to look for
- Not understanding = you blame the application, the network, everything
-
Choosing the right isolation level
- Understanding trade-offs = you pick the right level
- Not understanding = you use SERIALIZABLE everywhere, performance suffers
The Four Properties
┌─────────────────────────────────────────────────────────────────┐
│ ACID │
├─────────────────────────────────────────────────────────────────┤
│ │
│ A - ATOMICITY │
│ ═══════════════ │
│ "All or Nothing" │
│ │
│ BEGIN TRANSACTION │
│ UPDATE accounts SET balance = balance - 100 WHERE id = 1; │
│ UPDATE accounts SET balance = balance + 100 WHERE id = 2; │
│ COMMIT │
│ │
│ Either BOTH happen or NEITHER happens. │
│ No partial transfers! │
│ │
├─────────────────────────────────────────────────────────────────┤
│ │
│ C - CONSISTENCY │
│ ═══════════════ │
│ "Rules are always enforced" │
│ │
│ Constraints: │
│ • balance >= 0 (CHECK constraint) │
│ • email must be unique (UNIQUE constraint) │
│ • user_id must exist (FOREIGN KEY) │
│ │
│ Transaction moves DB from one valid state to another. │
│ │
├─────────────────────────────────────────────────────────────────┤
│ │
│ I - ISOLATION │
│ ═════════════ │
│ "Transactions don't see each other's uncommitted changes" │
│ │
│ User A: Reading balance │
│ User B: Transferring money │
│ │
│ User A sees EITHER: │
│ • Balance before transfer (B not committed yet) │
│ • Balance after transfer (B committed) │
│ NEVER: Balance during transfer │
│ │
├─────────────────────────────────────────────────────────────────┤
│ │
│ D - DURABILITY │
│ ═════════════ │
│ "Committed = Permanent" │
│ │
│ Once COMMIT returns successfully: │
│ • Power loss? Data survives. │
│ • Crash? Data survives. │
│ • Disk failure? (with replication) Data survives. │
│ │
│ Implemented via: Write-ahead logging (WAL) │
│ │
└─────────────────────────────────────────────────────────────────┘
Atomicity: The Bank Transfer Problem
WITHOUT ATOMICITY:
══════════════════
1. UPDATE accounts SET balance = balance - 100 WHERE id = 1;
✓ Success: Alice now has $100 less
>>> CRASH! Power failure! <<<
2. UPDATE accounts SET balance = balance + 100 WHERE id = 2;
✗ Never executed
Result: $100 vanished! Alice lost money, Bob got nothing.
WITH ATOMICITY:
═══════════════
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
>>> CRASH! Power failure! <<<
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Never reached
Result: ENTIRE transaction rolled back
Alice's balance unchanged
No money lost!
How Atomicity Works: Write-Ahead Logging
┌─────────────────────────────────────────────────────────────────┐
│ WRITE-AHEAD LOG (WAL) │
├─────────────────────────────────────────────────────────────────┤
│ │
│ BEFORE writing to data files, write to log: │
│ │
│ 1. Log: "TX 1: BEGIN" │
│ 2. Log: "TX 1: UPDATE accounts SET balance=900 WHERE id=1" │
│ 3. Log: "TX 1: UPDATE accounts SET balance=1100 WHERE id=2" │
│ 4. Log: "TX 1: COMMIT" │
│ 5. Actually update data files │
│ │
│ On crash recovery: │
│ • Find incomplete transactions in log │
│ • REDO committed transactions │
│ • UNDO uncommitted transactions │
│ │
└─────────────────────────────────────────────────────────────────┘
This is why databases use WAL—it ensures atomicity even during crashes. The log is written first (with fsync!), then data files are updated.
Isolation: The Dirty Read Problem
Without Proper Isolation
TIME USER A (Reading) USER B (Writing)
════ ═════════════════ ═════════════════
1 BEGIN
2 UPDATE balance = 0 WHERE id=1
3 SELECT balance WHERE id=1
→ Returns 0 (WRONG!)
4 ROLLBACK (oops, error!)
5 Uses balance=0 in calculation
→ CORRUPT DATA!
User A read data that was never committed = "Dirty Read"
Isolation Levels (Trade-off Spectrum)
WEAKER (Faster) STRONGER (Safer)
│ │
▼ ▼
┌──────────────┬───────────────┬───────────────┬──────────────┐
│ READ │ READ │ REPEATABLE │ SERIALIZABLE │
│ UNCOMMITTED │ COMMITTED │ READ │ │
├──────────────┼───────────────┼───────────────┼──────────────┤
│ Dirty reads │ ✗ Prevented │ ✗ Prevented │ ✗ Prevented │
│ possible │ │ │ │
├──────────────┼───────────────┼───────────────┼──────────────┤
│ Non-repeat- │ Possible │ ✗ Prevented │ ✗ Prevented │
│ able reads │ │ │ │
├──────────────┼───────────────┼───────────────┼──────────────┤
│ Phantom │ Possible │ Possible* │ ✗ Prevented │
│ reads │ │ │ │
├──────────────┼───────────────┼───────────────┼──────────────┤
│ Performance │ Best │ Good │ Good │Worst│
└──────────────┴───────────────┴───────────────┴──────────────┘
* PostgreSQL's REPEATABLE READ actually prevents phantoms too
Typical overhead: READ COMMITTED → SERIALIZABLE: 20-50% throughput reduction (varies greatly by workload).
Common Mistakes (I've Made These)
Mistake #1: "Autocommit is fine for everything"
Why it's wrong: Autocommit means each statement is its own transaction. If the second statement fails, the first is already committed. I've lost data because of this.
# BAD: Each statement is its own transaction
conn.autocommit = True
cursor.execute("UPDATE balance = balance - 100 WHERE id=1")
cursor.execute("UPDATE balance = balance + 100 WHERE id=2")
# If second fails, first is already committed!
# GOOD: Explicit transaction
conn.autocommit = False
try:
cursor.execute("UPDATE balance = balance - 100 WHERE id=1")
cursor.execute("UPDATE balance = balance + 100 WHERE id=2")
conn.commit()
except:
conn.rollback()
raise
Mistake #2: "Long-running transactions are fine"
Why it's wrong: Long transactions hold locks and put pressure on storage engine caches. MongoDB recommends breaking long transactions into smaller parts and enforces a 60-second default timeout.
Right approach: Minimize transaction scope. Don't hold transactions while doing slow operations (API calls, file I/O).
Mistake #3: "SERIALIZABLE is always better"
Why it's wrong: SERIALIZABLE has significant overhead (20-50% throughput reduction). For most applications, READ COMMITTED or REPEATABLE READ is sufficient.
Right approach: Use SERIALIZABLE only when perfect correctness is required (financial transactions, inventory management). For most apps, READ COMMITTED is fine.
Trade-offs: Choosing Isolation Level
When to Use Each Level
READ UNCOMMITTED:
- Almost never. Maybe for rough analytics on non-critical data.
- Risk: All anomalies possible.
READ COMMITTED (PostgreSQL default):
- ✅ Good for: Most OLTP applications
- ✅ Good for: When you can tolerate non-repeatable reads
- ❌ Bad for: Long-running reports (data may shift)
- Example: Web app where each request is a quick transaction
REPEATABLE READ:
- ✅ Good for: Reports that need consistent snapshot
- ✅ Good for: When you re-read data in same transaction
- ❌ Bad for: High contention (more conflicts)
- Example: End-of-day financial report
SERIALIZABLE:
- ✅ Good for: Financial transactions
- ✅ Good for: Inventory management (can't oversell)
- ❌ Bad for: High throughput (significant overhead)
- Example: Banking transfers, stock trading
Performance Impact
| Isolation Level | Throughput | Conflicts | Use Case |
|---|---|---|---|
| READ UNCOMMITTED | Highest | None | Analytics |
| READ COMMITTED | High | Rare | Most apps |
| REPEATABLE READ | Medium | Some | Reports |
| SERIALIZABLE | Lowest | Common | Financial |
Real-World Trade-off Stories
MongoDB: Long-Running Transactions and Cache Pressure
Situation: Extended transaction duration puts heavy pressure on storage engine caches, as they must maintain state for all writes since the oldest snapshot.
The problem:
- Long-running transactions accumulate writes in cache
- These writes cannot be flushed until old snapshots commit
- This can degrade overall database performance
- Cache exhaustion can occur if transactions run too long
MongoDB's solution:
- Break long transactions into smaller parts
- Enforce a 60-second default timeout to maintain predictable performance at scale
- Optimize query patterns with proper indexing to reduce transaction duration
References:
Lesson: Long-running transactions are expensive. Keep transactions short. If you need to process large datasets, break them into smaller transactions.
The Double-Spend Problem (E-commerce Inventory)
Situation: E-commerce site overselling inventory. Two users check stock simultaneously, both see "1 left", both buy it.
Code (broken):
stock = db.query("SELECT stock FROM products WHERE id=1")
if stock > 0:
db.execute("UPDATE products SET stock = stock - 1 WHERE id=1")
create_order()
Problem: Race condition. Two transactions both read stock=1, both decrement.
Fix 1: SELECT FOR UPDATE (pessimistic)
BEGIN;
SELECT stock FROM products WHERE id=1 FOR UPDATE; -- Lock row
-- Check and update
COMMIT;
Fix 2: Optimistic Locking
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5; -- Fails if version changed
-- Check affected rows, retry if 0
Fix 3: SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Normal queries, DB handles conflicts
-- Retry on serialization failure
Lesson: For inventory management, you need proper isolation. SERIALIZABLE with retry is often the simplest solution, even with the performance cost.
PostgreSQL vs MySQL Defaults (The Surprise)
PostgreSQL default: READ COMMITTED
MySQL/InnoDB default: REPEATABLE READ
Why it matters:
- Same code, different databases, different behavior!
- MySQL apps migrated to PostgreSQL may see anomalies they never saw before
- You might get non-repeatable reads in PostgreSQL that you didn't get in MySQL
Real example: I once saw a report that showed different numbers when run twice in PostgreSQL, but was consistent in MySQL. The difference? Default isolation levels.
Lesson: Always explicitly set isolation level for critical transactions. Don't rely on defaults—they differ between databases.
Durability: The fsync Trade-off
DURABILITY SETTINGS SPECTRUM:
════════════════════════════
MAXIMUM DURABILITY MAXIMUM SPEED
│ │
▼ ▼
┌──────────────────┬──────────────────┬──────────────────┐
│ fsync + WAL │ WAL only │ No WAL │
│ every commit │ (async fsync) │ (in-memory) │
├──────────────────┼──────────────────┼──────────────────┤
│ ~1000 TPS │ ~10,000 TPS │ ~100,000 TPS │
├──────────────────┼──────────────────┼──────────────────┤
│ Zero data loss │ Lose ~1 sec │ Lose everything │
│ │ on crash │ on crash │
├──────────────────┼──────────────────┼──────────────────┤
│ Banking │ Most apps │ Cache only │
└──────────────────┴──────────────────┴──────────────────┘
PostgreSQL Durability Settings
-- Maximum durability (default)
SET synchronous_commit = on;
-- Faster, but might lose last few transactions
SET synchronous_commit = off;
-- Check current setting
SHOW synchronous_commit;
This connects to what you learned in Article 3 (File I/O)—synchronous_commit = on means PostgreSQL calls fsync() on every commit, ensuring durability but adding latency.
Code Examples
Basic Transaction
import psycopg2
def transfer_money(from_account, to_account, amount):
conn = psycopg2.connect(database="bank")
try:
with conn.cursor() as cur:
# Start transaction (implicit with autocommit=False)
# Check balance (with lock to prevent concurrent modification)
cur.execute(
"SELECT balance FROM accounts WHERE id = %s FOR UPDATE",
(from_account,)
)
balance = cur.fetchone()[0]
if balance < amount:
raise ValueError("Insufficient funds")
# Debit
cur.execute(
"UPDATE accounts SET balance = balance - %s WHERE id = %s",
(amount, from_account)
)
# Credit
cur.execute(
"UPDATE accounts SET balance = balance + %s WHERE id = %s",
(amount, to_account)
)
# Commit - all or nothing
conn.commit()
except Exception as e:
conn.rollback() # Undo everything
raise
finally:
conn.close()
Handling Serialization Failures
import psycopg2
from psycopg2 import errors
import time
def execute_with_retry(operation, max_retries=3):
"""
SERIALIZABLE transactions may fail due to conflicts.
Retry is the standard pattern.
"""
for attempt in range(max_retries):
try:
return operation()
except errors.SerializationFailure:
if attempt < max_retries - 1:
# Exponential backoff
time.sleep(0.1 * (2 ** attempt))
continue
raise
Decision Framework
□ What happens if transaction partially completes?
→ Catastrophic: Use explicit transactions
→ OK: Autocommit might be fine
□ Do you re-read data within the transaction?
→ Yes: Use REPEATABLE READ or higher
→ No: READ COMMITTED is fine
□ Is perfect correctness required?
→ Yes (finance, inventory): SERIALIZABLE
→ No: Lower isolation is fine
□ What's your throughput requirement?
→ Very high: READ COMMITTED, optimize conflicts
→ Moderate: REPEATABLE READ acceptable
→ Lower OK: SERIALIZABLE works
□ Can you retry on failure?
→ Yes: SERIALIZABLE with retry is powerful
→ No: Use pessimistic locking (SELECT FOR UPDATE)
Memory Trick
"ACID = A Careful Insurance Deal"
- Atomic: All or nothing (no partial deals)
- Careful: Consistency (rules enforced)
- Insurance: Isolation (protected from others)
- Deal sealed: Durability (permanent once committed)
Self-Assessment
Before moving on:
- [ ] Can you explain what happens during a crash with WAL?
- [ ] Know the difference between REPEATABLE READ and SERIALIZABLE?
- [ ] Understand when SELECT FOR UPDATE is needed?
- [ ] Can identify dirty read, non-repeatable read, phantom read?
- [ ] Know your database's default isolation level?
Key Takeaways
- Atomicity: All or nothing via write-ahead logging (WAL)
- Consistency: Constraints enforced at commit time
- Isolation: Trade-off between safety and performance (20-50% overhead for SERIALIZABLE)
- Durability: fsync ensures committed data survives crashes (connects to Article 3!)
- Know your defaults: PostgreSQL (READ COMMITTED) vs MySQL (REPEATABLE READ) differ
- Handle conflicts: Retry is the standard pattern for SERIALIZABLE
- Keep transactions short: Long transactions cause cache pressure and lock contention
What's Next
Now that you understand ACID, the next question is: What are the different isolation levels, and what anomalies does each prevent?
In the next article, Isolation Levels & Anomalies - Safety vs Concurrency, you'll learn:
- What dirty reads, non-repeatable reads, and phantom reads actually are
- When each isolation level prevents which anomalies
- Real-world examples of each anomaly
- How to choose the right isolation level for your use case
This builds on what you learned here—isolation is the "I" in ACID, and understanding the trade-offs helps you choose the right level.
→ Continue to Article 10: Isolation Levels & Anomalies
This article is part of the Backend Engineering Mastery series. ACID knowledge is essential for data correctness.