SQL Server misunderstanding 30th about the 23rd day about lock escalation error _mssql

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
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-->
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.