Set Lock_escalation (LOCK upgrade) option

If you set the Lock Upgrade option to Auto (set Lock_escalation to Auto) for the partition table, the maximum granularity to lock the table is partition, which can improve the concurrency of the table to some extent, improve the update speed, and even prevent deadlock from happening.

Syntax

ALTER TABLE []SET=| TABLE | DISABLE})

SET (lock_escalation = {AUTO | TABLE | DISABLE})

Specifies the allowed methods of lock escalation for a table.

AUTO

This option allows SQL Server, Database Engine to select the lock escalation granularity, that's appropriate for the table Schema.

• If The table is partitioned, lock escalation'll be allowed to partition. After the lock was escalated to the partition level, the lock would not be escalated later to TABLE granularity.

• If The table is not partitioned, the lock escalation'll be done to the table granularity.

TABLE

Lock escalation would be do at table-level granularity regardless whether the table was partitioned or not partitioned. This behavior are the same as in SQL Server 2005. TABLE is the default value.

DISABLE

Prevents lock escalation in the most cases. Table-level locks is not completely disallowed. For example, when you were scanning a table that have no clustered index under the Serializable isolation level, Database En Gine must take a table lock to protect data integrity.

Sample

Allowing lock escalation on partitioned tables

The following example enables lock escalation to the partition level on a partitioned table. If The table is not partitioned, lock escalation are to the table level.

ALTER TABLE SET = AUTO);

Reference Document:

https://msdn.microsoft.com/en-us/library/ms190273 (v=sql.105). aspx

Set Lock_escalation (LOCK upgrade) option