Transactions and ACID Properties: Complete Beginner's Guide

A transaction is a bundle of database operations that must succeed or fail as one unit — the classic example is transferring money between accounts. The ACID properties are the four guarantees making transactions trustworthy: Atomicity, Consistency, Isolation, Durability.

They matter because real systems crash mid-work: power fails between debiting one account and crediting another, or two users grab the last train seat simultaneously. Without ACID, databases would silently lose money and double-book seats. With it, a multi-step operation behaves as one reliable step.

Key Concepts

• Atomicity: all or nothing — every operation happens, or none does. No half-finished transfers.
• Consistency: each transaction moves the database from one valid state to another, preserving every constraint.
• Isolation: concurrent transactions behave as if they ran one after another.
• Durability: once committed, effects survive crashes — written to permanent storage first.
• COMMIT / ROLLBACK: make changes permanent, or undo everything since the transaction began.
• Write-ahead log (WAL): every change is logged to disk before touching data pages, so crashes can be replayed or undone.

How It Works

A transaction runs in a private workspace tracked by the write-ahead log. On COMMIT, the log is flushed to disk first and changes become visible — durability. On any failure — constraint violation, crash, or explicit ROLLBACK — the log undoes the partial changes, restoring the exact pre-transaction state — atomicity. Isolation comes from concurrency control (locking or multiversioning), which schedules overlapping transactions so the final effect matches some serial order.

Worked Example

Transfer ₹5,000 from A (₹20,000) to B (₹8,000):

1. BEGIN. Read A = 20000, B = 8000. Check: 20000 − 5000 = 15000 ≥ 0 — allowed.
2. Debit A: 20000 − 5000 = 15000. Credit B: 8000 + 5000 = 13000. Total: 15000 + 13000 = 28000 — exactly the starting total, so money is conserved.
3. Crash after the debit: no COMMIT in the log, so recovery rolls back — A restored 15000 → 20000, B stays 8000. Nothing lost, nothing gained.
4. Success: COMMIT flushes the log. Even a crash one second later replays it: balances stay 15000 and 13000.
5. Isolation: a concurrent reader sees either (20000, 8000) or (15000, 13000) — never the impossible (15000, 8000) where money vanishes.

Common Misconceptions

• "COMMIT makes data instantly safe." It makes it logically permanent, but durability needs the log on disk. Synchronous commit is safer and slower; asynchronous is faster with a small risk window.
• "ACID means transactions never fail." They fail often — deadlocks, constraint violations, timeouts. ACID guarantees they fail cleanly, leaving a valid state.
• "Isolation means one-at-a-time execution." Transactions run concurrently for speed; isolation only guarantees the result looks serial.

Real-World Applications

• Banking: every transfer, loan disbursal, and ATM withdrawal is an ACID transaction.
• E-commerce: ordering debits stock, charges payment, and creates the order atomically.
• Ticket booking: the last seat is reserved inside a transaction so two users cannot both succeed.
• Phone apps: SQLite uses ACID transactions — that is why notes survive app crashes.

ACID is the database's contract: atomicity promises no half-done work, consistency keeps the rules intact, isolation hides concurrent mess, durability survives crashes. Whenever money, stock, or seats are on the line, wrap the steps in a transaction and let the database keep that promise.