SQL Server myth: a misunderstanding about lock escalation

Source: Internet
Author: User
Tags memory usage

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/

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.