Lock Escalation Limit

Question

Lock Escalation Limit

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

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

SimonLiew SSCrazy Eights Points: 8434 More actions · Answer 1

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 2

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