Microsoft® SQL Server 2012 Unleashed

Lock Escalation

When SQL Server detects that the locks acquired by a query are using too much memory and consuming too many system resources for the Lock Manager to manage the locks efficiently, it automatically attempts to escalate row, key, or page locks to table-level locks. For example, because a query on a table continues to acquire row locks and every row in the table will eventually be accessed, it makes sense for SQL Server to escalate the row locks to a table-level lock. After the table-level lock is acquired, the row-level locks are released. This helps reduce locking overhead and keeps the system from running out of available lock structures. Recall from earlier sections in this chapter that the potential need for lock escalation is ...