Row-Level Locking vs Table-Level Locking: What is the Difference?
Compare row-level and table-level locking in databases, their concurrency trade-offs, and when each is used.
Expected Interview Answer
Row-level locking locks only the specific rows a transaction reads or modifies, allowing many transactions to work on different rows of the same table concurrently, while table-level locking locks the entire table, blocking all other transactions from accessing any row until it is released.
Row-level locking gives fine-grained concurrency: two transactions updating different customers in the same Orders table proceed in parallel without blocking each other, at the cost of extra memory and bookkeeping to track each individual lock. Table-level locking is coarse and cheap to manage, useful for operations like schema changes, bulk loads, or full-table scans that touch most of the table anyway, but it serializes access across the whole table, which can cripple throughput on a busy transactional workload. Most production relational databases default to row-level locking for normal DML and reserve table-level locks for specific operations like DDL, TRUNCATE, or explicit LOCK TABLE statements.
- Row-level: high concurrency for transactional workloads
- Table-level: lower locking overhead for bulk or full-table operations
- Choosing correctly avoids unnecessary contention or unnecessary overhead
- Most engines mix both, escalating or choosing granularity automatically
AI Mentor Explanation
Row-level locking is like reserving only the specific practice net a player is using, letting other players use the other nets on the same ground at the same time. Table-level locking is like reserving the entire ground for one team's exclusive use, so no other team can practice anywhere on it, even nets nobody from that team is using. A ground used for many simultaneous small sessions benefits from net-level reservations, while a full-ground event justifies the whole-ground booking.
Step-by-Step Explanation
Step 1
Identify the operation scope
Determine whether the transaction touches a few specific rows or effectively the entire table.
Step 2
Acquire the appropriate lock granularity
Normal DML acquires row-level locks; DDL, TRUNCATE, or bulk operations typically take a table-level lock.
Step 3
Allow or block concurrent access
Row-level locks let unrelated rows be accessed concurrently; a table-level lock blocks all access to the table.
Step 4
Release on commit or rollback
All held locks, at whatever granularity, are released when the transaction ends.
What Interviewer Expects
- Clear trade-off between concurrency and locking overhead
- Knowledge of when each granularity is used by default (DML vs DDL/bulk ops)
- Awareness that table-level locks can severely hurt throughput on hot tables
- Mention of lock escalation as the bridge between the two
Common Mistakes
- Assuming all databases always use row-level locking for everything
- Not recognizing that DDL or TRUNCATE typically require table-level locks
- Ignoring the memory/overhead cost of holding millions of row locks
- Confusing table-level locking with sharding or partitioning
Best Answer (HR Friendly)
“Row-level locking only locks the specific rows a transaction touches, so other transactions can freely work on different rows in the same table at the same time. Table-level locking locks the whole table, which is simpler and cheaper for the database to manage but blocks everyone else from that table until it's released, so it's mainly used for bulk operations or schema changes rather than everyday queries.”
Code Example
-- Row-level: only the matching row is locked; other rows remain accessible
BEGIN;
UPDATE Orders SET status = 'shipped' WHERE order_id = 501;
-- other sessions can still read/update order_id = 502 concurrently
-- Table-level: explicitly locking the whole table blocks all other access
BEGIN;
LOCK TABLE Orders IN EXCLUSIVE MODE;
-- no other session can read or write ANY row in Orders until COMMIT/ROLLBACK
COMMIT;Follow-up Questions
- When would a database choose a table-level lock over row-level locking by default?
- How does lock escalation connect row-level and table-level locking?
- What is the performance impact of table-level locks on a high-traffic table?
- How do isolation levels influence how many row-level locks are taken?
MCQ Practice
1. Row-level locking primarily improves what?
Row-level locking lets transactions on different rows of the same table proceed without blocking each other.
2. Which operation is most likely to require a table-level lock?
DDL operations like ALTER TABLE typically require exclusive table-level locks because they change the table structure itself.
3. What is a downside of table-level locking on a busy transactional table?
A table-level lock serializes all access to the table, severely limiting throughput under concurrent transactional load.
Flash Cards
What is row-level locking? — Locking only the specific rows a transaction reads or modifies, allowing concurrent access to other rows.
What is table-level locking? — Locking the entire table, blocking all other access until the lock is released.
When is table-level locking typical? — For DDL, TRUNCATE, bulk loads, or explicit LOCK TABLE statements.
Main trade-off between the two? — Row-level maximizes concurrency at higher bookkeeping cost; table-level is cheap to manage but serializes all access.