Intent Locks and Lock Hierarchies
You'll learn how databases use intent locks to efficiently coordinate locking at different levels of granularity without checking every row.
The Coordination Problem
Imagine you want to lock an entire table for a large update. Before doing so, the database needs to check that no individual rows are already locked by other transactions. Without a smart approach, this would require scanning every single row — prohibitively expensive for large tables.
What Are Intent Locks?
Intent locks are lightweight locks placed at higher levels of a hierarchy (like tables or pages) to signal that finer-grained locks (like row locks) exist or are needed below. They act as bookmarks that say "someone is working on data underneath me."
The hierarchy typically looks like:
- Database → Table → Page → Row
When you lock a row exclusively, the database first places intent-exclusive (IX) locks on the table and page above it. This way, if another transaction wants to lock the entire table, it can immediately see the intent lock and know "wait, someone has row locks inside" — without scanning all rows.
Common Intent Lock Types
- IS (Intent Shared): Indicates shared locks exist at lower levels
- IX (Intent Exclusive): Indicates exclusive locks exist at lower levels
- SIX (Shared with Intent Exclusive): A shared lock on the container with intent to modify specific rows
These work alongside the shared and exclusive locks you learned earlier, creating a compatibility matrix that prevents conflicts efficiently.
Key Takeaway:
Intent locks enable databases to coordinate locking across different granularities (row, page, table) efficiently by marking higher-level resources, avoiding expensive scans to detect lock conflicts.