Hugo Kornelis (6/20/2013)
Lokesh Vij (6/20/2013)
Rich Weissler (6/20/2013)
My memory was that Lock Escalation from row or page level always jumps to table. I assume I'm simply misinterpreting the question, but figured I'd ask/point just 'cause I haven't had my coffee yet...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 } )
I'm sorry, but you are wrong. SQL Server will never escalate row level locks to page level locks.
By default, SQL Server will take row level locks, and at the threshold indicated in today's question they will escalate to either table level or partition level (for partitioned tables) locks.
With index-level options or query hints, you can cause SQL Server to start out by taking page level locks instead of row level locks. The same threshold is then still used to escalate, again either to table level or partition level.
The ALTER_ TABLE option you mention can only be used to force SQL Server to escalate to table level locks instead of partition level locks for a partitioned table, or to (almost) completely eliminate lock escalation.
Note that the default LOCK_ESCALATION setting is TABLE, which means that SQL Server will escalate directly to table locks, even on partitioned tables. You have to change the LOCK_ESCALATION setting to AUTO to allow SQL Server to escalate from row/page locks to partition locks. This does increase chances of deadlocks among SPIDs accessing the table, though, because when they take the partition lock, they'll also take an intent lock on the table. The classic deadlock scenario plays out like this:
SPID 1 takes an X lock on partition 10 (and an IX lock on the table)
SPID 2 takes an X lock on partition 20 (and an IX lock on the table)
SPID 2 tries to escalate its IX table lock to an X table lock, which is incompatible with SPID 1's IX lock, so it waits.
SPID 1 requests X locks on rows in partition 20, which is incompatible with SPID 2's X lock, so it waits, and whammo - deadlock.
It's really no different than any other deadlock scenario, it's just that adding the additional layer of escalation increases the chance that two SPIDs will get crossed up.
Jason Wolfkill