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

Lock Escalation - What's happening here?

提问by James Alexander

While altering a table (removing a column) in SQL Server 2008, I clicked the Generate Change Script button and I noticed that the change script it generated drops the column, says "go" and then runs an additional ALTER TABLE statement that appears to set the lock escalation for the table to "TABLE". Example:

在 SQL Server 2008 中更改表（删除列）时，我单击了“生成更改脚本”按钮，我注意到它生成的更改脚本删除了该列，显示“go”，然后运行一个附加的 ALTER TABLE 语句，该语句似乎设置表的锁升级到“TABLE”。例子：

ALTER TABLE dbo.Contract SET (LOCK_ESCALATION = TABLE)

I should also note that this is the last thing the change script is doing. What is it doing here and why is it setting the LOCK_ESCALATION to TABLE?

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

回答by Justin Grant

"Lock Escalation" is how SQL handles locking for large updates. When SQL is going to change a lot of rows, it's more efficient for the database engine to take fewer, larger locks (e.g. entire table) instead of locking many smaller things (e.g. row locks).

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

But this can be problematic when you have a huge table, because taking a lock on the entire table may lock out other queries for a long time. That's the tradeoff: many small-granularity locks are slower than fewer (or one) coarse-grained locks, and having multiple queries locking different parts of a table creates the possibility for deadlock if one process is waiting on another.

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

There is a table-level option, LOCK_ESCALATION, new in SQL 2008, which allows control of lock escalation. The default, "TABLE" allows locks to escalate all the way to the table level. DISABLE prevents lock escalation to the entire table in most cases. AUTO allows table locks except if the table is partitioned, in which case locks are only made up to the partition level. See this blog postfor more info.

LOCK_ESCALATIONSQL 2008 中新增了一个表级选项，它允许控制锁升级。默认情况下，“TABLE”允许锁一直升级到表级别。在大多数情况下，DISABLE 可防止锁升级到整个表。AUTO 允许表锁定，除非表已分区，在这种情况下，锁定仅针对分区级别。有关更多信息，请参阅此博客文章。

I suspect that the IDE adds this setting when re-creating a table because TABLE is the default in SQL 2008. Note that LOCK_ESCALATION isn't supported in SQL 2005, so you'll need to strip it if trying to run the script on a 2005 instance. Also, since TABLE is the default, you can safely remove that line when re-running your script.

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

Also note that, in SQL 2005 before this setting was present, all locks could escalate to table level-- in other words, "TABLE" was the only setting on SQL 2005.

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

回答by Bogdan Verbenets

You can check if you need to include the LOCK_ESCALATION statement in your script by comparing this value before and after running the main part of your script:

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

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

In my case, altering table to drop or add a constraint doesn't seem to modify this value.

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

回答by Vladimir Baranov

The answer by Justin Grant explains what LOCK_ESCALATIONsetting does in general, but misses one important detail and it doesn't explain why SSMS generates the code that sets it. Especially, it looks very strange that the LOCK_ESCALATIONis set as a last statement in the script.

Justin Grant 的回答解释了LOCK_ESCALATION设置的一般作用，但遗漏了一个重要的细节，并且没有解释为什么 SSMS 会生成设置它的代码。特别是，将LOCK_ESCALATION设置为脚本中的最后一条语句看起来很奇怪。

I did few tests and here is my understanding of what is happening here.

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

Short version

精简版

The ALTER TABLEstatement that adds, drops or alters a column implicitly takes a schema modify (SCH-M) lock on the table, which has nothing to do with the LOCK_ESCALATIONsetting of a table. LOCK_ESCALATIONaffects locking behaviour during the DML statements (INSERT, UPDATE, DELETE, etc.), not during the DDL statements (ALTER). SCH-M lock is always a lock of the whole database object, table in this example.

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

This is likely where the confusion comes from.

这可能是混淆的来源。

SSMS adds the ALTER TABLE <TableName> SET (LOCK_ESCALATION = ...)statement to its script in all cases, even when it is not needed. In cases when this statement is needed, it is added to preserve the current setting of the table, not to lock the tablein some specific way during the change to the table schemathat happens in that script.

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

In other words, the table islocked with the SCH-M lock on the first ALTER TABLE ALTER COLUMNstatement while all the work of changing the table schema is done. The last ALTER TABLE SET LOCK_ESCALATIONstatement doesn't affect it. It affects only future DML statements (INSERT, UPDATE, DELETE, etc.) for that table.

换句话说，该表被锁定，就先SCH-M锁ALTER TABLE ALTER COLUMN，同时改变模式表的所有工作完成陈述。最后ALTER TABLE SET LOCK_ESCALATION一句不影响它。它只会影响未来的DML语句（INSERT，UPDATE，DELETE等），该表。

At a first glance it does look as if SET LOCK_ESCALATION = TABLEhas something to do with the fact that we are changing the whole table (we are altering its schema here), but it is misleading.

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

Long version

长版

When altering the table in some cases SSMS generates a script that re-creates the whole table and in some simpler cases (like adding or dropping a column) the script doesn't re-create the table.

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

Let's take this sample table as an example:

我们以这个示例表为例：

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

Each table has a LOCK_ESCALATIONsetting, which is set to TABLEby default. Let's change it here:

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

ALTER TABLE dbo.Test SET (LOCK_ESCALATION = DISABLE)

Now, if I try to change the Col1type in SSMS table designer, SSMS generates a script that re-creates the whole table:

现在，如果我尝试更改Col1SSMS 表设计器中的类型，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

You can see above that it sets LOCK_ESCALATIONfor the newly created table. SSMS does it to preserve the current setting of the table. SSMS generates this line, even if the current value of the setting is the default TABLEvalue. Just to be safe and explicit and prevent possible future problems if in the future this default changes, I guess. This makes sense.

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

In this example it is really needed to generate the SET LOCK_ESCALATIONstatement, because the table is created afresh and its setting has to be preserved.

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

If I try to make a simple change to the table using SSMS table designer, such as adding a new column, then SSMS generates a script that doesn't re-create the table:

如果我尝试使用 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

As you can see, it still adds the ALTER TABLE SET LOCK_ESCALATIONstatement, even though in this case it is not needed at all. The first ALTER TABLE ... ADDdoesn't change the current setting. I guess, SSMS developers decided that it is not worth the effort to try to determine in what cases this ALTER TABLE SET LOCK_ESCALATIONstatement is redundant and generate it always, just to be safe. There is no harm in adding this statement every time.

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

Once again, the table-wide LOCK_ESCALATIONsetting is irrelevant while the table schema changes via the ALTER TABLEstatement. LOCK_ESCALATIONsetting affects only the locking behaviour of DML statements, like UPDATE.

再一次，LOCK_ESCALATION当表模式通过ALTER TABLE语句更改时，表范围的设置是无关紧要的。LOCK_ESCALATION设置仅影响 DML 语句的锁定行为，例如UPDATE.

Finally, a quote from ALTER TABLE, emphasise mine:

最后，引自ALTER TABLE，强调我的：

The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end. In an ALTER TABLE…SWITCH operation, the lock is acquired on both the source and target tables. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or, on some editions of SQL Server, adding a NOT NULL column with a default value, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows.

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