I blogged some time ago about the basics of Lock Escalations in SQL Server, and how even the plan shape can influence Lock Escalations. Today I want to talk further about Lock Escalations: do they always happen?

When does a Lock Escalation happen?

In general a Lock Escalation happens in SQL Server if you have acquired more than a total of 5000 row level locks in a SQL statement (SELECT, INSERT, UPDATE, DELETE). For example when you run a SELECT statement in the Repeatable Read Isolation Level, and you read more than 5000 rows from your table, a Lock Escalation is triggered by SQL Server.

When you run an UPDATE or DELETE statement against more than 5000 rows, it will also trigger a Lock Escalation. As a side-effect you end up with a Shared (S) or Exclusive (X) table lock. This will definitely hurt your concurrency and degrades the performance and throughput of your workload.

“Blocking” Lock Escalations

The whole idea of Lock Escalations sounds quite straightforward, but there is a huge implication and side effect: what happens if you can’t acquire a Shared or Exclusive table lock, because someone else has acquired an incompatible lock on the table? Should the Lock Escalation block in this case? Hopefully not…

So let’s engineer a simple example, where we try to reproduce this scenario to see how SQL Server reacts in this specific case. The following query acquires an X lock on the last row in the Clustered Index of the table Person.Person.

-- This transaction locks the last row in the Clustered Index of the 
-- table Person.Person
BEGIN TRANSACTION

UPDATE Person.Person
SET LastName = '...'
WHERE BusinessEntityID = 20777

This also means that SQL Server acquires an Intent Exclusive Lock (IX) on the corresponding page and the table itself. And now imagine that you run a SELECT statement in the Repeatable Read Isolation Level and you acquire more than 5000 row level locks. In this case SQL Server has to trigger a Lock Escalation, and escalate the individual S locks to one S lock at the table level.

But in our case it is not possible to acquire the S lock that we need at the table level, because the S lock is incompatible with the IX lock that has already been granted for our UPDATE statement. The whole locking hierarchy makes sense, because someone else has already acquired somewhere within our locking hierarchy an incompatible X lock. So let’s SELECT the first 6000 rows from the Clustered Index of the table Person.Person.

-- This statement would trigger a Lock Escalation
-- Run this in a different session...
BEGIN TRANSACTION

SELECT TOP(6000) * FROM Person.Person WITH (HOLDLOCK)

Fortunately this SELECT statement doesn’t block! That’s great! In our case SQL Server has tried to perform the Lock Escalation, but immediately gave up, because there was an incompatible lock on the table level (IX). It would be much, much worse if the Lock Escalation were to block, because this would degrade the concurrency of parallel queries for nothing!

Summary

Lock Escalations are very important in SQL Server, because they help SQL Server save space in the hashtable of the Lock Manager. But Lock Escalations are only *tried* by SQL Server. If SQL Server can’t perform a Lock Escalation because of an incompatible lock at the table level, nothing happens: the Lock Escalation doesn’t take place, and the SQL statement that triggered the Lock Escalation will not block.

I hope that this specific scenario helped you give you a better understanding of the Locking behavior of SQL Server.

Thanks for your time,

-Klaus