What Granularity Means
A lock can protect a single row, a page of rows, or an entire table. The chosen size is the lock granularity, and it balances concurrency against the cost of tracking many locks.
The Trade Off
- Row locks allow the most concurrency because two transactions touching different rows never conflict. The cost is many lock objects and more memory and CPU for the lock manager.
- Page locks cover all rows stored on one data page. Fewer locks to manage, but two transactions on different rows of the same page now block each other.
- Table locks are a single lock for the whole table. Almost no bookkeeping, but any two writers serialize completely.
Lock Escalation
When a transaction acquires too many fine grained locks, an engine may perform lock escalation, swapping thousands of row locks for one table lock. This saves memory but suddenly reduces concurrency, which can cause unexpected blocking under load.
Choosing
Fine granularity suits short transactions touching scattered rows. Coarse granularity suits bulk operations that touch most of a table anyway.
Key idea
Finer lock granularity raises concurrency but costs more bookkeeping, and escalation trades many fine locks for one coarse lock.