The Problem They Solve
If one transaction holds row locks and another wants a table lock, the table lock must know whether any row inside is locked. Scanning every row would be slow. Intention locks announce the plan at the table level so conflicts are detected cheaply.
The Lock Modes
- Intention Shared (IS): the transaction intends to take shared locks on some rows below.
- Intention Exclusive (IX): the transaction intends to take exclusive locks on some rows below.
- Before locking a row in shared mode, a transaction first takes IS on the table.
- Before locking a row in exclusive mode, it first takes IX on the table.
How Conflicts Resolve
A transaction wanting a full table exclusive (X) lock checks the table level. If it sees an IX or IS held by another transaction, it knows rows below are locked and waits, without scanning a single row. Intention locks are compatible with each other, so many row level transactions coexist freely.
The Compatibility Idea
Intention locks do not lock data themselves. They are flags at higher levels of the hierarchy that summarize what is happening below.
Key idea
Intention locks flag at the table level that rows below are locked, letting coarse and fine locks coexist without scanning every row.