Row-Level vs Table-Level Locking
You'll learn: How different lock granularities affect concurrency and system overhead in databases.
What Is Lock Granularity?
When a database locks data during a transaction, it must choose how much to lock. Lock granularity refers to the size of the resource being locked—from an entire table down to individual rows or even smaller units.
The two most common levels are:
- Table-level locks: The entire table is locked, preventing others from modifying (or sometimes reading) any row in that table
- Row-level locks: Only specific rows are locked, allowing concurrent transactions to work with other rows in the same table
The Core Trade-Off
Think of it like a parking lot: table-level locking is like closing the entire lot when one person parks—simple to manage but terrible for concurrency. Row-level locking is like marking individual spaces—many people can park simultaneously, but you need more coordination overhead.
Table-Level Locking
Pros: Low overhead—one lock to acquire and track
Cons: Poor concurrency—other transactions wait even if they need different rows
Row-Level Locking
Pros: High concurrency—transactions can work on different rows simultaneously
Cons: Higher overhead—more locks to acquire, track, and release; more memory usage
Choosing the Right Level
Use table-level locks when transactions typically affect most rows or when simplicity matters more than concurrency (e.g., maintenance operations, small databases).
Use row-level locks when transactions affect few rows and you need many concurrent users accessing different records (e.g., high-traffic OLTP systems).
Key Takeaway: Row-level locking maximizes concurrency at the cost of overhead, while table-level locking minimizes overhead at the cost of parallelism—choose based on your workload's access patterns and concurrency requirements.