I was asked this question at an interview and had no answer. Can anyone here explain?


4 Answers 4


DB locks can exist on rows, pages or whole tables or indexes. When a transaction is in progress, the locks held by the transaction take up resources. Lock escalation is where the system consolidates multiple locks into a higher level one (for example consolidating multiple row locks to a page or multiple pages to a whole table) typically to recover resources taken up by large numbers of fine-grained locks.

It will do this automatically, although you can set flags on the tables (see ALTER TABLE in the books on line) to control the policy for lock escalation on that particular table. In particular, premature or overly eager lock escalation used to be a problem on older versions of Sybase and SQL Server when you had two processes writing separate rows into the same page concurrently. If you go back far enough (IIRC SQL Server 6.5) SQL Server didn't actually have row locking but could only lock tables or pages. Where this happened, you could get contention between inserts of records in the same page; often you would put a clustered index on the table so new inserts went to different pages.

  • 4
    It should also be noted that we have no control over when the server escalates.
    – Josef
    Oct 15, 2008 at 20:15
  • We do now. It's call T1211.
    – Joshua
    Apr 4, 2013 at 16:17
  • @Joshua, trace flag 1211 is used to disable lock escalation. There is no way for us to tell SQL Server to escalate at a specific time. Mar 5, 2015 at 16:32
  • 12
    Row locks are not escalated to Page Locks, but directly to Table locks. Mar 17, 2016 at 7:09
  • It is true what Manoj stated. You should correct that part in your answer to avoid misleading of less experienced SQL Server users
    – NikolaD
    Nov 21, 2018 at 16:02

It's a method for reducing system overhead, by converting many fine grained locks to fewer coarse grained ones. More detailed information can be found here and here.

For example, if you have many (usually hundreds or more) locks on specific rows in a table, once you exceed your maximum allowed number of locks, these might be exchanged for a lock on the whole table.


SQL Server does Lock Escalation in order to reduce memory overhead, by converting several fine-grained low level locks to coarse-grained high level locks.

  • Row Locks are always escalated to Table Locks, and
  • Page Locks are also escalated to Table Locks.

It is a myth that Row locks are escalated to Page locks, same mentioned above by @ConcernedOfTunbridgeWells is wrong.

If a table having very few row-updates, SQL engine will try to take of Row-Locks on those rows or Page-Lock on those Pages. Let's say it has taken Row-Lock. But if the row-updates increases the threshold (~5k locks) then instead of taking several Row-Locks it takes a single Table-Lock. Thus, this reduces memory overhead by releasing several Row-Locks and taking a single Table-Lock, but increases concurrency. Same happens with the Page lock.

The Lock Escalation threshold is at-least 5000 locks, and depends on several factors, a detailed explanation of Lock Escalation has been mentioned here in MSDN BoL: https://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx

  • Doesn’t escalating a lock reduce concurrency rather than increase it?
    – binki
    Jul 27, 2020 at 18:18

Lock escalation means the conversion of a lock to a more restrictive mode. This is most often seen in databases. A query might have a resource locked for "shared" and escalate it to "exclusive" to perform an update.