Transactions — ACID properties (each one in detail)
WHAT is a transaction?
BEGIN;
UPDATE acct SET bal = bal - 100 WHERE id = 'Alice';
UPDATE acct SET bal = bal + 100 WHERE id = 'Bob';
COMMIT; -- both lines persist together, or neither doesWHY do we need this? Because real work rarely fits in one statement, and the world is hostile: power can fail mid-write, two users can edit the same row, an app can throw an exception halfway. Without guarantees, you get money created or destroyed.
The four letters
Atomicity · Consistency · Isolation · Durability.
A — Atomicity
WHY it feels obvious but is hard: subtracting from Alice and adding to Bob are physically separate disk writes. If the machine dies between them, ₹100 has vanished. Atomicity forbids this intermediate state from ever being permanent.
HOW it's implemented — the undo log: before changing a row, the DB writes the old value to an undo log. On ROLLBACK (or crash recovery for an uncommitted transaction), the engine replays the undo log backwards to restore old values.
C — Consistency
WHY this is different from Atomicity: atomicity is about completeness of execution; consistency is about correctness of rules. A transaction could complete fully (atomic) yet still try to leave a negative balance — consistency is the contract that says "if your transaction would break a rule, it is rejected (rollback), not committed."
HOW: the DB enforces declared constraints (CHECK (bal >= 0), PRIMARY KEY, FOREIGN KEY) at commit/statement time. The application is responsible for invariants the DB can't know (e.g. "total money is constant").
I — Isolation
WHY: if Alice's transfer and a bank-wide "sum all balances" report run at the same time, the report might count Alice's debit but miss Bob's credit → ₹100 looks lost. Isolation prevents reading such half-states.
HOW — isolation levels (weaker = faster, stronger = safer):
| Level | Prevents dirty read | prevents non-repeatable read | prevents phantom |
|---|---|---|---|
| Read Uncommitted | ✗ | ✗ | ✗ |
| Read Committed | ✓ | ✗ | ✗ |
| Repeatable Read | ✓ | ✓ | ✗ |
| Serializable | ✓ | ✓ | ✓ |
The three anomalies:
- Dirty read: reading another transaction's uncommitted data.
- Non-repeatable read: re-reading a row gives a different value (someone updated+committed in between).
- Phantom read: re-running a range query returns new rows (someone inserted).
D — Durability
WHY the hard part: disk writes are buffered in RAM for speed. If COMMIT returned before data actually hit non-volatile storage, a crash one millisecond later would lose a "successful" payment.
HOW — Write-Ahead Logging (WAL): the rule is log first, data later. Before COMMIT returns success, the DB flushes the redo log to durable storage (fsync). The actual data pages can be written lazily afterwards. On restart, recovery replays the redo log to re-apply any committed changes that hadn't reached the data files yet.
Steel-manning the classic mistakes
Recall Feynman: explain to a 12-year-old
Imagine you and your friend are passing a marble between cups, and someone keeps switching the lights on and off (crashes!) and other kids reach in too (other users!). ACID is four house rules so the marble game never goes wrong:
- A: you either fully pass the marble or you don't — never drop it mid-air.
- C: you're never allowed to have a "−1 marble" cup; that breaks the rules, so we undo.
- I: when other kids play at the same time, it must look like everyone took clean turns, not a chaotic grab.
- D: once you say "done!", that move is written in permanent ink — even if the lights die, it stays.
Active recall
What does a transaction guarantee at the boundary BEGIN…COMMIT/ROLLBACK?
Atomicity in one phrase
Mechanism that implements Atomicity / rollback
Consistency (ACID) in one phrase
How does ACID-Consistency differ from CAP-Consistency?
Isolation in one phrase
Define a dirty read
Define a non-repeatable read
Define a phantom read
Which isolation level prevents all three anomalies?
Read Committed prevents which anomaly?
Durability in one phrase
Mechanism that implements Durability
Why log before writing data pages?
Atomicity vs Isolation — key difference
Trade-off of using Serializable everywhere
Connections
- Write-Ahead Logging (WAL)
- Undo and Redo Logs
- Concurrency Control · Locking and 2PL · MVCC
- Isolation Levels
- CAP Theorem (contrast the meaning of "Consistency")
- Database Constraints (CHECK, FK, PK — the enforcers of ACID-C)
- Two-Phase Commit (atomicity across distributed nodes)
Concept Map
Hinglish (regional understanding)
Intuition Hinglish mein samjho
Socho ek transaction ek "ek hi kaam ki packet" hai — jaise Alice se Bob ko ₹100 bhejna. Yeh do steps hain (Alice se minus, Bob ko plus), lekin database ko isse ek hi event ki tarah dikhana hai. ACID yahi guarantee deta hai — chaar promises taaki yeh "ek unit" sach mein ek unit ki tarah behave kare, chahe crash ho jaaye ya hazaar log ek saath kaam kar rahe hon.
Atomicity matlab all-or-nothing: ya dono writes ho ya ek bhi nahi — beech mein paisa gayab nahi hona chahiye. Iske liye undo log use hota hai (purani value pehle save karo, taaki crash pe wapas la sako). Consistency matlab database hamesha valid rules mein rahe — agar balance negative ho raha hai toh poora transaction reject (rollback) ho jaata hai. Dhyaan rahe: ACID ka "C" aur CAP theorem ka "C" bilkul alag cheezein hain.
Isolation ka matlab hai ki jab bahut saare transactions ek saath chalein, toh result aisa lage jaise sab ek-ek karke (serial) chale the. Warna "dirty read", "non-repeatable read", "phantom read" jaise bugs aate hain. Strong isolation (Serializable) safe hai par slow — isliye engineer hamesha sabse weak level chunte hain jo unke invariant ko bacha le. Durability matlab jab COMMIT ho gaya, toh data permanent ho gaya, crash ke baad bhi. Iske liye Write-Ahead Logging (WAL) hota hai: pehle redo log disk pe fsync karo, phir COMMIT ko "OK" bolo; restart pe log replay karke sab wapas aa jaata hai.
Yaad rakho: A = apne crash se bachao, I = doosron se bachao, C = rules todo mat, D = commit ke baad kabhi mat khona. Yahi chaar mil ke database ko bharosemand banate hain.