Course contentsShow
← Software Engineering
Lesson 916 of 1,93328. Transactions and ConsistencyFree lesson

Row-Level vs Table-Level Locking

Comparing lock granularity and the trade-offs between concurrency and overhead.

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.