Part 128: Lock Granularity: Row-Level vs Table-Level Locks

The level of precision at which the database applies locks.

1. Hierarchy of Locks

• Row-Level Lock: Locks only the specific record modified. Highest concurrency.
• Page-Level Lock: Locks an 8KB data page containing multiple rows.
• Table-Level Lock: Locks the entire table (LOCK TABLE). Blocks all concurrent writers.

2. Lock Escalation

If a transaction updates 500,000 rows, tracking 500,000 individual row locks exhausts memory. The database will automatically "escalate" row locks into a single table-level lock, instantly blocking all concurrent users.

Key Takeaways

• Keep transactions short to avoid lock escalation.

In Part 129, we analyze Deadlocks: Detection, Prevention & Resolution!