ALTER上的Server lock_escalation标志--我真的可以在没有表锁的情况下进行重构吗？

Question

Server 2014标准版

在我的情况下，我有一个巨大的表(100个million+行)，并且需要：

• 向其添加列
• 在该列中设置默认值

我在https://technet.microsoft.com/en-us/library/ms190273(v=sql.110).aspx中看到了一个新的lock_escalation选项。

例如，这是否允许我在没有表锁的情况下执行以下操作：

例如：

ALTER TABLE Protocols set (LOCK_ESCALATION = DISABLE);
go

ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO

然而，令人关切的是：

( a) LOCK_ESCALATION =禁用--它到底是做什么用的，在这里适用吗？( Server文档有点薄.也许它只适用于已分区表？)

( b) LOCK_ESCALATION =禁用是否影响事务大小？(看起来它仍然会创建一个巨大的事务？)

( c) LOCK_ESCALATION =禁用、甚至应用和避免表锁吗？

( d)有没有办法把它分块？(无法想象，但是在新的SQL版本中有这么多新的魔力.我应该问！)

Answer 1

你说文档很薄，但答案都在那里。

锁升级

您确实需要阅读整个页面，但本质上，锁升级只适用于“常规”锁(例如共享的、排他的)。更改对象结构的操作需要更严格的模式锁(例如Sch-M)，这会阻止对对象的所有并发访问被修改。因此，不需要限制较少的“普通”锁。最终，您提出的问题是没有意义的，因为ALTER TABLE不接受可能升级到分区或对象级别锁的行或页级别的锁。

关于ALTER_TABLE (在备注部分)：

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

以及：

将NULL列添加为从Server 2012企业版开始的联机操作，如果默认值是运行时常量，则添加具有默认值的NULL列是联机操作。这意味着无论表中的行数如何，操作几乎是瞬间完成的。这是因为在操作期间没有更新表中的现有行；相反，默认值只存储在表的元数据中，并且在访问这些行的查询中会根据需要查找该值。此行为是自动的；除了ADD列语法之外，不需要额外的语法来实现联机操作。

如果您正在使用Enterprise，您建议的更改可能是即时的元数据更改(当然，新列中对数据的任何后续更改都需要真正的分配、数据移动和日志记录)。

实际上，添加本专栏将是一个昂贵的、长期运行的、完全日志记录的、单事务操作、数据大小操作。如果不更改列的定义，就无法进行“块”更改(可以将可空列添加为元数据，然后以块形式更新)。