22 April,2013 by Jack Vamvas
SQL Lock Escalation – Everything you ever wanted to know but were to afraid to ask.
What is lock escalation?
Lock escalation is the SQL Server process of consolidating granular multiple locks into a less granular locks.
By placing a lock at a higher level , such as escalating multiple row locks to a table lock , less resources are used but at the cost of decreased concurrency.
E.g On Table_A there are a number of locks held on Heap Data Pages. The threshold is met , which triggers the lock escalation. The intent table lock changes into an Exclusive Lock. The Heap Data Pages locks are released , which decreases the resources used. But , as there is now a Table Exclusive Lock , other transactions cannot grab a lock on the same resources
What objects can be locked?
Indexes and index key ranges
Leaf-level pages of nonclustered indexes
Data pages of clustered indexes
Heap data pages
What triggers lock escalation?
The number of locks (including intent) held by a statement on an index or a heap within a statement exceeds the threshold (5000). It won’t be triggered if there are 1000 locks on 5 indexes , it has to be on one.
If the locks configuration run_value is set at 0 – (use sp_configure to check) , and lock resources are consuming greater than 40% of the memory (on 64 bit) .
Can lock escalation be disabled?
Yes it is possible to disable lock escalation in most cases. The command: ALTER TABLE SET LOCK_ESCALATION = DISABLE , will disable all lock escalation except a table scan with no clustered index using the serializable isolation level.
Trace Flages 1211 and 1224 are used in disabling lock escalations
Can lock escalation lead to blocking?
Yes, sometimes during the lock escalation process , another transaction may request access to an index or heap, with a conflicting lock mode.
For example, if a lock escalation was triggered on a transaction accessing multiple rows into a table lock – another transaction requiring an Exclusive lock on a row, in the same table would be blocked.
To monitor blocking use the the post Blocked Process Report and how to read
How can I decrease the amount of locks held?
An effective method is to identify queries with excessive locking delays and focus on decreasing the duration of time held by the locks. Query tuning
Stored procedure versus adhoc queries - Read more on Stored Procedures versus ad-hoc paramaterized queries.Which is faster?
Analyse indexes – for example , if you have a heavily updated column don’t include it in a Clustered Index as it will will also lock the non clustered index via the row locator