SQL Server myth: a misunderstanding about lock escalation

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

Actually not, in SQL Server 2005 and prior versions, lock escalation will rise directly to the table lock.

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 is said in Bol (books Online entry), which requires table locks when doing table scans under conditions of serialization isolation levels.

In January 2008, I wrote a blog with an example of a partition lock, see: SQL Server 2008:partition-level Lock escalation details and examples.

Maybe you wonder why lock_escalation = Auto is not the default in the XXX setting, because some people in the 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.

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/