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

Question

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

ALTER TABLE dbo.Contract SET (LOCK_ESCALATION = TABLE)

我还应该注意到，这是更改脚本要做的最后一件事。它在这里做什么?为什么它要将LOCK_ESCALATION设置为TABLE？

Answer 1

"Lock Escalation“是SQL处理大型更新锁定的方式。当SQL要更改很多行时，对于数据库引擎来说，采用更少、更大的锁(例如整个表)而不是锁定许多较小的东西(例如行锁)会更有效。

但是当您有一个很大的表时，这可能会有问题，因为锁定整个表可能会在很长一段时间内锁定其他查询。这就是权衡:许多细粒度的锁比较少的(或一个)粗粒度的锁慢，并且如果一个进程正在等待另一个进程，则多个查询锁定一个表的不同部分可能会导致死锁。

SQL2008中新增了一个表级选项LOCK_ESCALATION，它允许控制锁的升级。默认的" table“允许锁一直升级到表级。在大多数情况下，DISABLE可防止锁升级到整个表。AUTO允许使用表锁，除非对表进行了分区，在这种情况下，锁仅由分区级别组成。有关详细信息，请参阅this blog post。

我怀疑IDE在重新创建表时添加了此设置，因为表是SQL2008中的默认设置。请注意，SQL2005中不支持LOCK_ESCALATION，因此如果尝试在2005实例上运行该脚本，则需要去掉它。此外，由于表是缺省的，因此在重新运行脚本时可以安全地删除该行。

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

Answer 2

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 = ...)语句添加到脚本中，即使在不需要它的情况下也是如此。在需要此语句的情况下，添加此语句是为了保留表的当前设置，在对脚本中发生的表模式进行更改期间，不会以某种特定的方式锁定表。

换句话说，在第一个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设置只影响UPDATE等DML语句的锁定行为。

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

ALTER TABLE中指定的更改将立即生效。如果更改需要修改表中的行，则ALTER table将更新行。ALTER TABLE获取表上的模式修改( SCH-M )锁，以确保在更改期间没有其他连接引用甚至是表的元数据，除了在结束时需要一个非常短的SCH-M锁的联机索引操作。在ALTER TABLE…中切换操作，则同时获取源表和目标表上的锁。对表所做的修改会被记录下来，并且是完全可恢复的。影响非常大的表中的所有行的更改可能需要很长时间才能完成并生成许多日志记录。这些ALTER TABLE语句的执行应与影响许多行的任何INSERT、UPDATE或DELETE语句一样谨慎。

Answer 3

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

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

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