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? 

Rows

Indexes and index key ranges

Leaf-level pages of nonclustered indexes

Data pages of clustered indexes

Heap data pages

Pages

Tables

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

Row versioning

Read More

Find queries causing Lock Timeouts per second - SQL Server DBA

SQL Server – Find sql server Locks - SQL Server DBA

SQL Server – LCK_M_X and how to reduce it - SQL Server DBA