锁升级 - 这里发生了什么？

“Lock Escalation”是 SQL 处理大型更新锁定的方式。当 SQL 将要更改大量行时，数据库引擎使用更少、更大的锁（例如整个表）而不是锁定许多较小的东西（例如行锁）更有效。

但是当你有一个巨大的表时，这可能会出现问题，因为锁定整个表可能会长时间锁定其他查询。这就是权衡：许多小粒度锁比更少（或一个）粗粒度锁慢，并且如果一个进程正在等待另一个进程，则多个查询锁定表的不同部分会产生死锁的可能性。

SQL 2008 中新增了一个表级选项 LOCK_ESCALATION，它允许控制锁升级。默认情况下，“TABLE”允许锁一直升级到表级别。在大多数情况下，DISABLE 可防止锁定升级到整个表。 AUTO 允许表锁，除非表是分区的，在这种情况下，锁只构成分区级别。有关详细信息，请参阅 this blog post。

我怀疑 IDE 在重新创建表时会添加此设置，因为 TABLE 是 SQL 2008 中的默认设置。请注意，SQL 2005 不支持 LOCK_ESCALATION，因此如果尝试在2005 年实例。此外，由于 TABLE 是默认值，因此您可以在重新运行脚本时安全地删除该行。

另请注意，在此设置出现之前的 SQL 2005 中，所有锁都可以升级到表级别 - 换句话说，“TABLE”是 SQL 2005 上的唯一设置。

---

answer by Justin Grant 解释了 LOCK_ESCALATION 设置的一般作用，但遗漏了一个重要细节，也没有解释 SSMS 生成设置它的代码的原因。特别是，LOCK_ESCALATION 被设置为脚本中的最后一条语句，这看起来很奇怪。

我做了很少的测试，这是我对这里发生的事情的理解。

精简版

添加、删除或更改列的 ALTER TABLE 语句隐式地在表上采用架构修改 (SCH-M) 锁，这与表的 LOCK_ESCALATION 设置无关。 LOCK_ESCALATION 在 DML 语句（INSERT、UPDATE、DELETE 等）期间影响锁定行为，而不是在 DDL 语句（ALTER）期间影响锁定行为。 SCH-M 锁始终是整个数据库对象的锁，在这个例子中是表。

这很可能是混乱的来源。

SSMS 在所有情况下都将 ALTER TABLE <TableName> SET (LOCK_ESCALATION = ...) 语句添加到其脚本中，即使在不需要时也是如此。在需要此语句的情况下，添加它以保留表的当前设置，不锁定表在更改表时以某种特定方式schema这发生在那个脚本中。

换句话说，表 被第一个 ALTER TABLE ALTER COLUMN 语句上的 SCH-M 锁锁定，同时完成了更改表架构的所有工作。最后的 ALTER TABLE SET LOCK_ESCALATION 语句不影响它。它仅影响该表的未来 DML 语句（INSERT、UPDATE、DELETE 等）。

乍一看，它确实看起来好像 SET LOCK_ESCALATION = TABLE 与我们正在更改整个表的事实有关（我们在此处更改其架构），但它具有误导性。

长版

在某些情况下更改表时，SSMS 会生成一个脚本来重新创建整个表，而在一些更简单的情况下（例如添加或删除列），脚本不会重新创建表。

让我们以这个示例表为例：

CREATE TABLE [dbo].[Test](
    [ID] [int] NOT NULL,
    [Col1] [nvarchar](50) NOT NULL,
    [Col2] [int] NOT NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

每个表都有一个 LOCK_ESCALATION 设置，默认情况下设置为 TABLE。让我们在这里改变它：

ALTER TABLE dbo.Test SET (LOCK_ESCALATION = DISABLE)

现在，如果我尝试更改 SSMS 表设计器中的 Col1 类型，SSMS 会生成一个脚本来重新创建整个表：

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Test
    (
    ID int NOT NULL,
    Col1 nvarchar(10) NOT NULL,
    Col2 int NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Test SET (LOCK_ESCALATION = DISABLE)
GO
IF EXISTS(SELECT * FROM dbo.Test)
     EXEC('INSERT INTO dbo.Tmp_Test (ID, Col1, Col2)
        SELECT ID, CONVERT(nvarchar(10), Col1), Col2 FROM dbo.Test WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Test
GO
EXECUTE sp_rename N'dbo.Tmp_Test', N'Test', 'OBJECT' 
GO
ALTER TABLE dbo.Test ADD CONSTRAINT
    PK_Test PRIMARY KEY CLUSTERED 
    (
    ID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

您可以在上面看到它为新创建的表设置了 LOCK_ESCALATION。 SSMS 这样做是为了保留表的当前设置。 SSMS 会生成此行，即使设置的当前值是默认的 TABLE 值。我想，如果将来这个默认值发生变化，只是为了安全和明确并防止未来可能出现的问题。这是有道理的。

在此示例中，确实需要生成 SET LOCK_ESCALATION 语句，因为表是重新创建的，并且必须保留其设置。

如果我尝试使用 SSMS 表设计器对表进行简单更改，例如添加新列，则 SSMS 会生成一个不会重新创建表的脚本：

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Test ADD
    NewCol nchar(10) NULL
GO
ALTER TABLE dbo.Test SET (LOCK_ESCALATION = DISABLE)
GO
COMMIT

如您所见，它仍然添加了 ALTER TABLE SET LOCK_ESCALATION 语句，尽管在这种情况下根本不需要它。第一个 ALTER TABLE ... ADD 不会更改当前设置。我想，SSMS 开发人员认为，为了安全起见，尝试确定在什么情况下此 ALTER TABLE SET LOCK_ESCALATION 语句是多余的并始终生成它是不值得的。每次都添加这个语句并没有什么坏处。

同样，表范围的 LOCK_ESCALATION 设置是不相关的，而表架构通过 ALTER TABLE 语句更改。 LOCK_ESCALATION 设置仅影响 DML 语句的锁定行为，例如 UPDATE。

最后，引用 ALTER TABLE 的一段话，强调我的：

ALTER TABLE 中指定的更改会立即实施。如果更改需要修改表中的行，则 ALTER TABLE 会更新这些行。 ALTER TABLE 在表上获取模式修改 (SCH-M) 锁，以确保在更改期间没有其他连接甚至引用表的元数据，除了最后需要非常短的 SCH-M 锁的在线索引操作。在 ALTER TABLE...SWITCH 操作中，在源表和目标表上都获得了锁。对表所做的修改会被记录并完全恢复。影响非常大表中所有行的更改（例如删除列或在某些版本的 SQL Server 上添加具有默认值的 NOT NULL 列）可能需要很长时间才能完成并生成许多日志记录。这些 ALTER TABLE 语句的执行应与影响许多行的任何 INSERT、UPDATE 或 DELETE 语句一样小心。

---

您可以通过在运行脚本的主要部分之前和之后比较此值来检查是否需要在脚本中包含 LOCK_ESCALATION 语句：

SELECT lock_escalation_desc FROM sys.tables WHERE name='yourtablename'

就我而言，更改表以删除或添加约束似乎不会修改此值。

---