Locks: Foundations of Concurrent Control
Concept. A lock is a database-managed exclusion primitive: a transaction acquires a lock on a row before reading or writing it, and the lock manager prevents other transactions from acquiring incompatible locks until it's released.
Intuition. When Mickey holds a shared (S) lock on a Listens row to read it, Minnie can also take an S lock and read concurrently. But if Minnie wants an exclusive (X) lock to update the row, the lock manager makes her wait until Mickey releases.
Three Problems Locks Solve
3 Transactions (T1, T2, T3) engaged in stock trading on X, Y accounts
Problem 1: Correctness
Lost Updates
Without Locks:
T1: READ(x)=100
T2: READ(x)=100
T1: WRITE(x=150)
T2: WRITE(x=130)
→ T1's update lost!
With Locks:
T1: LOCK→READ→WRITE→UNLOCK
T2: [waits]→LOCK→READ(150)✓
Problem 2: Long Calculations
CPU Idle During Complex Logic
Serial Execution:
T1: Do 10 - 200,000 operations on data X
T2: Wait... then process data Y
T3: Wait... wait... then process data Z
→ Only 1 CPU core used!
Parallel Execution:
T1: LOCK(X) → Process X
T2: LOCK(Y) → Process Y (parallel!)
T3: LOCK(Z) → Process Z (parallel!)
→ All CPU cores utilized!
Problem 3: IO Lag
CPU Idle During Disk Access
Read Operations:
Rs = Read Start (CPU→Disk)
Re = Read End (Disk→CPU)
Gap = ~5ms of waiting
Write Operations:
Ws = Write Start (CPU→Disk)
We = Write End (Disk confirms)
Gap = ~10ms of waiting
With Locks:
T2 uses CPU during T1's IO gaps
The Lock Cycle: How It Works
3 Transactions (T1, T2, T3) competing for 2 Concert Tickets (seat A, seat B)
Key Insight: T1 and T3 can work simultaneously (different tickets), but T2 must wait for T1.
Legend: Operation Types
Locking Request, Get, Unlock operations
IO Disk reads/writes (Rs→Re, Ws→We)
Logic CPU computation on data
Step-by-Step Lock Lifecycle
During IO gaps (Rs→Re), locks coordinate access between T1, T2, T3:
Locking
1⃣ Request
Transaction asks for lock on concert ticket
T1: REQ_LOCK(Ticket_A, lock)
T3: REQ_LOCK(Ticket_B, lock)
Locking
2⃣ Get
Lock manager checks who gets access
Got: T1 gets lock on Ticket_A
Waiting: T2 waits
IO + Logic
3⃣ Use Data
Read ticket data (IO) then process purchase (Logic)
IO: Rs→Re (fetch ticket price)
Logic: Calculate total, apply discount
Locking
4⃣ Unlock
Release lock so others can buy
T1: UNLOCK(Ticket_A) → T2 can now try
After: We (write confirmed)
Types of Locks: Why We Need Two Kinds
The Opportunity: Reads vs Writes are Different
Key Insight: Reading doesn't change data, writing does. So we can optimize!
Shared Lock (S-lock)
For Reading Data
Real Example: Checking Seat A-15 Availability
T1: S-LOCK(Seat_A15) → READ status = "available"
T2: S-LOCK(Seat_A15) → READ status = "available"
T3: S-LOCK(Seat_A15) → READ status = "available"
+ All can check availability simultaneously!
Everyone can look at the same seat's status at once without interfering.
Exclusive Lock (X-lock)
For Writing Data
Real Example: Purchasing Seat A-15
T1: X-LOCK(Seat_A15) → WRITE status = "sold to T1"
T2: [BLOCKED] Can't even check availability
T3: [BLOCKED] Can't check or buy
Only one can buy (or even look) at a time!
During purchase, no one else can read OR write to prevent seeing partial updates.
Lock Compatibility Matrix
| Shared (S) | Exclusive (X) | |
|---|---|---|
| Shared (S) | + Compatible Many readers OK |
− Conflict No read during write |
| Exclusive (X) | − Conflict No write during read |
− Conflict Only one writer |
Example: Bank Transfer with IO Timing
Key Point: During the Rs→Re and Ws→We gaps, other transactions can acquire locks on different accounts and make progress!