Lock Escalations – do they always happen?

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

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 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.

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!


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.

Like or share to get the source code.

Thanks for your time,



  • Andrea Caldarone

    Hello Klaus,
    what’s happens if the first transaction tried to update 6000 rows? SQL Server will try to do lock escalation and it will succeed since there are no other transaction, but in this case till the first transaction will commit, the whole table will be blocked even for a select statement?

    • Klaus Aschenbrenner

      Hello Andrea,

      Yes, in that case the triggered Lock Escalation would block the SELECT statement.



  • Ranjan

    Does it mean that if <5000 records will get modified in a table then lock escalation will not happen?

    • Klaus Aschenbrenner

      Hello Ranjan,

      Yes, SQL Server tries the Lock Escalation after 5000 rows.



  • anil kumar

    Useful insight on lock escalation – Thanks for putting it up !!

It`s your turn

Your email address will not be published. Required fields are marked *


Copyright © 2018 by SQLpassion e.U. · Imprint · Offerings · Academy · Contact · Data Protection · Go to Top