Chapter 13: Advanced Concurrency Control
Metadata Card
Dimension Value Difficulty (Advanced) Prerequisites Chapter 11 (ACID), Chapter 12 (MVCC) Keywords Two-Phase Locking (2PL), deadlock, deadlock detection, deadlock prevention, optimistic concurrency control, timestamp ordering, OCC, validation Code Language Python (simulation)
Your Progress
"More complex warehouse coordination rules: two-phase locks, optimistic locks, deadlock resolution."
Two-Phase Locking (2PL)
Transactions go through two phases:
- Expanding/Growing Phase: Acquire locks, cannot release any
- Shrinking Phase: Release locks, cannot acquire any
Strict 2PL (most common): All exclusive locks held until transaction commits/aborts. Prevents cascading aborts.
Rigorous 2PL: Both shared and exclusive locks held until commit.
Deadlocks
A deadlock occurs when two transactions each hold locks the other needs:
T1: LOCK(A) → LOCK(B) → ... waiting for B
T2: LOCK(B) → LOCK(A) → ... waiting for ADetection: Waits-for graph. Periodically check for cycles. If a cycle exists, abort one transaction (the victim).
Prevention: Assign priorities. Lower-priority transaction aborts when conflict arises. Or use wait-die / wound-wait schemes.
| Scheme | Older Transaction | Younger Transaction |
|---|---|---|
| Wait-Die | Waits for younger | Aborts (dies) and retries |
| Wound-Wait | Aborts younger (wounds) | Waits for older |
Optimistic Concurrency Control (OCC)
Assumes conflicts are rare. Don't lock — validate at commit time.
Three phases:
- Read phase: Execute transaction in private workspace (copy of data)
- Validation phase: Check if any conflicts occurred (concurrent writes)
- Write phase: If valid, apply changes; if invalid, abort
Validation checks:
- Timestamp of transaction's read set hasn't changed
- No overlapping write sets with concurrent transactions
Best for: Low-contention workloads, read-mostly scenarios.
Timestamp Ordering (TO)
Assign each transaction a unique timestamp (monotonically increasing). Schedule operations in timestamp order.
Basic TO: If a read arrives "too late" (target has been written by a newer transaction), abort.
Multiversion TO (MVOCC): Combines MVCC with optimistic concurrency control. Uses timestamps for validation.
Serializable Snapshot Isolation (SSI)
Detects read-write conflicts between concurrent snapshots. If a cycle is detected in the serialization graph, the transaction must abort.
This achieves full serializability while allowing MVCC-style non-blocking reads.
Traveler's Notes
Concurrency control is where theory meets real-world complexity. The choice between locking, OCC, and MVCC depends on your workload: contention level, read/write ratio, and latency requirements. Understanding the trade-offs helps you configure your database correctly and troubleshoot performance issues under high concurrency.