|
What is lock escalation? What is its purpose?Lock escalation: In SQL Server, if one acquires a lock at higher level, it can lock more resources than what we may consume. This kind of locking has an overhead with lower concurrency. E.g.: If we select all the rows of a table and we acquire a lock on the table, we would not need to lock rows themselves but then it will block any concurrent update transactions. Based on estimates during query compilation, SQL Server recommends the locking granularity appropriately and during query execution, based on the concurrent work load, the appropriate locking granularity is applied. While locking granularity is chosen at the beginning of query execution, during the execution SQL Server may choose to escalate the lock to higher level of granularity depending on the number of locks acquired and the availability of memory at runtime. SQL Server supports escalating the locks to the table level .i.e. the locks can only be escalated from rows to table level. Locks are never escalated from rows to the parent page.What is lock escalation? What is its purpose? Lock escalation is when the system combines multiple locks into a higher level one. This is done to recover resources taken by the other finer granular locks. The system automatically does this. The threshold for this escalation is determined dynamically by the server.
Purpose:
- To reduce system over head by recovering locks
- Maximize the efficiency of queries
- Helps to minimize the required memory to keep track of locks.
|
