I was asked this question at an interview and had no answer. Can anyone here explain?
SQL Server – What is Lock Escalation?
lock-escalationlockingsql server
Related Question
- Sql-server – Which component of SQL Server architecture handles the user login requests
- SQL Server – Two Protocols Used by Windows Authentication
- SQL Server 2017 – Why Table Lock Escalation Happens
- Sql-server – SQL Server Lock Escalation clarity required
- Lock Escalation and Count Discrepancy in SQL Server Extended Event
- SQL Server AlwaysON – Difference Between Lease Timeout and HealthCheck Timeout
- SQL Server Database Size Requirements and Design Considerations
Best 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.