主页 / dba / 问题 / 115260
Accepted
Jonesome Reinstate Monica
Asked: 2015-09-17 08:11:08 +0800 CST

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

  • 772

SQL Server 2014 标准版

在我的情况下，我有一个巨大的表（超过 1 亿行），需要：

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

我在 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 = DISABLE -- 它到底是做什么用的，它在这里适用吗？（SQL Server 文档有点薄......也许它只适用于分区表？）

b) LOCK_ESCALATION = DISABLE 会影响事务大小吗？（看起来它仍然会产生一笔巨大的交易？）

c) LOCK_ESCALATION = DISABLE 是否会应用并避免表锁？

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

sql-server t-sql

1 个回答

  1. Best Answer

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

    锁升级

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

    关于ALTER_TABLE（在备注部分）：

    锁和 ALTER TABLE

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

    和：

    添加 NOT NULL 列作为联机操作

    从 SQL Server 2012 企业版开始，当默认值为运行时常量时，添加具有默认值的 NOT NULL 列是联机操作。这意味着无论表中的行数如何，操作几乎都是立即完成的。这是因为表中已有的行在操作过程中没有更新；相反，默认值仅存储在表的元数据中，并根据需要在访问这些行的查询中查找该值。这种行为是自动的；除了 ADD COLUMN 语法之外，不需要额外的语法来实现在线操作。

    如果您使用的是企业版，您建议的更改可能是即时的，仅更改元数据（当然，对新列中数据的任何后续更改都需要实际分配、数据移动和日志记录）。

    实际上，添加此列将是一项昂贵的、长时间运行的、完全记录的、单一事务的、数据大小的操作。如果不更改列的定义，就无法“分块”此更改（可以将可为空的列添加为仅元数据更改，然后以块的形式更新）。

    • 5

相关问题