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

在 SQL Server 2008中修改一个表(删除一个列)时，我单击了 Generate Change Script 按钮，我注意到它生成的更改脚本删除了该列，说“ go”，然后运行一个额外的 ALTER TABLE 语句，该语句似乎将表的锁升级设置为“ TABLE”。例如:

ALTER TABLE dbo.Contract SET (LOCK_ESCALATION = TABLE)

我还应该指出，这是更改脚本正在做的最后一件事。它在这里做什么? 为什么要将 LOCK _ ESCALATION 设置为 TABLE？

60258 次浏览
小开
最佳答案

“ 锁定升级”是 SQL 处理大型更新锁定的方式。当 SQL 要更改大量行时，数据库引擎使用更少、更大的锁(例如整个表)比锁定许多更小的东西(例如行锁)效率更高。

但是，当您有一个巨大的表时，这可能会有问题，因为对整个表进行锁定可能会在很长时间内锁定其他查询。这就是折衷方案: 许多小粒度锁比较少(或一个)粗粒度锁要慢，并且如果一个进程正在等待另一个进程，多个查询锁定一个表的不同部分会产生死锁的可能性。

SQL2008中新增了一个表级选项 LOCK_ESCALATION，它允许控制锁升级。默认情况下，“ TABLE”允许锁一直升级到表级别。在大多数情况下，DISABLE 可以防止锁升级到整个表。AUTO 允许使用表锁，除非表是分区的，在这种情况下，锁仅仅是分区级别的锁。有关更多信息，请参见 这篇博文。

我怀疑 IDE 在重新创建表时添加了此设置，因为在 SQL2008中 TABLE 是默认设置。请注意，SQL2005不支持 LOCK _ ESCALATION，因此如果试图在2005实例上运行脚本，则需要删除它。而且，由于 TABLE 是默认值，所以在重新运行脚本时可以安全地删除该行。

还要注意，在 SQL2005中，在此设置出现之前，所有锁都可以升级到表级——换句话说，“ TABLE”是 SQL2005上唯一的设置。

小开

通过比较运行脚本主要部分之前和之后的值，可以检查是否需要在脚本中包含 LOCK _ ESCALATION 语句:

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

在我的例子中，更改表以删除或添加约束似乎不会修改此值。

小开

贾斯汀 · 格兰特回答解释了 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 = ...)语句添加到其脚本中，即使在不需要时也是如此。在需要此语句的情况下，添加此语句是为了在脚本中对表 模式进行更改期间，以某种特定的方式保留表 不要锁桌子的当前设置。

换句话说，在完成所有更改表模式的工作时，对第一个 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 这样做是为了保留表的当前设置。即使设置的当前值是默认 TABLE值，SSMS 也会生成此行。只是为了安全和明确，并防止可能的未来问题，如果在未来这个默认的变化，我想。这就说得通了。

在本例中，确实需要生成 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语句是多余的并且总是生成它。每次都加上这句话也没什么坏处。

同样，当表模式通过 ALTER TABLE语句更改时，表范围的 LOCK_ESCALATION设置是不相关的。LOCK_ESCALATION设置仅影响 DML 语句(如 UPDATE)的锁定行为。

最后，引用 ALTER TABLE的一句话，强调了我的观点:

在 ALTERTABLE 中指定的更改将立即实现 更改需要修改表中的行 ALTER TABLE 更新行。 < strong > ALTER TABLE 获取模式修改(SCH-M) 锁定表，以确保没有其他连接引用 甚至在 更改期间表的元数据(联机索引除外) 操作，这些操作在末尾需要一个非常短的 SCH-M 锁 开关操作，锁在两个源上都被获取 以及目标表。对表所做的修改将被记录下来，并且 完全可恢复的。影响非常大的所有行的更改 表，例如删除列，或者在某些版本的 SQLServer 上, 添加一个带有默认值的 NOTNULL 列，可能需要很长时间才能 完成并生成许多日志记录 应该像执行任何 INSERT、 UPDATE 或 DELETE 一样小心 影响多行的语句。


提交答案