Lock Escalation

When a query requests rows from a table, Microsoft SQL Server automatically generates page-level locks. However, if the query requests a large percentage of the table’s rows, SQL Server escalates the locking from page level to table level. This process is called lock escalation.

Lock escalation makes table scans and operations against a large result set more efficient because it reduces locking overhead. SQL statements that lack WHERE clauses typically cause lock escalation.

A shared table lock (TABLOCK) is applied when a shared page lock is escalated to a table lock during a read operation. Shared table locks are applied when:

• A HOLDLOCK or SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement is used.
• A table scan is chosen by the optimizers.
• The number of share locks accumulated in a table exceeds the lock escalation threshold.

The lock escalation threshold defaults to 200 pages in a table, but this can be customized to be based on a percentage of table size with minimum and maximum bounds. A shared table lock is also used when building a nonclustered index. For more information about the lock escalation threshold, see the SQL Server Books Online.

An exclusive table lock (TABLOCKX) is applied when an UPDATE lock has been escalated to a table lock during a write operation. Exclusive table locks are applied when:

• No index can be used for an update or delete operation.
• The number of pages in a table that has an exclusive lock exceeds the lock escalation threshold.
• A clustered index is created.

Oracle’s inability to escalate row-level locks can cause problems in some queries that include the FOR UPDATE clause. For example, assume that the STUDENT table has 100,000 rows of data, and the following statement is issued by an Oracle user:

SELECT * FROM STUDENT FOR UPDATE

This statement forces the Oracle RDBMS to lock the STUDENT table one row at a time; this can take quite a while. It never escalates the request to lock the entire table.

The same query in SQL Server is:

SELECT * FROM STUDENT (UPDLOCK)

When this query is run, page-level locking escalates to table-level locking, which is much more efficient and significantly faster.