Lock Escalation in SQL

Question

What is Lock Escalation in SQL?

Prabhu Raja · Answer

Hi Lock escalation is a event which occurs when SQL Server decides to upgrade a lock at a lower level hierarchy to a lock to a table level lock. In other words when a particular query obtains a large number of row level locks/ page level locks SQL Server decides that instead of creating and granting number of row level/page level locks it is effective to grant a single table level lock. Or to be precise SQL Server upgrades the row/page level locks to table level locks. The above process is termed as lock escalation. Lock escalation is good as it reduces the overhead in maintaining a large number of smaller level locks. A lock structure is about occupies about 100 bytes of memory and too many locks can cause a memory pressure.Similarly applyinggranting  and releasing locks for each row or page is a resource consuming processes which can be reduced by lock escalation. However Lock escalation also reduces concurrency. ie If a query causes lock escalation then the query obtains a full table level lock and another query attempting to access the table will have to wait till the first query releases the lock. For more Reference : Click Here

Hemant Kumar · Answer

DB locks can exist on rows pages or whole tables or indexes. When a transaction is in progress the locks held by the transaction take up resources. Lock escalation is where the system consolidates multiple locks into a higher level one (for example consolidating multiple row locks to a page or multiple pages to a whole table) typically to recover resources taken up by large numbers of fine-grained locks. It will do this automatically although you can set flags on the tables (see ALTER TABLE in the books on line) to control the policy for lock escalation on that particular table. In particular premature or overly eager lock escalation used to be a problem on older versions of Sybase and SQL Server when you had two processes writing separate rows into the same page concurrently. If you go back far enough (IIRC SQL Server 6.5) SQL Server didn't actually have row locking but could only lock tables or pages. Where this happened you could get contention between inserts of records in the same page; often you would put a clustered index on the table so new inserts went to different pages. Refer this link http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx