Transactions — ACID properties (each one in detail)

4.4.16 · Coding › Databases

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 does

WHY do we need this? Kyunki real kaam akele ek statement mein kabhi fit nahi hota, aur duniya hostile hai: power mid-write fail ho sakti hai, do users ek hi row edit kar sakte hain, ek app beech mein exception throw kar sakta hai. Guarantees ke bina, paisa create ya destroy ho jaata hai.

The four letters

Atomicity · Consistency · Isolation · Durability.

Figure — Transactions — ACID properties (each one in detail)

A — Atomicity

WHY it feels obvious but is hard: Alice se ghataana aur Bob ko jodna physically alag disk writes hain. Agar machine dono ke beech mein mar jaaye, toh ₹100 gaayab ho gaya. Atomicity is intermediate state ko kabhi permanent hone se rokta hai.

HOW it's implemented — the undo log: kisi row ko change karne se pehle, DB purani value ko ek undo log mein likhta hai. ROLLBACK par (ya uncommitted transaction ki crash recovery par), engine undo log ko backwards replay karta hai purani values restore karne ke liye.

C — Consistency

WHY this is different from Atomicity: atomicity execution ki completeness ke baare mein hai; consistency rules ki correctness ke baare mein hai. Ek transaction poori tarah complete ho sakta hai (atomic) phir bhi negative balance chhodne ki koshish kar sakta hai — consistency wo contract hai jo kehta hai "agar teri transaction koi rule todegi, toh use reject kiya jaayega (rollback), commit nahi kiya jaayega."

HOW: DB declared constraints enforce karta hai (CHECK (bal >= 0), PRIMARY KEY, FOREIGN KEY) commit/statement time par. Application un invariants ke liye zimmedaar hai jo DB nahin jaanta (jaise "total money constant hai").

I — Isolation

WHY: agar Alice ka transfer aur ek bank-wide "sab balances jodo" report ek saath chalein, toh report Alice ka debit count kar sakti hai lekin Bob ka credit miss kar sakti hai → ₹100 kho gaya lagta hai. Isolation aisi half-states ko padhne se rokta hai.

HOW — isolation levels (kamzor = tez, zyada mazboot = zyada safe):

Level	Dirty read rokta hai	Non-repeatable read rokta hai	Phantom rokta hai
Read Uncommitted	✗	✗	✗
Read Committed	✓	✗	✗
Repeatable Read	✓	✓	✗
Serializable	✓	✓	✓

Teen anomalies:

Dirty read: doosri transaction ka uncommitted data padhna.
Non-repeatable read: ek row dobara padhne par alag value milna (beech mein kisi ne update+commit kiya).
Phantom read: range query dobara chalane par nayi rows milna (kisi ne insert kiya).

D — Durability

WHY the hard part: disk writes speed ke liye RAM mein buffered hote hain. Agar COMMIT data actually non-volatile storage mein jaane se pehle return kar de, toh ek millisecond baad crash ek "successful" payment kho dega.

HOW — Write-Ahead Logging (WAL): rule hai pehle log, baad mein data. COMMIT success return karne se pehle, DB redo log ko durable storage mein flush karta hai (fsync). Actual data pages baad mein lazily likhe ja sakte hain. Restart par, recovery redo log replay karta hai kisi committed changes ko re-apply karne ke liye jo abhi tak data files mein nahi pahunche the.

Steel-manning the classic mistakes

Recall Feynman: explain to a 12-year-old

Socho tum aur tumhara dost cups ke beech ek marble pass kar rahe ho, aur koi badhta-ghata lights on-off kar raha hai (crashes!) aur doosre bachche bhi haath daalne lagte hain (other users!). ACID chaar ghar ke rules hain taaki marble game kabhi galat na ho:

A: ya toh tum puri tarah marble pass karte ho ya nahi karte — kabhi beech mein nahi girata.
C: tumhein kabhi "−1 marble" cup rakhne ki permission nahi hai; yeh rules todata hai, toh hum undo karte hain.
I: jab doosre bachche ek saath khelein, toh lagana chahiye jaise sab ne clean turns liye, chaotic grab nahi.
D: jab tum "done!" keh do, toh woh move permanent ink mein likha hai — lights bhi mar jaayein, phir bhi rahega.

Active recall

BEGIN…COMMIT/ROLLBACK boundary par transaction kya guarantee karta hai?

Saari operations ek saath effect mein aati hain (commit) ya koi bhi nahi aati (rollback) — yeh ek logical unit of work hai.

Atomicity ek phrase mein

All-or-nothing: transaction mein har operation hoti hai, ya koi bhi nahi hoti, kabhi partial state nahi.

Atomicity / rollback implement karne ka mechanism

Undo log — changes se pehle purani values log ki jaati hain taaki reverse kiya ja sake.

Consistency (ACID) ek phrase mein

Ek transaction DB ko ek valid (constraint-satisfying) state se doosri valid state mein le jaata hai.

ACID-Consistency aur CAP-Consistency mein farak kya hai?

ACID-C = ek node par declared integrity rules obey karna; CAP-C = saare distributed nodes same data return karte hain. Unrelated concepts hain.

Isolation ek phrase mein

Concurrent transactions ka result kisi serial (ek-ke-baad-ek) execution ke barabar hota hai.

Dirty read define karo

Doosre transaction ka likha hua data padhna jo abhi commit nahi hua (aur roll back ho sakta hai).

Non-repeatable read define karo

Ek hi row dobara padhne par alag value milna kyunki doosre committed transaction ne usse update kar diya.

Phantom read define karo

Range query dobara chalane par nayi rows milna jo doosre transaction ne insert+commit ki hain.

Kaun sa isolation level teeno anomalies rokta hai?

Serializable.

Read Committed kaun si anomaly rokta hai?

Sirf dirty reads.

Durability ek phrase mein

Ek baar commit hone par, transaction ke effects kisi baad ki crash ya power loss mein survive karte hain.

Durability implement karne ka mechanism

Write-Ahead Logging (WAL): COMMIT return karne se pehle redo log flush karo (fsync); recovery par use replay karo.

Data pages se pehle log kyun likhte hain?

Log commit ka durable proof hai; data pages lazily flush kiye ja sakte hain aur log replay karke reconstruct kiye ja sakte hain.

Atomicity vs Isolation — key difference

Atomicity ek transaction ko crashes se protect karta hai (all-or-nothing); Isolation transactions ko ek doosre se protect karta hai (concurrency).

Har jagah Serializable use karne ka trade-off

Zyada locking/aborts → kam throughput; sabse kamzor level chuno jo abhi bhi tumhare invariants preserve kare.

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)