Two-Phase Locking (2PL)

Concept. Two-Phase Locking (2PL) is a protocol where every transaction has two phases: a growing phase in which it only acquires locks, and a shrinking phase in which it only releases. This guarantees that all conflicts between any two transactions resolve in the same direction.

Intuition. When Mickey's transaction grabs S locks on 5 Listens rows then takes an X lock on his Profile row, all in the growing phase, then releases them all at COMMIT, no other transaction can sneak between his reads and his write. The entire transaction looks atomic to everyone else.

How They Work

Two-Phase Locking (2PL)

Rule: Two distinct phases

Phase Structure:
1. Growing Phase: Acquire locks, no releases
2. Shrinking Phase: Release locks, no acquisitions

Transition: The first unlock marks the switch to the shrinking phase

Two-Phase Locking: locks rise during growing phase, peak, then fall during shrinking phase

Strict 2PL (S2PL)

Rule: Hold all locks until commit/abort

• No shrinking phase during execution
• All locks released atomically at transaction end

Key Benefit: Prevents cascading aborts

Strict 2PL: locks rise during growing phase, plateau, then drop to zero at COMMIT

The Cascading Abort Problem

Kitchen Disaster: The Salad Dressing Cascade

Regular 2PL - The Cascade

Strategy: Release ingredients as soon as done

Timeline:
1. Sous-chef1 starts making vinaigrette dressing
2. Sous-chef1 finishes and releases dressing to shared station
3. Sous-chef2 takes the dressing for Caesar salad
4. Sous-chef2 mixes dressing with lettuce and serves
5. Head chef tastes Sous-chef1's dressing: "Too acidic!"
6. Disaster: Sous-chef1 must redo dressing, BUT Sous-chef2's salad already served with bad dressing!

Result: Both dishes ruined - cascading failure

Strict 2PL - No Cascade

Strategy: Keep everything until approved

Timeline:
1. Sous-chef1 starts making vinaigrette dressing
2. Sous-chef1 finishes but keeps dressing until approved
3. Sous-chef2 wants dressing but waits...
4. Head chef tastes: "Too acidic!"
5. Sous-chef1 fixes dressing privately, then releases
6. Success: Sous-chef2 gets perfect dressing for salad

Result: Only good dressing ever leaves the station

The Point: S2PL ensures others only see your "approved" (committed) work. Like waiting for head chef approval before sharing ingredients. Regular 2PL allows dirty reads via early lock release; S2PL holds locks until transaction end, preventing both dirty reads and the cascade.

2PL and S2PL for Concurrency

+ Benefits

  • Correctness: Prevents data corruption

  • Simplicity: Easy to implement and understand

  • Proven: Used by all major databases

  • Parallelism: Non-conflicting transactions run concurrently

⚠ Costs

  • Blocking: Must wait for conflicting locks

  • Reduced Concurrency: Conservative lock holding

  • Lock Overhead: Managing lock tables and queues

  • Wait Cycles: Transactions may need to wait for each other