Myth #23: The process of lock escalation is escalated from row locks to page locks, and then page locks are upgraded to table locks
Error
Not really, in SQL Server 2005 and earlier versions, page locks are upgraded directly to table locks.
In SQL Server 2005 or SQL Server 2008, you can change the behavior of a lock escalation by following a trace flag:
Flag 1211-A lock upgrade is completely prohibited, but the memory used by the lock is limited to 60% of the dynamically allocated memory, and when this value is exceeded, more locks will fail with a memory overflow error.
- Flag 1224-Lock escalation is prohibited, but lock escalation is automatically turned on when memory usage exceeds 40%
If the flag 1211 and 1224 tracking flags are set at the same time, only flag 1211 will take effect. For more detailed information, please see Books Online。
In SQL Server 2008, you can also set the lock behavior in a table by using ALTER TABLE blah set (lock_escalation = XXX), in which XXX represents one of the following:
Table: Upgrades directly from row locks to table locks.
- AUTO: If a table partition exists, it is upgraded to a partition lock but will not be upgraded further.
- DISABLE: Disabling lock escalation does not mean disabling table locks, as stated in Bol (books Online entry ). Table locks are also required for operations such as table scans under the condition of serialization of isolation levels.
in January 2008, I wrote a blog post containing an example of a partition lock, see: sql Server 2008: Partition-level Lock escalation details and examples .
You might wonder why lock_escalation = Auto is not the default value in the XXX setting, because some people in early tests found this option more likely to cause deadlocks. As with the two trace flags for locks, it is also prudent to set this option to auto. Part of the!--reward--> the!--voting part-->
