Course contents
On this page
Lesson plan 20: Lock-based concurrency control
Explain two-phase locking, lock compatibility, granularity, and predicate protection.
Placement: Part 4: Transactions, concurrency, and recovery.
Prerequisites: Lesson 19: Atomic transactions and logging, plus the earlier dependency chain.
Learning outcomes
- Explain two-phase locking, lock compatibility, granularity, and predicate protection.
- Construct and resolve a wait-for cycle while preserving transaction correctness.
- Connect hot-key lock occupancy to throughput and fairness rather than assuming more workers remove serialization.
- Separate a general mechanism, a documented implementation choice, and an illustrative numerical assumption.
Teaching sequence
- Establish the motivating situation: Two transfers lock the same pair of accounts in opposite orders and cannot progress.
- Introduce the vocabulary and the correctness obligation before an optimization. Define two-phase locking, granularity, predicate protection.
- Develop the mechanism: Build the wait-for cycle, compare victim abort with ordered acquisition, and explain phantom protection.
- Follow the complete worked example: A hot record held 4 ms limits a fully serialized path to at most 250 completions/s. Explain the assumptions, intermediate units, and limiting interpretation.
- Extend the scenario to changed load, skew, or failure, explaining deadlocks, prevention, fairness.
- Connect the mechanism to a documented implementation and an alternative with a different workload fit. Close by stating the remaining cost and the next lesson dependency.
Required chapter material
Use a complete execution trace or worked calculation, supported by an annotated static diagram or table. Include language-neutral pseudocode or labeled SQL when it clarifies the mechanism. State preconditions, ownership, visibility, and failure boundaries for algorithms. Describe operational costs as well as the normal path. All questions raised in the narrative must be resolved in the text.
Visual explanation
A wait-for graph exposes a deadlock; a lock timeline distinguishes lock acquisition, the lock point, and release under the chosen locking discipline. The accompanying prose explains the arrows, state changes, or table entries so the lesson remains understandable in a plain Markdown reader.
Editorial checks
Confirm that the numerical example agrees with its units and assumptions. Check that a faster path preserves the stated contract, or explicitly identify the changed guarantee. Keep hypothetical figures separate from product measurements. Explain both an appropriate use and a limitation of the case design. Check terminology against the glossary and links against the course index.