(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)
Another really commonly-held belief…
Myth #23: lock escalation goes row-to-page and then page-to-table.
Nope, never. Lock escalation in SQL Server 2005 and before goes directly to a table lock always.
From SQL Server 2005 onward you can change the behavior of lock escalation (if you really know what you’re doing) using these trace flags:
- 1211 – disables lock escalation totally and will allow lock memory to grow to 60% of dynamically allocated memory (non-AWE memory for 32-bit and regular memory for 64-bit) and will then further locking will fail with an out-of-memory error.
- 1224 – disables lock escalation until 40% of memory is used and then re-enables escalation.
1211 takes precedence over 1224 if they’re both set – so be doubly careful.
From SQL Server 2008 onward you can change the behavior of lock escalation per table using the ALTER TABLE blah SET (LOCK_ESCALATION = XXX) where XXX is one of:
- TABLE: always escalate directly to a table lock.
- AUTO: if the table is partitioned, escalate to a partition-level lock, but then don’t escalate any further.
- DISABLE: disable lock escalation. This doesn’t disable table locks – a table lock may be required under some circumstances, like a table scan of a heap under the SERIALIZABLE isolation level.
Back in January 2008 I blogged an example of setting up a partitioned table and showing partition-level lock escalation in action – see SQL Server 2008: Partition-level lock escalation details and examples.
You may ask why the AUTO option isn’t the default? It’s because some early-adopters found that their applications started to deadlock using that option. So, just as with the lock escalation trace flags, be careful about turning on the AUTO option.