DBMS: Locks & Concurrency

# Problem: Concurrent Transactions

T1: Read(A) ✓ A = 1000
T2: Read(A) ✓ A = 1000
T1: Write(A, A-200) ✓ A = 800
T2: Write(A, A-100) ✓ A = 900 (OVERWRITES T1!)

# T1 ka update lost! Expected: A = 700, Got: A = 900

# Issues:
1. Lost Update � T1 ka change T2 ne overwrite kiya
2. Dirty Read � T2 ne T1 ka uncommitted data padha
3. Non-Repeatable Read � T1 ne padha, T2 ne change kiya, T1 ne phir padha � alag result
4. Phantom Read � T1 ne query ki, T2 ne naya row add kiya, T1 ne phir query ki � naya row dikha

# Solution: LOCKS
✓ Data pe lock lagao, jab tak transaction complete na ho doosri access na kare

# 1. Shared Lock (S-Lock) / Read Lock
✓ Multiple transactions ek saath READ kar sakte hain
✓ But WRITE nahi kar sakte
✓ T1: S-Lock(A) ✓ T2: S-Lock(A) ✓ OK
✓ T1: S-Lock(A) ✓ T2: X-Lock(A) ✓ BLOCKED

# 2. Exclusive Lock (X-Lock) / Write Lock
✓ Sirf ek transaction access kar sake (READ + WRITE)
✓ T1: X-Lock(A) ✓ T2: S-Lock(A) ✓ BLOCKED
✓ T1: X-Lock(A) ✓ T2: X-Lock(A) ✓ BLOCKED

# Lock Compatibility Matrix:
 S-Lock X-Lock
S-Lock ✓ YES ✓ NO
X-Lock ✓ NO ✓ NO

# Two-Phase Locking (2PL):
✓ Growing Phase: Locks acquire karte jao (release mat karo)
✓ Shrinking Phase: Locks release karte jao (acquire mat karo)

# Strict 2PL:
✓ Saare locks transaction end tak rakho
✓ Prevents cascading rollback
✓ Most databases use this

# DBMS Deadlock Example:

T1: X-Lock(A) ✓ Waiting for X-Lock(B)
T2: X-Lock(B) ✓ Waiting for X-Lock(A)

✓ Circular wait! Dono kabhi complete nahi honge.

# Deadlock Handling:

# 1. Prevention
✓ Resource ordering: Har resource ko number do
✓ T1 aur T2 dono pehle A lock karein, phir B
✓ Circular wait impossible!

# 2. Detection
✓ Wait-for graph banao
✓ Cycle detect karo
✓ Ek transaction rollback karo (victim selection)

# 3. Timeout
✓ Lock timeout (e.g., 5 seconds)
✓ Timeout ✓ Transaction rollback
✓ Simple but can cause unnecessary rollback

# 4. Wait-Die / Wound-Wait
✓ Timestamp based � older transaction ko priority
✓ Wait-Die: Younger wait kare, Older die (rollback)
✓ Wound-Wait: Older wound (rollback younger), Younger wait

# Problem: Locks kam karo for better concurrency
# Solution: MVCC � Har transaction ko snapshot dikhao

# How MVCC Works:
1. Har row ke multiple versions hote hain (timestamp ke saath)
2. Transaction ko uske start time ka snapshot dikhta hai
3. Reads ke liye lock ki zaroorat nahi
4. Writes ke liye sirf conflicting rows pe lock

# Benefits:
✓ Reads block nahi hote (non-blocking reads)
✓ Better concurrency (multiple readers + 1 writer)
✓ Snapshot isolation (consistent reads)

# Implementations:
- PostgreSQL: MVCC with vacuum for cleanup
- MySQL InnoDB: MVCC with undo logs
- Oracle: MVCC with rollback segments

# Trade-off:
✓ Better read performance
✓ Write overhead (multiple versions maintain)
✓ Storage overhead (old versions store)
✓ Vacuum/cleanup needed