在SQL Server 2008中，添加列是否会锁定表？

Question

我希望在大约1200万条记录的表上运行以下操作。

ALTER TABLE t1
ADD c1 int NULL;
ALTER TABLE t2
ADD c2 bit NOT NULL
DEFAULT(0);

我已经在测试环境中完成了这个操作，时间看起来还不错，但在生产环境中进行之前，我想知道在创建新列时表格锁定是如何工作的（特别是当指定默认值时）。那么，有人知道吗？整个表格会被锁定，还是在插入默认值期间逐行锁定？或者会发生完全不同的事情吗？

- Eli

---

https://dev59.com/dnI-5IYBdhLWcg3wu7FU - Jacob

Answer 1

在 SQL Server 11（代号：Denali）之前，使用“非空列添加默认值”的方法会在后台运行更新操作以填充新的默认值。因此，在更新 1200 万行的过程中，此方法将锁定表。但在 SQL Server 11 中，情况已经改变，该列在线添加且不进行更新操作，详情请参阅SQL Server 11 中在线添加带有非 NULL 值列。

无论是在 SQL Server 11 中还是之前的版本中，修改字段定义时都会在表上获得 Sch-M 锁。这个锁与任何其他可能的访问（包括脏读）不兼容。两个版本的区别在于锁的持续时间：在 SQL Server 11 之前，这个锁需要保持一段时间来执行数据操作（更新 1200 万行）。而在 SQL Server 11 中，这个锁只需短暂保持即可。在 SQL Server 11 之前对行的更新不需要获得行锁，因为表上的 Sch-M 锁可以保证不存在任何单个行的冲突。

- Remus Rusanu

---

虽然我猜Denali现在对他没什么帮助，但是你提到新版本的那个小提示让我给你加上一个赞。 - E.J. Brennan

Answer 2

是的，它将锁定表格。

一个表格作为一个整体，具有单一的模式（列集合，带有相关类型）。因此，要更新表格的定义，至少需要一个模式锁。

---

试着想想相反情况下会怎样——如果每个行都被单独更新，任何并行查询将如何工作（特别是涉及新列的查询）？

---

默认值仅在INSERT和DDL语句期间有用——因此，如果您为1000万行指定了一个新的默认值，则必须将该默认值应用于所有这些行。

- Damien_The_Unbeliever

---

默认值仅在插入和DDL语句期间有用--那么ON UPDATE SET DEFAULT和ON DELETE SET DEFAULT的引用操作呢？ ;) - onedaywhen

我明白在更新表的定义时会锁定，但是在为每一行更新默认值时，这个锁会保持多久？更新表定义非常快，可能只需要2秒钟。然而，为每一行更新默认值则远远不如此快，因此如果有性能问题，那么就会出现在这里。 - Eli

如果该列将被设置为非空，您需要添加一个默认值才能进行添加。 - HLGEM

Answer 3

是的，它会锁定。

DDL语句会发出模式锁(请参见此链接)，这将防止访问该表直到操作完成。

实际上没有绕过这个问题的方法，如果您考虑一下就会明白。SQL需要知道表中有多少字段，在此操作期间，某些行将比其他行具有更多的字段。

另一种选择是创建一个包含正确字段的新表，插入数据，然后重命名表以交换它们。

- JNK

Answer 4

我还没有阅读添加列时锁定机制的工作原理，但我几乎可以确定逐行操作是不可能的。

当您在 SQL Server Manager 中使用拖放进行此类操作时，请注意（我知道您在这里没有这样做，但这是一个公共论坛），因为某些更改是具有破坏性的（幸运的是，SQL Server 2008，至少是 R2，在这里更安全，因为它会告诉您“不能这样做”，而不仅仅是这样做）。

您可以在单个语句中运行两个列添加操作，并减少操作次数。

- Gregory A Beamer