Lock Escalation Limit

Question

Lock Escalation Limit

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

TomThomson SSC Guru Points: 104773 More actions · Answer 1

I don't much like the question, because the correct answer is (row locks are never escalated to page locks) is not available as an option. Of course it's easy enough to say "OK, that was just a mistake, I 'll pick 5000 because that's the threshold for escalation to table (or sometime partition) locks" if you already know how escalation works. That however doesn't help someone who reads the question and doesn't know the answer, makes a guess, sees that the answer is 5000, and goes away with the impression that he has now learnt that row locks are escalated to page locks. The idea of QOTD is to help people learn (while having a bit of fun) not to mislead them.

Tom

SimonLiew SSCrazy Eights Points: 8305 More actions · Answer 2

That depends. There is a lock escalation hierarchy, usually after page it is an extent (group of 8 pages). For more on escalation hierarchy, refer the link below:
http://msdn.microsoft.com/en-us/library/ms189849(v=sql.105).aspx
Furthermore, this hierarchy can be altered with ALTER TABLE commanding using SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )

There's no it depends situation for lock escalation. Row or Page lock only ever escalate to partition or table lock. There's no lock escalation as row to page to table\partition. I've not seen such behaviour.

Before SQL 2008, row or page lock only escalate to table lock

From SQL 2008 onwards, the below LOCK_ESCALATION

AUTO - escalate row or page lock to table partition if available, otherwise the behaviour will be similar to TABLE if partition is not present

TABLE - as the description imply, escalate row or page to table lock. This is the default behaviour

DISABLE - do not escalate row or page lock to table lock in "most cases". Scanning a heap under serializable isolation will still require table lock to protect data integrity

Hope this helps to debunk a myth.

Simon Liew
Microsoft Certified Master: SQL Server 2008

SQLWinther SSCertifiable Points: 6294 More actions · Answer 3

Nice question, but the right option is missing. Row locks are never escalated to page locks according to BOL.

"The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks. In SQL Server 2008, locking of partitioned tables can escalate to the HoBT level for the associated partition instead of to the table lock. A HoBT-level lock does not necessarily lock the aligned HoBTs for the partition."

/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform