• 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.

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