What's the difference between row-level, table-level, and page-level locking?
Quick Answer
**Row-level** locking locks only the specific rows a transaction touches, maximizing concurrency but with more overhead to track many small locks. **Table-level** locking locks the entire table, simple and low-overhead but blocks unrelated concurrent access to any row in it. **Page-level** locking is a middle ground, locking a whole disk page (which holds multiple rows). Most modern OLTP engines default to row-level locking for regular DML, escalating to table-level only for schema changes or explicit bulk operations.
Detailed Answer
Row-level locking
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- Only row id=1 is locked; other transactions can freely update rows 2, 3, 4...
COMMIT;
This is the default granularity for InnoDB (MySQL), PostgreSQL, and SQL Server's row-locking mode — it maximizes concurrency because unrelated rows in the same table remain fully accessible to other transactions. The cost is more bookkeeping: the engine must track potentially many individual row locks per transaction.
Table-level locking
LOCK TABLE accounts IN EXCLUSIVE MODE; -- blocks ALL other access to the whole table
Locks the entire table regardless of which specific rows are touched — simple to implement and low per-operation overhead, but drastically reduces concurrency, since even transactions touching completely unrelated rows must wait. Common uses: DDL operations (ALTER TABLE) that must see a globally consistent view of the schema, or explicit bulk maintenance operations where you deliberately want exclusive access.
Page-level locking
Locks a whole disk page (which typically holds multiple rows) rather than a single row or the whole table — a middle ground some engines (older SQL Server versions, some MyISAM configurations) used for lower locking overhead than pure row-level, at the cost of unrelated rows on the same page blocking each other (a form of false contention sometimes called "false sharing" at the storage layer).
Lock escalation
Some engines (notably SQL Server) automatically escalate from many row-level locks to a single table-level lock once a transaction holds "too many" row locks (a threshold, often ~5,000), to reduce lock-management memory overhead — this can unexpectedly turn a seemingly-fine-grained update into a full-table lock if it touches a large number of rows in one transaction, a subtle gotcha worth knowing when debugging unexpected blocking on large batch updates.
Default to trusting your engine's row-level locking for normal OLTP transactions — it's what's designed for high-concurrency workloads. Reach for explicit table-level locks only for genuinely table-wide maintenance operations, and be aware that a very large single-transaction batch update might unintentionally trigger lock escalation (SQL Server) or simply hold a very large number of row locks for a long time (PostgreSQL/MySQL), either of which can significantly increase blocking for other transactions — consider batching large updates into smaller committed chunks instead.