Lesson 7 � Beginner

DBMS: Transactions & ACID

Transaction ek logical unit of work hai jo database mein consistency maintain karta hai. ACID properties ensure karti hain ki transactions reliable hon.

? 20 min✓ Beginner✓ DBMS basics

Transaction hota kya hai?

WHAT

Transaction ek sequence of operations hai jo ek logical unit banata hai. Ya toh sab operations complete ho jaayein ya koi bhi na ho. Bank transfer � debit + credit � dono ya dono nahi.

WHEN

Bank transfer, order placement, inventory update � har critical operation transaction mein hona chahiye. Partial update = data corruption.

WHERE

SQL mein BEGIN, COMMIT, ROLLBACK se transactions control hoti hain. MySQL, PostgreSQL, Oracle � sab transactional databases hain.

# Bank Transfer Example:
BEGIN TRANSACTION;
 UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
 UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;

# Agar beech mein kuch galat ho jaaye:
BEGIN TRANSACTION;
 UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
 -- Server crash!
ROLLBACK; -- Pehla update undo ho jaayega

# Transaction States:
ACTIVE ✓ PARTIALLY COMMITTED ✓ COMMITTED
 ?
FAILED ✓ ABORTED (ROLLBACK)

# Properties:
1. All or Nothing (Atomicity)
2. Consistent state (Consistency)
3. Isolated (Isolation)
4. Permanent (Durability)

ACID Properties

# A - Atomicity
✓ Transaction ya toh poora ho ya kuch bhi na ho
✓ Partial execution nahi chalega
✓ Bank: 1000 debit ho gaye but credit nahi hua = PROBLEM!
✓ Solution: ROLLBACK se undo karo

# C - Consistency
✓ Transaction ke baad database consistent state mein hona chahiye
✓ All constraints satisfied honi chahiye
✓ Bank: Total money same rahe (1000 - 1000 = 0 change)
✓ Check constraints, foreign keys, triggers enforce karte hain

# I - Isolation
✓ Concurrent transactions ek doosre ko affect nahi karein
✓ Ek transaction ka data doosri ko visible nahi hona chahiye (intermediate)
✓ T1: debiting, T2: reading balance � T2 ko purana balance dikhe
✓ Isolation Levels: READ UNCOMMITTED ✓ SERIALIZABLE

# D - Durability
✓ Committed transaction permanently save hoti hai
✓ System crash ke baad bhi data safe rahega
✓ Write-Ahead Logging (WAL) se ensure hota hai
✓ Crash recovery: log se data restore

Mental Model: ACID jaise bank ki guarantee. Atomicity = Paisa dono taraf se katna chahiye ya kahin se nahi. Consistency = Total balance same rahega. Isolation = Do transactions ek doosre ko affect nahi karenge. Durability = Paisa kat gaya toh permanently kat gaya.

Isolation Levels

# Isolation Levels (Low ✓ High):

1. READ UNCOMMITTED
✓ Dirty reads allowed (doosre transaction ka uncommitted data dikhta hai)
✓ Fast but unsafe

2. READ COMMITTED
✓ Only committed data dikhega
✓ Non-repeatable reads possible (same query different results)

3. REPEATABLE READ
✓ Same query same results degi (within transaction)
✓ Phantom reads possible (new rows appear)

4. SERIALIZABLE
✓ Sabse strict � transactions serially execute honge
✓ Sab problems solved but SLOW

# Trade-off:
High Isolation ✓ Low Concurrency (slow)
Low Isolation ✓ High Concurrency (but data issues)

# Real World:
- Banking: SERIALIZABLE (critical data)
- Social Media: READ COMMITTED (acceptable)
- Analytics: READ UNCOMMITTED (speed over accuracy)

Exercise

Question: ACID mein A kya represent karta hai✓ Transaction ka kya guarantee deta hai? (1-2 words)

Question: ACID mein D kya hai � committed data kya hota hai? (1 word)

Common mistakes

ACID rattne ke liye: 4 properties samjho with examples. Ratne se kaam nahi chalega � interview mein examples poochte hain.
Isolation levels confusion: Har level ka specific problem solve karta hai. Dirty read, non-repeatable read, phantom read � alag cheezein hain.
ROLLBACK skip: Transaction mein error aane pe ROLLBACK zaroor karo. Partial update se data corruption hota hai.
Durability ignore: Committed data safe hai � ye database ki guarantee hai. WAL (Write-Ahead Logging) se ensure hota hai.

Lesson complete?

ACID samajh aa gaya✓ Ab Normalization seekhte hain � database design kaise optimize karte hain.