Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

migrated from Feb 13 '12 at 21:39

This question came from our site for professional and enthusiast programmers.

up vote 34 down vote accepted

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.

share|improve this answer
It should also be noted that we have no control over when the server escalates. – Josef Oct 15 '08 at 20:15
We do now. It's call T1211. – Joshua Apr 4 '13 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. – Ogrish Man Mar 5 '15 at 16:32

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 table, or on a range of rows in the table.

share|improve this answer

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.

share|improve this answer

protected by Paul White Aug 27 '15 at 9:54

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?