Granularities
- Database level (Slightly Rare)
- Table level (Very Common)
- Page level (Common)
- Tuple level (Very Common)
- Attribute level (Rare)
Intention Locks
if a transaction is using tuple level locks, it needs to communicate that no other transaction can grab a page-level lock (or anything higher) since that would conflict. To facilitate this, intention locks are implicit locks that signal that there are explicit locks held at lower levels.
- Intention Shared (IS): Indicates explicit locking at a lower level with shared locks.
- Intention Exclusive (IX): Indicates explicit locking at a lower level with exclusive or shared locks.
- Shared + Intention Exclusive (SIX): The subtree rooted at that node is locked explicitly in shared mode, and explicit locking is being done at a lower level with exclusive-mode locks.
| Compatibility | IS | IX | S | SIX | X |
|---|---|---|---|---|---|
| IS | ✅ | ✅ | ❌ | ✅ | ❌ |
| IX | ✅ | ✅ | ❌ | ❌ | ❌ |
| S | ✅ | ❌ | ✅ | ❌ | ❌ |
| SIX | ❌ | ❌ | ❌ | ✅ | ❌ |
| X | ❌ | ❌ | ❌ | ❌ | ❌ |
Locking Protocol
To get S or IS lock on a node, the txn must hold at least IS on the parent node. To get X, IX, or SIX on a node, must hold at least IX on parent node.
Index Locking
- Key-Value Locks
- Locks that cover a single key-value in an index.
- Need “virtual keys” for non-existent values.
- Gap Locks
- Key-Range Locks
- A txn takes locks on ranges in the key space.
- Hierarchical Locking