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. It maximizes concurrency but with more overhead to track many small locks. Table-level locking locks the entire table. It's 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. It's simple to implement and has low per-operation overhead, but it 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. Some engines (older SQL Server versions, some MyISAM configurations) use this as a middle ground 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. 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.