Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead. Microsoft® SQL Server™ automatically escalates row locks and page locks into table locks when a transaction exceeds its escalation threshold.
For example, when a transaction requests rows from a table, SQL Server automatically acquires locks on those rows affected and places higher level intent locks on the pages and table, or index, that contain those rows. When the number of locks held by the transaction exceeds its threshold, SQL Server attempts to change the intent lock on the table to a stronger lock (for example, an intent exclusive would change to an exclusive lock). After acquiring the stronger lock, all page and row level locks held by the transaction on the table are released, reducing lock overhead.
SQL Server may choose to do both row and page locking for the same query, for example, placing page locks on the index (if enough contiguous keys in a nonclustered index node are selected to satisfy the query) and row locks on the data. This reduces the likelihood that lock escalation will be necessary.
Lock escalation thresholds are determined dynamically by SQL Server and require no configuration.